Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
troyhimes
Resolver I
Resolver I

ADDCOLUMNS with a FILTER?

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']
)
2 ACCEPTED SOLUTIONS

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

 

 

View solution in original post

Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

 

 

@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
Not applicable

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

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.