Forum Discussion
Create Ranking to dynamically update based on filters
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
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 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!
