Forum Discussion

VBLOT's avatar
VBLOT
Icon for Helper I rankHelper I
2 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 (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....

  • OwenAuger's avatar
    OwenAuger
    2 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

5 Replies

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

    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

    • VBLOT's avatar
      VBLOT
      Icon for Helper I rankHelper I
      2 years ago

      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 ?

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    2 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
        2 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