Rankx Ignore Blanks

[JamesBurke]

Hi all , 

I have a condional Column that returns the device names connected to a specifc customer For example 

Store Name	Usages
Device 1	12
Device 2	14
	14
Device 4	19

 As it's a conditonal Column it has blanks in the store name Column , which wne nranking becomes an issue

Rank  =

   CALCULATE(

    RANKX(

        ALL('Table1[StoreName]), // NOT ALLSELECTED

        [Total Kwh]))

Above is what i have so far but it includes blank in the Ranking , looking at using All( function so the device name keeps it's rank when drilled down. 

Desired output :

Rank 1 : Device 4

Rank 2 : Device 2

Rank 3 : Device 1 

Thanks , James.

[v-junyant-msft]

Hi @JamesBurke ,

Please try this DAX:

Rank = 
VAR _RANK =
RANKX(ALL('Table'), [Measure],,DESC,Dense)
RETURN
IF(
    MAX('Table'[Store Name]) <> BLANK(),
    _RANK,
    BLANK()
)

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

[JamesBurke]

Hi @v-junyant-msft , 

Thanks for this ! 

unfortunately for me this filters the blank from the ranking but does not change the value in the sense that blank is hidden but it still effects the rankings. 

Rank Test v2 =

VAR _RANK =

RANKX(ALL('Emporia Devices'[Device name KP]), [Total Kwh],,DESC,Dense)

RETURN

IF(

    MAX('Emporia Devices'[Device name KP]) <> BLANK(),

    _RANK,

    BLANK()

)

Is the measure im using , 

Thanks ! 

[v-junyant-msft · Accepted solution]

Hi @JamesBurke ,

Sorry, please change the DAX into this:

Rank = 
VAR _RANK =
RANKX(FILTER(ALL('Table'), 'Table'[Store Name] <> BLANK()), [Measure],,DESC,Dense)
RETURN
IF(
    MAX('Table'[Store Name]) <> BLANK(),
    _RANK,
    BLANK()
)

The final output is as below:

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

[JamesBurke]

Hi @v-junyant-msft , 

You are amazing , Thank you so much !! 

Been stuck on this an embarssing amount of time. 

Thanks again , James !!