What exactly does Allexcept() Function return in expanded tables? And why?

Question

The function Allexcept is descriped quite the same both in "Definitive Guide to DAX":

"You can also specify one entire table instead of all the columns of a table that is part of the expanded table"(P430)

and on dax.guide:

"ALLEXCEPT removes filters from the expanded version of Sales, which includes all the tables that can be reached through a many-to-one relationship starting from Sales." https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

And they all imply that Allexcept returns a table when not used as a top function in Calculate as filter arguments, just like what All() does, while in my practice it is something different:

Consider a single-table model like:

Name    Datetime
John    2018/6/25
James   2018/7/7
Smith   2018/7/27
Smith   2018/11/21
Smith   2018/6/9
Mary    2019/1/31
Emily   2018/8/20
John    2018/6/9
Mary    2018/11/11
John    2018/8/21

with a related calendar using Calendarauto() with a calculated column:

YearMonth = FORMAT('Date'[Date],"yyyymm")

Now I want to know how many YearMonth does every Name correspond to, which should be like this:

Name MonthNum
John    2
James   1
Smith   3
Mary    2
Emily   1

With knowledge of expanded tables and Allexcept(and how context transition works), I used formula like this:

Wrong = 
ADDCOLUMNS (
    VALUES ( Data[Name] ),
    "MonthNum", CALCULATE (
            DISTINCTCOUNT ( 'Date'[YearMonth] ),
            CALCULATETABLE ( ALLEXCEPT ( Data, Data[Name] ) )
        )
    )

The result turns out to be:

Name    MonthNum
John    5
James   5
Smith   5
Mary    5
Emily   5

But I DO KNOW how to make it right, by adding "Data," after Calculatetable:

  Correct = 
ADDCOLUMNS (
    VALUES ( Data[Name] ),
    "MonthNum", CALCULATE (
            DISTINCTCOUNT ( 'Date'[YearMonth] ),
            CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[Name] ) )
        )
    )

Please explain the exact reason why Wrong Version does not behave as expected.

Answer 1

interesting question. I am no dax expert, but I'll give this a try. First, from my understanding, ALL, ALLSELECTED... do not return anything by themselves, they simply clear filters. Not sure, if it's relevant, but might be worth mentioning.

Why doesn't the wrong column work? It has a lot to do with the relationship. If you simply connected data and calendar table, it created a one-way relationship from the calendar to data-table.

If you change it to both way relationship (for scientific purposes only), even the "wrong" column formula will work, because the engine can keep the name filter active.

Now let's look why correct one works, but the other fails in your case. Let's isolate the calculate tables first.

The correct one - correct_calc = CALCULATETABLE ( Sheet1, ALLEXCEPT ( Sheet1, Sheet1[Name] ) )

returns

while wrong one - wrong_calc = CALCULATETABLE ( ALLEXCEPT ( Sheet1, Sheet1[Name] ) )

returns

With these tables in mind, I would guess, that the correct one keeps "name" filters, as they exist within the calculated table, so the engine sees for every "name" only part of the table.

The wrong one on the other hand just keeps all dates, regardless of the "names" filter, as the "name" values do not come into consideration at all, because there is no relationship between the VALUES ( Data[Name] ) and table you are making the calculation on ie CALCULATETABLE ( ALLEXCEPT ( Data, Data[Name] ) ).

While writing the answer, I've realized, that it's not an exact reason as you've requested so my apologies.

Answer 2

I did a lot of tests to find how exactly Allexcept() behaves, and here's my conclusion:

Allexcept() has 2 different behavior, just like All():

1.as a "removefilter", this way it removes all filters from the columns which are not excluded from the expanded table, but by itself it does not return anything;

2.returns a "special" expanded table which only contains those columns that are not excluded from the original expanded table.In this case, the result table still act like an expanded table whenever possible.

Behavior 1 is too clear to be explained.To activate this behavior, just use allexcept() as a top function in Calculate() or Calculatetble() as a filter argument.

Behavior 2 is far more complicated to prove and understand.

This formula proves that Allexcept() can return a table and that the result table is incomplete(using DAXStudio to test the result is highly recommended):

EVALUATE
GENERATE (
    VALUES ( Data[Name] ),
    CALCULATETABLE ( ALLEXCEPT ( Data, Data[Name] ) )
)

This one shows that Allexcept() can return a table by itself when required:

EVALUATE
GENERATE ( VALUES ( Data[Name] ), ALLEXCEPT ( Data, Data[Name] ) )

This one shows that the result table returned by Allexcept() is an expanded one:

EVALUATE
CALCULATETABLE ( 'Date', CALCULATETABLE ( ALLEXCEPT ( Data, Data[Name],Data[Datetime],Data[Type] ) ) ) 

And the last formula shows that when used as a table returner, Allexcept() ignores any filter argument on the original expanded table, this include both the excluded columns and the ones not excluded, which means that all filters on the original expanded table are useless:

CALCULATETABLE (
    'Date',
    CALCULATETABLE (
        ALLEXCEPT ( Data, Data[Name] ),
        Data[Name] = "Tom",
        'Date'[YearMonth] ="2018-1-1"
    )
)

Please comment if you have any advice!