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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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]

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      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.

    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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