Forum Discussion

Stemar_Aubert's avatar
Stemar_Aubert
Icon for Resolver I rankResolver I
6 years ago
Solved

RANKX values while excluding some items, with a twist.

Hello,   I have the following measure:   EyzRank = SWITCH ( ISBLANK ( 'Actual'[YTD 19] ), FALSE (), RANKX ( ALLSELECTED ( Reps[INDUSTRY REP] ), 'Actual'[YTD 19] ), BLANK () )    I...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Stemar_Aubert ,

     

    You can try add conditions before using RANKX().

    Please refer to the formula below.

     

     

    Measure 2 =
IF (
    ISFILTERED ( 'Table'[region] ),
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[region] = SELECTEDVALUE ( 'Table'[region] ) ),
        [Measure],
        ,
        ASC
    ),
    RANKX ( ALL ( 'Table' ), [Measure],, ASC )
)

     

     

    Result would be shown as below.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Stemar_Aubert ,

 

You can try add conditions before using RANKX().

Please refer to the formula below.

 

 

Measure 2 =
IF (
    ISFILTERED ( 'Table'[region] ),
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[region] = SELECTEDVALUE ( 'Table'[region] ) ),
        [Measure],
        ,
        ASC
    ),
    RANKX ( ALL ( 'Table' ), [Measure],, ASC )
)

 

 

Result would be shown as below.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Stemar_Aubert's avatar
Stemar_Aubert
Icon for Resolver I rankResolver I
6 years ago

Anonymous 

 

Thank you for the help. At first it didn't work because I have data in multiple tables. However, using the logic you've provided, I got it working.

EyzRank =
SWITCH (
    ISCROSSFILTERED ( Location[REGION] ),
    FALSE (), IF (
        ISBLANK ( ( 'Actual'[actuals YTD 19] ) ),
        BLANK (),
        CALCULATE (
            RANKX ( ALL ( Reps[INDUSTRY REP] ), 'Actual'[actuals YTD 19] ),
            FILTER (
                ALL ( Location[REGION] ),
                Location[REGION] = SELECTEDVALUE ( Location[REGION] )
            )
        )
    ),
    IF (
        ISBLANK ( ( 'Actual'[actuals YTD 19] ) ),
        BLANK (),
        CALCULATE ( RANKX ( ALL ( Reps[INDUSTRY REP] ), 'Actual'[actuals YTD 19] ) )
    )
)

 

I use ISCROSSFILTERED because my regional selection is done through a map visual, not a slicer.

The whole measure is not the most elegant solution at the moment, but I can now work to integrate the other parts.