Forum Discussion
Creating Dynamic Dax measure for a virtual table to filter table instead of calculated table(static)
- Nov 30, 2023
In the sample CreatedOn is returned as text, thus I added Createddate column as DATEVALUE() from it
Bar date is added as measure
Depends on your actual model, you may take it as SELECTEDVALUE() on Date table, whatever.
Filtered table is calculated as
Filtered Table = VAR belowDate = FILTER ( Main, Main[CreatedDate] < [pDate] ) VAR setRowId = ADDCOLUMNS ( Main, "Id", ROWNUMBER ( belowdate, ORDERBY ( Main[DataId], DESC ), PARTITIONBY ( Main[Dataset Id] ) ) ) VAR firstRows = FILTER ( setRowId, [Id] = 1 ) VAR result = SELECTCOLUMNS ( firstRows, "CreatedDate", Main[CreatedDate], "DataId", Main[DataId], "Dataset Id", Main[Dataset Id], "Name", Main[Name], "Floor Area", Main[Floor Area] ) RETURN resultResulting table is
You may return firstRows variables if don't care about extra Id column.
In the sample CreatedOn is returned as text, thus I added Createddate column as DATEVALUE() from it
Bar date is added as measure
Depends on your actual model, you may take it as SELECTEDVALUE() on Date table, whatever.
Filtered table is calculated as
Filtered Table =
VAR belowDate =
FILTER ( Main, Main[CreatedDate] < [pDate] )
VAR setRowId =
ADDCOLUMNS (
Main,
"Id",
ROWNUMBER (
belowdate,
ORDERBY ( Main[DataId], DESC ),
PARTITIONBY ( Main[Dataset Id] )
)
)
VAR firstRows =
FILTER ( setRowId, [Id] = 1 )
VAR result =
SELECTCOLUMNS (
firstRows,
"CreatedDate", Main[CreatedDate],
"DataId", Main[DataId],
"Dataset Id", Main[Dataset Id],
"Name", Main[Name],
"Floor Area", Main[Floor Area]
)
RETURN
result
Resulting table is
You may return firstRows variables if don't care about extra Id column.
Hi SergeiBaklan
Thanks for your prompt response!
I have got the solution for this and thanks for your patience![]()
Your solution was the backbone of what i have found! many thanks!
I will accept your solution as the best response and close this query
Please let me know if i can do anything for you!
Thanks!