Forum Discussion

CREsearch's avatar
CREsearch
Frequent Visitor
6 years ago
Solved

Return nth ranked value based on criteria

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.

 

AddressSuiteRankSwapRankThoughts/Notes
Address 110015<- Returns nth largest "rank" based on "address" criteria for each address in dataset
Address 112015
Address 120024
Address 130033<- Can it somehow be simplified by utilizing the "Rank" as the nth heighest position, thus it would return the swapped value?
Address 132033
Address 140042
Address 150051 
Address 210014Returns "4" because it is the "1st" largest number based on the Address 2 criteria
Address 215014 
Address 220023Returns "3" because it is the "2nd" largest number based on the Address 2 criteria
Address 230032 
Address 233032 
Address 240041 
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

     

8 Replies

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi CREsearch ,

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

     

    Best Regards,

    Icey

    • CREsearch's avatar
      CREsearch
      Frequent Visitor
      6 years ago
      The Rank field is provided. And yes - it’s essentially a reverse sort in a way!
  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      CREsearch
      Frequent Visitor
      6 years ago

      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's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        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.

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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.