Forum Discussion

gomezc73's avatar
gomezc73
Helper V
4 years ago

Use Switch with other condition

Hi,

 

  I need help with this issue.

 

  I have a measure to choose the amount according to the month selected

  

Amount_Selected = (
SWITCH(
SELECTEDVALUE(Slicer_Table[Month]]),
"January", SUM(F0902[JAN])*0.001,
"February", SUM(F0902[FEB])*0.001,
"March", SUM(F0902[MAR])*0.001,
"April", SUM(F0902[APR])*0.001,
"May", SUM(F0902[MAY])*0.001,
"June", SUM(F0902[JUN])*0.001,
"July", SUM(F0902[JUL])*0.001,
"August", SUM(F0902[AUG])*0.001,
"September", SUM(F0902[SEP])*0.001,
"October", SUM(F0902[OCT])*0.001,
"November", SUM(F0902[NOV])*0.001,
"December", SUM(F0902[DEC])*0.001))
 
It worked fine.
 
Now, I have a table called Categories 
  
ColorValue
REDA01
REDA02
REDA03
BlackB01
BlackB02
etc 
 
In each Month I need validate first  the Color before summarize.
By Example,
    
SWITCH(
SELECTEDVALUE(Slicer_Table[Mes_Largo]),
"January", SUM(IF(Categories[Color]="RED",F0902[JAN],0))*0.001,
"February", SUM(IF(Categories[Color]="RED",F0902[FEB],0))*0.001,
.
.
etc.
 
 Is it possible?.. I've tried but I can't find a formula that help me...
 
Thank you very much!!
Need Help

8 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    Hi gomezc73 ,

     

    Try the following:

     

     

    SWITCH( true(),
SELECTEDVALUE(Slicer_Table[Mes_Largo]) ="January" && SELECTEDVALUE(Categories[Color])="RED"
, SUM(F0902[JAN],0))*0.001,
SELECTEDVALUE(Slicer_Table[Mes_Largo]) ="February" && SELECTEDVALUE(Categories[Color])="RED", SUM(IF(Categories[Color]="RED",F0902[FEB],0))*0.001,
...
    • gomezc73's avatar
      gomezc73
      Helper V
      4 years ago

      Thank you for your help, but the command generate an error:

       

      This is the command:

      TEst = SWITCH( true(),
      SELECTEDVALUE(Slicer_Table[Mes_Largo]) ="January" && SELECTEDVALUE(F0902[BU_CAT06])="MAR"
      , SUM(F0902[JAN],0))*0.001,
      SELECTEDVALUE(Slicer_Table[Mes_Largo]) ="February" && SELECTEDVALUE(F0902[BU_CAT06])="MAR"
      , SUM(F0902[JAN],0))*0.001)
       
      This the error Error:

      "The Synatx for ',' is incorrect. (Dax(Swith(true(),Selecttedvalue...

      • MFelix's avatar
        MFelix
        Super User
        4 years ago

        Hi gomezc73 ,

         

        My bad did not clean your measure on the SUM it had more than one parameter, should be like this:

         

        TEst =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Slicer_Table[Mes_Largo] ) = "January"
        && SELECTEDVALUE ( F0902[BU_CAT06] ) = "MAR", SUM ( F0902[JAN] ) * 0.001,
    SELECTEDVALUE ( Slicer_Table[Mes_Largo] ) = "February"
        && SELECTEDVALUE ( F0902[BU_CAT06] ) = "MAR", SUM ( F0902[JAN] ) * 0.001
)
  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    You should be able to put the color validation outside of the SWITCH.

     

    This might work for you:

     

    Amount_Selected =
CALCULATE (
    SWITCH (
        SELECTEDVALUE ( Slicer_Table[Month] ),
        "January",   SUM ( F0902[JAN] ),
        "February",  SUM ( F0902[FEB] ),
        "March",     SUM ( F0902[MAR] ),
        "April",     SUM ( F0902[APR] ),
        "May",       SUM ( F0902[MAY] ),
        "June",      SUM ( F0902[JUN] ),
        "July",      SUM ( F0902[JUL] ),
        "August",    SUM ( F0902[AUG] ),
        "September", SUM ( F0902[SEP] ),
        "October",   SUM ( F0902[OCT] ),
        "November",  SUM ( F0902[NOV] ),
        "December",  SUM ( F0902[DEC] )
    ) * 0.001,
    Categories[Color] = "RED"
)

     

    Or you could try this

     

    Amount_Selected =
IF (
	SELECTEDVALUE ( Categories[Color] ) = "RED",
    SWITCH (
        SELECTEDVALUE ( Slicer_Table[Month] ),
        "January",   SUM ( F0902[JAN] ),
        "February",  SUM ( F0902[FEB] ),
        "March",     SUM ( F0902[MAR] ),
        "April",     SUM ( F0902[APR] ),
        "May",       SUM ( F0902[MAY] ),
        "June",      SUM ( F0902[JUN] ),
        "July",      SUM ( F0902[JUL] ),
        "August",    SUM ( F0902[AUG] ),
        "September", SUM ( F0902[SEP] ),
        "October",   SUM ( F0902[OCT] ),
        "November",  SUM ( F0902[NOV] ),
        "December",  SUM ( F0902[DEC] )
    ) * 0.001,
    0
)

     

    The best solution, however, would likely be to avoid this switching entirely by unpivoting your month columns.

     

    • gomezc73's avatar
      gomezc73
      Helper V
      4 years ago

      Thank you for your help, but I can't use it, because in each month I need different validation..

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        In that case, you can just clean up the syntax MFelix suggested

        Test =
VAR _Mes   = SELECTEDVALUE ( Slicer_Table[Mes_Largo] )
VAR _Color = SELECTEDVALUE ( F0902[BU_CAT06] )
RETURN
    SWITCH (
        TRUE (),
        _Mes = "January"  && _Color = "RED",   SUM ( F0902[JAN] ) * 0.001,
        _Mes = "February" && _Color = "BLUE",  SUM ( F0902[FEB] ) * 0.001,
        _Mes = "March"    && _Color = "GREEN", SUM ( F0902[MAR] ) * 0.001,
        ETC
    )