Forum Discussion
Filter multiple columns with one column
Hello,
I am relatively new to Power BI and encountered a problem with filtering multiple columns via one single column. My table is like the following:
| Date | number | column A | column B | column C | column D | |
| 01.01.2019 | [email protected] | 1 | Blue | Green | Yellow | Black |
| 02.01.2019 | [email protected] | 2 | Pink | White | Orange | Green |
| 03.01.2019 | [email protected] | 3 | Black | Blue | Green | Yellow |
| 04.01.2019 | [email protected] | 4 | Blue | Black | Green | Yellow |
| 05.01.2019 | [email protected] | 5 | White | Black | Pink | Orange |
| 06.01.2019 | [email protected] | 5 | Yellow | Green | Black | White |
| 07.01.2019 | [email protected] | 6 | Black | Yellow | Blue | Pink |
| 08.01.2019 | [email protected] | 8 | Blue | Green | Yellow | Black |
| 09.01.2019 | [email protected] | 7 | Pink | White | Orange | Green |
| 10.01.2019 | [email protected] | 9 | Black | Blue | Green | Yellow |
| 11.01.2019 | [email protected] | 1 | Blue | Black | Green | Yellow |
| 12.01.2019 | [email protected] | 2 | White | Black | Pink | Orange |
| 13.01.2019 | [email protected] | 3 | Yellow | Green | Black | White |
| 14.01.2019 | [email protected] | 4 | Black | Yellow | Blue | Pink |
| 15.01.2019 | [email protected] | 5 | Blue | Green | Yellow | Black |
| 16.01.2019 | [email protected] | 6 | Pink | White | Orange | Green |
| 17.01.2019 | [email protected] | 7 | Black | Blue | Green | Yellow |
| 18.01.2019 | [email protected] | 324 | Blue | Black | Green | Yellow |
| 19.01.2019 | [email protected] | 3 | White | Black | Pink | Orange |
| 20.01.2019 | [email protected] | 3 | Yellow | Green | Black | White |
| 21.01.2019 | [email protected] | 5 | Black | Yellow | Blue | Pink |
| 22.01.2019 | [email protected] | 3 | Blue | Green | Yellow | Black |
| 23.01.2019 | [email protected] | 3 | Pink | White | Orange | Green |
| 24.01.2019 | [email protected] | 3 | Black | Blue | Green | Yellow |
| 25.01.2019 | [email protected] | 4 | Blue | Black | Green | Yellow |
| 26.01.2019 | [email protected] | 34 | White | Black | Pink | Orange |
| 27.01.2019 | [email protected] | 3 | Yellow | Green | Black | White |
| 28.01.2019 | [email protected] | 3 | Black | Yellow | Blue | Pink |
| 29.01.2019 | [email protected] | 3 | Blue | Green | Yellow | Black |
| 30.01.2019 | [email protected] | 3 | Pink | White | Orange | Green |
| 31.01.2019 | [email protected] | 3 | Black | Blue | Green | Yellow |
| 01.02.2019 | [email protected] | 4 | Blue | Black | Green | Yellow |
| 02.02.2019 | [email protected] | 5 | White | Black | Pink | Orange |
| 03.02.2019 | [email protected] | 3 | Yellow | Green | Black | White |
| 04.02.2019 | [email protected] | 1 | Black | Yellow | Blue | Pink |
The rows are always different and also the column A - D won't contain the same value in a row.
So I want to display the following:
I want to filter the whole table with the slicer. At this moment I only get the rows that contains the value in column A.
Moreover, I want to display the following:
I want to display the number of each value for each column in a matrix. At the moment it is correlated to column A.
Thanks for the help!
The first thing I would do is create a separate table with a distinct list of colors.
You could do this by creating a calculated table with an expression like the following:
Colors = DISTINCT(UNION(DISTINCT(Table1[column A]),DISTINCT(Table1[column B]), DISTINCT(Table1[column C]),DISTINCT(Table1[column D]) ))
Then I would rename the column in this table from [Column A] to simply [Color] as it's no longer directly linked to ColumnA. I would then use this new column in your slicer.
Then I would create a measure like the following and put a filter on your table visual where this measure is >= 1
Count All 2 = countrows(Filter(Table1 , Table1[column A] in values(Colors[Color]) || Table1[column B] in values(Colors[Color]) || Table1[column C] in values(Colors[Color]) || Table1[column D] in values(Colors[Color]) ))
To achieve your matrix result I would put the Colors[Color] column on the rows then create 4 measures to put on the columns which use expressions like the following (creating one measure for each of columns A, B, C and D):
Count A = CALCULATE(countrows(Table1) , TREATAS(values(Colors[Color]), Table1[column A]) )
1 Reply
- d_gosbellSuper User
The first thing I would do is create a separate table with a distinct list of colors.
You could do this by creating a calculated table with an expression like the following:
Colors = DISTINCT(UNION(DISTINCT(Table1[column A]),DISTINCT(Table1[column B]), DISTINCT(Table1[column C]),DISTINCT(Table1[column D]) ))
Then I would rename the column in this table from [Column A] to simply [Color] as it's no longer directly linked to ColumnA. I would then use this new column in your slicer.
Then I would create a measure like the following and put a filter on your table visual where this measure is >= 1
Count All 2 = countrows(Filter(Table1 , Table1[column A] in values(Colors[Color]) || Table1[column B] in values(Colors[Color]) || Table1[column C] in values(Colors[Color]) || Table1[column D] in values(Colors[Color]) ))
To achieve your matrix result I would put the Colors[Color] column on the rows then create 4 measures to put on the columns which use expressions like the following (creating one measure for each of columns A, B, C and D):
Count A = CALCULATE(countrows(Table1) , TREATAS(values(Colors[Color]), Table1[column A]) )