Effect of ALL when Applied to Model Comprising Related Column from Another Table

[Jos_Woolley]

Hi,

This thread is related to the posts I made in the thread DAX Head Scratcher, though contains a simplified example.

I am hoping someone can offer a technical explanation as to the following behaviour:

Given the following table, named Films:

a simple table visual which comprises the Title and Genre columns from that table as well as the following measure:

Box Office Dollars (FILTER) =
CALCULATE (
    SUM ( Films[BoxOfficeDollars] ),
    FILTER ( Films, Films[Title] > "C" )
)

returns, as expected:

The following variation on the above measure:

Box Office Dollars (CALCULATE) =
CALCULATE ( SUM ( Films[BoxOfficeDollars] ), Films[Title] > "C" )

returns precisely the same table.

However, when the Genre column is removed from this visual and instead replaced with the Genre column from the following Genre table (which has a 1-to-many relationship with the Films table):

then, whilst the Box Office Dollars (FILTER) measure continues to produce an identical visual to those above:

replacing this measure with the Box Office Dollars (CALCULATE) version results in the following table:

i.e. one in which the number of rows returned has been increased by a factor of 3 (which, in this case, is due to the fact that, between them, those 5 films account for 3 distinct entries from the Genre column (Action, Adventure, Romance).

Why is this happening? I understand that the only difference between the two versions of the measure is that the non-FILTER version contains an implicit use of the ALL function, i.e. is equivalent to:

Box Office Dollars (CALCULATE) EXPANDED =
CALCULATE (
    SUM ( Films[BoxOfficeDollars] ),
    FILTER ( ALL ( Films[Title] ), Films[Title] > "C" )
)

but why does this implicit ALL result in a table whose number of rows appears to result from a Cartesian product involving the number of distinct entries in the Genre column?

And why does this this only occur for the case in which the Genre column is from another table?

Many thanks in advance,

Jos

[marcorusso · Accepted solution]

This article contains the explanation (at least part of): Understanding DAX Auto-Exist - SQLBI

You should check that, and the effect of the expanded table used in the FILTER measure.

[Jos_Woolley]

Fantastic article!

Many thanks, I'd never heard of auto-exist before.

I've picked out one line from your article for the sake of summarising my issue:

"If the columns in the filters do not belong to the same table, then SUMMARIZECOLUMNS uses a cross-join operation instead of auto-exist."

though obviously anyone viewing this post should read the entire article as linked - most informative.

Regards