cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tammyl
New Member

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors