Forum Discussion

Txtcher's avatar
Txtcher
Helper V
1 year ago
Solved

Help With SWITCH - d/n support comparing values...

I was using nested IFs for a calculated column creation. It was not working correctly. After doing a bit of research, I came across the SWITCH function, but I get an error stating SWITCH does not sup...
Need Help
  • speedramps's avatar
    speedramps
    1 year ago

    Here are valid examples of SWITCH syntax

    test1 = 
var myvalue = 6
RETURN
SWITCH(TRUE,
myvalue = 5, "Five",
myvalue = 6, "Six",
myvalue = 8, "Eight",
"other")

     

    test2 = 
var myvalue = "S"
RETURN
SWITCH(TRUE,
myvalue = "B", "Brazil",
myvalue = "S", "Spain",
myvalue = "I", "India",
"other")

     

    This type of sysntax is invalid SWITCH need a scalar data type (a single value).
    The RS case [Status] is a table data type

    Test1 = 
SWITCH(TRUE,
'RS case'[Status]  = "OPEN", "Yes",
"No"
)

     

    You can use MIN or MAX  return a scalar value but take care with totals. See example

    Test2 = 
SWITCH(TRUE,
MAX('RS case'[Status])  = "OPEN", "Yes",
"No"
)

     

    Consider using VAR variables to make your syntax easier to read and test 

    For example

    var mylookup = LOOKUPVALUE('RS Events'[Survey_Date__c],'RS Events'[Id], 'RS Cases'[RS Event ID])
var mycase = MIN('RS Cases'[Status])
var myduedate = MIN(RS Cases'[Due By])

SWITCH( TRUE,
    // if survey date null & status = Open and Due By is less than today's date, then date dif due by and today's date
    ISBLANK(mylookup)
&& myduedate="OPEN" 
&& mycase<TODAY(),
    DATEDIFF('RS Cases'[Due By], TODAY(),DAY),


    This code with perform faster because it only does the lookup once and then holds the anwer as a variable.

    Rather than your code which does the same lookup for each condition.
    You can also view and check each variable to debug and fix the problem.

     Please click thumbs up and accept solution

     

speedramps's avatar
speedramps
Super User
1 year ago

Thank you and sorry, that was careless of me to include the DATEDIFF function bug.  ðŸ˜¥

I prefer to teach methods so you can build a solution, rather than just give you a solution.

If you give someone a fish, then you just give them one meal.
But if you teach them to fish, then they can feed themselves and teach their friends and family.

 

In this example it is xmas day 25/12/2024 and Santa needs to know

which orders which are more than three days than overdue for delivery
and which orders are due for delivery  in the next two days

 

Note that SELECTEDVALUE will only work for fields in the visual.

If the field is not in the visual then use MIN or MAX to get scalar value.

But if you use MIN and MAX then watch out for totals where there is more than one value.

 

Answer = 
var todaydate = DATE (2024,12,25)
var mystatus= SELECTEDVALUE('RS case'[Status])
var mydate = MIN('RS case'[Due by])
var overduedays  = DATEDIFF(todaydate,mydate,DAY)
RETURN

SWITCH(TRUE,

// has order been overdue fot three days or more ?
mystatus="OPEN" && overduedays <= -3,
"Overdue",

// if order due for delicery withing in the next two days?
mystatus="OPEN" && overduedays <= 2,
"Arrving soon",

// otherwise return a space
" "

)

 

 

If you have more questions that raise a new ticker and quote speedramps in the text, which will automatically send me a notification