cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

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? 

 

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())
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Switch not working in a calculated column

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
Highlighted
Super User VI
Super User VI

Re: Switch not working in a calculated column

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Post Prodigy
Post Prodigy

Re: Switch not working in a calculated column

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.  

Highlighted
Super User IX
Super User IX

Re: Switch not working in a calculated column

@texmexdragon , 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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User VI
Super User VI

Re: Switch not working in a calculated column

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Post Prodigy
Post Prodigy

Re: Switch not working in a calculated column

@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]

Highlighted
Super User VI
Super User VI

Re: Switch not working in a calculated column

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Community Support
Community Support

Re: Switch not working in a calculated column

hi @texmexdragon 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors