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

     

Txtcher's avatar
Txtcher
Helper V
1 year ago

Thank you again.

I am not certain I completely understand the scalar value vs table data value. 

I do not understand what the result of Min(RS Case[Status]) would be. Would it be "Open" or "Closed?" Finding a min value of a text field is hard for me to understand. I think this function may be a little beyond me as a beginner. 

 

I originally approached this trying to nest IF statements. I know this is an old Excel habit, but because I am confused by scalar value using the SWITCH function, I may return to building IF statements & taking your advice using variables. The caution regarding scalar values and totals makes me concerned as I absolutely need the total number of days resulting from the DATEDIFF function.

Again, thank you for your efforts. My apologies for being too inexperienced to understand. I am going to find further reading and maybe some videos to help me understand.

 

EDIT TO ADD:  I re-read the MS description of the SWITCH funtion and learned something. In my original post, someone stated I failed to include the expression , "TRUE." When I included it, I got another error:  Expressions that yield variant data-type cannot be used to define calculated columns. I think I figured out why I got this error - the value provided for FALSE in my original measure was a text value ("0") and not an integer value which is the value type returned by the DATEDIF function.  When I return to work on Monday, I will change it from text to integer to see if that fixes the issues.

My only other concern is the warning that the order of the values matter. I will have to take a close look and be sure I have that correct also.