I need 3 separate slicers to work hierarchically

verinalobo · ‎10-13-2023

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.

v-stephen-msft · ‎10-17-2023

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.

verinalobo · ‎10-13-2023

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?

Syndicate_Admin · ‎10-13-2023

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.