Forum Discussion
Solved
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],...
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
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"
)