topic Re: Rankx Quirks in DAX Commands and Tips

Rankx Quirks

User5231 — Wed, 06 Apr 2022 17:12:53 GMT

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

Re: Rankx Quirks

tamerj1 — Thu, 07 Apr 2022 03:44:55 GMT

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.

Re: Rankx Quirks

User5231 — Thu, 07 Apr 2022 14:10:37 GMT

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?

Re: Rankx Quirks

tamerj1 — Thu, 07 Apr 2022 16:11:32 GMT

Why would the ALLEXCEPT be an exception?

Re: Rankx Quirks

User5231 — Thu, 07 Apr 2022 16:50:18 GMT

DAX can be funky

Re: Rankx Quirks

tamerj1 — Thu, 07 Apr 2022 17:19:14 GMT

Indeed😅