Help with Rank

[gclements]

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:

Selector Valid Date = IF(MAX('Dim Billing Period'[YearMonth]) <= 202007 && 'Fact Charge'[Charge Quantity] > 0, 1, 0)

Selector Rank =

VAR vValidDates = IF(MAX('Dim Billing Period'[YearMonth]) <= 202007 && 'Fact Charge'[Charge Quantity] > 0, 1, 0)

RETURN

RANKX(FILTER(ALLSELECTED('Dim Billing Period'[Date]), vValidDates = 1), CALCULATE(MAX('Dim Billing Period'[Date])), , DESC)

Selector Rank 2 = RANKX(FILTER(ALLSELECTED('Dim Billing Period'[Date]), [Selector Valid Date] = 1), CALCULATE(MAX('Dim Billing Period'[Date])), , DESC)

Output:

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.

[Anonymous]

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 

How CALCULATE works in DAX 

Regards,

Xiaoxin Sheng

[gclements]

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.

[Anonymous · Accepted solution]

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