Forum Discussion
Filter on Multiple Columns
Here is a measure expression that shows one way to do it. I hard coded in A and B, but you could get them dynamically with a disconnected table slicer with the desired values (and store the VALUES of that column used in slicer in another variable, to replace the hard-coded tables).
Has AB in Both =
VAR col1 =
MIN( T1[Col1] ) IN { "A", "B" }
VAR col2 =
MIN( T1[Col2] ) IN { "A", "B" }
RETURN
IF( col1 && col2, 1 )Pat
First create a new Calculated Column which concatenates your two columns:
ComboColumn = Column1 & Column2You can then use this new column as a filter.
But I would take it one step further and create a new Dimension Table which would include all your possible combinations. You can do this in Excel and import it, or use the Enter Data option.
Create a relationship (1: many) between this new Dimension Table and your data table above. Then use this Dimension table in your Slicer.
Hope this helps.
In my real situation there are thousands of combinations and might be more in a growing database. Additionally, as I mentioned, I might want to select group AB or I might want to select group ABC which would include all posibilities with A B or C in both columns. I may also want to select group ABCDEF. This is a fairly large dataset with a lot of permutations and I'm not going to ever be able to specify everything out manually.
I would create two dimension tables - one for Column1 and one for Column2.
Table1 = SummarizeColumns(YourTable[Column1]) and Table2 = SummarizeColumns(YourTable[Column2])(My syntax might not be perfect for this, but should get you close)
These tables will then grow as your data grows.
Then simply create your two slicers from each of these tables.
In Selection Controls, I would enable both the Multi-select and Select All options.
This should then cover all your potential combinations.
I can't see any other way of doing this other than creating 2 slicers.
Thank you but I can't see any way except by using two slicers either which is why I asked this question.
