Reply
Highlighted
Regular Visitor
Posts: 16
Registered: ‎03-30-2017
Accepted Solution

SummarizeColumns with Multiple Filters

Is it possible to add multiple filters to SUMMARIZECOLUMNS()?

 

I tried the below and its not giving me the correct number of rows:

 

Table = SUMMARIZECOLUMNS('Product'[Emonth], 'Product'[Bills], FILTER('Product', Product'[Emonth]' = "May"), FILTER('Product', 'Product'[Bills] = "Groceries")


Accepted Solutions
Established Member
Posts: 166
Registered: ‎02-29-2016

Re: SummarizeColumns with Multiple Filters

@spoony

 

You can do something like this:

Table =
CALCULATETABLE (
    SUMMARIZECOLUMNS ( 'Product'[Emonth], 'Product'[Bills], 'Product' ),
    'Product'[Emonth] = "May",
    'Product'[Bills] = "Groceries"
)

I added 'Product' as a filter argument to SUMMARIZECOLUMNS, then wrapped in CALCULATETABLE containing the column filters.

(Since the table is calculated in an unfiltered context, I turned your FILTER functions into single column filters.)

 

Does this give the intended result?

 

Cheers,

Owen

 

 

View solution in original post


All Replies
Senior Member
Posts: 395
Registered: ‎06-24-2015

Re: SummarizeColumns with Multiple Filters

Hi @spoony

 

When creating a Calculated Table I typically use the syntax below.

 

Cash Flow Table = 
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'TableName',
            'TableName'[Fiscal Year],
            'TableName'[Fiscal Date]
        ),
        "CFF", SUM ( 'TableName'[Operating Cash Flow] ),
        "Rowz", DISTINCTCOUNT ( 'TableName'[Fiscal Year] )
    ),
    'TableName'[Budget Version] = "Cash Flow",
    'TableName'[Fiscal Date]
        >= VALUES ( 'TableName'[Fiscal Date] )
)

The last section is where I have put in my filters, in which there are multiple filters.

Twitter: @guavaq
Established Member
Posts: 166
Registered: ‎02-29-2016

Re: SummarizeColumns with Multiple Filters

@spoony

 

You can do something like this:

Table =
CALCULATETABLE (
    SUMMARIZECOLUMNS ( 'Product'[Emonth], 'Product'[Bills], 'Product' ),
    'Product'[Emonth] = "May",
    'Product'[Bills] = "Groceries"
)

I added 'Product' as a filter argument to SUMMARIZECOLUMNS, then wrapped in CALCULATETABLE containing the column filters.

(Since the table is calculated in an unfiltered context, I turned your FILTER functions into single column filters.)

 

Does this give the intended result?

 

Cheers,

Owen

 

 

Regular Visitor
Posts: 16
Registered: ‎03-30-2017

Re: SummarizeColumns with Multiple Filters

Thanks  @OwenAuger and @guavaq

 

I tried @OwenAuger method and it looks like its working now.