Filter table data based on Slicer selection

Hello ,

I have a question on applying filter based on Slicer selection. I am using direct connection to connect a database.

Q : we have Slicer Table as an example which will display in power BI Slicer.

Slicer Header

--------------

Status 1

Status 2

Status 3

And I have a main data table with actual dimentions and measures . I am displying this data in a table and Column chart is Power BI.

ID Quantity Status1 Status2 Status3

100 12.2 Y N N

101 9.6 N Y Y

102 10.3 N Y N

104 19 N N N

105 18 Y Y N

If Slicer selection is "Status 1" I want to apply filter as Sttaus1=Y, elseif "Staus2" then Status2=N,elsefif "Status 3" then Status3="Y"

And I have to use this filter on Table and Bar chart visuals . I cannot create any relation between these two tables .

Can you post solution if we have any in Power BI.

Thanks in advance!

v-jingzhang
Community Support
4 years ago
Hi Anonymous

You can create the following measure and use it as a filter on the visual. Set it to show items when the value is 1. If you don't select any status, it will show all.

Filter Flag = SWITCH(SELECTEDVALUE(Slicer[Status]), "Status 1",IF(SELECTEDVALUE('Table (2)'[Status1])="Y",1,0), "Status 2",IF(SELECTEDVALUE('Table (2)'[Status2])="Y",1,0), "Status 3",IF(SELECTEDVALUE('Table (2)'[Status3])="Y",1,0), 1)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
0720_Filter table data based on Slicer selection.pbix32 KB
tamerj1
Community Champion
4 years ago
Hi Anonymous
are Status 1, 2 & 3 columns in your source data or attributes of one column? Is the slicer table connected with main table using a relationship?
amitchandak
Super User
4 years ago
Anonymous , Try like

var _filter = Switch(Selectedvalue(Status[Status]),
"Status1" , filter(Table, Table[Status1] = "Y"),
"Status2" , filter(Table, Table[Status2] = "Y"),
"Status3" , filter(Table, Table[Status3] = "Y") )
return
calculate( countrows(Table), _filter)

Forum Discussion