Filter table column on multiple conditions using slicer

[powerbisach]

I have a table with text values in all the four columns. I need to filter the table column depending on slicer selection even if it satisfies on condition of all the columns. Refer screenshot

Please note i have 15 other columns and around 900 records in the data table. Appreciate your help.

[v-huizhn-msft]

Hi @powerbisach,

Until now, the slicer is used to filter rows in table, we can't filter columns. Please review the following feature request and vote them. 

Multi-Columns Slicer
Filtering/slicer by Columns

Best Regards,
Angelia

[powerbisach]

Hi v-huizhn-msft

Sorry for communicating my issue in wrong way.

Basically i would like to display all rows who satisfies any one of the condition.

Example = If i select "a" in slicer i should be able to display all rows which contains "a"

[v-huizhn-msft]

Hi @powerbisach,

All right, I got it. But the slicer filter the rows based on the value in the column added in slicer. You want to return all rows contain the selected value in different columns, it's impossible now.

Best Regards,
Angelia

[powerbisach]

Any workaround to acheive the expected result..Any other approach/solution.

Thanks in advance

[DAX0110]

One workaround I can think of is to "unpivot" the table, so that all the values you want to search are in the same column :

(1) add an identifier column to each row (so that you can tell which row/s contain a match)

(2) unpivot all the columns you want to search

(3) add a slicer on the "Value" column

The original table you had was like this:

Column1  Column2  Column3  Column4

  a                b               c               s

  b                a               a               y

  d                a               b              c

  g                c                c              d

And the new table should look like this:

RowID      Column    Value

row1         Column1      a

row1         Column2      b

row1         Column3      c

row1         Column4      s

row2         Column1      b

row2         Column2      a

row2         Column3      a

row2         Column4      y

row3         Column1      d

row3         Column2      a

row3         Column3      b

row3         Column4      c

row4         Column1      g

row4         Column2      c

row4         Column3      c

row4         Column4      d

If you put a slicer on the Value column, you will be able to find out which row and which column contains a match.  The output won't be in the form of your original rows, but it's a workaround that will support searching.

[v-huizhn-msft]

Hi @powerbisach,

If my solution is acceptable, please mark it as right answer, so that more members like you will find workaround easily.

Thanks,
Angelia

[v-huizhn-msft · Accepted solution]

Hi @powerbisach,

After research, please create a new table including distinct all values from four columns. You can get a result which is similar with but not the same. Please see the result shown in the following screenshot.



Please download the .pbix file(attachment) for more details.

Best Regards,
Angelia

[powerbisach]

Hi v-huizhn-msft

I did tried Union only before posting the question but i was not able to filter the table. Thanks for your solution.

Is there a way to hide/remove "filtered columns measures"? If i remove these measures from table, i am not able to filter the table.