Forum Discussion
Filter on Multiple Columns
I have a table with data like this:
| Id | Col1 | Col2 |
| 1 | A | B |
| 2 | A | A |
| 3 | C | A |
| 4 | B | A |
| 5 | C | C |
I need to make an AB filter that filters for everything that has A or B in both columns. So I want only rows 1,2 and 4. These groupings will change and be added to over time, like adding an AD filter (which would return nothing from this table) or an ABC filter (which would return everything from this table). I'm not sure how to go about setting this up.
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
6 Replies
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.
mahoneypat thanks for your solution, could you please explain how to :
" 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)." ?
I need to let the users select different countries in a slicer than use it to search different columns with countries in it and filter the rows that show the selected countries in any column. It is nearly the same as in this post, as if A and B were countries, but I have to let people choose.
