Forum Discussion

hannahGala's avatar
hannahGala
Advocate I
8 years ago
Solved

RANKX excluding blanks

Hiya all,   I've not posted before, apologies if this isn't in the right place.   I want to rank clubs by their YOY (year on year) variances - however there are some which may not have any sales ...
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    hannahGala,

     

    You may refer to DAX below.

    YOY Rank =
IF (
    NOT ( ISBLANK ( [YOY] ) ),
    RANKX (
        FILTER ( ALLSELECTED ( 'Core ClubsVW'[Club] ), NOT ( ISBLANK ( [YOY] ) ) ),
        [YOY]
    )
)
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
8 years ago

hannahGala,

 

You may refer to DAX below.

YOY Rank =
IF (
    NOT ( ISBLANK ( [YOY] ) ),
    RANKX (
        FILTER ( ALLSELECTED ( 'Core ClubsVW'[Club] ), NOT ( ISBLANK ( [YOY] ) ) ),
        [YOY]
    )
)
  • hannahGala's avatar
    hannahGala
    Advocate I
    8 years ago

    Love this, thank you! Couldn't have done it without your help :)

     

    I've amended it slightly so that it:

    1) doesn't filter on selected (I want to show estate rank, even if you choose region in a slicer)

    2) has a filter on open clubs 

    3) only shows if hasonevalue (so that it doesn't show a total at the bottom)

     

     

    YOY Estate Rank = 
    IF(
        HASONEVALUE('Core ClubsVW'[CostCentre]),        
        IF (
            NOT ( ISBLANK ( [YOY] ) ),
            RANKX (
                FILTER ( ALL ( 'Core ClubsVW' ), NOT ( ISBLANK ( [YOY] ) ) && 'Core ClubsVW'[OpenFlag]=1),
                [YOY]
            )
        ),
        "")

    Thanks SO much.

     

    Hannah

  • madawak's avatar
    madawak
    Frequent Visitor
    7 years ago

    Thanks Sam.

     

    Regards,

    Madawa