Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) )
)
Thank you in advance.
Solved! Go to 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
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |