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 found Rankx can be completed if used as a measure. Any help will be greatly appreciated!
I have built a Date table and one to many relationship with the Test Table on the “Date” Column ( Please see the screenshot below).
I would like to achieve the following:
Date | Link Code | Volumes | PM Volumes | Rank | PM Rank |
01/04/2021 | A | 18 |
| 3 | 1 |
01/04/2021 | B | 62 |
| 1 | 1 |
01/04/2021 | C | 19 |
| 2 | 1 |
01/05/2021 | A | 12 | 19 | 3 | 2 |
01/05/2021 | B | 103 | 12 | 1 | 3 |
01/05/2021 | C | 15 | 103 | 2 | 1 |
01/06/2021 | A | 33 | 15 | 1 | 3 |
01/06/2021 | B | 18 | 33 | 3 | 1 |
01/06/2021 | C | 29 | 18 | 2 | 2 |
However, this is what returned with my Dax below
Date | Link Code | Volumes | PM Volumes | Rank | PM Rank |
01/04/2021 | A | 18 |
| 3 | 1 |
01/04/2021 | B | 62 |
| 1 | 1 |
01/04/2021 | C | 19 |
| 2 | 1 |
01/05/2021 | A | 12 | 19 | 3 | 1 |
01/05/2021 | B | 103 | 12 | 1 | 1 |
01/05/2021 | C | 15 | 103 | 2 | 1 |
01/06/2021 | A | 33 | 15 | 1 | 1 |
01/06/2021 | B | 18 | 33 | 3 | 1 |
01/06/2021 | C | 29 | 18 | 2 | 1 |
Rank =
RANKX(filter(all('Test table'[Date],'Test table'[Link Code]),'Test table'[Date]=max('Test table'[Date])),CALCULATE(sum('Test table'[ Volumes])),,DESC)
PM Volumnes = CALCULATE(sum('Test table'[ Volumes]),dateadd('Dim Date 2'[Date],-1,month))
PM rank =
rankx(filter(all('Test table'[Date],'Test table'[Link Code]),'Test table'[Date]=max('Test table'[Date])),[PM Volumnes],,desc)
May I ask where I did wrong? Why PM Rank doesn’t return as expected?
Thank you.
Jing
Solved! Go to Solution.
Hi @Anonymous ,
Please try this code:
pm rank =
RANKX (
CALCULATETABLE (
ALLSELECTED ( 'Table'[Link Code] ),
ALLEXCEPT ( 'Dim Date', 'Dim Date'[Date].[Day] )
),
[PM Volumes],
,
DESC
)
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous with time intelligence functions you need to use date table in visual too
try with these changes and date table in visual too
rankx(filter(all('Test table'[Date],'Test table'[Link Code]),'Test table'[Date]=max('Test table'[Date])),[PM Volumnes],,desc)
Hi Amichandak
Thank you for your help.
I am using the Date column from the Date table ('Dim Date 2'[Date]) in visual. PM Rank just doesn't return as expected.
The rankx Dax you provided is the same with mine?
Thank you.
Jing
@Anonymous , Please use in the rank too
Hi Amitchandak
Thank you for your reply.
I thought about using the Date column from the Dim Date table ('Dim Date 2'[Date])before. Below is the Dax I tried.
rankx(filter(all('Dim Date 2'[Date],'Test table'[Link Code]),'Dim Date 2'[Date]=max('Dim Date 2'[Date])),[PM Volumnes],,desc)
However, becaues the All function can only be used if the columns are from the same table, the fomular above returned error.
I also tried rankx(filter(all('Dim Date 2'[Date],'Test table'[Link Code]),'Test table'[Date]=max('Test table'[Date])),,desc), but it still didn't work either.
Thank you.
Jing
Hi @Anonymous ,
Please try this code:
pm rank =
RANKX (
CALCULATETABLE (
ALLSELECTED ( 'Table'[Link Code] ),
ALLEXCEPT ( 'Dim Date', 'Dim Date'[Date].[Day] )
),
[PM Volumes],
,
DESC
)
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello
Thank you so much for your help! It works well.
However, I don't really understand the code. Would you mind explaining the following code?
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 |
---|---|
100 | |
99 | |
76 | |
66 | |
61 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |