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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!