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!