1

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.

Nhon Tran
  • 113
  • 1
  • 1
  • 7

0 Answers0