Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |