Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I have a problem. I want to show the latest 3 dates only for status "Completed" as illustrated bellow. Tried rankx, topN value and max values approaches, but didn't work out. Basically I need to return the 3 most recent dates just for "Completed" along with any number of results for "Overdue" and "Future" (e.g. regardless of the number of their dates for the latter). A measure must be generated. Any solutions will be highly appreciated.
Thanks, KGeorge
Solved! Go to Solution.
A showcase of powerful Excel formula,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
A showcase of powerful Excel formula,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hello , thank you for you solution. As I specified the main goal is to have a measure with the latest 3 dates for completed; no filter / no ranks ( having anydates) for overdo and future status in one cumulative table. However, I believe your proposition is approaching the final solution of the issue.
Regards,
K.
Hi @Anonymous
please try
FilterMeasure =
VAR CurrentDate =
SELECTEDVALUE ( 'Table'[Date] )
VAR CurrentStatus =
SELECTEDVALUE ( 'Table'[Status] )
VAR T1 =
CALCULATETABLE ( VALUES ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR T2 =
TOPN ( 3, T1, 'Table'[Date], ASC )
RETURN
IF ( CurrentStatus = "Completed", IF ( CurrentDate IN T2, 1 ), 1 )
place this measure in the filter pane of the visual and select "is not blank" then spply the filter
try to create a new table with the code below:
Thank you very much for the response, I haven't specified that I need a measure , where the top 3 "Completed" will be shown along with the other status "Overdue","Future", regardless of both numbers of results.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |