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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Setting the values of a slicer based on the selection of another slicer

Hello All, 

 

I'm pretty new in Power BI, so I'm not sure if what I ask is possible (due to the limitations of measures and slicers).

I have the following tables:

 

                      Filter_Values:

 Screenshot_1.png

 

 

                       Source:

Screenshot_3.png

 

 So I want to have two slicer, one with FIlter_Values as source:

Screenshot_4.png

And the second one with values depending on what column is selected in the first slicer, First_Column or Second_Column from Source table.

Then, I want to have a metric with some calculation based on Number_Value from Source table - for example SUM.  

Is there an option this Sum to change it's values based on the selections of the filters, where one depends on the other?

So, one scenario would be: I select Second_Column from the first slicer, then ValueB2 from the second, and the SUM metric should be 11.

 

Any help would be greatly appreciated.

7 REPLIES 7
Michiel
Resolver III
Resolver III

It's not possible to have a slicer dynamically take values from different columns. What you could do, is to have all the values in the Source table in separate rows (i.e. a row with ValueA1 and a row with ValueB1) and add a column indicating whether the row corresponds with First_Column or Second_Column. This column can be linked to the Filter_Values table using a relationship. Now, selecting a column in a slicer filters the Source table to only the values corresponding to the selected column.

Anonymous
Not applicable

@Michiel, I thought about this solution, but the downside is that the number of rows in the Source table is doubled (all rows from First_Column and Second_Column should be added). So, in the first Source there are 3 rows and with this solution there are 6.. if you add one more category(column), the number will be tripled. I think that it's not a best practice if I work with huge datasets..

 

Anyway, thanks for the prompt responce.

You are right in that you will have more rows, but keep in mind that the Power BI data model is a column store database which is very good at compressing and working with data in columns. Especially when the number of different values is low. It would probably all depend on what and how many other columns you have.

Hi @Anonymous,

I agree with Michiel.

To make the scenario that filter slicer based on the another slicer work, you would need to create the source table as shown in the following screenshot, then create a relationship between the Source table and Filter_Values table using Column_Selection field.
1.PNG



This way, you are able to create a slicer using Column field of Source table and create another slicer using Column_value field from Filter_Values table. For more details, please review the example in this attached PBIX file.
3.PNG

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This method is useful only few records are there in data. Suppose if table has thousands of record then multiplication of records doesn't make sense.

Well, the problem is mainly in the slicer acommodating so many values. The model doesn't have any trouble filtering a couple thousands of rows. You could argue whether having thousands of rows in a slicer makes any sense; on the other hand, with search and drop-down features it would work.

I've seen slicers with as much as 25000 values, it works but it becomes quite slow. 

Deepa
Frequent Visitor

I want to bind values for second slicer based on first slicer.  My data is as in below table, In first slicer I am displaying Filter By types and in second slicer I have display the values based on filter by type. For example if i select city in first slicer(Filter BY slicer), I need to display all the cities in second slicer(Values Slicer). Capture.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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