Forum Discussion

tchiang7's avatar
tchiang7
Frequent Visitor
1 year ago
Solved

NATURALLEFTOUTERJOIN not finding common join columns

I have three tables.

1. One is directQuery table "QuantifiedSelves Welln" which has direct query data from SQL database,

2. One is custom "CalendarDate" table created with Calendar() function -- CalendarDate = CALENDAR(DATE(2024,9,1), TODAY())

3. One is custom "CalendarMonth" table created based on "CalendarDate" table (see 2.) -- 

CalendarMonth = DISTINCT(CalendarDate[CalendarYearMonth])
 
There is a relation between "QuantifiedSelves Welln" table and "CalendarMonth". See below for the relation definition.

 

I want to left outer join "CalendarMonth" and  "QuantifiedSelves Welln" table together, so I can see aggregated values per CalendarMonth. If no values in a CalendarMonth, then aggregate value is null.

I tested NATURALLEFTOUTERJOIN() in DAX view (see below). Notice that, if comment out NATURALLEFTOUTERJOIN() line, then other two "EVALUATE" sections return correct results. It proves that two tables has correct values. Any problem I have???

 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi tchiang7 ,

     

    It seems to be fine, please check again for the following limitations:
    Columns being joined must have the same data type and the same name in both tables.
    The columns considered for the join are those of the expanded table, not just the base table: two tables can be joined through common columns in related tables.
    The columns used in the join condition that correspond to physical columns of the data model must also have the same data lineage; two columns with the same name and different data lineage generate an error.
    Two columns with the same data lineage must have also the same full column name, which includes both table name and column name; otherwise, they are not matched for the join.

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    1 year ago

    tchiang7 You can join those tables by breaking the lineage for numeric columns use  +0 for text use & ""

    Join Sales & Product = 
VAR SalesTable = 
    SELECTCOLUMNS ( 
        Sales, 
        "ProductKey", Sales[ProductKey] + 0, 
        Sales[Quantity], Sales[Net Price] 
    )
VAR ProductsTable = 
    SELECTCOLUMNS ( 
        'Product', 
        "ProductKey", 'Product'[ProductKey] + 0, 
        'Product'[Brand], 'Product'[Color] 
    )
VAR Result = 
    NATURALLEFTOUTERJOIN ( SalesTable, ProductsTable )
RETURN
    Result

     

    • AntrikshSharma's avatar
      AntrikshSharma
      Icon for Community Champion rankCommunity Champion
      1 year ago

      There is another way of solving this, since JOIN functions expect same column names and same lineage but 2 Key column from different tables do not have the same lineage, even if they are connected through a relationship.

       

      In this scenario adding 0 or "" disconnects their lineage from the model and they both act as columns that don't exists in the model so JOIN functions work fine.

       

      However to make the lineage of both columns same we can use TREATAS which actually Treats one or more columns as the columns of some other tables.

       

      Join Sales & Products TREATAS = 
VAR SalesTable = 
    SELECTCOLUMNS ( 
        Sales, 
        Sales[ProductKey], Sales[Quantity], Sales[Net Price] 
    )
VAR ProductsTable = 
    TREATAS ( 
        SELECTCOLUMNS ( 
            'Products', 
            'Products'[ProductKey], 'Products'[Brand], 'Products'[Color]
        ),
        Sales[ProductKey], Products[Brand], Products[Color] 
    ) 
VAR Result = 
    NATURALLEFTOUTERJOIN ( SalesTable, ProductsTable )
RETURN
    Result

       

      So for the duration of the calculation Products[ProductKey] will be treated as Sales[ProductKey].