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

[sivarajan21]

Hi,

I have a below table in power bi:

CreatedOn	DataId	Dataset Id	Name	Floor Area
28/10/2023 01:17	705756	1	Site 1	2342
28/10/2023 01:17	705753	2	Site 2	76829
28/10/2022 01:17	705752	2	Site 2	75775
28/10/2021 01:17	705751	2	Site 2	74721
28/10/2023 01:17	705750	3	Site 3	73667
28/10/2022 01:17	705749	3	Site 3	72613
28/10/2021 01:17	705748	3	Site 3	71559

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:

CreatedOn	DataId	Dataset Id	Name	Floor Area
28/10/2022 01:17	705752	2	Site 2	75775
28/10/2022 01:17	705749	3	Site 3	72613

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  

[Ashish_Mathur]

Hi,

PBI file attached.