# 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.

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

Friday, November 7, 2014 10:30 AM

### All replies

• You have a context transition calling a measure in ADDCOLUMNS.

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