Dynamic Filter on Attribute Tied To Ranking

[JeffDude]

Hi all,

I have a report which contains 3 line/column combo graphs which each display the same information but are filtered on different state values. Rather than hardcoding a filter for the state, I need to first determine rankings of the states based on % from Goal metric. Using RANKX(), I'm able to get a ranking correctly - even if I include a slicer on the report to filter by month. It changes correctly as I flip through months.

When I attempt to use this ranking as a filter on the combo charts, it refers the ranking to month, rather than state, and filters the visual down to the month with the highest value.

How can I filter on the state value tied to that ranking dynamically, rather than the number ranking itself? It seems rather simple in my head, but for some reason it is throwing me off.

Here is the table with rankings:

Desired future state filters would be...

First graph - State = CMD

Second graph - State = CPA

Third Graph - State = CVA

I need to avoid hardcoding those state filters, and have them dynamically change as the metric (% to goal) changes monthly.

Thanks ahead of time. Let me know for further clarification.

[v-caliao-msft]

@JeffDude,

Sorry for the delay.

In your scenario, did you use a slicer to filter data? Generally, if your rank column is a measure, the measure will be re-calculated when selecting items in your slicer. In your scenario, I not quite understand your requirement. So could you please provide us more information about your report, so that we can make further analysis. If possible you can upload a pbix file to Onedrive or any web storage and share the link. Do mask sensitive data before uploading.

Regards,

Charlie Liao

[JeffDude]

@v-caliao-msft

Thanks for taking the time to look. Let's see if I can clarify further.

For any given month, I have eight attributes (companies in this case) that are being ranked 1-8. I need to dynamically filter three graphs to show the bottom three performing companies based on rank - therefore 6, 7, 8 ranking companies would show based on the filtered month. Here is an example table and slicer showing how companies would rank based on month. May and October are displayed with differences in ranking.

Here is an image of the October graph, filtered on 'CMD' company as this is the worst ranking company for the given month.

Ideally, I would like a solution which dynamically filters the company based on the numeric ranking rather than manually changing the company filter once seeing the rankings. As you can see in the tables above, May ranking would have the graph display 'CVA' values, yet October would display 'CMD' values.

If I add the numeric measure for ranking as a filter, it looks at the numeric value as a month number and filters the x-axis down to that given month only. I want all 12 months to remain, yet the values on the graph to change based on filter of company.

Current Workaround

The current workaround requires a bit of manual intervention. I added chiclet slicers to the report and sorted the companies by ranking. When updating slicer values for month, the values change in the chiclet slicers based on rankings. I have to manually re-select that top company when it changes to reflect the values in the graph. I'd like to remove this manual step.

Thanks,

Jeff