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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.