Forum Discussion

JamesBurke's avatar
JamesBurke
Icon for Helper III rankHelper III
2 years ago
Solved

Rankx Ignore Blanks

Hi all , 

 

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

 

Store NameUsages
Device 112
Device 214
 14
Device 419

 

 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.
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      JamesBurke
      Icon for Helper III rankHelper III
      2 years ago

      Hi Anonymous , 

       

      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 ! 
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.