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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors
Top Kudoed Authors