Switch Formula on Multiple Selection on Slicer?

[HELPME_JFLO]

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!

[Anonymous]

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

[tamerj1]

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]
)
)

[HELPME_JFLO]

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

[tamerj1]

@HELPME_JFLO 

Can you please provide some screenshots?

[FreemanZ]

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.

[HELPME_JFLO]

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