Forum Discussion

PowerBI-Newbie's avatar
PowerBI-Newbie
Helper IV
1 year ago
Solved

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

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!).

Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    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?

     

     

11 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • PowerBI-Newbie's avatar
    PowerBI-Newbie
    Helper IV
    1 year ago

    Hi Anonymous, lbendlin,

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

    DateLocationAsset TypeSub-Type
    22/12/2024Location 1Asset Type 1Sub-Type 1
    22/12/2024Location 1Asset Type 1Sub-Type 1
    22/12/2024Location 1Asset Type 2Sub-Type 2
    22/12/2024Location 1Asset Type 2Sub-Type 2
    22/12/2024Location 1Asset Type 2Sub-Type 2
    22/12/2024Location 2Asset Type 1Sub-Type 1
    22/12/2024Location 2Asset Type 1Sub-Type 1
    22/12/2024Location 2Asset Type 2Sub-Type 2
    22/12/2024Location 3Asset Type 2Sub-Type 2
    23/12/2024Location 1Asset Type 1Sub-Type 1
    23/12/2024Location 1Asset Type 1Sub-Type 1
    23/12/2024Location 1Asset Type 1Sub-Type 1
    23/12/2024Location 1Asset Type 2Sub-Type 2
    23/12/2024Location 1Asset Type 2Sub-Type 2
    23/12/2024Location 1Asset Type 2Sub-Type 2
    23/12/2024Location 2Asset Type 1Sub-Type 1
    23/12/2024Location 2Asset Type 1Sub-Type 1
    23/12/2024Location 2Asset Type 2Sub-Type 2
    23/12/2024Location 2Asset Type 2Sub-Type 2
    23/12/2024Location 2Asset Type 2Sub-Type 2
    23/12/2024Location 2Asset Type 2Sub-Type 2
    23/12/2024Location 3Asset Type 1Sub-Type 1
    23/12/2024Location 3Asset Type 2Sub-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.

      • PowerBI-Newbie's avatar
        PowerBI-Newbie
        Helper IV
        1 year ago

        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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        PowerBI-Newbie
        Helper IV
        1 year ago

        Hi Anonymous,

        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.