The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I am trying to use slicers. Rather than using 1 hierarchical slicer, I want 3 separate slicers that will apply to my report. The 3 filters depend on each other. i.e. you first select Slicer 1 - AUT, then Slicer 2 - Env, then Silcer 3- Date from the Summary table below.
If you select an option in Slicer 1, only the options pertaining to the selection in Slicer 1 should be shown. Similarly, if you select an option in Slicer 2, only those pertaining should show in Slicer 3.
The data in Summary table looks like this:
Summary:
RunID | AUT | Env | Date | Summary | ValueXYZ |
100 | CRM | DEV | 01-Aug-23 | Lorem ipsum dolor | 12323 |
101 | CRM | QA | 02-Aug-23 | tempor incididunt ut labore | 123768 |
102 | CRM | DEV | 03-Aug-23 | Lorem ipsum dolor | 324324 |
103 | CRM | QA | 05-Aug-23 | cillum dolore eu fugiat | 43543 |
104 | CRM | QA | 10-Aug-23 | quis nostrud exercitation | 345345 |
105 | OMS | DEV | 11-Aug-23 | officia deserunt | 67868 |
In addition to this Summary table, I have many other tables in my report with main relationship between them being RunID. Examples of the tables:
Transactions
RunID | AUT | Env | Date | Transaction | Pass | Fail |
100 | CRM | DEV | 01-Aug-23 | Login | 10 | 1 |
100 | CRM | DEV | 01-Aug-23 | Search | 10 | 0 |
100 | CRM | DEV | 01-Aug-23 | Order | 9 | 1 |
100 | CRM | DEV | 01-Aug-23 | Logout | 8 | 0 |
101 | CRM | QA | 02-Aug-23 | Login | 10 | 0 |
101 | CRM | QA | 02-Aug-34 | Logout | 9 | 1 |
…etc | CRM |
|
|
|
|
|
Nodes
RunID | AUT | Env | Date | Node | Time | Value |
100 | CRM | DEV | 01-Aug-23 | A1 | 10:00 | 90 |
100 | CRM | DEV | 01-Aug-23 | A1 | 10:01 | 95 |
100 | CRM | DEV | 01-Aug-23 | B1 | 10:00 | 80 |
100 | CRM | DEV | 01-Aug-23 | B1 | 10:01 | 85 |
101 | CRM | QA | 02-Aug-23 | A1 | 11:00 | 76 |
101 | CRM | QA | 02-Aug-34 | B1 | 11:01 | 78 |
…etc | CRM |
|
|
|
|
|
The tables are more complex than this and have many rows of data. There are more tables and they all have common columns RunID, AUT, Env, Date. Merging all this data into a single table is hard as they come from different sources and have many columns and rows.
Now the issue is, when I use 3 slicers in my report and make selections in these slicers, the selections get applied to all 3 slicers rather than hierarchically. I am trying to understand how I can get 3 slicers to work hierarchically. I believe my data is not modelled well enough and my knowledge in PowerBi is basic. But I am trying to figure a solution where I can get the slicers to work as expected without having to remodel my data – if that is possible.
Thanks in advance.
Hi @verinalobo ,
Based on your description, it seems that the issue lies in the data model and the relationships between the tables. To achieve the desired hierarchical behavior of the slicers, you will need to make sure that the relationships between the tables are set up correctly. Here's an action plan to help you resolve this issue:
Check the relationships: Verify that the relationships between the tables are correctly defined. Make sure that the columns used for the relationships (such as RunID, AUT, Env, Date) have the same data type and values in all the tables. Make sure that the filtering direction for the relationship is correct. If it is incorrect, for example, the filter of table A and table B flows from table B to table A, then you use the fields of table A as a slicer to select, which will not affect the results of table B.
Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I tried this, but I cannot get it to work for me. I don't know if I am doing something wrong...
I created a measure:
Filter Slicers = COUNTROWS (Summary)
I added the measure to the filter pane for each slicer and set the value to greater or equals to 1.
Did I do this correctly?
If all three columns you use to segment come from the same table, they'll filter each other. That's the way Power BI works by default, and you can't change it.
If the columns come from different tables and you want them to be filtered when you select a value in one of the filters, you must apply a filter from the filter panel using a measure that indicates that its result is not blank.
For example:
filter measure = CountRows(Table)
Apply the measure in the filter pane of the segmenter and tell it not to be blank in the filter.
@Syndicate_Admin Yes, the 3 are from the same table. That's probably why. Thank you!
Hey @verinalobo ,
Take a look in this link: Solved: Power BI One Slicer to filter other slicers - Microsoft Fabric Community
Regards,
Marcel
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
82 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |