Forum Discussion

Floriankx's avatar
Floriankx
Icon for Solution Sage rankSolution Sage
8 years ago
Solved

Rankx filter empty cells

Hello together,

 

I'm struggling with creating rank for projects when project cell is empty.

 

ProjectordervalueOrderRank
Project 1100$10.0001
 101$250.0005
Project 2102$15.0003
Project 1103$12.0002
Project 2104$80.0004


As you can see for order number 101 there is no Project reference.

 

Rank for order I created using:

OrderRank:=RANKX(ALL(fOrders[order]);[Selected TopN Value];;0)

This works great.

 

Adapting this to Project level PrjRank:=RANKX(ALL(fOrders[Project]);[Selected TopN Value];;0)

What I would like to have:

ProjectvalueProject Rank
Project 1$22.0001
Project 2$95.0002

 

What I unfortunately get:

ProjectvalueProject Rank
 $250.0001
Project 1$22.0002
Project 2$95.000

3

 

I have tried wrapping the all with a filter as well as wrapping the expresseion [Selected TopN Value] in a Calculate with filter but non of this worked. 

 

I've also checked:

https://community.powerbi.com/t5/Desktop/Rankx-with-filter/m-p/63457#M26162

https://community.powerbi.com/t5/Desktop/Rankx-with-filter/m-p/8213#M1308

 

I haven't found a solution yet.

Maybe there is a BI hero out there able to help me.

 

Best regards.

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Floriankx

     

    Try this MEASURE

     

    ProjectLevelRank =
IF (
    NOT ( SELECTEDVALUE ( fOrders[Project] ) = BLANK () ),
    RANKX (
        FILTER ( ALLSELECTED ( fOrders[Project] ), fOrders[Project] <> BLANK () ),
        CALCULATE ( SUM ( fOrders[value] ) ),
        ,
        ASC,
        DENSE
    )
)

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Floriankx

     

    Try this MEASURE

     

    ProjectLevelRank =
IF (
    NOT ( SELECTEDVALUE ( fOrders[Project] ) = BLANK () ),
    RANKX (
        FILTER ( ALLSELECTED ( fOrders[Project] ), fOrders[Project] <> BLANK () ),
        CALCULATE ( SUM ( fOrders[value] ) ),
        ,
        ASC,
        DENSE
    )
)
    • Floriankx's avatar
      Floriankx
      Icon for Solution Sage rankSolution Sage
      8 years ago

      Hello Zubair_Muhammad,

       

      Amazing! It works, I would never have figured this out by myself.

       

      unfortunately SELECTEDVALUE isn't available to me, so I had to rephrase it with IF(HASONEVALUE(<column>);Values(<column))

      Maybe I will find the time to understand this formula entirely.

       

      Best regards.