Left Outer Join using DAX in PowerBI (Many-to-Many Relationship)

Question

How would I do a left join in DAX? When I try to adding a relationship or using the left outer join DAX function I get the following errors (see below). Any ideas would be greatly appreciated!

Error when creating relationship:

Error when trying NaturalLeftOuterJoin()

For reference, I'm trying to create calculated rows of an income statement.

Example:

Revenue: 100
Cost: 80
Profit: 20 (Revenue-Cost)

My tables are like below:

Fact table:
╔═══════════╦═════════╦═══════════╦════════╗
║ YearMonth ║ StoreID ║ AccountID ║ Amount ║
╠═══════════╬═════════╬═══════════╬════════╣
║ 2017-01   ║ A       ║         1 ║    100 ║
║ 2017-01   ║ B       ║         1 ║    200 ║
║ 2017-01   ║ A       ║         2 ║    -50 ║
║ 2017-01   ║ B       ║         2 ║    -50 ║
║ 2017-02   ║ A       ║         1 ║     20 ║
║ 2017-02   ║ B       ║         1 ║    150 ║
║ 2017-02   ║ B       ║         2 ║    -20 ║
╚═══════════╩═════════╩═══════════╩════════╝

Template table:
╔════════════╦═══════════╦═════════╗
║ TemplateID ║ AccountID ║  Line   ║
╠════════════╬═══════════╬═════════╣
║        105 ║         1 ║ Revenue ║
║        105 ║         2 ║ Cost    ║
║        105 ║         1 ║ Profit  ║
║        105 ║         2 ║ Profit  ║
╚════════════╩═══════════╩═════════╝

In SQL this is super easy - I just do a left outer join on the AccountID field which creates records for the Profit line, like below:

 SELECT 
       f.[YearMonth]
      ,f.[StoreID]
      ,f.[AccountID]
      ,f.[Amount]
      ,t.[TemplateID]
      ,t.[AccountID]
      ,t.[Line]
  FROM [dbo].[Fact] f
  left join [dbo].[Templates] t
  on f.[AccountID] = t.[AccountID]

Result:

╔═══════════╦═════════╦═══════════╦════════╦════════════╦═══════════╦═════════╗
║ YearMonth ║ StoreID ║ AccountID ║ Amount ║ TemplateID ║ AccountID ║  Line   ║
╠═══════════╬═════════╬═══════════╬════════╬════════════╬═══════════╬═════════╣
║ 2017-01   ║ A       ║         1 ║    100 ║        105 ║         1 ║ Revenue ║
║ 2017-01   ║ B       ║         1 ║    200 ║        105 ║         1 ║ Revenue ║
║ 2017-02   ║ A       ║         1 ║     20 ║        105 ║         1 ║ Revenue ║
║ 2017-02   ║ B       ║         1 ║    150 ║        105 ║         1 ║ Revenue ║
║ 2017-01   ║ A       ║         2 ║    -50 ║        105 ║         2 ║ Cost    ║
║ 2017-01   ║ B       ║         2 ║    -50 ║        105 ║         2 ║ Cost    ║
║ 2017-02   ║ B       ║         2 ║    -20 ║        105 ║         2 ║ Cost    ║
║ 2017-01   ║ A       ║         1 ║    100 ║        105 ║         1 ║ Profit  ║
║ 2017-01   ║ B       ║         1 ║    200 ║        105 ║         1 ║ Profit  ║
║ 2017-02   ║ A       ║         1 ║     20 ║        105 ║         1 ║ Profit  ║
║ 2017-02   ║ B       ║         1 ║    150 ║        105 ║         1 ║ Profit  ║
║ 2017-01   ║ A       ║         2 ║    -50 ║        105 ║         2 ║ Profit  ║
║ 2017-01   ║ B       ║         2 ║    -50 ║        105 ║         2 ║ Profit  ║
║ 2017-02   ║ B       ║         2 ║    -20 ║        105 ║         2 ║ Profit  ║
╚═══════════╩═════════╩═══════════╩════════╩════════════╩═══════════╩═════════╝

Then I can pivot it like so:

╔═════════╦═════════╦═════════╗
║  Line   ║ Store A ║ Store B ║
╠═════════╬═════════╬═════════╣
║ Revenue ║     120 ║     350 ║
║ Cost    ║     -50 ║     -70 ║
║ Profit  ║      70 ║     280 ║
╚═════════╩═════════╩═════════╝

In DAX it seems much more complex - hopefully someone can prove me wrong on this! I've read bi-directional filtering may allow many-to-many relationships but I wasnt able to get it working here. The reason I'm trying to do this join in DAX rather than SQL because I have several statement templates and would prefer not having several loads with very similar data if it could be done dynamically through DAX. Thanks!

have you tried NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>) msdn.microsoft.com/en-us/library/… — Horaciux
Also, take a look at this post sqlbi.com/articles/from-sql-to-dax-joining-tables — Horaciux
Thanks Horaciux, NaturalInnerJoin will not give the desired result (really want a left join), that said, it still produces the same error. I looked at the link, but it didn't make too much sense - was hoping someone may have a solution for this scenario. Thanks for the suggestion though!! : ) — FirstRedPepper

Foxan Ng Foxan Ng · Accepted Answer · 2017-07-10T05:13:14

Is there any reasons why the Template table is needed, other than as dummy for calculations? Because just from the sample data I saw that the fact table is duplicated (7 -> 14 rows) unnecessarily (maybe I'm missing some key points).

If not, you can simply write a few Measures in DAX to do the calculations in Power BI (which is exactly the power of Power BI), and only the Fact table is needed.

DAX:

Revenue:

Revenue = 
CALCULATE(
    SUM('Fact'[Amount]),
    FILTER(
        'Fact',
        'Fact'[Amount] > 0
    )
)

Cost:

Cost = 
CALCULATE(
    SUM('Fact'[Amount]),
    FILTER(
        'Fact',
        'Fact'[Amount] < 0
    )
)

Profit:

Profit = [Revenue] + [Cost]

Then you can use a Matrix visualization to get the desired results:

P.S. If you really need the Revenue/Cost/Profit to be in the row instead of column, you may need to pivot the data or write the calculations as new Column (but not Measure) instead. This is due to a product limitation in Power BI.

Left Outer Join using DAX in PowerBI (Many-to-Many Relationship)

1 Answers