Forum Discussion
Help with Rank
Hi,
I have two ways of writing a measure which seem the same but both give different outputs and I do not know why.
The measures are below. Selector Rank 2 calculates the rank as I would like to see it but it relies on having a nested measure; Selector Valid Date. Selector Rank instead attempts to be self-contained by taking the logic for Selector Valid Date and putting it within a variable. The problem is that this results in a different output and I do not know why. Output is also below.
Measures:
| Date | Selector Valid Date | Selector Rank | Selector Rank 2 |
| 01/11/2020 | 0 | 1 | 1 |
| 01/10/2020 | 0 | 1 | 1 |
| 01/09/2020 | 0 | 1 | 1 |
| 01/08/2020 | 0 | 1 | 1 |
| 01/07/2020 | 1 | 5 | 1 |
| 01/06/2020 | 1 | 6 | 2 |
| 01/05/2020 | 1 | 7 | 3 |
| 01/04/2020 | 1 | 8 | 4 |
| 01/03/2020 | 1 | 9 | 5 |
| 01/02/2020 | 1 | 10 | 6 |
| 01/01/2020 | 1 | 11 | 7 |
| 01/12/2019 | 1 | 12 | 8 |
| 01/11/2019 | 1 | 13 | 9 |
Hope you can help.
Hi gclements,
A variable with standalone calculations expression usually only calculates the main row contents level. (they defined by current value/chart category)
The expression which you nested another measure formulas with rankx may be calculated on the parameter table of rankx functions instead of main row contents.For example:
Raw contents have 3 value, the measure should get the aggregate of 3 values; rankx parameter table has 5 value, nested measure formula calculate on the filtered 5 values instead of the original row content)
Optimizing DAX expressions involving multiple measures
Row Context and Filter Context in DAX
Regards,Xiaoxin Sheng
3 Replies
Hi gclements,
It seems like you are using aggregate function max to get the current date and calculate with your measure expression.
Aggregate functions are possible to extract the current value, but they not always work to extract the current value. When you processing with the row contents that summarize with multiple records, they should work with raw function effect instead of directly return the current value. (e.g. if your row content has one record, it returns the current value; if your row content has multiple records, it will return the aggregate value based on raw function; max function will return the maximum value, min function will return the minimum value, etc..)
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT
Regards,
Xiaoxin Sheng
Thanks for the response.
I do not see how this explains the different results as both Selector Rank and Selector Rank 2 use the MAX function in the same way. The only difference between the two is that once of them calls a measure whereas the other defines the measure calcuation within a variable.
Hi gclements,
A variable with standalone calculations expression usually only calculates the main row contents level. (they defined by current value/chart category)
The expression which you nested another measure formulas with rankx may be calculated on the parameter table of rankx functions instead of main row contents.For example:
Raw contents have 3 value, the measure should get the aggregate of 3 values; rankx parameter table has 5 value, nested measure formula calculate on the filtered 5 values instead of the original row content)
Optimizing DAX expressions involving multiple measures
Row Context and Filter Context in DAX
Regards,Xiaoxin Sheng
