Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Source:
So I want to have two slicer, one with FIlter_Values as source:
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.
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.
@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.
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.
Thanks,
Lydia Zhang
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.
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).
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |