Report level filters and SummarizeColumns

[viktor_b]

Hello,

I noticed one strange thing in Report level filters behavior.

To reproduce that, let's have a look at a very simple model of one table: 

We have 3 measures defined:

[Sales] 			= SUM('Sales Table'[Amount])
[Sales All Sales Categories] 	= CALCULATE([Sales], ALL('Sales Table'[Category]) )
[Sales All Sales Table] 	= CALCULATE([Sales], ALL('Sales Table') )

Also, I have a Report level filter applied. I am using "is not blank", so nothing actually is being filtered out. The issue is also reproduced with any filter applied on Product Column.

Everything is OK until a filter on Category is applied.

As you can see measure [Sales All Sales Categories] fails to show values for all categories. But when I remove Report level filter on Products everything starts to work as expected. 

I think that is because of SummarizeColumns function. Here are 2 DAX expressions that are returning different results: one is with SummarizeColumns, the other is the same, but written with Summarize/AddColumns. 

EVALUATE
SUMMARIZECOLUMNS (
    'Sales Table'[Category],
    TREATAS ( { "A" }, 'Sales Table'[Category] ),
    FILTER (
        KEEPFILTERS ( VALUES ( 'Sales Table'[Product] ) ),
        NOT ( ISBLANK ( 'Sales Table'[Product] ) )
    ),
    "x", CALCULATE ( SUM ( 'Sales Table'[Amount] ), ALL ( 'Sales Table'[Category] ) )
)

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE ( 'Sales Table', 'Sales Table'[Category] ),
        "x", CALCULATE ( SUM ( 'Sales Table'[Amount] ), ALL ( 'Sales Table'[Category] ) )
    ),
    TREATAS ( { "A" }, 'Sales Table'[Category] ),
    FILTER (
        KEEPFILTERS ( VALUES ( 'Sales Table'[Product] ) ),
        NOT ( ISBLANK ( 'Sales Table'[Product] ) )
    )
)

You can find a file here:

https://1drv.ms/u/s!AmpFsJc0QFFjlT7EzfwfoVMs6MwF

Any thoughts on this? To me seems like a bug.

Thank you.

[Daniil]

Indeed seems to be a bug.

Some more details:

1. The behavior is the same if you place this filter into page level filters or visual level filters.
2. The filter does not have to be "not is blank"; for example, if you exclude Product 5, the numbers change, but the issue is the same.
3. This does not seem to be an issue in Excel, which sends MDX instead of DAX.

[v-qiuyu-msft]

Hi @viktor_b@Daniil,

I have reported this issue internally: CRI 81693658. Will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi all, 

I got below information internally:

This is by design. The filter on [Product] column, even if user predicate is "not null" initially, will also pick up the GroupBy on [Category] column. This means the filter on [Product] will eventually become [Product]='Product1' or [Product]='Product2' because these two products are in [Category]='A'. By doing ALL([Category]), we do clear the filter context on [Product], but not on [Product]. So the measure is evaluated under [Product]='Product1' or [Product]='Product2' and gives 25.3

Best Regards,
Qiuyun Yu 

[Daniil]

Hello, Qiuyun Yu.

Could you please clarify the following sentence?

"By doing ALL([Product]), we do clear the filter context on [Product], but not on [Product]."

I find it confusing for two reasons:

1. We did not apply ALL ( 'Sales Table'[Product] ) anywhere in our formulas -- it was ALL('Sales Table'[Category]). Why did you mention ALL([Product])?
2. How can we "clear the filter context on [Product], but not on [Product]"???

Furthermore, could you please explain why SUMMARIZECOLUMNS and CALCULATETABLE provide different results with the same filters?

[viktor_b]

Hi, Qiuyun Yu.

Could you please clarify the following:

"This means the filter on [Product] will eventually become [Product]='Product1' or [Product]='Product2' because these two products are in [Category]='A'"

Why is a filter on [Category] is being transferred to [Product]?

I mean why filter on [Product] column does not stay: 

[Product] = "Product 1" OR
[Product] = "Product 2" OR
[Product] = "Product 3" OR
[Product] = "Product 4" OR
[Product] = "Product 5"

And is reduced just to:

[Product] = "Product 1" OR
[Product] = "Product 2"

[v-qiuyu-msft]

Hi @viktor_b@Daniil,

I have delivered your concern internally, will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi all, 

It's' a typo in last update. It should read "By doing ALL([Category]), we do clear the filter context on [Category], but not on [Product]. " I will correct it. 

Best Regards,
Qiuyun Yu