RANKX Help

[Anonymous]

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...

[v-yalanwu-msft]

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.

[v-yalanwu-msft]

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.

[Ashish_Mathur]

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

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

[Anonymous]

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]

@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. 

[Greg_Deckler]

@Anonymous Probably need an aggregation function around your [Cycle], like MAX([Cycle]. This is a fun read:

To *Bleep* with RANKX! - Microsoft Power BI Community

[Anonymous]

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

[Greg_Deckler]

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