Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.   ...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    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's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

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