replacing multiple slicers with one slicer - filter context issues

[wilson_smyth]

I have a table with a number of attributes and a score. I Want to display a a report that shows two things:

1 - a chart showing the top 10 stores for a particular Store Specialty

2 - the rank of a selected store & speciality, when measured against other stores with the same specialty

For chart 1, i added a slicer for store specialty, and it compares the stores with same specialty . That is requirement 1 satisfied.

I also need the user to  see its ranking against other [store Specialty]. This means they will have to select a specific store/specialty, which means using two slicers. Also, if i use a Card to display just the rank of the Store Specialty selected, it is always = 1.

Is there a way to do this so that the user just selects their [store specialty] value from a slicer, then chart 1 will use ALL(stores) but keep the specialty filter context, while the chart 2 (rank)work as it does now?

There is a link to a sample pbix file for this. it has a small set of sample data.

appreciate any help you can provide.

Link to pbix on onedrive

[v-caliao-msft · Accepted solution]

@wilson_smyth,

Is there a way to do this so that the user just selects their [store specialty] value from a slicer, then chart 1 will use ALL(stores) but keep the specialty filter context, while the chart 2 (rank)work as it does now?

In your sample PBIX file, when selecting a [Store Speciality] from slicer one, it compares the stores with same specialty in chart one. And then in chart two, it display the rank for those [Store Speciality] for different stores.

Do you need the rank column to show 3,49 other than 1,2 3, right?

If that is the case, I don't think we can do it in a slicer. You need another table with the store specialty column and has no relationship with your original table.

1. create a table by with Enter Data
   
2. Create a masure in this new created table
   SelectedSpeciality = max(Table1[Store Specialty])
3. Create a measure in original table
   CheckSpecialty = IF(ISERROR(SEARCH(Table1[SelectedSpecialty],MAX('Store Details'[Store Specialty]))),0,1)
4. Add this measure to your visual filter
   

Results

Regards,

Charlie Liao

[wilson_smyth]

Thank you. this is very helpful, and a useful technique in general to know!