cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
krisstok
Frequent Visitor

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

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).

dataModel.PNG

 

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

 

chart.PNG

 

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.

3 REPLIES 3
tamerj1
Community Champion
Community Champion

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
Frequent Visitor

@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
Community Champion
Community Champion

@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 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors