I am having trouble applying a filter that switches the column used for the filter based on the selected slicer value. I have a table that has the following 3 columns with a value of 1 or 0. The 3 columns are "Email", "SMS", and "Email and SMS". Note: The records where SMS=1 can overlap with records where Email and SMS =1 because the "Email and SMS" column is basically records where SMS=1 and Email=1. I want to have a slicer with the following options: "All", "SMS", "Email", and "Email & SMS". Based on the user's selection, I want the page to be filtered based on the specific column. For example, when the user selects "SMS" in the slicer, I want the page to be filtered to column SMS=1. When the user selects "Email" in the slicer, I want the page to be filtered to column Email=1. When the user selects "Email & SMS", I want the page to be filtered to Email and SMS=1. Finally when the user selectes "All" in the slicer, then no filter is applied.
I wanted to create a calculated column that takes the selected value from the slicer and then using an if statement to assign a value of 1 or 0 on what records to include but I read online that selectedvalue() does not work with calculated column.
In Spotfire, I usually just create a Property Control drop down list that assigns a value to each option in the drop down list when selected. I can then use the value from the selected option in the drop down list to create a calculated field as a filter to be applied to my visualisations.
Does PowerBI have anything similar to Spotfire or what options do I have to achieve what I want to do?
Is there a solution for creating a dynamic filter?