Forum Discussion

tammyl's avatar
tammyl
Regular Visitor
2 years ago
Solved

Create Ranking to dynamically update based on filters

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...
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

OwenAuger's avatar
OwenAuger
Super User
2 years ago

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's avatar
tammyl
Regular Visitor
2 years ago

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's avatar
    OwenAuger
    Super User
    2 years ago

    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's avatar
      tammyl
      Regular Visitor
      2 years ago

      Thank so much for your help!

    • tammyl's avatar
      tammyl
      Regular Visitor
      2 years ago

      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's avatar
        OwenAuger
        Super User
        2 years ago

        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?