Forum Discussion

sivarajan21's avatar
sivarajan21
Post Prodigy
2 years ago

Creating Dynamic Dax measure for a virtual table to filter table instead of calculated table(static)

Hi,

 

I have a below table in power bi:

CreatedOnDataIdDataset IdNameFloor Area
28/10/2023 01:177057561Site 12342
28/10/2023 01:177057532Site 276829
28/10/2022 01:177057522Site 275775
28/10/2021 01:177057512Site 274721
28/10/2023 01:177057503Site 373667
28/10/2022 01:177057493Site 372613
28/10/2021 01:177057483Site 371559

 

My requirement is to create a virtual table using a dax measure(not calculated table) to filter this table by the first records for each dataset id that are less than or equal to filtered date(CreatedOn).

 

For example,if the above table is filtered for below date:

Parameter/Slicer

01/01/2023

 

Then the resulting virtual table from the dax measure is as below:

CreatedOnDataIdDataset IdNameFloor Area
28/10/2022 01:177057522Site 275775
28/10/2022 01:177057493Site 372613

 

The resulting virtual table ignored 705751 &705748 DataId because our dax measure should select only the first records from the multiple Dataset Id that exist

 

PFA screenshot of the above tables and result:

 

PFA sample file in below link

Virtual_Dax.pbix

 

Thanks in advance! 

marcorusso Ahmedx Greg_Deckler Ashish_Mathur amitchandak