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.
Hi friends,
I have a table for ranking position of runners for races on different dates. I want to create a table to get the ranking position of a runner for his last three races. For that, I need to first find out these three dates i.e. the last date, last but one date and last but two dates. Could you please advise how can I do that?
Please check out the link
https://community.powerbi.com/t5/Desktop/DAX-Ranking-or-Indexing-Dates/td-p/40776
This should give you the answer.
Using the same I created two calculated columns
1. YearMonth = VALUE ( YEAR ( 'Table1'[Date] ) * 100 + MONTH ( 'Table1'[Date] ) )
2. RankbyDate = RANKX (
FILTER (
'Table1',
EARLIER ( Table1[Name] ) = Table1[Name]
),
Table1[YearMonth],
,
DESC,
DENSE
)
In the martix table, set Rows as Name, Columns as RankbyDate, Values as RaceDate, RaceRank.
Set the visual filter for RankByDate is less than 4.
Sample output
If it works please accept it as solution and also give KUDOS.
Cheers
CheenuSing
Hi,
Thanks for your quick suggestion. Based on that I created 1st column as 'Month&year'. I created 2nd column for ranking using following function:
DateRank = RANKX(filter('Result', EARLIER('Result'[Runner])='Res'[Runner]), 'Result'[Month&year],0)
But it is giving same rank for all dates to each runner. Is it because the [Runner] is text column?
What can be a solution for this?
Regards,
Kamal
Please share your data model and data or the pbix in Google drive / One Drive and the paste the link here to check.
Cheers
CheenuSing
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |