Forum Discussion

BI_Prachi's avatar
BI_Prachi
Frequent Visitor
2 years ago
Solved

How to assign Rank for zero but exclude null or blank values

Hi, I have the following tables Dim :  LocationCodeName Locationcode A A001 B B001 C C001 D D001 E E001 F F001  SalesFact :  Locationcode Qty A001 8 B00...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

     

lbendlin's avatar
lbendlin
Super User
2 years ago

 

  • BI_Prachi's avatar
    BI_Prachi
    Frequent Visitor
    2 years ago

    Thanks lbendlin for your response, 
    It's working perfectly. Really appreciate it. 

     

    Also, I found another way to resolve this :

    if(NOT(ISBLANK([Target %])), rankx(FILTER(ALL('dim'[LocationCodeName]),NOT(ISBLANK([Target]))),CALCULATE([Target %]),,,Dense),BLANK())
    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      If you are interested you can run both versions of this through DAX Studio and compare the query plans and server timings.

       

      CALCULATE([Target %])  seems redundant.

      • BI_Prachi's avatar
        BI_Prachi
        Frequent Visitor
        2 years ago

        Yeah, you are right lbendlin . Much appreciated. Thanks again ğŸ™‚