Forum Discussion

patrickmlsk's avatar
patrickmlsk
Frequent Visitor
7 years ago
Solved

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:

 

Spoiler
Dateemailnumbercolumn Acolumn Bcolumn Ccolumn D
01.01.2019[email protected]1BlueGreenYellowBlack
02.01.2019[email protected]2PinkWhiteOrangeGreen
03.01.2019[email protected]3BlackBlueGreenYellow
04.01.2019[email protected]4BlueBlackGreenYellow
05.01.2019[email protected]5WhiteBlackPinkOrange
06.01.2019[email protected]5YellowGreenBlackWhite
07.01.2019[email protected]6BlackYellowBluePink
08.01.2019[email protected]8BlueGreenYellowBlack
09.01.2019[email protected]7PinkWhiteOrangeGreen
10.01.2019[email protected]9BlackBlueGreenYellow
11.01.2019[email protected]1BlueBlackGreenYellow
12.01.2019[email protected]2WhiteBlackPinkOrange
13.01.2019[email protected]3YellowGreenBlackWhite
14.01.2019[email protected]4BlackYellowBluePink
15.01.2019[email protected]5BlueGreenYellowBlack
16.01.2019[email protected]6PinkWhiteOrangeGreen
17.01.2019[email protected]7BlackBlueGreenYellow
18.01.2019[email protected]324BlueBlackGreenYellow
19.01.2019[email protected]3WhiteBlackPinkOrange
20.01.2019[email protected]3YellowGreenBlackWhite
21.01.2019[email protected]5BlackYellowBluePink
22.01.2019[email protected]3BlueGreenYellowBlack
23.01.2019[email protected]3PinkWhiteOrangeGreen
24.01.2019[email protected]3BlackBlueGreenYellow
25.01.2019[email protected]4BlueBlackGreenYellow
26.01.2019[email protected]34WhiteBlackPinkOrange
27.01.2019[email protected]3YellowGreenBlackWhite
28.01.2019[email protected]3BlackYellowBluePink
29.01.2019[email protected]3BlueGreenYellowBlack
30.01.2019[email protected]3PinkWhiteOrangeGreen
31.01.2019[email protected]3BlackBlueGreenYellow
01.02.2019[email protected]4BlueBlackGreenYellow
02.02.2019[email protected]5WhiteBlackPinkOrange
03.02.2019[email protected]3YellowGreenBlackWhite
04.02.2019[email protected]1BlackYellowBluePink

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

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