Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: Ranking the prices without using RANKX from DA...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Ranking the prices without using RANKX from DAX book

01-31-2022
04:14 AM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-07-2022
01:08 AM

Hi @bandermansor ,

```
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.

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-07-2022
03:33 AM

Hi @bandermansor ,

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:

**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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2022
04:25 AM

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?

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-01-2022
12:05 PM

up

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-07-2022
01:08 AM

Hi @bandermansor ,

```
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2022
01:44 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2022
01:33 PM

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 🙄

Announcements

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors

User | Count |
---|---|

132 | |

67 | |

37 | |

26 | |

25 |

Top Kudoed Authors

User | Count |
---|---|

143 | |

75 | |

44 | |

40 | |

23 |