topic Re: Ranking with multiple rows and drilldown in DAX Commands and Tips

Ranking with multiple rows and drilldown

VBLOT — Fri, 03 Nov 2023 16:51:54 GMT

Hello all,

I tried for many hours and differents ways but I can't manage to rank my matrix.

I have two lines in my Matrix :

As you can see in the screen bellow, I want to rank REF (higher level) separately from Line Designation (sublevel)

With ISINSCOPE function, Im able to see that to calculate REF ranking I need to have REFInScope = True && LineInScope = False

But with that in mind I somehow can't manage to do the right calculation (Ranking works when I have only 1 line)

See one of the DAX measure I've done :

Rank = VAR IsLineInScope = ISINSCOPE('Invoices List'[Line Designation]) VAR IsREFInScope = ISINSCOPE('Invoices List'[REF]) RETURN IF( IsLineInScope && IsREFInScope, RANKX(ALLSELECTED('Invoices List'[Line Designation]), [Total Samples collected]), IF( NOT IsLineInScope && IsREFInScope, RANKX(ALLSELECTED('Invoices List'[REF]), [Total Samples collected]), BLANK() ) )

Can you please help me? I've tried like 50 differents measures, watch videos and nothing works. I also tried with RANK and not RANKX....

Re: Ranking with multiple rows and drilldown

OwenAuger — Sat, 04 Nov 2023 00:27:02 GMT

Hi VBLOT

Your logic is essentially correct!

In fact your measure works fine in a test PBIX I created, as long as [Total Samples Collected] always has positive values (example in attached PBIX).

However, since it wasn't working correctly for you, it's possible that there is a complication in your model. Are there any sort-by columns defined by any chance?

A more robust way I would suggest writing this measure is using the RANK function, since we can specify partitioning.

Rank = VAR IsLineInScope = ISINSCOPE ( 'Invoices List'[Line Designation] ) VAR IsREFInScope = ISINSCOPE ( 'Invoices List'[REF] ) VAR Result = SWITCH ( TRUE ( ), IsLineInScope && IsREFInScope, RANK ( DENSE, CALCULATETABLE ( SUMMARIZE ( 'Invoices List', 'Invoices List'[Line Designation], 'Invoices List'[REF] ), ALLSELECTED ( ) ), ORDERBY ( [Total Samples Collected], DESC ), PARTITIONBY ( 'Invoices List'[REF] ) ), NOT IsLineInScope && IsREFInScope, RANK ( DENSE, CALCULATETABLE ( SUMMARIZE ( 'Invoices List', 'Invoices List'[REF] ), ALLSELECTED ( ) ), ORDERBY ( [Total Samples Collected], DESC ) ) ) RETURN Result

PBIX attached for reference

Rank Original is your posted measure.
Rank is updated measures.

Regards

Re: Ranking with multiple rows and drilldown

VBLOT — Mon, 06 Nov 2023 08:27:44 GMT

Hello OwenAuger !

Thanks for your answer ! It is indeed working in the PBI you linked but sadly it doesn't work on mine.

I've checked and I don't have any sort-by columns in Invoices List, the code in entering conditions as expected but somehow, the rank part in the REF scope isn't working and I still have 1 everywhere...

I also know that I have blanks in my Nb samples collected. But it shouldn't have any impacts as I filtered the result to have only those greater than 0 and in my Total Samples collected measure, I make sure that I don't consider BLANK().

Total Samples collected = CALCULATE( SUM('Invoices List'[Nb samples collected]), FILTER('Invoices List','Invoices List'[Nb samples collected] <> BLANK()) )

Do you have another idea ?

Re: Ranking with multiple rows and drilldown

OwenAuger — Mon, 06 Nov 2023 09:59:08 GMT

Hi again VBLOT

Could you share a sanitised PBIX file that exhibits the problem? Even filtered down to a couple of REF values.

(share a link to Google Drive / OneDrive etc).

From what you've posted, it should be working, but there must be some particular feature of your data model that is causing this.

Re: Ranking with multiple rows and drilldown

VBLOT — Mon, 06 Nov 2023 10:16:28 GMT

Hi again OwenAuger,

Please find a sanitised file here :
https://norishare.com/P9TDkeS9yJF

Thanks for your help.

Re: Ranking with multiple rows and drilldown

OwenAuger — Tue, 07 Nov 2023 10:03:36 GMT

Hi again VBLOT

Thanks so much for the file! That made it a lot easier to debug.

It seems that the problem actually relates to how the visual level filter "Sum of Nb samples collected > 0" interacts with the ALLSELECTED () modifier used to produce the table for ranking.

A safer way to write the measure is below (see attached PBIX). Actually in their Whitepaper, SQLBI recommend this general approach of adding column(s) to the "relation" argument of window functions.

I might have to get back to you with an explanation of why it works when I've analyzed further.

Rank FIX = VAR IsLineInScope = ISINSCOPE ( 'Invoices List'[Line Designation] ) VAR IsREFInScope = ISINSCOPE ( 'Invoices List'[REF] ) VAR Result = SWITCH ( TRUE ( ), IsLineInScope && IsREFInScope, RANK ( DENSE, CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( 'Invoices List', 'Invoices List'[Line Designation], 'Invoices List'[REF] ), "@TotalSamples", [Total Samples collected] ), ALLSELECTED () ), ORDERBY ( [@TotalSamples], DESC ), , PARTITIONBY('Invoices List'[REF]) ), NOT IsLineInScope && IsREFInScope, RANK ( DENSE, CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( 'Invoices List', 'Invoices List'[REF] ), "@TotalSamples", [Total Samples collected] ), ALLSELECTED ( ) ), ORDERBY ( [@TotalSamples], DESC ) ) ) RETURN Result

Regards