Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
My source data is following (pbix is attached)
| Property | Attribute | Value |
|----------|-----------|-------|
| P1 | A1 | |
| P1 | A2 | |
| P1 | A3 | |
| P2 | A1 | |
| P2 | A2 | |
| P3 | A1 | |
| P4 | A1 | P3 |
| P5 | A3 | P1 |
I want DAX to return a calculated column in the dataset which will lookup {Value,Attribute} in {Property,Attribute} and return Property.
My desired output is following
| Property | Attribute | Value | return |
|----------|-----------|-------|--------|
| P1 | A1 | | |
| P1 | A2 | | |
| P1 | A3 | | P1 |
| P2 | A1 | | |
| P2 | A2 | | |
| P3 | A1 | | P3 |
| P4 | A1 | P3 | |
| P5 | A3 | P1 | |
If I have a seperate lookup table like this
I can write a DAX calculated column like this to reach my end goal
_lookupFromOtherTableTREATAS =
CALCULATE (
CALCULATE (
MAXX ( lookupTbl, lookupTbl[Value] ),
TREATAS (
SUMMARIZE ( 'Table', 'Table'[Property], 'Table'[Attribute] ),
lookupTbl[Value],
lookupTbl[Attribute]
)
)
)
However, if I want to do a self join in the same table using TREATAS it fails. I can't seem to understand why TREATAS fails in self join and what needs to be done here to make it behave as desired.
Thank you in advance.
Solved! Go to Solution.
This is a rather tangled mess of row context and filter context that is hard to disentangle. I spent a long while trying to fully understand it, but it was time well-spent honing my DAX knowledge.
For the sake of concreteness, let's walk through what happens for the row ("P2", "A1", "") of Table2.
Calling CALCULATE converts the row context to filter context so that before considering TREATAS, we have filters [Property] = "P2", [Attribute] = "A1", and [Value] = "" as well as the filters on the columns of the expanded table, lookupTbl[Attribute] = "A1" and lookupTbl[Value] = "P3".
The SUMMARIZE table looks like this:
So the TREATAS filter argument is essentially equivalent to
( Table2[Property], Table2[Attribute] ) IN
{ ( "", "A1" ), ( "", "A2" ), ( "", "A1" ), ( "P3", "A1" ), ( "P1", "A3" ) }
So how do these filters combine? As explained here:
A filter argument overrides the existing corresponding filters over the same column(s), unless it is embedded within KEEPFILTERS.
This means that the filters on Table2[Property] and Table2[Attribute] from the context transition are overridden by the TREATAS filter argument (see step 5 from the link above). The Table2[Property] = "P2" filter gets overridden but while the filter on Table2[Attribute] is also overridden, the column in the expanded table, lookupTbl[Attribute], is not affected. Results will change if you delete the relationship from lookupTbl[Attribute] 1-->* Table2[Attribute].
Keeping lookupTbl[Attribute] = "A1" from the expanded table, the remaining filters on Table2 reduce to
( Table2[Property], Table2[Attribute] ) IN { ( "", "A1" ), ( "P3", "A1" ) } && Table2[Value] = ""
Only the row ("P3", "A1", "") meets this condition, so the max of [Property] over this row is "P3".
You could fix your DAX by wrapping KEEPFILTERS around the TREATAS argument (this would prevent the overriding of the row context) but the easier way to do this is with a simple lookup:
LOOKUPVALUE (
Table2[Value],
Table2[Attribute], Table2[Attribute],
Table2[Value], Table2[Property]
)
Or if you insist on using CALCULATE:
CALCULATE (
MAX ( Table2[Value] ),
ALLEXCEPT ( Table2, Table2[Attribute] ),
Table2[Value] = EARLIER ( Table2[Property] )
)
@AlexisOlson can't thank you for the time you spent to disect and ultimately give me a solution and nice explanation.
When I feel I have better grip on DAX, then DAX finds ways to humble me 😂😂
This is such an epic solution you provided that KEEPFILTERS can do that, OMG..this has bugged me for a while. I will rather take my time to read through your explanation and the links your provided and post any follow up Q I might have.
I have also asked Marco the same question.
Thanks for your time, effort, patience and willingness to help me tame DAX.
We need to grab a beer someday together !!! 🍺
Just so the future readers know
Column =
CALCULATE (
MAXX ( Table2, Table2[Property] ),
KEEPFILTERS (
TREATAS (
SUMMARIZE ( Table2, Table2[VALUE], Table2[Attribute] ),
Table2[Property],
Table2[Attribute]
)
)
)
This is a rather tangled mess of row context and filter context that is hard to disentangle. I spent a long while trying to fully understand it, but it was time well-spent honing my DAX knowledge.
For the sake of concreteness, let's walk through what happens for the row ("P2", "A1", "") of Table2.
Calling CALCULATE converts the row context to filter context so that before considering TREATAS, we have filters [Property] = "P2", [Attribute] = "A1", and [Value] = "" as well as the filters on the columns of the expanded table, lookupTbl[Attribute] = "A1" and lookupTbl[Value] = "P3".
The SUMMARIZE table looks like this:
So the TREATAS filter argument is essentially equivalent to
( Table2[Property], Table2[Attribute] ) IN
{ ( "", "A1" ), ( "", "A2" ), ( "", "A1" ), ( "P3", "A1" ), ( "P1", "A3" ) }
So how do these filters combine? As explained here:
A filter argument overrides the existing corresponding filters over the same column(s), unless it is embedded within KEEPFILTERS.
This means that the filters on Table2[Property] and Table2[Attribute] from the context transition are overridden by the TREATAS filter argument (see step 5 from the link above). The Table2[Property] = "P2" filter gets overridden but while the filter on Table2[Attribute] is also overridden, the column in the expanded table, lookupTbl[Attribute], is not affected. Results will change if you delete the relationship from lookupTbl[Attribute] 1-->* Table2[Attribute].
Keeping lookupTbl[Attribute] = "A1" from the expanded table, the remaining filters on Table2 reduce to
( Table2[Property], Table2[Attribute] ) IN { ( "", "A1" ), ( "P3", "A1" ) } && Table2[Value] = ""
Only the row ("P3", "A1", "") meets this condition, so the max of [Property] over this row is "P3".
You could fix your DAX by wrapping KEEPFILTERS around the TREATAS argument (this would prevent the overriding of the row context) but the easier way to do this is with a simple lookup:
LOOKUPVALUE (
Table2[Value],
Table2[Attribute], Table2[Attribute],
Table2[Value], Table2[Property]
)
Or if you insist on using CALCULATE:
CALCULATE (
MAX ( Table2[Value] ),
ALLEXCEPT ( Table2, Table2[Attribute] ),
Table2[Value] = EARLIER ( Table2[Property] )
)
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |