topic Re: Switch Formula on Multiple Selection on Slicer? in DAX Commands and Tips

Switch Formula on Multiple Selection on Slicer?

HELPME_JFLO — Sun, 09 Apr 2023 03:36:47 GMT

Hi All,

I have a what I think, a pretty simple model/ask I am asking of Power BI (I think), and still can't figure out what I'm doing wrong.

I have one fact table and a product line (category) dimension table.

My current code is this:

VAR selection_lowest = SELECTEDVALUE ( 'Product Line Filter'[Lowest] )

var SALES_GAL = [9775 - SALES_GAL]

var SALES_LBS = [9380 - SALES_LBS]

var SALES_LBS_ETH = [16034 - SALES_LBS_ETHYLENE]

var A = CALCULATE(SALES_GAL, FILTER('Product Line Filter',[Lowest] = "A"))

var B = CALCULATE(SALES_LBS_ETH, FILTER('Product Line Filter',[Lowest] = "B"))

return

switch(

true(),

selection_lowest = "A", A,

selection_lowest = "B", B,

selection_lowest in {"A","B"}, A + B,

0 )

----------------------------

Context: Essentially I have a slicer (Product Line Lowest), that works for "A" and "B" on their own levels, however, when both selected they do not sum together as expected, but instead return a 0.

Any help or direction would be appreciated!

Re: Switch Formula on Multiple Selection on Slicer?

FreemanZ — Sun, 09 Apr 2023 06:25:27 GMT

hi HELPME_JFLO

at least first try like:

VAR selection_lowest = SELECTEDVALUE ( 'Product Line Filter'[Lowest] )

//var SALES_GAL = [9775 - SALES_GAL]

//var SALES_LBS = [9380 - SALES_LBS]

//var SALES_LBS_ETH = [16034 - SALES_LBS_ETHYLENE]

var A = CALCULATE([9775 - SALES_GAL], FILTER('Product Line Filter',[Lowest] = "A"))

var B = CALCULATE([16034 - SALES_LBS_ETHYLENE], FILTER('Product Line Filter',[Lowest] = "B"))

return

switch(

true(),

selection_lowest = "A", A,

selection_lowest = "B", B,

selection_lowest in {"A","B"}, A + B,

0 )

Hard to believe, but variables in DAX are actually constant values.

Re: Switch Formula on Multiple Selection on Slicer?

tamerj1 — Sun, 09 Apr 2023 07:48:14 GMT

Hi HELPME_JFLO

please try

=
SUMX (
VALUES ( 'Product Line Filter'[Lowest] ),
SWITCH (
'Product Line Filter'[Lowest],
"A", [9775 - SALES_GAL],
"B", [16034 - SALES_LBS_ETHYLENE]
)
)

Re: Switch Formula on Multiple Selection on Slicer?

HELPME_JFLO — Sun, 09 Apr 2023 16:08:13 GMT

Hey thanks for trying, but it still produced the same output unfortunately.

Re: Switch Formula on Multiple Selection on Slicer?

HELPME_JFLO — Sun, 09 Apr 2023 16:08:57 GMT

Thanks for trying, but regardless of the variables it still produced the same output as original 😞

Re: Switch Formula on Multiple Selection on Slicer?

tamerj1 — Sun, 09 Apr 2023 17:50:55 GMT

HELPME_JFLO

Can you please provide some screenshots?

Re: Switch Formula on Multiple Selection on Slicer?

Anonymous — Mon, 01 May 2023 12:54:57 GMT

You could try using SELECTEDVALUE().

Var A=....
Var B=...

Return SWITCH(SELECTEDVALUE(Selection_lowest),

"A", A,

"B", B,

A+B)

If you choose distinct filter choices, you get the respective measures back. But once you choose more than one filter, you will get the alternate result (in this case A+B). https://learn.microsoft.com/en-us/dax/best-practices/dax-selectedvalue