Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

RANKX Help

I am trying to create a measure to create a ranked column for a table.

 

Table Name: vCallBackBC (See image below for columns)

 

The rank will be determined in order by the data in the following columns:

1st) Cycle

2nd) Hire Date

3rd) Name

 

Here is the DAX I have thus far:

BC Rank = RANKX(vCallBackBC,[Cycle],,DESC)
 
( I know this is only looking at one of the three columns I need to sort by, but I wanted to at least sort by [Cycle] first to make sure it works before adding the other columns.) 

 

BUT I keep getting an error: "A single value for column 'Cycle' in table 'vCallBackBC' cannot be determind. This can happen when a meansure formula refers to a column that contains many value without specifying an aggregation such as min, max, count, or sum to get a single result

 

 

Any clues? I'm sure my DAX is missing something...

Need Help

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I'm not sure which aggregation to use. Starting to think i'm not cut out for DAX...

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        5 years ago

        Anonymous Doesn't matter the aggregation, I tend to use MAX.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    There are only 3 values in the [Cycle] column, 16, 17, and 18,...that's the main sorting criteria, then using Hire Date as a tiebreaker, Name is the final tiebreaker. I hope that helps.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      Anonymous That's fantastic, but if you reference a column in a measure, you still need an aggregator, even if you only have a single value in that column or even just a single row in the table. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Additionally, the first input of the RANK() should be with the ALL or ALLELECTED function

    BC Rank = RANKX(ALL(vCallBackBC),[Cycle],,DESC)

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi, Anonymous ;

    You could modify the mesaure as follows:

    Measure = RANKX(ALL(vCallBackBC),CALCULATE(MAX([Cycle])),,DESC)

    If you want to base it on three columns, try it:

    BC Rank =
RANKX (
    ALL ( vCallBackBC ),
    RANKX ( ALL ( vCallBackBC ), CALCULATE ( MAX ( [Cycle] ) ),, DESC ) * 10000
        + RANKX ( ALL ( vCallBackBC ), CALCULATE ( MAX ( [Hire Date] ) ),, DESC ) * 100
        + RANKX ( ALL ( vCallBackBC ), CALCULATE ( MAX ( [Name] ) ),, DESC ),
    ,
    ASC
)

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi, Anonymous ;

    Is the above answer helpful to you? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.