Forum Discussion

HELPME_JFLO's avatar
HELPME_JFLO
New Member
3 years ago

Switch Formula on Multiple Selection on Slicer?

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_GALFILTER('Product Line Filter',[Lowest] = "A"))

var B = CALCULATE(SALES_LBS_ETHFILTER('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!

6 Replies

  • 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's avatar
      HELPME_JFLO
      New Member

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

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

  • Anonymous's avatar
    Anonymous
    Not applicable

    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