cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

3 REPLIES 3
Anonymous
Not applicable

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

Helper II

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

Solution Sage

@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.