cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Matty
Helper II
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:

 

 

FruitHierarchy 1Hierarchy 2
AppleXX
PearYW
OrangeZW

 

FactTable as follows:

 

ChoiceFruitSales
AApple10
APear5
AOrange15
BApple20
BPear15
BOrange5

 

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

View solution in original post

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

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

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

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

Ted's Dev Camp - October 6, 2022

Watch the playback of Session 26 with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors