Rankx filter empty cells

[Floriankx]

Hello together,

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

Project	order	value	OrderRank
Project 1	100	$10.000	1
	101	$250.000	5
Project 2	102	$15.000	3
Project 1	103	$12.000	2
Project 2	104	$80.000	4

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:

Project	value	Project Rank
Project 1	$22.000	1
Project 2	$95.000	2

What I unfortunately get:

Project	value	Project Rank
	$250.000	1
Project 1	$22.000	2
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.

[Zubair_Muhammad · Accepted solution]

@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]

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.