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

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.