Forum Discussion

YASHASHRI's avatar
YASHASHRI
Icon for Helper I rankHelper I
2 years ago
Solved

Create slicer based on condition

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, Au...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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.