Forum Discussion

troyhimes's avatar
troyhimes
Resolver I
6 years ago
Solved

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']
)
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
    Anonymous
    6 years ago
    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

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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']
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        troyhimes
        Resolver I
        6 years ago

        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?

    • troyhimes's avatar
      troyhimes
      Resolver I
      6 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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