Seeking ALLEXCEPT Behaviour explanation

[smpa01]

Hello experts,

I am trying to understand how ALLEXPECT behaves in a measure when multiple tables are involved.

e.g. I have the following two tables

Table Name - Left

Left.index	Left.name	Left.jk
1	a	a-1
2	a	a-2
3	a	a-3
4	a	a-4
1	b	b-1
2	b	b-2
3	b	b-3

Table name-Right

Right.index	Right.name	Right.value	Right.Index.1	Right.jk
1	a	100	1	a-1
2	a	200	2	a-2
3	a	300	3	a-3
4	a	400	4	a-4
1	b	500	5	b-1
2	b	600	6	b-2
3	b	700	7	b-3

Relationship wise Left filters Right based on jk (joinkey) column

My desired output is following where I want to display the max of value grouped by name

Right.index	Right.name	Right.value	Output
1	a	100	400
2	a	200	400
3	a	300	400
4	a	400	400
1	b	500	700
2	b	600	700
3	b	700	700

I can easily show this with the following measure when I pull everything only from the Right Table

Measure 3 = CALCULATE(MAX('right'[Right.value]),ALLEXCEPT('right','right'[Right.name]))

but if I want to display the same by getting the dim columns from left table like follwoing then the measure falls flat. I can't figure why ALLEXCEPT can't retain the grouping in this context.

I however got to where I wanted to with Measure2. It is just that I am trying to find an explanation on the ALLEXCEPT behaviour.

Measure2 :=
MAXX (
    FILTER (
        ALLSELECTED ( 'right' ),
        'right'[Right.name] = MAX ( 'right'[Right.name] )
    ),
    CALCULATE ( MAX ( 'right'[Right.value] ) )
)

@OwenAuger @MFelix 

Thank you in advance.

[OwenAuger · Accepted solution]

Hi @smpa01 

First off, I think you will get the result you want with this measure instead:

Measure 3 Fixed = 
CALCULATE (
    MAX ( 'right'[Right.value] ),
    ALL ( 'Right' ),
    VALUES ( 'Right'[Right.name] )
)

The basic explanation is that ALLEXCEPT removes all filters on columns of the (expanded) table in the first argument, except for existing filters on the remaining arguments. So in order for a filter to be retained, it must exist in the first place.

In this data model, the 1:1 relationship means that the expanded table of 'Right' includes all columns of 'Left' (and vice versa).

When you use columns from 'Left' in the table visual, there are no filters on columns of 'Right', including 'Right'[name]. So ALLEXCEPT has the effect of clearing all filters on 'Right' and 'Left (due to table expansion) and, in this visual, there are no filters on 'Right'[Name] to retain.

[Measure 3 Fixed] instead uses ALL/VALUES, rather than ALLEXCEPT. This clears all filters from 'Right' (and 'Left' due to table expansion), but brings back visible values of 'Right'[Right.name], which takes account of crossfiltering between 'Left' and 'Right.

Good article from SQLBI on this general topic:

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ 

Kind regards,

Owen

[smpa01]

@OwenAuger  many thanks for your time and writing this detailed explanation. I now fully undertand the issue and thanks again for simplifying the complexity behind it.