Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.) --
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???
Solved! Go to Solution.
@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
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].
Thank you for the answer. It is working.
@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
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].
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |