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.
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:
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...
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.
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.
Additionally, the first input of the RANK() should be with the ALL or ALLELECTED function
BC Rank = RANKX(ALL(vCallBackBC),[Cycle],,DESC)
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.
@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.
@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
I'm not sure which aggregation to use. Starting to think i'm not cut out for DAX...
@Anonymous Doesn't matter the aggregation, I tend to use MAX.
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 |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |