Unable to connect Fact Tables to Calendar Table

Question

In all my fact tables, I have separate columns for quarter, and year.

I also have a calendar table with columns for Date, Quarter, Year, Month Name etc.

I want to connect both columns from my fact tables to the calendar table but PowerBI wont allow me. It also tells me that I have cardinality many-to-many since I have multiple records for the same Year and Quarter in both the Calendar and Fact tables.

My goal is to have a report consisting of slicers / dropdowns for Yearand Quarter, so that I can see how my metrics performed over time.

I'd like to know how to best handle such instances. How should I design my data model that will allow me to filter my reports by quarter and year.

Really appreciate your help!

Answer 1

The fact tables need to have an value that is unique in the calendar table, so the corresponding row from the calendar table can be identified.

In the most common scenarios, the fact table will have a transaction date column and the calendar table has a date column. In the calendar table, the date column has only unique values, whereas the columns for Month, year, etc, contain duplicates, of course.

In the Fact table, there may be many records with the same date, but each date can be connected to just ONE single entry in the calendar table.

Your screenshot does not show any date columns in the fact tables.

You don't have to use a date, but you need a key column in the Fact tables that can be linked to a column with unique values in the Calendar table.

If you add a combination of Year-Quarter to all tables, Fact AND Calendar, then you can use these in the relationship and the error will go away