Using a slicer to control another slicer

[mneumann]

Wondering if anyone has run into this before and may have a solution.

All of our products are sold through intermediaries. The State the product was sold to/in may be different than the state when the intermediary resides. I can have a CA customer selling items to any of the 50 states.

Sales reps are assigned to different states, they want to know both the products sold in their state and the products sold by the customers that are based in their state.

So we have a data table with all products which includes the state sold to, and we have a lookup table that looks up the intermediary that sells each item and also gives me their state.

In this particular table the end users want to be able to filter by slicer by both state sold in and state intermediary resides in. So my Colorado rep wants to know all the products sold to Colorado by intermediaries nationwide as well as all products sold by Colorado intermediaries to all states including CO.

They want this all on one table, and they don't want 2 seperate state slicers listing all 50 states, they want a slicer for states, and one for "Intermediary Home State/Product Sold State" that tells the state slicer which view to give them.

I've been racking in my head how this may be possible. I'm thinking the only option is to create another lookup table with the 50 states and use that to create my state slicer and then somehow using some sort of userelationship-esque function on the state slicer based on the selection on the "Intermediary Home State/Product Sold State" slicer. Is this even possible to anyone's knowledge? Maybe through VBA (i'm a VBA lightweight but trying to learn).

My solution to this point is that if they don't want 2 slicers then I'm going to give them 2 tables.

Thanks for any advice. Really appreciate this forum and all the contributors here I've done a lot of reading that's gotten me through a lot of issues but this is the first one I just can't solve on my own yet.

 

[scottsen]

I typed up a response, but then... found myself confused by my answer

Total Sales := Products[Dollars]

If you have a relationship beween State and Products... then Total Sales will happily respond to the slicer... but obviously just on 1 relationship, say, Products[FinalShippingState]

So, you create a 2nd relationship between Products[ResellerState] (which will be inactive), then write a measure to use it:

Total Sales by Reseller State := CALCULATE([Total Sales], USERELATIONSHIP(Products[ResellerState], States[State]))

I .. uh... think the 1 slicer will properly control both measures (make sure slicer States are coming from the States table of course).

I think!?

 

[mneumann]

Thanks for the reply Scott. That does work and control both measures.

Of course after I showed that to them...

Thats not gonna cut it apparently. They want one measure, that dynamically changes based on the selection ( I partly understand, as we are already at a 9 column table, and turning it into an 18 column table would cause issues with on screen views and 1 page prints).

So after 4 days of back and forth on other things an answer finally seems to have popped into my head. Here's what I did in case anyone finds this in the future and wants to copy.

Step 1: Create a State Table with all 50 states. Primary Relationship from Products[product state], secondary relationship from Resellers[Reseller State]
Step 2: Create Disconnected Table State Selection with 2 colums, ID 1 and 2, Selection Reseller State and Product State
Step 3: Create Slicer using the disconnected Table giving user 2 options to choose from, Reseller State and Product State
Step 4: State Slicer now comes from State Table
Step 5: Measure for Selection on the state selection Slicer [state selection]=if(max('State Selection'[ID])=1,"Reseller State","Product State"). Returns Product State by Default and Reseller State if that is the only slicer button Selected
Step 6: All measures now have to be wrapped as =if([state selection]="Reseller state",*measure formula*,calculate(*measure formula*),userelationship(Resellers[reseller state],State[state]))

I have to admit this is extremely complicated vs just giving them 2 tables and I'm not sure how performance will suffer with all these if statements in my measures but when this is how the guy that writes the checks wants it then sometimes theres no other option.