Dynamic Index/Rank (1st, 2nd, 3rd) on Graphs

[PowerBI-Newbie]

I have the following graph on 3 separate pages of a Power BI report showing the breakdown of a category and sub-category:

The idea is that each page will represent a location (dynamic) based on the user's selection from the filter panel:

• Location 1 - Highest count (from count measure)
• Location 2 - 2nd highest count (from count measure)
• Location 3 - 3rd highest count (from count measure)

So locations 1, 2 and 3 will dynamically change based on the user's selection from the filter panel. In the event that two or more locations have the same value then the alphabetical order is taken into, see table below:

So what I need is someway of applying an Index/Rank Measure to the graph above - rank 1 for highest, rank 2 for 2nd highest, rank 3 for 3rd highest. The measures that I have for the count are as follows:

Count Measure:

Count Measure = CALCULATE(COUNT(Issues[Date]),Issues[Not Cancelled] = 1)+0

The respective graphs are to be dynamically filtered on each of those pages. Any help would be greatly appreciated (and providing the pbix file would be a huge bonus!).

[PowerBI-Newbie]

Hi @v-jianpeng-msft, @lbendlin,

Apologies if the question wasn't clear, here's some sample data to help:

Date	Location	Asset Type	Sub-Type
22/12/2024	Location 1	Asset Type 1	Sub-Type 1
22/12/2024	Location 1	Asset Type 1	Sub-Type 1
22/12/2024	Location 1	Asset Type 2	Sub-Type 2
22/12/2024	Location 1	Asset Type 2	Sub-Type 2
22/12/2024	Location 1	Asset Type 2	Sub-Type 2
22/12/2024	Location 2	Asset Type 1	Sub-Type 1
22/12/2024	Location 2	Asset Type 1	Sub-Type 1
22/12/2024	Location 2	Asset Type 2	Sub-Type 2
22/12/2024	Location 3	Asset Type 2	Sub-Type 2
23/12/2024	Location 1	Asset Type 1	Sub-Type 1
23/12/2024	Location 1	Asset Type 1	Sub-Type 1
23/12/2024	Location 1	Asset Type 1	Sub-Type 1
23/12/2024	Location 1	Asset Type 2	Sub-Type 2
23/12/2024	Location 1	Asset Type 2	Sub-Type 2
23/12/2024	Location 1	Asset Type 2	Sub-Type 2
23/12/2024	Location 2	Asset Type 1	Sub-Type 1
23/12/2024	Location 2	Asset Type 1	Sub-Type 1
23/12/2024	Location 2	Asset Type 2	Sub-Type 2
23/12/2024	Location 2	Asset Type 2	Sub-Type 2
23/12/2024	Location 2	Asset Type 2	Sub-Type 2
23/12/2024	Location 2	Asset Type 2	Sub-Type 2
23/12/2024	Location 3	Asset Type 1	Sub-Type 1
23/12/2024	Location 3	Asset Type 2	Sub-Type 2

From the above table the graph will look like this:

A simple count of the number of rows against each Asset Type and Sub-Type taking all locations and dates into account. A breakdown of the above graph below in pivot format:

There are 3 graphs as mentioned in the initial question - 1st, 2nd and 3rd. What I'm looking for is when the user chooses a date from the slicer (which will be single selection only) then the graph for the 1st rank/index location for example will be dynamically filtered to show data for 1st rank/index location only - so using the above data then it'll be Location 1 when 22nd December 2024 is selected since it has a count of 5 so the graph will look like this:

When the user selects 23rd December 2024, there's a tie between Location 1 and Location 2 so I would want Location 1 to be considered 1st due to alphabetical order.

I hope the request/question is clear now.

[v-jianpeng-msft]

Hi, @PowerBI-Newbie 

Thank you very much for your reply and the data provided. I've updated the measures as follows:

rank = RANK(
    DENSE,
    ALLSELECTED('Table'),
    ORDERBY([Count],DESC,'Table'[Sub-Type],ASC)
)

Best Regards

Jianpeng Li

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

[PowerBI-Newbie]

Hi @v-jianpeng-msft,

Thank you for your response.

The user will have a number of filters other than Location to filter data with - the user won't be selecting the location, it should be selected for them based on what graph they're looking at. Similar to what Ibendlin has produced where he created a measure to calculate the highest count (1st rank) and applied that measure to the visual to filter only the location with the highest count.

[lbendlin]

For your second request you can change the formula to pick the TOPN(1) based on location name.

[PowerBI-Newbie]

Hi @lbendlin,

Thank you for your response.

Your solution works for 1st ranking (ish) - so when I change it to a graph it works for 22nd December:

But when I change it to 23rd December it doesn't work since there's a tie:

I would want Location 1 to be selected due to alphabetical order. How do I update your measure to cater for this?

And also, how can I get 2nd ranking and 3rd ranking?

[lbendlin]

"I would want Location 1 to be selected due to alphabetical order. How do I update your measure to cater for this?"  

As I mentioned use TOPN(1) and sort by name, or use RANK instead of RANKX.

"And also, how can I get 2nd ranking and 3rd ranking?"

I don't understand the question. Can you please elaborate or indicate a desired outcome based on the sample data you provided?

[PowerBI-Newbie]

Hi @lbendlin,

Thank you for your response.

With regards to your first comment, are you able to assist me with this? I'm not very good with DAX.

As for your question then if the user was to select 22nd December then the graphs would look like this:

1st Ranking - Location 1 has the highest count

2nd Ranking - Location 2 has the 2nd highest count

3rd Ranking - Location 3 has the 3rd highest count

When the user selects 23rd December then the graphs would look like the following:

1st Ranking - Location 1 - despite it being a tie it comes first due to alphabetical order

2nd Ranking - Location 2 - despite it being a tie it comes second due to alphabetical order

3rd Ranking - Location 3 has the 3rd highest

I hope that is clear now.

[lbendlin · Accepted solution]

Here is  an example of using RANK  which allows you to rank by more than one column.

R = 
var a = ADDCOLUMNS(ALLSELECTED('Table'[Location]),"c",CALCULATE(COUNTROWS('Table'),REMOVEFILTERS('Table'[Asset Type],'Table'[Sub-Type])))
return rank(DENSE,a,ORDERBY([c],DESC,[Location],ASC))

is that what you are looking for?

[PowerBI-Newbie]

Thank you very much @lbendlin, this is what I'm looking for.

[v-jianpeng-msft]

Thank you lbendlin 

Hi, @PowerBI-Newbie 

Based on your description, I've created the following sample data:

Create a rank measure:

rank = 
RANK(DENSE,ALLSELECTED('Table'[Column1]),ORDERBY([Count Measure],DESC))

Put it in the tooltips of the chart:

I've provided the PBIX file used this time below. You can check out the following article about Rank:

Introducing the RANK window function in DAX - SQLBI

Best Regards

Jianpeng Li

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...