Ranking the prices without using RANKX from DAX book

[Anonymous]

Hi guys,

Actually I have been studying"The Definitive-guide" book of alberto ferrari, in chapter 4 page number 95 alberto gave me an example, it is very importtant for developers to have a strong understanding before moving on as he mentioned.

In the below example we want to rank the products in term of which product is most expensive and give it rank number "1" and the second most expensive and rank it with number "2" and so on.

In the below picture, we can see we have two variables

First var used Product[Unit Price] and actually I have no issue with this.

Second var used the first var 'PriceOfCurrentProduct' as an argument comparing with same column? like below:

Product[Unit Price] > PriceOfCurrentProduct ----> (this argument refer to the same column Unit Price)

I am very confusing for the result as I couldn't understand why should we compare the column with itself?

I need clear explanation of the formula and its result.

Note: I know RANKX can do the job, I just want to understand the idea of that example.

[tamerj1]

Hi @Anonymous ,
I will  try to make it simple. 
The function "FILETR" is actually an iterator. Therefore for each cell in the newly caculated column we have the current value of unit price (which is in the same row of this cell) and we have "FILTER" which iterates over the complete table. For each iteration we have a different value of the unit price (well, it could be the same value but belongs to a different row).
This iteration will happen for each cell in the newly calculated column. Now you can just use your immagination to view the whole picture.
Let me further add that by this iteraltion "FILTER" creates a new row context. Therefore we have two values of the same column:
1. The value delivered by the original row context (created by the calculated column) which is stored in a variable outside the iteration of "FILTER". Note: Inside "FILTER", you can replace:
Product[Unit Price] > PriceOfCurrentProduct
with 
Product[Unit Price] > EARLIER ( roduct[Unit Price] )

2. The value delivered by the new row context created by "FILTER".

In other words, for each row of the newly calculated column Product[Unit Price] > PriceOfCurrentProduct will compare the "Unit Price" value in the current row with the "Unit Price" value of all other rows in the "Unit Price" column.

[ValtteriN]

Hi,

The idea here is to FILTER the Product table based on the current price. E.g. We only consider products which have higher price than the current product.

After we have filtered the product table based on this condition we count the rows of this filtered table thus returning the amount of more expensive products.

Example: a Table has 4 products with prices like 1,2,3,4. Current products price is 2 -> we filter the table -> now we have table with 2 rows with prices of 3 and 4.

Does this help to explain what is going on in the DAX?

[Anonymous]

up

[v-rzhou-msft · Accepted solution]

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.

[Anonymous]

Vatt

First of all, thank you. 

I hope you understand my question, my issue here is how can we compare the column with itself like:

Product[Price] > PriceOfCurrentProduct (this variable also reffering the same column? 

[Anonymous]

Thanks Valtt

But how can we compare the product price to itself

Product[price] > variable which actually has the same column name

I hope you understand my question

How possible the price of product will be higher than itself 🙄