topic Create Ranking to dynamically update based on filters in DAX Commands and Tips

Create Ranking to dynamically update based on filters

tammyl — Sat, 21 Oct 2023 05:18:49 GMT

Hi, I'm looking to create ranking measure to dynamically rank when certain filters are applied:

In the below table, I want to group by ID, and rank based on sorting the ind to ascending and offer_dt to descending.

offer_dt	Offer_Action	ID	ind	Rank (All data)
9/26/2023	Accepted	123	1	1
9/12/2023	Rejected	123	2	2
9/26/2023	Delivered	123	3	3
9/12/2023	Delivered	123	3	4
9/21/2023	Accepted	456	1	1
9/9/2023	Deferred	456	2	2
9/21/2023	Delivered	456	3	3
9/9/2023	Delivered	456	3	4
9/8/2023	Accepted	789	1	1
9/8/2023	Rejected	789	2	2
9/8/2023	Delivered	789	3	3
9/8/2023	Delivered	789	3	4

When the slicer date filter is filtered to Sept 1 - Sept 12 then the following rank should show:

offer_dt	Offer_Action	ID	ind	Rank (All data)	Rank (filter on date)
9/12/2023	Rejected	123	2	2	1
9/12/2023	Delivered	123	3	4	2
9/9/2023	Deferred	456	2	2	1
9/9/2023	Delivered	456	3	4	2
9/8/2023	Accepted	789	1	1	1
9/8/2023	Rejected	789	2	2	2
9/8/2023	Delivered	789	3	3	3
9/8/2023	Delivered	789	3	4	4

Thanks,

Tammy

Re: Create Ranking to dynamically update based on filters

OwenAuger — Sat, 21 Oct 2023 10:30:33 GMT

Hi tammyl

I have attached a PBIX showing how I would approach it.

One initial observation: in order to assign different ranks to the last two rows of your sample data, there would need to be another field to distinguish them. In my PBIX, these two rows are automatically merged in the visual and receive a single rank.

Summary of method:

1. Create measures using the RANK function:

Rank (All data) = IF ( NOT ISEMPTY ( Offers ), -- Only return result when Offers is nonempty VAR RankingTable = CALCULATETABLE ( SUMMARIZE ( Offers, Offers[ind], Offers[offer_dt], Offers[ID] ), REMOVEFILTERS () -- rank based on all data ) VAR CurrentRank = RANK ( DENSE, RankingTable, ORDERBY ( Offers[Ind], ASC, Offers[offer_dt], DESC ), DEFAULT, PARTITIONBY ( Offers[ID] ) ) RETURN CurrentRank )

Rank (filter on date) = VAR RankingTable = CALCULATETABLE ( SUMMARIZE ( Offers, Offers[ind], Offers[offer_dt], Offers[ID] ), ALLSELECTED () -- Rank within overall filter context of visual ) VAR CurrentRank = RANK ( DENSE, RankingTable, ORDERBY ( Offers[Ind], ASC, Offers[offer_dt], DESC ), DEFAULT, PARTITIONBY ( Offers[ID] ) ) RETURN CurrentRank

2. Create a field parameter containing these two measures. I called the table Measure Selection.

3. Create a table visual containing the relevant columns and the field parameter column Measure Selection[Measure Selection].

4. Add a Date table and Date slicer, related to Orders[order_dt].

5. Add the Preselected Slicer custom visual to the page,

Create a Dummy table with a column containing True/False (as required for Preselected Slicer's "Dirty Status" field).

5. Place the Measure Selection column in the Preselected Slicer's Fields field well.

6. Create a Pre Selection measure for the Preselected Slicer:

Measure Include Flag = VAR CurrentMeasure = MAX ( 'Measure Selection'[Measure Selection] ) RETURN OR ( CurrentMeasure = "Rank (All data)", ISFILTERED ( 'Date' ) )

7. Now the Ranks measures included in the main table visual will update depending on whether Date is filtered.

Hopefully this is close to what you're lookng for and can be adapted to your model/report.

Regards

Re: Create Ranking to dynamically update based on filters

tammyl — Sat, 21 Oct 2023 17:57:48 GMT

Hi OwenAuger,

Thanks for this, I was able to create the measures and it works. However, i want to use the rank measures as filters to only include the records ranked as 1 based on the dynamic Rank measure created. When I try to use it as a filter and create visualisations, it does not give me any results.

For example, when the slicer date filter is filtered to Sept 1 - Sept 12 then based on the Rank (filter on date), i only want the records highlighted in Red and use it to for my visualisations - . Not sure if there is a better way to do this. Appreciate your thoughts/other solution to achieve this:

offer_dt	Offer_Action	ID	ind	Rank (All data)	Rank (filter on date)
9/12/2023	Rejected	123	2	2	1
9/12/2023	Delivered	123	3	4	2
9/9/2023	Deferred	456	2	2	1
9/9/2023	Delivered	456	3	4	2
9/8/2023	Accepted	789	1	1	1
9/8/2023	Rejected	789	2	2	2
9/8/2023	Delivered	789	3	3	3
9/8/2023	Delivered	789	3	4	4

Thanks!

Re: Create Ranking to dynamically update based on filters

OwenAuger — Sun, 22 Oct 2023 02:39:40 GMT

Hi again tammyl

Thanks for the clarification!

If you need to apply a filter corresponding to Rank = 1 regardless of the layout of the visual (where the type of Rank changes depending on whether there is a 'Date' filter), I recommend using a Calculation Group.

I have attached an updated PBIX with a Calculation Group called Offer Filter Calculation Group.

Have you worked with Calculation Groups before? If not, there are various good articles out there, such as

https://www.sqlbi.com/articles/introducing-calculation-groups/

You can now author Calculation Groups in Power BI Desktop with this month's version.

In this case, I created a Calculation Group with 3 Calculation Items.

Rank = 1 (All data)
Rank = 1 (filter on date)
Rank = 1 Dynamic
Appies one of the other Calculation Items depending on whether 'Date' table is filtered.

These Calculation Items contain the ranking logic and apply a filter corresponding to Rank = 1.

The script for the Calculation Group (from Tabular Editor 3) is:

------------------------------------------------------ -- Calculation Group: 'Offer Filter Calculation Group' ------------------------------------------------------ CALCULATIONGROUP 'Offer Filter Calculation Group'[Offer Filter] CALCULATIONITEM "Rank = 1 (All data)" = VAR RankingTable = CALCULATETABLE ( SUMMARIZE ( Offers, Offers[ind], Offers[offer_dt], Offers[ID] ), REMOVEFILTERS ( ) -- rank based on all data ) VAR FilterRank1 = INDEX ( 1, RankingTable, ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ), DEFAULT, PARTITIONBY ( Offers[ID] ) ) VAR Result = CALCULATE ( SELECTEDMEASURE ( ), KEEPFILTERS ( FilterRank1 ) ) RETURN Result CALCULATIONITEM "Rank = 1 (filter on date)" = VAR RankingTable = CALCULATETABLE ( SUMMARIZE ( Offers, Offers[ind], Offers[offer_dt], Offers[ID] ), ALLSELECTED ( ) -- Rank within overall filter context of visual ) VAR FilterRank1 = INDEX ( 1, RankingTable, ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ), DEFAULT, PARTITIONBY ( Offers[ID] ) ) VAR Result = CALCULATE ( SELECTEDMEASURE ( ), KEEPFILTERS ( FilterRank1 ) ) RETURN Result CALCULATIONITEM "Rank = 1 Dynamic" = IF ( ISFILTERED ( 'Date' ), -- Option 1: -- If Date is filtered, -- then Rank within overall filter context of visual CALCULATE ( SELECTEDMEASURE (), 'Offer Filter Calculation Group'[Offer Filter] = "Rank = 1 (filter on date)" ), -- Option 2: -- Rank with filters removed (i.e. global rank) CALCULATE ( SELECTEDMEASURE (), 'Offer Filter Calculation Group'[Offer Filter] = "Rank = 1 (All data)" ) )

Here is an example report page where I have applied a page-level filter of "Rank = 1 Dynamic".

Note that you must use explicit measures (i.e. written with DAX) for this to work:

Regards

Owen

Re: Create Ranking to dynamically update based on filters

tammyl — Mon, 23 Oct 2023 19:36:22 GMT

Hi OwenAuger ,

Thanks so much for this. I don't have tabular editor but i was able to leverage this measure you provided and I was able to create the charts. I want to create additional calculations on this measure. How can I adjust this or use this measure to calculate a percentage. For example, I want to calculate the total # of unique customers who Accepted (based on the ranking=1 with date filter AND offer_action ='Accepted') / total # of customers who got an offer delivered (distinct count of ID and offer_action ='Delivered' - this does not need to leverage the ranking)

    CALCULATIONITEM "Rank = 1 (filter on date)" = 
        VAR RankingTable =
            CALCULATETABLE (
                SUMMARIZE (
                    Offers,
                    Offers[ind],
                    Offers[offer_dt],
                    Offers[ID]
                ),
                ALLSELECTED ( ) -- Rank within overall filter context of visual
            )
        VAR FilterRank1 =
            INDEX (
                1,
                RankingTable,
                ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ),
                DEFAULT,
                PARTITIONBY ( Offers[ID] )
            )
        VAR Result =
            CALCULATE (
                SELECTEDMEASURE ( ),
                KEEPFILTERS ( FilterRank1 )
            )
        RETURN
            Result

Thanks,

Tammy

Re: Create Ranking to dynamically update based on filters

OwenAuger — Wed, 25 Oct 2023 03:23:57 GMT

Hi again tammyl

Sure thing. If we put calculation groups aside for the moment, I would recommend creating measures for each of the components of the ratio, then creating a measure that divides them.

See Version 3 page of attached PBIX.

Here are the measures I created:

# Customers Accepted Rank 1 = VAR RankingTable = CALCULATETABLE ( SUMMARIZE ( Offers, Offers[ind], Offers[offer_dt], Offers[ID], Offers[Offer_Action] -- include this column to ensure it applies as a filter in Result ), ALLSELECTED ( ), -- Rank within overall filter context of visual Offers[Offer_Action] = "Accepted" -- Filter "Accepted" before ranking ) VAR FilterRank1 = INDEX ( 1, RankingTable, ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ), DEFAULT, PARTITIONBY ( Offers[ID] ) ) VAR Result = CALCULATE ( DISTINCTCOUNT ( Offers[ID] ), KEEPFILTERS ( FilterRank1 ) ) RETURN Result

# Customers Delivered = CALCULATE ( DISTINCTCOUNT ( Offers[ID] ), KEEPFILTERS ( Offers[Offer_Action] = "Delivered" ) )

Accepted Rank 1 / Delivered Ratio = DIVIDE ( [# Customers Accepted Rank 1], [# Customers Delivered] )

I'm hoping I captured your requirements correctly, otherwise the logic might need to be adjusted.

Side note: Calculation Groups can now be authored in Power BI Desktop, in case you would like to look into that later.

Regards

Re: Create Ranking to dynamically update based on filters

tammyl — Mon, 06 Nov 2023 16:56:11 GMT

Thank so much for your help!

Re: Create Ranking to dynamically update based on filters

tammyl — Thu, 16 Nov 2023 04:21:40 GMT

Hi Owen,

I realized the measure doesn't really work as intended. I want the calculation to filter on Offer Action after ranking. For the filtered date, it should be 1 accepted, 1 deferred and 1 rejected. Could you advise how to filter offer action after the ranking?

# Customers Rejected =

VAR RankingTable =

CALCULATETABLE (

SUMMARIZE (

Offers,

Offers[ind],

Offers[offer_dt],

Offers[ID],

Offers[Offer_Action] -- include this column to ensure it applies as a filter in Result

ALLSELECTED ( ) , -- Rank within overall filter context of visual

Offers[Offer_Action] = "Rejected" -- Filter "Rejected" before ranking

)

VAR FilterRank1 =

INDEX (

RankingTable,

ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ),

DEFAULT,

PARTITIONBY ( Offers[ID] )

)

VAR Result =

CALCULATE (

DISTINCTCOUNT ( Offers[ID] ),

KEEPFILTERS ( FilterRank1 )

)

RETURN

Result

# Customers Deferred Rank 1 =

VAR RankingTable =

CALCULATETABLE (

SUMMARIZE (

Offers,

Offers[ind],

Offers[offer_dt],

Offers[ID],

Offers[Offer_Action] -- include this column to ensure it applies as a filter in Result

ALLSELECTED ( ), -- Rank within overall filter context of visual

Offers[Offer_Action] = "Deferred" -- Filter "Deferred" before ranking

)

VAR FilterRank1 =

INDEX (

RankingTable,

ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ),

DEFAULT,

PARTITIONBY ( Offers[ID] )

)

VAR Result =

CALCULATE (

DISTINCTCOUNT ( Offers[ID] ),

KEEPFILTERS ( FilterRank1 )

)

RETURN

Result

Re: Create Ranking to dynamically update based on filters

OwenAuger — Fri, 17 Nov 2023 10:13:21 GMT

Hi again Tammy,

I think this structure is what you want:

# Customers Rejected = VAR RankingTable = CALCULATETABLE ( SUMMARIZE ( Offers, Offers[ind], Offers[offer_dt], Offers[ID], Offers[Offer_Action] -- include this column to ensure it applies as a filter in Result ), ALLSELECTED ( ) -- Rank within overall filter context of visual ) VAR FilterRank1 = INDEX ( 1, RankingTable, ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ), DEFAULT, PARTITIONBY ( Offers[ID] ) ) VAR Result = CALCULATE ( DISTINCTCOUNT ( Offers[ID] ), KEEPFILTERS ( FilterRank1 ), KEEPFILTERS ( Offers[Offer_Action] = "Rejected" ) ) RETURN Result

The Offer_Action filter is shifted to the last step, and applied alongside the Rank = 1 filter.

Does this work for you?