Slicer based on a measure

[PS_78]

Hello – I have a requirement to use values (type - string) from a measure in Slicer. As it is not feasible to use measures, in a slicer, I followed the suggestions in other community posts by creating a summarized table. But this wouldn’t work in my scenario**. I will try my best to explain the requirement. Please check below and help if we have a way to achieve this

I have a dataset with transactional data (TDATA) which has cost center as one of the attributes

Cost Center	Cost Center Name
CC1	1st CC
CC2	2nd CC
CC3	3rd CC
CC4	4th CC

Another table (Cost Center Master) with Hierarchy 1 and Hierarchy 2

Cost Center	Hier_1	Hier_2
CC1	H1_1	H1_2
CC2	H1_1	H1_2
CC3	H1_2	H1_1
CC4	H1_2	H1_1

I would like to create a slicer (1st slicer) with values “Hier_1” and “Hier_2” and have the values in another slicer (2nd slicer) updated automatically. For which

1. I have created a table in DAX with these two values (Hier_1 and Hier_2) --> Used it in 1st slicer
2. Created a measure (CC_Grp) to populate hierarchy from either Hier_1 or Hier_2. I have used SELECTEDVALUE and LOOKUPVALUE based on the value selected in 1st slicer and derived respective Hierarchy value from Cost Center Master Table --> It works fine if I just use this measure in a table visual. But I want to use it in a slicer (2nd slicer) and have the values updated based on what I select
   1. If I chose “Hier_1” on the 1st slicer, the 2nd slicer should show the cost center names grouped per Hierarchy selected in 1st slicer
   2. For example,
   3. if I chose “Hier_1” in 1st slicer, 2nd slicer should show values as below

4. If I chose “Hier_2” in 1st slicer, 2nd slicer should show values as below

5. Subsequent matrix visual would filter based on what is selected in Slicer 2

** Summarized table is not getting updated based on 1st slicer. Summarized table has Cost Center #, Cost Center # and Cost Center Group

Thanks in Advance.

- PS

[PS_78 · Accepted solution]

To avoid further complications, I have created two columns in the original dataset one each to store different hierarchies and used these new columns in two different slicers.

Thanks,

PS

[v-hashadapu]

Hi @PS_78 , Can we please know if your issue has been resolved. If not, can you please share the details.
If it is solved, Can you please mark it 'Accept as Solution'.

[johnt75]

You could create a new table like

Hierarchy = 
SELECTCOLUMNS(
	GENERATE(
		'Table',
		UNION(
			ROW(
				"Hierarchy Level", "Hier_1",
				"Hierarchy Value", 'Table'[Hier_1]
			),
			ROW(
				"Hierarchy Level", "Hier_2",
				"Hierarchy Value", 'Table'[Hier_2]
			)
		)
	),
	"Cost Center", 'Table'[Cost Center],
	[Hierarchy Level],
	[Hierarchy Value]
)

Then create 2 slicers, the first using the Hierarchy Level column and the second using the Hierarchy Value and Cost Center columns.

You would need to create a many-to-many bidirectional relationship to the table with the cost center names.

[v-hashadapu]

Hi @PS_78 ,Thank you for reaching out to Microsoft Fabric Community Forum.

I have worked with the sample data you provided and explored several approaches to achieve the expected output. Unfortunately, it seems that the desired result is not attainable with the current data. I would kindly suggest reviewing the sample data and the reasoning behind the expected output. If you would still like to proceed with the same result, could you please provide an updated or improved dataset?

If you think this post helps, please mark it as Accept as Solution, so others with similar queries may find it more easily.