topic ADDCOLUMNS with a FILTER? in DAX Commands and Tips

ADDCOLUMNS with a FILTER?

troyhimes — Fri, 01 May 2020 12:37:20 GMT

I would like to add a date filter to the ADDCOLUMNS function below, but I can't seem to get it right. What structure do I use?

FILTER('Development Roll-up','Development Roll-up'[Date]< Date(2021,1,1))

EVALUATE ADDCOLUMNS ( SUMMARIZECOLUMNS( 'Development Roll-up'[Date], 'Development Roll-up'[Area], 'Development Roll-up'[Heading], 'Development Roll-up'[Plan], 'Development Roll-up'[Cost Code], 'Development Roll-up'[Mining Desc], 'Development Roll-up'[Development Tons]), "Ft. Advance", [Sum of Ft. Advance], "11'x12' eq ft", [Sum of Eq ft 11'x12'], "13.5'x15' eq ft", [Sum of Eq ft 13.5'x15'] )

Re: ADDCOLUMNS with a FILTER?

Greg_Deckler — Fri, 01 May 2020 12:56:12 GMT

I believe the syntax for SUMMARIZECOLUMNS is:

EVALUATE ADDCOLUMNS ( SUMMARIZECOLUMNS( 'Development Roll-up'[Date], 'Development Roll-up'[Area], 'Development Roll-up'[Heading], 'Development Roll-up'[Plan], 'Development Roll-up'[Cost Code], 'Development Roll-up'[Mining Desc], 'Development Roll-up'[Development Tons]), FILTER('Development Roll-up','Development Roll-up'[Date]< Date(2021,1,1)), "Ft. Advance", [Sum of Ft. Advance], "11'x12' eq ft", [Sum of Eq ft 11'x12'], "13.5'x15' eq ft", [Sum of Eq ft 13.5'x15'] )

Using SUMMARIZE would be:

EVALUATE ADDCOLUMNS ( SUMMARIZECOLUMNS( FILTER('Development Roll-up','Development Roll-up'[Date]< Date(2021,1,1)), 'Development Roll-up'[Date], 'Development Roll-up'[Area], 'Development Roll-up'[Heading], 'Development Roll-up'[Plan], 'Development Roll-up'[Cost Code], 'Development Roll-up'[Mining Desc], 'Development Roll-up'[Development Tons]), "Ft. Advance", [Sum of Ft. Advance], "11'x12' eq ft", [Sum of Eq ft 11'x12'], "13.5'x15' eq ft", [Sum of Eq ft 13.5'x15'] )

Re: ADDCOLUMNS with a FILTER?

troyhimes — Fri, 01 May 2020 13:31:30 GMT

Hi Greg,

That's what I thought...but I had tried both of those methods and got the following errors.

For SUMMARIZECOLUMNS: A single value for column 'Date' in table 'Development Roll-up' cannot be determined.

For ADDCOLUMNS: Function ADDCOLUMNS expects a column name as argument number 2.

Re: ADDCOLUMNS with a FILTER?

Anonymous — Fri, 01 May 2020 19:31:14 GMT

HI troyhimes ,

Instead of Summarize Column, you can just try summarize.

Let me know if that works

==
ADDCOLUMNS (
SUMMARIZE (
FILTER (
'Development Roll-up',
'Development Roll-up'[Date]
< DATE ( 2021, 1, 1 )
),
'Development Roll-up'[Date],
'Development Roll-up'[Area],
'Development Roll-up'[Heading],
'Development Roll-up'[Plan],
'Development Roll-up'[Cost Code],
'Development Roll-up'[Mining Desc],
'Development Roll-up'[Development Tons]
),
"Ft. Advance", [Sum of Ft. Advance],
"11'x12' eq ft", [Sum of Eq ft 11'x12'],
"13.5'x15' eq ft", [Sum of Eq ft 13.5'x15']
)

Regards,

Re: ADDCOLUMNS with a FILTER?

Anonymous — Fri, 01 May 2020 20:03:56 GMT

It's much easier than you think and what others would have you believe...

EVALUATE CALCULATETABLE( ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Development Roll-up'[Date], 'Development Roll-up'[Area], 'Development Roll-up'[Heading], 'Development Roll-up'[Plan], 'Development Roll-up'[Cost Code], 'Development Roll-up'[Mining Desc], 'Development Roll-up'[Development Tons] ), "Ft. Advance", [Sum of Ft. Advance], "11'x12' eq ft", [Sum of Eq ft 11'x12'], "13.5'x15' eq ft", [Sum of Eq ft 13.5'x15'] ), FILTER( 'Development Roll-up', 'Development Roll-up'[Date] < Date(2021,1,1) ) )

Best

Re: ADDCOLUMNS with a FILTER?

troyhimes — Fri, 01 May 2020 21:08:13 GMT

Anonymous that did the trick, thanks!

Anonymous I understood the logic of your method but got the following error: SummarizeColumns can not have outside filter context. For future use of CALCULATETABLE command, do you know why I would get that error?

Re: ADDCOLUMNS with a FILTER?

Anonymous — Fri, 01 May 2020 21:29:38 GMT

EVALUATE CALCULATETABLE( ADDCOLUMNS ( SUMMARIZE( 'Development Roll-up', 'Development Roll-up'[Date], 'Development Roll-up'[Area], 'Development Roll-up'[Heading], 'Development Roll-up'[Plan], 'Development Roll-up'[Cost Code], 'Development Roll-up'[Mining Desc], 'Development Roll-up'[Development Tons] ), "Ft. Advance", [Sum of Ft. Advance], "11'x12' eq ft", [Sum of Eq ft 11'x12'], "13.5'x15' eq ft", [Sum of Eq ft 13.5'x15'] ), 'Development Roll-up'[Date] < Date(2021, 1, 1) )

Do the above and you're good to go. This is much easier to manipulate with filters than the solution based on SUMMARIZECOLUMNS.

Best