Forum Discussion

VBLOT's avatar
VBLOT
Icon for Helper I rankHelper I
3 years ago
Solved

Ranking with multiple rows and drilldown

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 (h...
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    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

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
3 years ago

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.

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    3 years ago

    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