Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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")
Solved! Go to Solution.
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
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
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.
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, I just created some sample data:
Bills | Emonth | Type | Costs |
Groceries | January | A | 200 |
Groceries | May | A | 200 |
Groceries | February | A | 200 |
Groceries | March | A | 200 |
Groceries | April | A | 200 |
Groceries | May | A | 100 |
Groceries | June | A | 100 |
Groceries | July | A | 100 |
Groceries | August | A | 100 |
Groceries | September | A | 100 |
Groceries | October | A | 100 |
Groceries | November | A | 100 |
Groceries | December | A | 100 |
Groceries | January | B | 100 |
Groceries | February | B | 100 |
Groceries | March | B | 100 |
Groceries | April | B | 100 |
Groceries | May | B | 100 |
Other | June | B | 100 |
Other | July | B | 100 |
Other | August | B | 100 |
Other | September | B | 100 |
Other | October | B | 100 |
Other | November | B | 100 |
Other | December | B | 100 |
The result should be:
Bills | Emonth | Costs |
Groceries | May | 400 |
Hi @spoony
When I take your sample data and put it into a table as shown below.
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?
Hmm wat happens when you put in the Type column, Does it still show the breakdown?
Bills | Emonth | TYPE | Costs |
Groceries | May | A | 300 |
Groceries | May | B | 100 |
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" )
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" )
Ah everything is working now, Thanks heaps!!
Now i can justify my $20/hr job !!
Glad to be of assistance... Kudos!
User | Count |
---|---|
88 | |
84 | |
65 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |