Create slicer Yes / No to filter the grid. -Power BI Desktop

Question

I have a slicer that shows various colour (Red, Blue, Green). I have a grid that shows details for fruits (name, colour, size, taste etc). I want to create a slicer with Yes/ No option for the user, If the user selects "yes" then filter the grid with colour slicer if the user selects "no" then do not filter the grid with colour slicer (show all colour in the grid, irrespective of the selection in the colour slicer). Just like the sync capability but with dynamic Yes/no.

mkRabbani mkRabbani · Accepted Answer · 2020-10-01T04:15:14

Follow these below steps to achieve your required output-

Step-1: Create a custom table as below to create the Yes/No slicer-

yes_no = 
UNION(
    ROW("option","Yes"),
    ROW("option","No")
)

Step-2: Now, you need to separate your colour slicer table as keeping the value in the same table you can not achieve the requirement. You can create custom table named colour with this below code-

considering your base table name fruits

create the custom table colour

No relation can be there between table fruits and colour

colour = 
SELECTCOLUMNS(
    fruits,
    "colour", fruits[colour]
)

Step-3: Create Yes/No and Colour slicer using above tables.

Step-4: Create this following measure in Fruits table-

do_filter = 
 
IF(
    SELECTEDVALUE(yes_no[option]) = "No",
    "No",
    IF(
        MIN(fruits[colour]) IN VALUES(colour[colour]),
        "Yes",
        "No"
    )
)

Here is sample output for different slicer option selection-

Now you just need to apply visual level filter using measure do_filter

Create slicer Yes / No to filter the grid. -Power BI Desktop

1 Answers