Forum Discussion
Switch not working in a calculated column
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?
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.
7 Replies
- edhans
Community Champion
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 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.
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]
- amitchandak
Super User
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/
