Create Ranking to dynamically update based on filters

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

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