Filtering a table based on multi column custom grouping

dethompson97 · October 3, 2018, 3:04am

I am puzzled on how to do something. The users want to create custom filter selections based on two columns. They do not want to independent slicers (which would be so easy). They have a few combinations of two columns they like to see together and would like users to be able to select from those. For example, given this data

ID Leave Mode Reason
1 Involuntary Death
2 Voluntary Other
3 Involuntary Other
4 Voluntary Retirement
5 Involuntary Retirement
6 Involuntary Death
7 Voluntary Retirement
8 Voluntary Other

They would like to have the user’s select from the following choices in a filter:

Death
All Involuntary
All Voluntary
Voluntary (Excluding retirement)
Involuntary (Excluding retirement)

They would like them to be able to select one of the above choices and then the table would filter to show only those rows with the matching Leave Mode and/or Reason that is in the filter.

My question is, how to I filter a table when the slicer selection does not represent a row value. When I had two slicers, it was easy to have them select a mode and/or a reason and the data filtered naturally. I am puzzled as to how I can filter the table based on the selections that they want especially when the combine values from two different columns in the data.

Hope that is clear!

Thanks in advance,
Dawn

dethompson97 · October 3, 2018, 12:32pm

DonsData.pbix (83.2 KB)

I have attached a .pbix file to make this a little easier to understand and provide assistance.

Thanks!
Dawn

sam.mckay · October 4, 2018, 12:53am

Thanks for the file, I will have a look and see if there is a good solution to this.

sam.mckay · October 10, 2018, 3:45am

Sorry it’s taking me a while to come back on this. I’ve been in transit and it’s taken me a little longer than I thought to come up with a solid solution on this one.

Will keep working on it.

sam.mckay · October 10, 2018, 3:55am

I want to show you where I’m at with this and then you can guide me to exactly what you need.

I think changes could be made to this but need to get your feedback first.

Attached.DonsData.pbix (77.5 KB)

You’ll see that based on a selection the formulas do there work and bring up the results for modes and reasons.

What I’m not sure on is if you need exact matches or just matching for either.

Another example

Have a look through the resource and let me know what you think of where things are at.