Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear all.
I have a problem and did not find something similar.
I have a table with IDs:
ID | State | Date |
223 | 20 | 06.05.2018 |
223 | 24 | 08.05.2018 |
223 | 26 | 11.05.2018 |
223 | 66 | 12.05.2018 |
225 | 20 | 07.05.2018 |
225 | 24 | 09.05.2018 |
225 | 26 | 14.05.2018 |
225 | 66 | 15.05.2018 |
301 | 20 | 09.05.2018 |
301 | 24 | 10.05.2018 |
301 | 26 | 12.05.2018 |
301 | 31 | 13.05.2018 |
301 | 66 | 15.05.2018 |
What I want to have is that I can select a date (e.q. via slicer) and for each ID I get the state (for that specific date or before). That means, selecting the date 13.05.2019 should result in:
ID | State |
223 | 66 |
225 | 24 |
301 | 31 |
Is there any possiblity to realize that?
Thanks a lot in advance and
warm Regards
Martin
Solved! Go to Solution.
Create a calendar table. Then we can use this formula
Please see attached file as well
Measure = MAXX ( TOPN ( 1, FILTER ( table1, [Date] <= SELECTEDVALUE ( 'Calendar'[Date] ) ), Table1[Date], DESC ), [State] )
Create a calendar table. Then we can use this formula
Please see attached file as well
Measure = MAXX ( TOPN ( 1, FILTER ( table1, [Date] <= SELECTEDVALUE ( 'Calendar'[Date] ) ), Table1[Date], DESC ), [State] )
Hi Zubair_Muhammad.
Thanks a lot for your fast solution !! That works perfectly.
May one additional question. The table works perfect. It is also possible to use that measure in a diagramm.
In that example I would like to have for a certain date the number of worflow IDs depending on the measure.
I did try that but I cannot summarize the measure. I thinkt that is possible but that topic is really new for me.
Best regards,
Martin
User | Count |
---|---|
101 | |
90 | |
78 | |
71 | |
68 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |