Create Ranking to dynamically update based on filters

[tammyl]

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

[OwenAuger]

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.

1. Rank = 1 (All data)
2. Rank = 1 (filter on date)
3. 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

[tammyl]

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

[OwenAuger · Accepted solution]

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

[tammyl]

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 (

        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 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 (

        1,

        RankingTable,

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

        DEFAULT,

        PARTITIONBY ( Offers[ID] )

    )

VAR Result =

    CALCULATE (

        DISTINCTCOUNT ( Offers[ID] ),

        KEEPFILTERS ( FilterRank1 )

    )

RETURN

    Result

[OwenAuger]

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?

[tammyl]

Thank so much for your help!

[OwenAuger]

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

[tammyl]

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!