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 ] The rows are always differ...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

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

     

d_gosbell's avatar
d_gosbell
Super User
7 years ago

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