Forum Discussion
sivarajan21
2 years agoPost Prodigy
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:
| 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
Thanks in advance!
marcorusso Ahmedx Greg_Deckler Ashish_Mathur amitchandak
1 Reply
- Ashish_MathurSuper User