The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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']
)
Solved! Go to 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
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
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
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']
)
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?
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.
User | Count |
---|---|
25 | |
10 | |
8 | |
6 | |
5 |
User | Count |
---|---|
31 | |
11 | |
10 | |
10 | |
9 |