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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
spoony
Helper I
Helper I

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")

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

@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

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

13 REPLIES 13
OwenAuger
Super User
Super User

@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

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks  @OwenAuger and @GilbertQ

 

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

GilbertQ
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ@OwenAuger

 

Im having problems getting a Sum column to work with this, can either of you help? Im using guavag's method below:

 

Table = 
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Product',
            'Product'[Emonth],
            'Product'[Bills]
        ),
        "Costs", SUM('Product'[Costs] )
    ),
    'Product'[Emonth] = "May",
'Product'[Bills] = "Groceries"
)

Its giving me total sum of everything per row without taking account of the filters or row information. 

 

 Or how do you add a Sum() in to SummarizeColumns with filters?

Hi @spoony

 

Do you have any sample data to share?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

 

Hi, I just created some sample data:

 

BillsEmonthTypeCosts
GroceriesJanuaryA200
GroceriesMayA200
GroceriesFebruaryA200
GroceriesMarchA200
GroceriesAprilA200
GroceriesMayA100
GroceriesJuneA100
GroceriesJulyA100
GroceriesAugustA100
GroceriesSeptemberA100
GroceriesOctoberA100
GroceriesNovemberA100
GroceriesDecemberA100
GroceriesJanuaryB100
GroceriesFebruaryB100
GroceriesMarchB100
GroceriesAprilB100
GroceriesMayB100
OtherJuneB100
OtherJulyB100
OtherAugustB100
OtherSeptemberB100
OtherOctoberB100
OtherNovemberB100
OtherDecemberB100

 

The result should be:

 

BillsEmonthCosts
GroceriesMay400

Hi @spoony

 

When I take your sample data and put it into a table as shown below.

 

Power BI - DELETE.png

I then take your exact Syntax and get the following table output, which is what you are expecting to see, based on the Filters in the CalculateTable?

 

Power BI - DELETE.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hmm wat happens when you put in the Type column, Does it still show the breakdown?

 

BillsEmonthTYPECosts
GroceriesMayA300
GroceriesMayB100

This will work below if you only want values for the Groceries

 

Table = CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Product',
            'Product'[Emonth],
            'Product'[Bills],
			'Product'[Type]
        ),
        "Costs", CALCULATE(SUM('Product'[Costs] ),'Product'[Bills] = "Groceries")
    )//,
    //'Product'[Emonth] = "May"
   // 'Product'[Bills] = "Groceries"
)

Or this if you want the other Bills data also.

 

Table = CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Product',
            'Product'[Emonth],
            'Product'[Bills],
			'Product'[Type]
        ),
        "Costs", CALCULATE(SUM('Product'[Costs] ),'Product'[Bills] = "Groceries" || 'Product'[Bills] = "Other")
    )//,
    //'Product'[Emonth] = "May"
   // 'Product'[Bills] = "Groceries"
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thanks @GilbertQ

 

Its almost working, i try to use it on the month and its not showing the correct totals for each type or Bills though:

 

Table = 
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Product',
            'Product'[Emonth],
            'Product'[Bills],
            'Product'[Type]
        ),
        "Costs", CALCULATE(SUM('Product'[Costs] ),'Product'[Emonth] = "May" || 'Product'[Emonth] = "June")
    )
,
'Product'[Emonth] = "May" || 'Product'[Emonth] = "June"
)

Hi there

 

In order to get it working the way that you want, you do not want to place the filter on your calculation. But you rather want to place the filter on your Table you are creating.

 

The code below will get you the data for just the Months of May and June

 

Table = CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Product',
            'Product'[Emonth],
            'Product'[Bills],
			'Product'[Type]
        ),
        "Costs", CALCULATE(SUM('Product'[Costs] ))
    ),
    'Product'[Emonth] = "May" || 'Product'[Emonth] = "June"
)

Power BI - DELETE.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Ah everything is working now, Thanks heaps!!

 

Now i can justify my $20/hr job !!

Glad to be of assistance... Kudos!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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