Answered by:
Anomaly in the ADDCOLUMNS function
Question

Hi,
Here's another one I cannot figure out. I have a three column dataset with Date, Machine Number and Hours worked (File link  http://1drv.ms/1smaYvp). I drag machine number to the Row labels and then compute the following calculated field formulas
1. Machine Hours worked_sec2q4
=SUM(sec2q4[Hours Worked])
2. Last date of operation_sec2q4
=MAXX(FILTER(sec2q4,sec2q4[Hours Worked]>0),sec2q4[Date])
3. Last date of operation_sec2q4_try  An alternative method
=CALCULATE(LASTDATE(sec2q4[Date]),sec2q4[Hours Worked]>0)
4. Hours worked on last day_sec2q4
=sumx(FILTER(ADDCOLUMNS(sec2q4,"LDO",MAXX(FILTER(sec2q4,sec2q4[Hours Worked]>0),sec2q4[Date])),[LDO]=sec2q4[Date]),sec2q4[Hours Worked])
5. Hours worked on last day_sec2q4_try  An alternative method
=SUMX(FILTER(ADDCOLUMNS(sec2q4,"LDO",[Last date of operation_sec2q4_try]),[LDO]=sec2q4[Date]),sec2q4[Hours Worked])
All is well till point 4. The answer in 5 is wrong. The only difference between 4 and 5 is that in 4, I have typed the MAXX function where in 5, I have referred to the Last date of operation_sec2q4 measure.
Why is the answer wrong?
Also, when I type the following calculated field formula for computing "Hours worked on last day", the answer is wrong
=SUMX(FILTER(ADDCOLUMNS(sec2q4,"LDO",CALCULATE(LASTDATE(sec2q4[Date]),sec2q4[Hours Worked]>0)),[LDO]=sec2q4[Date]),sec2q4[Hours Worked])
What is the mistake with this calculated field formula
Thank you.
Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
 Edited by Ashish Mathur Friday, November 7, 2014 10:31 AM
Friday, November 7, 2014 10:30 AM
Answers

You have a context transition calling a measure in ADDCOLUMNS.
Take a look at this article: http://sqlblog.com/blogs/marco_russo/archive/2013/10/22/differencebetweenlastdateandmaxforsemiadditivemeasuresindax.aspx
Marco Russo (Blog, Twitter, LinkedIn)  sqlbi.com: Articles, Videos, Tools, Consultancy, Training
Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular. Marked as answer by Ashish Mathur Saturday, November 8, 2014 2:59 AM
Friday, November 7, 2014 4:12 PM
All replies

You have a context transition calling a measure in ADDCOLUMNS.
Take a look at this article: http://sqlblog.com/blogs/marco_russo/archive/2013/10/22/differencebetweenlastdateandmaxforsemiadditivemeasuresindax.aspx
Marco Russo (Blog, Twitter, LinkedIn)  sqlbi.com: Articles, Videos, Tools, Consultancy, Training
Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular. Marked as answer by Ashish Mathur Saturday, November 8, 2014 2:59 AM
Friday, November 7, 2014 4:12 PM 
Hi,
Thank you for replying. This seems really complex. I will have to read it a few times over. Here is my understating in my own simple language
"Somehow the reference of a measure ([Last date of operation_sec2q4]) within the ADDCOLUMNS function is negating condition passed in the FILTER function (which is written to equate the [Last date] to sec2q4[Date]. Instead of referencing the measure, if the MAXX function itself is written, the condition given the FILTER function is respected."
Is this simple explanation correct?
Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com
Saturday, November 8, 2014 3:02 AM