Rankx Quirks

[User5231]

Sum by Category Filter Method = CALCULATE(sum('Table'[Time]), filter(ALLSELECTED('Table'),'Table'[Sub Category] = max('Table'[Sub Category])))

Sum by Sub Category Allexcept = calculate(sum('Table'[Time]), ALLEXCEPT('Table','Table'[Sub Category]))

Rank1 =
rankx(ALLSELECTED('Table'), [Sum by Category Filter Method],,DESC,Dense) //could use either measure above and works

Rank 2 = //Does Not Work
Var Sumof = calculate(sum('Table'[Time]), filter(allselected('table'), 'Table'[Sub Category] = max('Table'[Sub Category])))
Var Sumof1 = calculate(sum('Table'[Time]), ALLEXCEPT('Table','Table'[Sub Category]))
Return
rankx(ALLSELECTED('Table'[Sub Category]), Sumof1,,DESC,Dense) //sumof or sumof1 both do not work

Rank3 = rankx(ALLSELECTED('Table'), CALCULATE(sum('Table'[Time]), ALLEXCEPT('Table','Table'[Sub Category])),,DESC,Dense) /// Works

Rank4 = rankx(ALLSELECTED('Table'[Sub Category]), CALCULATE(sum('Table'[Time]), ALLEXCEPT('Table','Table'[Sub Category])),,DESC,Skip) //Works

Rank5 = rankx(ALLSELECTED('Table'[Sub Category]), CALCULATE(sum('Table'[Time]), filter(ALLSELECTED('Table'), 'Table'[Sub Category] = max('Table'[Sub Category]))),,DESC,Skip) //Doesn't Work ---- this one really perplexes me

Rank6 =
Var Table1 = SUMMARIZE(ALLSELECTED('Table'),'Table'[Sub Category], "sum", CALCULATE(sum('Table'[Time]), ALLEXCEPT('Table','Table'[Sub Category])))
Var RankedTable = ADDCOLUMNS(Table1, "Rank", rankx(Table1, [sum],,DESC))
Return
maxx(filter(RankedTable, [Sub Category] = max('Table'[Sub Category])), [Rank]) //Works --- convoluted, but this one is actually easier to apply to different uses.

Can anyone try and explain what is happenning with these RankX iterations that certian ones work and others do not. I have a simple table that I want to display the Tim summed by Sub Category, regardless of ID displayed, and then the respective rankx at the sub category level.

Im assuming there is some context I am missing to explain why one method works and another doesn't.

Thanks

[tamerj1 · Accepted solution]

Hi @User5231 
I think it is the context transition. Context transition does not apply to variables as they are evaluated once. This is why rank2 does not work.
I agreed with you on rank5 but if you think about it when refering

[Sum by Category Filter Method]

it is actually translated to 

CALCULATE (
    CALCULATE (
        SUM ( 'Table'[Time] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Sub Category] = MAX ( 'Table'[Sub Category] )
        )
    )
)

 As the engine wraps any measure referenced in any kind of calculation with CALCULATE. 

Just a thought. you may try and confirm.

[User5231]

Thanks! I think you explained it well enough... and sure enough wrapping that expression for Rank5 in a calculate does indeed make it work. That is interesting though that the Allexcept version does not need that. Any ideas on why that is the case?

[tamerj1]

Why would the ALLEXCEPT be an exception?

[User5231]

DAX can be funky

[tamerj1]

Indeed😅