Ranking the prices without using RANKX from DAX book

Hi Anonymous ,

Your problem is 
how can we compare the column with itself like:
Product[Price] > PriceOfCurrentProduct (this variable also reffering the same column? 

Let's see the code.

UnitPriceRank = 

VAR PriceOfCurrentProduct = 'Product'[Unit Price]
VAR MoreExpensiveProducts =
    FILTER ( 'Product', 'Product'[Unit Price] > PriceOfCurrentProduct )
RETURN
    COUNTROWS ( MoreExpensiveProducts ) + 1

This is to build a calcualted column, so we can use column directly as Current value like 

PriceOfCurrentProduct = 'Product'[Unit Price].

Then we will filter "Product" table by filter logic 'Product'[Unit Price] > PriceOfCurrentProduct . I think your problem is here: the first 'Product'[Unit Price] contains all data, not only current data. So MoreExpensiveProducts will return a table with Products whose [Unit Price] > current [Unit Price] .

To explain more clearly, I build a sample.

Eg1:

Now Power BI is calculating the rank for ProductG. ProductG's [Unit Price] is 3199.99. 3199.99 is the max value in [Unit Price].

So MoreExpensiveProducts  will return a blank table. So result is 1 (countrow =0 then +1). The logic from A to N is the same.

Eg2:

Now Power BI is calculating the rank for ProductR. ProductR's [Unit Price] is 2899.99. 3199.99 is bigger than 2899.99.

So MoreExpensiveProducts  will return a table with all data from A to N. So result is 15 (countrow =14 then +1). The logic from O to R is the same.

If you think this way of writing the code is not easy to understand, you can try EARLIER function. This will give you same result.

UnitPriceRank = 

VAR MoreExpensiveProducts =
    FILTER ( 'Product', 'Product'[Unit Price] > EARLIER('Product'[Unit Price]) )
RETURN
    COUNTROWS ( MoreExpensiveProducts ) + 1

Like MAX/SUM in measure, we always use EARLIER to get current value in calclated column.

Best Regards,
Rico Zhou

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