Forum Discussion
Relationship Three Tables
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?
Hi Anonymous
I added a dimension table for Company and made the appropriate 1:* relationships.
Let me know if you have any questions.
10 Replies
Hi Anonymous "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
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?
Hi Anonymous share file (dropbox, g drive... )
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 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
Hi Anonymous
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?
