Problems creating a Master Calendar for Multiple Snowflaked Tables (Avoid Circular Dependency)

[emillanc]

 I've struggled to create a single master calendar (Canonical) for the following simplified data model:

When I  try to establish the relationships between the master calendar date (MC_Date)  and the creation dates (CreateDttm), I get circular dependency warnings. Please help me create a master calendar for the proposed data model.

Using a master calendar (Canonical) allows me to have different metrics in a single chart using the same time scale. It also filters the creation dates of all the tables with a single slicer.

The date of the master calendar would be related to the creation dates of each table.

Unifying the different snowflaked tables into one would further complicate the model (this is a simplified version of it) and significantly increase the refresh time.

Regards

[audreygerred · Accepted solution]

Hi! Here is a link to the SQLBI date ref table that I love to use. It can be used for a traditional calendar, fiscal, or fiscal weekly. This article describes the table: Reference Date Table in DAX and Power BI - SQLBI

This link is where you can download the pbix that has the DAX: DAX Date Template - SQLBI

In your model, which table is the fact? It looks like it is Product and the other two are snowflaked dimensions. Your date dimension table should have a relationship to your fact and if you have multiple facts they should not be joined to each other. They would be useable in one viz together by any shared dimensions they have and use of the dimension tables. Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

[emillanc]

Hi @audreygerred !
Thank you for your quick answer!

Indeed, there are 3 snowflaked tables (In reality there are two more tables, but, for simplicity I have only put 3), where the relationships are the following:
Customer ---------------------> Branch ----------------------------> Project
CustomerID --------> CustomerID | BranchID ---------------------> BranchID

My intention is to be able to create a slicer that allows me to filter the creation dates of each table or draw in the same chart the evolution using the same date on the time axis.

Regards