Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

NATURALLEFTOUTERJOIN with TREATAS not returning NULLs

According to sqlbi.com  when outer left joining two tables P_A (columns ProductKey, Code, and Color) and P_B (ProductKey, Name, and Brand) without any relationship, the following code

 

VAR B_TreatAs =
TREATAS ( P_A, P_B[ProductKey], P_A[Code], P_A[Color] )
VAR Result =
NATURALLEFTOUTERJOIN ( B_TreatAs, P_B )
RETURN
Result

 

has better performance than

 

VAR A =
SELECTCOLUMNS (
P_A,
"ProductKey", P_A[ProductKey]+0,
"Code", P_A[Code],
"Color", P_A[Color]
)
VAR B =
SELECTCOLUMNS (
P_B,
"ProductKey", P_B[ProductKey]+0,
"Name", P_B[Name],
"Brand", P_B[Brand]
)
VAR Result =
NATURALLEFTOUTERJOIN ( A, B )
RETURN
Result

 

I have tried to time both measures with a large data set and it appears to hold true, however the TREATAS solution does not give the same result for records with no matching ProductKey values in the P_B table. The second solution returns all rows from the P_A table with NULLs where there is no match, but the first solution ignores the records without the matching ProductKey values.

 

Is there a way to use the TREATAS version of this measure to return the non-matched values from the P_A table as well?

  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi Anonymous 

    You would have to switch it around so that P_B is the first argument of TREATAS, and P_A is the first argument of NATURALLEFTOUTERJOIN. Something like:

     

    VAR A_TreatAs =
    TREATAS ( P_B, P_A[ProductKey], P_B[Name], P_B[Brand] )
VAR Result =
    NATURALLEFTOUTERJOIN ( P_A, A_Treatas )
RETURN
    Result

     

    The reason the original TREATAS version didn't include unmatched values of P_A[ProductKey] is that

     

    TREATAS ( P_A, P_B[ProductKey],...)

     

    by definition can only return a table including existing values of P_B[ProductKey].

     

    Regards

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous 

    You would have to switch it around so that P_B is the first argument of TREATAS, and P_A is the first argument of NATURALLEFTOUTERJOIN. Something like:

     

    VAR A_TreatAs =
    TREATAS ( P_B, P_A[ProductKey], P_B[Name], P_B[Brand] )
VAR Result =
    NATURALLEFTOUTERJOIN ( P_A, A_Treatas )
RETURN
    Result

     

    The reason the original TREATAS version didn't include unmatched values of P_A[ProductKey] is that

     

    TREATAS ( P_A, P_B[ProductKey],...)

     

    by definition can only return a table including existing values of P_B[ProductKey].

     

    Regards