Forum Discussion
How to get a full outer join with relationship?
I have two tables Budget and Actual
Budget:
| Account | Year | Month | Value |
| 1 | 2021 | 1 | 100 |
| 2 | 2021 | 1 | 200 |
| 3 | 2021 | 1 | 300 |
Actual:
| Account | Year | Month | Value |
| 1 | 2021 | 1 | 100 |
| 2 | 2021 | 1 | 200 |
| 3 | 2021 | 1 | 300 |
I want a matrix like this:
| Year | 2021 | |
| Account | budget_value | actual_value |
| 1 | 100 | |
| 2 | 200 | 100 |
| 3 | 300 | 200 |
| 4 | 300 | |
| Total | 600 | 600 |
I tried creating an ID by stringing Account&Year&Month and then linking those. But I end up with this:
How would I go about doing this in PowerBI? In SQL I would do a full outer join and then COALESCE the two different accounts.
But now I do want to keep the two tables separate and use a relationship as they have different granularity in real life so I cannot merge them. (Actuals have a hierarchy level below account that I want to keep)
You need to add 2 dimension tables: Dates and Accounts as shown in the screenshot. You have to add a date column too to the Actuals and Budgets table so that you can relate them to the Dates dimension table.
Then you can display the data in a matrix using the Account column from the Accounts table and the Year column of the Dates table. The result will be as such:
2 Replies
You need to add 2 dimension tables: Dates and Accounts as shown in the screenshot. You have to add a date column too to the Actuals and Budgets table so that you can relate them to the Dates dimension table.
Then you can display the data in a matrix using the Account column from the Accounts table and the Year column of the Dates table. The result will be as such:
Thanks a lot. I see I need to go for full star modelling then (in my non-minimal version).
