Return nth ranked value based on criteria

[CREsearch]

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

[Ashish_Mathur]

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.

[Icey]

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.

[CREsearch]

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. 

[Icey · Accepted 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.

[CREsearch]

Yes, works like a charm! Thank you!

[Ashish_Mathur]

Hi,

If my reply helped, please mark it as Answer.

[Icey]

Hi @CREsearch ,

How do you calculate the “Rank” column? Is the “SwapRank” column a reverse sort of the “Rank” column?

Best Regards,

Icey

[CREsearch]

The Rank field is provided. And yes - it’s essentially a reverse sort in a way!