Create slicer based on condition

[YASHASHRI]

I have three columns I.e Autopilot, Copilot, autopilot knowledge.
These 3 columns are created on certain conditions.
It consist of options like Y and N.
So in the table I have opp name, opp ID, Autopilot, Copilot, autopilot knowledge.
So scenarios could be many like Opp ID 111 can be flagged as Y in autopilot column and N in autopilot knowledge and N in copilot.
OPP 222 can be flagged as N in autopilot and Y in autopilot knowledge and Y in copilot. There can be many other scenarios.  

Now, create a multi-select slicer, with 4 values:

1. Autopilot
2. Copilot
3. Knowledge
4. None

   If you select 'Autopilot' it will present all deals with 'Autopilot', regardless the other options

   If you select 'Autopilot' and 'Copilot' it will present all deals with Autopilot OR Copilot

   it doesn't matter if the deal has Copilot or Autopilot or both. It just present the deals that have at least one of the options

[Anonymous · Accepted solution]

Hi @YASHASHRI 

Please try this:

First of all, I create a set of sample:

Then add a new table:

Table 2 = {"autopilot","Copilot","autopilot knowledge","none"}

Finally, add a new measure:

MEASURE =
VAR _NewValue =
    SELECTEDVALUE ( 'Table 2'[Value] )
RETURN
    SWITCH (
        _NewValue,
        "autopilot", CALCULATE ( SELECTEDVALUE ( 'Table'[opp ID] ), 'Table'[autopilot] = "Y" ),
        "autopilot knowledge",
            CALCULATE (
                SELECTEDVALUE ( 'Table'[opp ID] ),
                'Table'[autopilot knowledge] = "Y"
            ),
        "Copilot", CALCULATE ( SELECTEDVALUE ( 'Table'[opp ID] ), 'Table'[Coilot] = "Y" ),
        "None",
            CALCULATE (
                SELECTEDVALUE ( 'Table'[opp ID] ),
                'Table'[autopilot] = "N"
                    && 'Table'[autopilot knowledge] = "N"
                    && 'Table'[Coilot] = "N"
            )
    )

Add a slicer and a matrix:

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[123abc]

To create the multi-select slicer as described, you'll first need to create a calculated column in your table that evaluates the conditions for each deal. Then, you can use this calculated column to filter your data based on the slicer selection. Here's how you can do it step by step:

1. Create a Calculated Column: Create a calculated column named "Slicer Filter" with the following DAX formula:

Slicer Filter =
SWITCH (
TRUE (),
'Table'[Autopilot] = "Y" && 'Table'[Copilot] = "Y", "Autopilot/Copilot",
'Table'[Autopilot] = "Y", "Autopilot",
'Table'[Copilot] = "Y", "Copilot",
'Table'[Autopilot Knowledge] = "Y", "Knowledge",
"None"
)

1. This formula evaluates the conditions for each row and returns the appropriate value for the slicer.

2. Create the Slicer: In Power BI, go to the "Visualization" pane, click on "Slicer" under "Visualizations", and drag the "Slicer Filter" column to the "Values" field of the slicer.

3. Enable Multi-Select: Click on the slicer visualization, and in the "Visualizations" pane, under the "Format" section, find the "Selection Controls" option. Enable "Multi-select with Ctrl" or "Multi-select with Shift", depending on your preference.

4. Filter the Data: Once you've created the slicer, you can select one or multiple values from it. Based on your selection, the table will filter accordingly.

Now, when you select "Autopilot", it will show all deals with "Autopilot". If you select both "Autopilot" and "Copilot", it will show deals with either "Autopilot" or "Copilot" or both. Similarly, selecting other options will filter the data accordingly. If you select "None", it will show deals that don't meet any of the conditions.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.