Chapter 18 Multiple Fact Tables

[DJBrennan]

Hi again,
On pages 251 and 252, the model view shows a relationship between Sales and BudgetPeriod but this is not referenced in the text and I cannot see how to relate these two tables. However, a relationship is needed as I'm getting the Category total for each period rather than that period's sub total.
Many thanks,
Denis

[DJBrennan]

I created a new Column "Period" - Period = (left(Sales[OrderDateKey,6) - in Sales table and used this to relate the two tables. That fixed the problem. Is this a correct solution?
Thanks in advance
Denis

[RamanaV]

The purpose of the example is how to report based on 2 data (fact) tables. We need to have common lookup tables for both fact tables in order to create such a report.
The BudgetPeriod (Lookup) table is connected to Budget table
The BudgetPeriod table is connected to Calendar table which in turn is connected to Sales table (this is called Snow flake schema)
The values will then be correct

[DJBrennan]

Sorry but this relationship (as you describe it) doesn't work but my direct relationship between BudgetPeriod and Sales does work (and this what is illustrated in the model views in the book

[MattAllington]

Hi Denis

I will help sort this out. Please contact me via https://exceleratorbi.com.au/contact/ and include your email address so I can contact you

[MattAllington]

I assume you are are talking about this image.

temp.png (15.55 KiB) Viewed 1132 times

The relationship is not to the sales table, it is to the Calendar table.

Does this fix it?

[DJBrennan]

Matt has kindly emailed me directly and provided a solution. This is his preferred approach.
You can turn on bi-directional filtering between BudgetPeriod and Calendar, and then hide the BudgetPeriod table and use the Calendar table Period. I think this is the best solution.
Many thanks
Denis