I am trying to filter the SUMMARIZECOLUMNS by using the CALCULATETABLE, it prompted me an error message:
"SummarizeColumns can not have outside filter context"
Is it because of SSAS version, my version is: 13.0.2213.0
EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Product'[Model Name],
'Product'[Product Line],
'Product'[Product Name],
'Customer'[First Name],
'Customer'[Last Name],
"Internet Current Quarter Sales", [Internet Current Quarter Sales],
"Internet Total Sales", [Internet Total Sales]
),
'Internet Sales'[Order Date] > DATEVALUE("1 Jan 2011"),
OR('Geography'[Country Region Name] = "Australia", 'Geography'[Country Region Name] = "United States")
)
ORDER BY 'Product'[Model Name]
I can filter by put the FILTER function inside SUMMARIZECOLUMNS, however it gave me a different result (less records than expected) as the table was filtered before corss-join: https://learn.microsoft.com/en-us/dax/summarizecolumns-function-dax
EVALUATE
SUMMARIZECOLUMNS(
'Product'[Model Name],
'Product'[Product Line],
'Product'[Product Name],
'Customer'[First Name],
'Customer'[Last Name],
FILTER('Internet Sales', [Order Date] > DATEVALUE("1 Jan 2011")),
FILTER('Geography', [Country Region Name] = "Australia" || [Country Region Name] = "United States"),
"Internet Current Quarter Sales", [Internet Current Quarter Sales],
"Internet Total Sales", [Internet Total Sales]
)
ORDER BY 'Product'[Model Name]
filterTable: A table expression which is added to the filter context of all columns specified as groupBy_columnName arguments. The values present in the filter table are used to filter before cross-join/auto-exist is performed.
Any idea how to achive the filter same as CALCULATETABLE function? Thank you.