cancel
Showing results for 
Search instead for 
Did you mean: 

Report level filters and SummarizeColumns

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: 

 

image.png

 

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.

image.png

 

Everything is OK until a filter on Category is applied.

 

 

image.png

 

image.png

 

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.

 

Status: Accepted
Comments
Member

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.
Moderator

Hi @viktor_b@Daniil,

 

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

 

Best Regards,
Qiuyun Yu 

Moderator
Status changed to: Accepted
 
Moderator

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 

Member

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?

Visitor

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"
Moderator

Hi @viktor_b@Daniil,

 

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

 

Best Regards,
Qiuyun Yu 

Moderator

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