DAX Measure to Filter Slicer Based on Selection of Another Slicer

[Anonymous]

Greetings!

I am trying to put together a Scatter Plot that allows the user to select which measure they want to see on the X-Axis (independent variable) and which measure they want to see on the Y-Axis. (dependent variable)

I have done some digging and tried a few things on my own but I can't seem to find a solution.

My objective is to make the slicers dynamic based on what the user selects as the independent variable because not all Y-Axis variables depend on what the selected X-Axis variable is.

Here are my variables:

1. Brokerage
2. Combined EBIT (dependent)
3. Consumer Promo Incentives
4. Demos
5. Fixed OH
6. Freight
7. Gross Margin (dependent)
8. Gross Sales
9. Inventory COGS
10. Net Sales (dependent)
11. Other COGS
12. Slotting
13. Trade
14. Warehouse
15. Variable CM (dependent)

Below are the items that affect each dependent variable:

Net Sales	Gross Margin	Combined EBIT	Variable CM
Gross Sales	Net Sales	Gross Margin	Gross Margin
Trade	Inventory COGS	Brokerage	Slotting
Demos	Other COGS		Consumer Promo Incentives
Slotting	Freight		Fixed OH
Consumer Promo Incentives	Warehouse		Other COGS
			Brokerage

Below are examples of how I want to slicers to behave:

Example 1: if the user selects Net Sales on the Y-Axis slicer, I only want Gross Sales, Trade, Demos, Slotting and Consumer Promo Incentives to be visable on the X-Axis slicer.

Example 2: if the user selects Gross Margin on the Y-Axis slicer, I only want Net Sales, Inventory COGS, Other COGS, Freight, and Warehouse to be visable on the X-Axis slicer.

Example 3: if the user selects Combined EBIT on the Y-Axis slicer, I only want Gross Margin and Brokerage to be visable on the X-Axis slicer.

Example 4: if the user selects Variable CM on the Y-Axis slicer, I only want Gross Margin, Slotting, Consumer Promo Incentives, Fixed OH, Other COGS, and Brokerage to be visable on the X-Axis slicer.

I need assistance to find a solution that would use DAX.

Does anyone have any good ideas on how I can achieve this?

Thank you!

[OwenAuger · Accepted solution]

Hi @Anonymous 

Interesting question 🙂

How are you currently selecting the measures for the two axes?

Two disconnected parameter tables or something else?

My initial thought is that you could create a two-column "VariableCombinations" table containing permitted combinations of Y-Axis and X-Axis measures. Then use the columns of this table on the slicers.

Then each slicer will naturally cross-filter the values available in the other. And if you want the Y-Axis selection to filter the X-Axis slicer but not vice versa, you could edit interactions so that the X-Axis slicer doesn't filter Y-Axis slicer.

Y-Axis	X-Axis
Combined EBIT	Gross Margin
Combined EBIT	Brokerage
Net Sales	Gross Sales
Net Sales	Trade
Net Sales	Demos
Net Sales	Slotting
Net Sales	Consumer Promo Incentives
Gross Margin	Net Sales
Gross Margin	Inventory COGS
Gross Margin	Other COGS
Gross Margin	Freight
Gross Margin	Warehouse
Variable CM	Gross Margin
Variable CM	Slotting
Variable CM	Consumer Promo Incentives
Variable CM	Fixed OH
Variable CM	Other COGS
Variable CM	Brokerage

Alternatively, if you already have tables set up for measure selection, you could "bridge" them with the VariableCombinations table, and have a bidirectional relationship between VariableCombinations and the X-Axis table. The other relationship could also be bidirectional if you want.

Neither of these options involve writing DAX, but should do the job. I generally prefer physical tables/relationships for any sort of filtering logic, for performance and maintenance reasons.

Will this work in the context of your model?

Please post back if you think we need a DAX solution, with more details of how the existing tables are set up. We can certainly write a 0/1 measure to filter the slicers.

Regards,

Owen

[Anonymous]

Yep, that does it!

Here is the result:

Here is the model view:

I input my own measures to calculate the values and everything works!

Thank you so much for your assitance. I apprecaite it!