Dynamic Rank 1st, 2nd and 3rd Filtering Based on Slicer Selection

[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 date selection:

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

So locations 1, 2 and 3 will dynamically change based on the user's date selection. 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 the 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

Rank Measure (I got this from another post in the Community Forum and adapted it for my use case):

Rank Measure = 

VAR _table =

    ADDCOLUMNS (

        ADDCOLUMNS (

            ALL ( Issues Location[Location] ),

            "@rankone", CALCULATE ( RANKX ( ALL ( Issues Location[Location] ), [Count Measure],, DESC ) ),

            "@ranktwo",

                CALCULATE (

                    RANKX (

                        ALL ( Issues Location[Location] ),

                        CALCULATE ( MAX ( Issues Location[Location] ) ),

                        ,

                        ASC

                    )

                )

        ),

        "@newindex",

            [@rankone] * 100 + [@ranktwo]

    )

VAR _newtable =

    ADDCOLUMNS ( _table, "@newrank", RANKX ( _table, [@newindex],, ASC ) )

RETURN

    IF (

        HASONEVALUE ( Issues Location[Location] ),

        MAXX (

            FILTER ( _newtable, Issues Location[Location] = MAX ( Issues Location[Location] ) ),

            [@newrank]

        )

    )

I don't know where to apply the Rank Measure so that I can filter the respective graphs on each of those pages. Any help is greatly appreciated.

[amitchandak]

@PowerBI-Newbie , Use index function. You can use Parameter and measure to make it dynamic

example

Top1 =

calculate([%scrap], KEEPFILTERS(index(1, All(SAPWorkCenter[CDL]), orderby([%scrap],desc) ) ))

Top 2=

calculate([%scrap], KEEPFILTERS(index(2, All(SAPWorkCenter[CDL]), orderby([%scrap],desc) ) ))

Top N=

calculate([%scrap], KEEPFILTERS(index([selected Parameter], All(SAPWorkCenter[CDL]), orderby([%scrap],desc) ) ))

Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

[PowerBI-Newbie]

Hi @amitchandak ,

Thank you very much for your response. It's gotten me half-way there after adapting it for my need.

I have the following measure:

2nd Highest Location = 
VAR _2ndValue = INDEX(2,SUMMARIZE(ALLSELECTED(Issues[Location]),Issues[Location], "_count not cancelled",[Count Not Cancelled]),ORDERBY([Count Not Cancelled],DESC))
RETURN
MAXX(_2ndValue,[Location])

This provides me with the name of the location with the 2nd highest value which is excellent and so I wanted to use it in the following measure for the y-axis so that the categories and sub-categories can be filtered based on 2nd highest location:

Count - Not Cancelled 2nd = 
CALCULATE(COUNT(Issues[Incident Date]),
Issues[Count If not cancelled] = 1,
Issues[Location] = [2nd Highest Location])+0

But this gives me PLACEHOLDER error and so I tried using variables but it doesn't filter the data properly. However, when I hardcode the value like the following it works perfectly:

 Issues[Location] = "Location 2"

I don't know what the issue is.

[amitchandak]

@PowerBI-Newbie , if this from a connected table, measure can be filtered using a code like

calculate([%scrap], KEEPFILTERS(index(2, All(SAPWorkCenter[CDL]), orderby([%scrap],desc) ) ))

but if you first want to get value and pass it on to another table

calculate([Meausre], filter(Table, Table [Location] = [2nd Highest location]))

if you will use +0, it will show all other location with blank values

[PowerBI-Newbie]

Hi @amitchandak,

Any assistance is greatly appreciated.

[PowerBI-Newbie]

Hi @amitchandak,

I hope you're well.

I don't want to create a new question so would greatly appreciate your help with the above query.