Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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/
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.
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.
I always try M first, and if too slow or unworkable, then resort to calculated columns. It really depends on what you are doing.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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]
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCalculated 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |