ADDCOLUMNS with a FILTER?

[troyhimes]

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']
)

[harshnathani · Accepted solution]

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,

HN

[Anonymous]

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

D

[Greg_Deckler]

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']
)

[harshnathani · Accepted solution]

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,

HN

[troyhimes]

@harshnathani  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?

[Anonymous · Accepted solution]

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

D

[troyhimes]

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.