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

[Txtcher]

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 support comparing values of type True/False with values of type Integer.

So, I assume the syntax I am using is not correct? 

Days Late2 = 
SWITCH(
    // 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(LOOKUPVALUE('RS Events'[Survey_Date__c],'RS Events'[Id], 'RS Cases'[RS Event ID])) && 'RS Cases'[Status]="OPEN" && 'RS Cases'[Due By]<TODAY(),
    DATEDIFF('RS Cases'[Due By], TODAY(),DAY),
   
   // if survey date is greater than due by date, then dif b/t survey date and due by date
    LOOKUPVALUE('RS Events'[Survey_Date__c],'RS Events'[Id],'RS Cases'[RS Event ID])>'RS Cases'[Due By],
    DATEDIFF(LOOKUPVALUE('RS Events'[Survey_Date__c],'RS Events'[Id],'RS Cases'[RS Event ID]),'RS Cases'[Due By],DAY),
    
    // if survey date is null & status is closed & status change date is greater than due by date, then date dif status change date & due by date
    ISBLANK(LOOKUPVALUE('RS Events'[Survey_Date__c],'RS Events'[Id], 'RS Cases'[RS Event ID])) && 'RS Cases'[Status]="CLOSED" && 'RS Cases'[Status Change Date]>'RS Cases'[Due By],
    DATEDIFF('RS Cases'[Status Change Date],'RS Cases'[Due By],DAY),
    "0")

[speedramps]

Well done on resolving the integer value. 

(It was me who suggested using TRUE and breaking the conditions down using VAR to find the problem.

You did not provide an data so it was impossible to check that for you.)

Please raise a new ticket with your new questions, rather than keep adding to this one.

If you quote @speedramps then I will receive an notification and will try reply.

There are lots of DAX functions.

Some return scalar values and other return tables
A scalar value is single value where a table is a list.
What confuses begineers is that a list with just one value is still a table and not scalar !

This will return a table

yourtable

This will return a scalar value

COUNTROWS(yourtable)

This will return a table

yourtable[Date]

This will return a scalar value

MAX(yourtable[Date])

Please don’t give up.  IF and SWITCH will have the same problem and I recommend you use SWITCH.

The rules are different for “dax calculated columns” and “dax measure”.

It is best practice to use a measure because calculated columns are calculated when you do a data refresh.  They have a physical footprint in the data table. A bit like EXCEL cells.

Whereas a measure is calculated when you view a visual, and will depend on the “context”.

The “natural context" is determined by the rows and the columns.

Santa has this XMAS table ...

He creates this measure

Max date = 
MAX(xmas[Date])

Look carefully at these context examples, 

note the total on the left and Mark's value on the right.

Also look at what happens when Filters are applied

Why does context matter?

If you want to count how many children had presents due on  23/12/2024

Then the report on the left shows 2 but the report on the right shows only 1 !!!

Please do the MS Power BI free training courses to learn more about dax.

[Txtcher]

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. 

[speedramps]

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

[Txtcher]

Thank you so much for the detailed response. I can't test this until Monday but I am marking your response as a solution as I am sure it is.

And thank you for the advice about variables. I use that lookup statement in another measure. I am going to revise the measure using a variable.

Quick question about the variable example you posted:  In the DATEDIFF function you used the table/field name instead of the variable. Is it not possible to use the variable in that portion of the statement?

[speedramps · Accepted solution]

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]

Learn how to use SWITCH here

https://learn.microsoft.com/en-us/dax/switch-function-dax 

After ther the SWITCH Try insert  TRUE, like this

Days Late2 = 
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(LOOKUPVALUE('RS Events'[Survey_Date__c],'RS Events'[Id], 'RS Cases'[RS Event ID])) && 'RS Cases'[Status]="OPEN" && 'RS Cases'[Due By]<TODAY(),
    DATEDIFF('RS Cases'[Due By], TODAY(),DAY),
   
   // if survey date is greater than due by date, then dif b/t survey date and due by date
    LOOKUPVALUE('RS Events'[Survey_Date__c],'RS Events'[Id],'RS Cases'[RS Event ID])>'RS Cases'[Due By],
    DATEDIFF(LOOKUPVALUE('RS Events'[Survey_Date__c],'RS Events'[Id],'RS Cases'[RS Event ID]),'RS Cases'[Due By],DAY),
    
    // if survey date is null & status is closed & status change date is greater than due by date, then date dif status change date & due by date
    ISBLANK(LOOKUPVALUE('RS Events'[Survey_Date__c],'RS Events'[Id], 'RS Cases'[RS Event ID])) && 'RS Cases'[Status]="CLOSED" && 'RS Cases'[Status Change Date]>'RS Cases'[Due By],
    DATEDIFF('RS Cases'[Status Change Date],'RS Cases'[Due By],DAY),
    "0")

[Txtcher]

Sorry, that yielded this error:

Expressions that yield variant data-type cannot be used to define calculated columns