Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
tammyl
Regular Visitor

Create Dynamic Filtering based on slicer selection

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?

3 REPLIES 3
Anonymous
Not applicable

What does it mean "switches the column"? You cannot dynamically switch a column to be used as an axis for your visual (as I guess this is what you attempt to do). If you want to change values on an axis dynamically you have to create a column that stores all your values from the slicer and then correctly joins to other tables. Easy as that.

The slicer options all reference different column for filtering so i want the filtering to be based on different column (e.g. When Email is selected, i want the page to filter on Email column =1, when SMS is selected in the slicer, i want the page to filter on SMS column = 1, etc). Another way I thought of was just create another column filter with the following condition:

If the selected value of the slicer is SMS then assign 1 when SMS column in the table is 1 else 0

If the selected value from the slicer is Email then assign 1 when Email column in the table is 1 else 0

If the selected value from the slicer is Email & SMS then assign 1 when Email and SMS column in the table is 1 else 0

If the selected value from the slicer is All then assign 1

 

Once i have the calculated column, i wanted to add it as a page filter and filter to 1. The trouble I am having is referencing the selected value in the calculated column.

harshnathani
Community Champion
Community Champion

Hi @tammyl ,

 

See if this helps.

 

http://www.maxwikstrom.se/powerbi/how-to-add-total-values-in-bar-and-line-charts-in-power-bi/

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors