Switch not working in a calculated column

[Anonymous]

Hello  -  I have this formula below working as a measure.  

But when trying to use as a calculated column, I do not get an error message, but I do not get any results past the first condition.   In other words, everything is returning as "NA".      I have the measure and the calculated column in a table side by side and the measure works perfectly fine.   

*Note that I have disabled some of the other statments for my test.  And I also changed the first DATEDIFF to be more column friendly to see if that helped.   

Any idea why the calculated column just stops at the "NA" and does not return any correct results for the other conditions? 

Rules = IF(MAX('AllOpps-Products'[Status])="Open",

SWITCH(

    TRUE(),

    MAX('AllOpps-Products'[Prob.])< 50, "NA",

    MAX('AllOpps-Products'[Prob.]) = 100, "NA",

    DATEDIFF(MAXX('AllOpps-Products','AllOpps-Products'[Est.Ship]),TODAY(),DAY) > -1 &&  DATEDIFF(MAXx('AllOpps-Products','AllOpps-Products'[Est.Ship]),TODAY(),DAY) > -30, "Passed already",   

    //DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) < -90 && DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) > -365, "90 to 365",

    //DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) < -62 && DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) > -92, "60 to 90",

    //DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) < -30 && DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) > -62, "30 to 60",

    //DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) < -15 && DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) > -45 , "15 to 30",

    //DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) < 0 && DATEDIFF(MAX('AllOpps-Products'[Est.Ship]),TODAY(),DAY) > -14, "Should be in ERP",

    "test"

),blank())

[amitchandak]

@Anonymous , you have created a measure. The measure will execute with row context, so if you do not select any row it will choose the first true for grand total. When used, Axis/Row/group by in a visual that it take that row context.

You can force a row context like this

maxx(summarize(table,table[ID],"_1",[Rules]),[_1])

In this, it will get executed at ID level and then max will be taken

refer : https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

[Anonymous]

Thanks @edhans     Great info.   I was thinking that perhaps I could transfer all of this logic to mquery instead using a conditional custom column.    That should be workable, correct?   Not verbatim copy the formula, but the logic itself.  

I was trying to get the logic sorted first in Dax then change to having this done in mquery.  At least that was my idea.  

[edhans]

Yes, but I will caution you that M may not be the best either. Sometimes with SWITCH you are picking the lesser of two evils.

• SWITCH is awesome in measures, but cannot be used as an axis or slicer.
• SWITCH is workable in calculated columns, with the normal caveats of calculated columns. Can be used as a slicer, axis, etc.
• SWITCH doesn't exist in M but it has very powerful if/then/else formulas. However, if it is based on MAX of entire columns, it can be very slow. But if you get it to work and refresh is acceptable, it is the best and Power BI/DAX treats it as a native column, and works fine in slicers, etc.

I always try M first, and if too slow or unworkable, then resort to calculated columns. It really depends on what you are doing. 

[Anonymous]

@edhans  So, on my first attempt, here is what I got.   Just trying to use a simple "date diff" type of formula for now.  Bpth columns are fomatted as "date"  (not datetime).     No syntax errors, but the new column says "Error".  

And FYI, using a custom color versus the actual conditional column wizard as it does not seem the wizard allows for this type of comparison between two dates?

=Duration.Days(Duration.From(DateTime.Date([Opportunity Create Date]())-DateTime.Date([Actual Close])))

Expression.Error: We cannot convert the value #date(2017, 4, 27) to type Function.
Details:
    Value=4/27/2017
    Type=[Type]

[v-lili6-msft]

hi @Anonymous 

First, your formula will work well in a measure, but for a calculate column, the formula row context will not work the way you think, please refer to edhans's reply.

and for the m code, the two columns are date type not datetime type, so just as this :

=Duration.Days(Duration.From([Opportunity Create Date]-[Actual Close]))

Regards,

Lin

[edhans · Accepted solution]

You have an extra () after the Opportunity Creation Date field. Use this:

= Duration.Days(DateTime.Date([Opportunity Create Date])-DateTime.Date([Actual Close]))

that will return the number of days as a whole number.

[edhans]

Calculated columns use row context, not filter context, so it is totally different. So when you use this:

MAX('AllOpps-Products'[Prob.])< 50, "NA",

It is the same as 

'AllOpps-Products'[Prob.]< 50, "NA",

 because a row's min, max, etc is the same, there is just one value. You'd need to remove the row context by using ALL() around the values. So the below as an example

MAXX(
    ALL('AllOpps-Products'[Prob.])
    ,'AllOpps-Products'[Prob.]
)< 50, "NA",

  
But just curious, why are you switching to a calculated column? In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:

Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables