Suspected BUG when using ALL with multiple non-ignored slicer values

[tsennott]

I have encountered what I think is a major bug and want to know if I'm missing something or if this is a real bug. 

I think it is a real bug, and if so, should be fixed immediately as it is extremely unintuitive and may be affecting users who don't even know about it. 

Summary

Values calculated to ignore a column filter are sometimes incorrect, depending on the use of non-ignored slicers or filters. 

Detail

This appears to be the case when 

1. a non-ignored slicer/filter has MULTIPLE values selected
2. the values selected in #1 are not present in each distinct value of the ignored column
3. a slicer/filter is applied to the ignored column to select a value desribed in #2

However, this does NOT happen when filtering to the same set of values using a column which is grouped so the slicer/filter is only selected on one value. It appears to be an issue of selecting multiple items in the slicer, not the fundamental slicing logic, which makes me think it is a bug.

The behavior is the same using a slicer or page filter, range filter, etc., and using ALLEXCEPT() instead of ALL()

Example

Screenshots of incorrect and correct behavior are shown below. Example file here.

Incorrect (value should be 3 not 2:

Correct (using grouped column in slicer):

 

[tsennott]

Thanks to @Sean, @Greg_Deckler, and @marcorusso for your excellent and quick responses, this was very helpful, and also hopefully helps others! 

[Sean]

Marco Russo said he will write about this and here it is! 

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

“The golden rule of data modeling is always the same: always use star schemas. If a column has to be used to slice and dice, then it needs to belong to a dimension. Numbers to aggregate, on the other hand, are stored in fact tables. Tabular lets a developer deviate from the regular star schema architecture. This does not mean that doing it is always a good idea. It seldom is.”

Thanks @marcorusso ! 

UPDATE:

Thanks @AlbertoFerrari !

Just noticed the above article was actually written by you.

[tsennott]

@Sean, thank you for linking to this great article by @AlbertoFerrari! 

[Greg_Deckler]

Upon first looking at this, this does indeed look troubling. The reason I say that is if you choose A, then the measures work correctly versus if you choose B, the measures do not work correctly. If you choose A, you get the correct answer 3 and if you choose B, you get the wrong answer of 2.

My guess of what is going on here is that when you use A, it includes a Future and an Unenrolled student and it picks both of those up plus the Future Student in B. But, when you choose B, it only has a Future student and that somehow this filter is being passed to the evaluation of those students in A, so it picks up the Future student but not the Unenrolled student.

That's my theory of what is going on but you are correct that this is very strange behanvior. Perhaps @marcorusso has some insights into the bowels of DAX here.

BTW, it works with the grouped values because it picks up the same group value regardless of A or B designation and passes that filter on when evaluating the other section so this lends some credence to the theory actually.

[marcorusso]

I confirm this seems a bug in DAX.

In the following repro (execute the DAX code below connecting DAX Studio to the PBIX file), the first EVALUATE is the one generated by Power BI using SUMMARIZECOLUMNS.

The second one uses a CALCULATETABLE applying the same filters.

It could be similar to another bug I've seen last week and that MS is already fixing, but it could be also a different one.

I suggest you opening a support ticket with Microsoft to prioritize a fix for it. 

DEFINE
    VAR __DS0FilterTable =
        TREATAS ( { "B" }, 'Students'[Section] )
    VAR __DS0FilterTable2 =
        TREATAS ( { "Future", "Unenrolled" }, 'Students'[Status] )
    MEASURE Students[Count Students (Total All Sections)] =
        CALCULATE (
            CONCATENATEX ( DISTINCT ( Students[Student] ), Students[Student], "," ),
            ALL ( Students[Section] )
        )
EVALUATE
SUMMARIZECOLUMNS (
    __DS0FilterTable,
    __DS0FilterTable2,
    "Count_Students__Total_All_Sections_", IGNORE ( 'Students'[Count Students (Total All Sections)] )
)
EVALUATE
CALCULATETABLE (
    { [Count Students (Total All Sections)] },
    __DS0FilterTable,
    __DS0FilterTable2
)

 EDIT: this behavior is by design, so it will be not fixed, despite it is confusing. The reason is that manipulating column filters in the table that contains also the measures creates a counterintuive behavior. The column filters are applied to the table by SUMMARIZECOLUMNS (used by Power BI) which optimizes this by creating a filter over the existing filtered combinations of the columns. Once you remove the filter on a single column, SUMMARIZECOLUMNS no longer restores the rows that were initially filtered. This behavior affects only SUMMARIZECOLUMNS and not CALCULATETABLE (in my second example).

The best practice is moving the filters in other tables - once again, the star schema is the best design, because you should never modify the filters on the fact table.

I will write an article about SUMMARIZECOLUMNS describing these behaviors - there are also other side effects that can affect certain measures in more complex scenarios, I didn't realize in my initial answer that the issue was another side effect of the internal implementation of SUMMARIZECOLUMNS.

[tsennott]

@marcorusso Thank you for the detailed response and followup, that's very interesting. I wasn't aware that filtering a fact table is bad practice, and will do some reading about this. I look forward to your blog!

[Sean]

@tsennott

If you want to see what other headaches I've had with the ALL function read here

Note that my post was from 12/29/2016

But save yourself the trouble and just read this from 8/13/2018 written by @marcorusso Really important!

Otherwise you'll find yourself going crazy one day trying to figure out why simple formulas with ALL don't work

and its all because of the Sort by Column feature!

[marcorusso]

Please read my edited message in this thread - I initially described this behavior as a bug, but (unfortunately) it is not.

The problem is not limited to ALL, but to any column filter applied to a fact table.

[Greg_Deckler]

Looking at this a little more, here's the real issue, you disconnected your two slicers for Section and Status. So, when you select B, your available Status values actually goes to 1, which is Future. Your slicer does not display this because you disconnected the interaction between them. But, this filter is still there under the hood in terms of DAX.

[Sean]

@tsennott

What happens if you add another Student in Section B that is unenrolled?

Or change for example Hannah to be unenrolled like I've done below...

Seems the lack of unenrolled in Section B is causing this or??? @OwenAuger

Reminded me of this

[OwenAuger]

@Sean just getting up to speed with this thread. Given the answers already I may not have much to add, but let me digest first 🙂

[Sean · Accepted solution]

You can post your Issue here

Include the link to your sample file. They may also respond to this posting...

But if you look at the response marked as solution here

Microsoft support response

Use the ALL function on the dimension attribute instead of the column in the fact table,

and use the slicer on the dimension attribute, this seems to workaround the problem.

That tells you everything! This is by design.

Unfortunately I don't think they'll do anything about it at this point. 

EDIT: Confirmed by Marco Russo below - this is by design!

UPDATE: 9/24/2018

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

“The golden rule of data modeling is always the same: always use star schemas.

If a column has to be used to slice and dice, then it needs to belong to a dimension.

Numbers to aggregate, on the other hand, are stored in fact tables.

Tabular lets a developer deviate from the regular star schema architecture.

This does not mean that doing it is always a good idea. It seldom is.”

Alberto Ferrari

[Greg_Deckler]

@Sean, yep, I came to the same conclusion that the Status should be included in a dimension table instead, related to the main fact table and that used as the slicer and for the calculations.

[tsennott]

@Sean, thank you for the reply. This will indeed fix the issue, but the underlying problem remains if you have a real dataset with the characteristics from my example, which I do, and I imagine is common.

I think you are correct, it is the lack of unernolled in section B that is causing this. But it seems like if it works with the grouped slicer it should work with the ungrouped slicer, which it does not. So it seems like a bug that should be addressed.

Is this the right place to post bugs, or is there another place?

Thank you,
Tim