Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.