Get all data from one table, not only matched in data model

[krisstok]

Hi, 

I need help to get all data from one table not only matched.

Here is my data model. I got one table with revenue (Sales) and one table with budget (BudgetPM). They are joined by key like 202201PM PST1MN-LE (date YYYYMM, salesmanID, category of product).

I want to show all budget compared to revenue like this:

The issue is that budget for PST1 should be 17mln, but it shows me 16mln because there is no revenue for one category so I see only matched data from both tables.

Both tables do not have same number of columns.

[tamerj1]

Hi @krisstok 

it is all in the data model. Don't create relationships between fact tables. Date table can filter Sales and can filter BudgetPM directly. Same for VendorDict. 

[krisstok]

@tamerj1 BudgetPM contains salesmanID, category and dateKey. I can't create relationship with calendar table only by dates, It would not match on salesman and category filters in sales table.

Also:

- sales is by date (year/month/day) / salesman / product category related to salesman

- budget is by year / months (no days) / salesman / product category related to salesman

VendorDict is a dimension table.

How should I create data model to work it well?

[tamerj1]

@krisstok 

I would creat common bridge dimension tables to connect them. I would avoid direct many to many relationships as much as possible. This won't be the only issue. Other broblems will start to appear the further you go with your analysis