2 fact tables with 2 dimensions tables ... I'm turning around

[Xavier]

Hello,

I have two dimension tables : Calendrar, Customers

I have two fact tables using this two dimensions : Fact table 1 & Fact table 2

If I created relationsships between this table, I have an inactive link (circle) : 

Calendar & Customers  --> Fact table 1 : OK 

Calendar & Custimers --> Fact table 2 : 1 link active, the other is not

Do you have a tips for me ? Should I duplicate a dimension table for example ?

Thank you very much.

[jdbuchanan71 · Accepted solution]

The relationship direction between 'Fact table 1' and 'Calendar' is wrong.  It should be 1:* (one to many) from 'Calendar' to 'Fact table 1'.  You should also only join to your calendar table using the date field.  Joining to fields that have things like Q1-22 or FY-2019 causes problems that you are running into.

You might have to add a real date filed to each of your Fact table.  If the period field is month-year for example, you make a field that is the first dat of the month for that year in the fact table, then join that to your calendar table.

[jdbuchanan71]

Can you show me a picture of your model?

[Xavier]

I tried to ... but Power BI not allowed it : "Ambiguity between Customers & Fact table 2" 

[jdbuchanan71]

@Xavier 

Your model view should look like this.

If one of the relationships is inactive you can double-click it to open the dialog to turn it on.

[Xavier]

Here is the model with the inactive relationship