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.
In the below dataset I have a column that ranks the order/position of a value within a grouping. I'm trying to find a dax function that will allow me to return the nth largest value based within a given [Address] using the [Rank] value to identify what [SwapRank] to return. Below is a sample data set that includes what the returned values would be including comments/notes to clarify my thoughts.
Address | Suite | Rank | SwapRank | Thoughts/Notes |
Address 1 | 100 | 1 | 5 | <- Returns nth largest "rank" based on "address" criteria for each address in dataset |
Address 1 | 120 | 1 | 5 | |
Address 1 | 200 | 2 | 4 | |
Address 1 | 300 | 3 | 3 | <- Can it somehow be simplified by utilizing the "Rank" as the nth heighest position, thus it would return the swapped value? |
Address 1 | 320 | 3 | 3 | |
Address 1 | 400 | 4 | 2 | |
Address 1 | 500 | 5 | 1 | |
Address 2 | 100 | 1 | 4 | Returns "4" because it is the "1st" largest number based on the Address 2 criteria |
Address 2 | 150 | 1 | 4 | |
Address 2 | 200 | 2 | 3 | Returns "3" because it is the "2nd" largest number based on the Address 2 criteria |
Address 2 | 300 | 3 | 2 | |
Address 2 | 330 | 3 | 2 | |
Address 2 | 400 | 4 | 1 |
Solved! Go to Solution.
Hi @CREsearch ,
Try this:
SwapRank 2 =
RANKX (
FILTER ( 'Table', 'Table'[Address] = EARLIER ( 'Table'[Address] ) ),
'Table'[Rank],
,
DESC,
DENSE
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Try these measure
Rank value = MIN(Data[Rank])
Reverse rank = RANKX(ALLEXCEPT(Data,Data[Address]),[Rank value],,DESC,dense)
To your visual drag the second measure.
Hope this helps.
Hi @CREsearch ,
If so, just try this:
Create a measure:
SwapRank =
RANKX (
ALLEXCEPT ( 'Table', 'Table'[Address] ),
CALCULATE ( MAX ( 'Table'[Rank] ) ),
,
DESC,
DENSE
)
Best Regards
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I'm getting a circular dependency error for some reason. Note that it has to be a custom column I believe and not a measure as I need to use it for an axis in a bar chart.
Hi @CREsearch ,
Try this:
SwapRank 2 =
RANKX (
FILTER ( 'Table', 'Table'[Address] = EARLIER ( 'Table'[Address] ) ),
'Table'[Rank],
,
DESC,
DENSE
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes, works like a charm! Thank you!
Hi,
If my reply helped, please mark it as Answer.
Hi @CREsearch ,
How do you calculate the “Rank” column? Is the “SwapRank” column a reverse sort of the “Rank” column?
Best Regards,
Icey
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |