Slicer to change reference column based on choice in another slicer...

[Matty]

Hi All,

I have a situation where I would like to have a slicer to reference 1 of 2 columns depending on what has chosen via another slicer.

Here's a simple example to try to explain...

DimTable as follows:

Fruit	Hierarchy 1	Hierarchy 2
Apple	X	X
Pear	Y	W
Orange	Z	W

FactTable as follows:

Choice	Fruit	Sales
A	Apple	10
A	Pear	5
A	Orange	15
B	Apple	20
B	Pear	15
B	Orange	5

These tables are linked via a relationship on 'Fruit'.

Slicer 1 is based on the 'Choice' column in the Fact table.  Slicer 2 is based on the 'Fruit' column in the Dim table.  What I would like to be able to do is if slicer 1 is set to A, then 'Hierarchy 1' on the Dim table should be referenced by the slicer.  But if slicer 1 is set to B, then 'Hierarchy 2' on the Dim table should be referenced by the slicer.

Hope this makes sense and someone can offer a way of doing this.

Cheers,

Matty

[Anonymous · Accepted solution]

Hey @Matty

I made a video about something very similar to this on my Youtube channel. Check out this video and let me know if it helps your situation.

https://youtu.be/k_Z1Uu5y9l8

Hope this helps,

Parker

[Matty]

Hi Parker,

Thanks for the link - great trick and video!

I will give it a try with my report tomorrow and I'll let you know how I get on.

Thanks again.

Matty

[Seward12533]

@Anonymousvery cool! One suggestion is to use SELECTEDVALUE function vs FIRSTNONBLANK which has the benfit of returning NULL fi more than one or no value is selected.