cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smpa01
Super User
Super User

Debugging TREATAS in self join

@AlexisOlson 

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

smpa01_0-1638542935534.png

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]
        )
    )
)

 

smpa01_1-1638543004630.png

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.

 

smpa01_2-1638543053371.png

Thank you in advance.

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1638555404287.png

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] )
)

View solution in original post

2 REPLIES 2
smpa01
Super User
Super User

@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]
        )
    )
)

 

smpa01_0-1638558760359.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


AlexisOlson
Super User
Super User

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:

AlexisOlson_0-1638555404287.png

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] )
)

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors