Filter multiple columns with one column

[patrickmlsk]

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:

Spoiler

Date	email	number	column A	column B	column C	column D
01.01.2019	test@test.com	1	Blue	Green	Yellow	Black
02.01.2019	test@test.com	2	Pink	White	Orange	Green
03.01.2019	test@test.com	3	Black	Blue	Green	Yellow
04.01.2019	test@test.com	4	Blue	Black	Green	Yellow
05.01.2019	test@test.com	5	White	Black	Pink	Orange
06.01.2019	test@test.com	5	Yellow	Green	Black	White
07.01.2019	test@test.com	6	Black	Yellow	Blue	Pink
08.01.2019	test@test.com	8	Blue	Green	Yellow	Black
09.01.2019	test@gmail.com	7	Pink	White	Orange	Green
10.01.2019	test@gmail.com	9	Black	Blue	Green	Yellow
11.01.2019	test@gmail.com	1	Blue	Black	Green	Yellow
12.01.2019	test@gmail.com	2	White	Black	Pink	Orange
13.01.2019	test@gmail.com	3	Yellow	Green	Black	White
14.01.2019	test@gmail.com	4	Black	Yellow	Blue	Pink
15.01.2019	test@gmail.com	5	Blue	Green	Yellow	Black
16.01.2019	test@gmail.com	6	Pink	White	Orange	Green
17.01.2019	test@gmail.com	7	Black	Blue	Green	Yellow
18.01.2019	test@gmail.com	324	Blue	Black	Green	Yellow
19.01.2019	test@gmail.com	3	White	Black	Pink	Orange
20.01.2019	test@gmail.com	3	Yellow	Green	Black	White
21.01.2019	test@gmail.com	5	Black	Yellow	Blue	Pink
22.01.2019	test@gmail.com	3	Blue	Green	Yellow	Black
23.01.2019	test@gmail.com	3	Pink	White	Orange	Green
24.01.2019	test@gmail.com	3	Black	Blue	Green	Yellow
25.01.2019	testtest@gmail.com	4	Blue	Black	Green	Yellow
26.01.2019	testtest@gmail.com	34	White	Black	Pink	Orange
27.01.2019	testtest@gmail.com	3	Yellow	Green	Black	White
28.01.2019	testtest@gmail.com	3	Black	Yellow	Blue	Pink
29.01.2019	testtest@gmail.com	3	Blue	Green	Yellow	Black
30.01.2019	testtest@gmail.com	3	Pink	White	Orange	Green
31.01.2019	testtest@gmail.com	3	Black	Blue	Green	Yellow
01.02.2019	testtest@gmail.com	4	Blue	Black	Green	Yellow
02.02.2019	testtest@gmail.com	5	White	Black	Pink	Orange
03.02.2019	testtest@gmail.com	3	Yellow	Green	Black	White
04.02.2019	testtest@gmail.com	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!

[d_gosbell · Accepted solution]

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])
)