Relationship Three Tables

[JLiljedahl]

I have three tables, Sales; Forecasr and Date (autocreated, used for all tables in the file), see below sample data

I can create a relationship between Sales-Date and Forecast-Date but I also want to create a relation between Sales-Forecast for "Company" with the goal to have Stacked column per date including both Sales and forecast per company. When trying this, I recieve error that I cannot have this relation together with the relations to date since they will create an ambiguous between Sales and Data table

Any idea how to solve?

[gmsamborn]

Hi @JLiljedahl 

In Power Query, I picked the table that had the most different companies duplicated the query, removed all columns other than Company, and removed duplicates.  (You could to this for each table if there isn't one table that includes all companies.  In this case, you would repeat the above for each table, merging the results, and removing duplicates.)

Typically you would have more company-related information other that the key itself that you could use as a dimension table.

Does this help?

[JLiljedahl]

@some_bih See attached sample data

I want to filter the Forecast matrix if I choose one of the companies in sales matrix and in the same way filter sales if choosing one in forecast.

https://drive.google.com/file/d/1fZ_88wsgrn4i-gqgNqv1cFNOAVvFvgBI/view?usp=drive_link

[gmsamborn]

Hi @JLiljedahl 

No access

[JLiljedahl]

@gmsamborn Solved

[gmsamborn · Accepted solution]

Hi @JLiljedahl 

I added a dimension table for Company and made the appropriate 1:* relationships.

My 3 Tables.pbix

Let me know if you have any questions.

[JLiljedahl]

@gmsamborn 

Could you advise on how to generate the dimension table? 

Outcome seams to solve the issue I have

[CoreyP]

Don't use CALENDARAUTO. Use CALENDAR and provide the first and last date based on the minimum and maximum date that will exist in your model.

[some_bih]

Hi @JLiljedahl "using" Sales as actual and Forecast values could be tricky:

- if there are different level of granularity of data, like Sales on daily level but Forecast on Monhtly level- check granularity

- in your Date table there are no unique dates which are eventually in other dates tables like Sales or Forecast (maybe you have future dates for Forecast, but in Sales or Date table only actual dates) - check details

- Date table is not properly created - link for set up

-check relationships between tables

[JLiljedahl]

Hi @some_bih 

- Date table is generated from Min Sales date -> Max forecast + 120days (All dates should be covered)

- Granulartiy is same, individual dates

- All dates in date table is unique

- I have a 1-Many relationship for both Date-Sales and Date-Forecast

- Whats wrong with date table?

- What could be the issue of combining Sales-Forecast?

[some_bih]

Hi @JLiljedahl share file (dropbox, g drive... )