Model Conception issue - How to have a date table linked to several fact tables ?

[Anonymous]

Hi , 

I have an issue that makes me crazy for some days now : how to have a date table linked to several fact tables to be able to have a synchronised date filter in my report ?

Here is a screen shot of my model to explain : 

So I have 4 fact tables :

- SecuriteAuTravail

- FactSecuriteAuTravail_Lesions

- FactSecuriteAuTravail_Categ

- FactSecuriteAuTravail_MoyLoc

There is a field "Annee" in each table. There is also a field "Month" in each table but I use the filtering by month only for SecuriteAuTravail table. The 3 other fact tables are filtered on year only in the report.

As you can see, there is also a dimension table HierarchieService which is linked to the 4 fact tables, it is just to get hierarchy and labels of the different levels of services in the company. This one creates problem of ambiguous path, you will see in my explanations below.

Today, what I have in the report is : 

1/ Tabs linked to "SecuriteAuTravail", all with a synchronized date filter, one filter on year, one filter on month (period to be exact). This is done with a filter on Calendrier[AnnéeSlicer] for the year and Calendrier[PériodeSlicer] for the period of month :

2/ tab linked to FactSecuriteAuTravail_Lesions where the year slicer is directly based on FactSecuriteAuTravail_Lesions[Annee]:

3/ tab linked to FactSecuriteAuTravail_Categ where the year slicer is based on FactSecuriteAuTravail_Categ[Annee]

4/ tab linked to FactSecuriteAuTravail_MoyLoc where the year slicer is based on FactSecuriteAuTravail_MoyLoc[Annee]

What I would like is to have a single year filter in the report that would be synchronised between the different tabs. Or at least, keeping the date filter on year+month for SecuriteAuTravail table but having one common year filter for the 3 Lesions/Categ and MoyLoc tables.

As you can see in my model, I have tried to import a new calendar table (calendrier_lesionscategloco), I have also added a "numero_year" table between calendrier_lesionscategloco and my fact table to get single values for the available years : 

Then I thought I could link the field Numero_year[Yearname] to each year field in my 3 fact tables, putting a bidirectional relationship.

But I can't 😕

 I can only link with one fact table, then I got an error message of ambiguity for the 2nd one because of the HierarchieService table which is linked to the 4 fact tables : 

Let me know if it's not clear and if you need more details to figure it out.

Thanks a lot for your help, AnSo (beginner in power bi)

[BA_Pete · Accepted solution]

Hi @Anonymous ,

I would create a 'YearPeriod" column in your calendar table and in each of your fact tables. This should be as easy as merging the Year column and the Period column into a new column in Power Query. This will give you a column with values such as: 201911, 201912, 202001, 202002 etc. You should then be able to join your calendar table, one-to-many, to each fact table using this YearPeriod column with no problem.

Use a column(s) from your Calendar table to populate slicers and it should filter all of your fact tables correctly.

Pete

[Anonymous]

Hi BA_Pete, 

Thanks for your answer. In the join between my calendar table, and each fact table, should it be a bidirectional join or a "normal" one ? because if it has to be a bidirectional ,i will still have the error of ambiguous path 😕

thanks for your help, AnSo

[BA_Pete]

Hi @Anonymous ,

Did this work out OK for you?

Pete

[BA_Pete]

@Anonymous 

A 'normal' many-to-one, (many side fact table, one side calendar) should work fine.

Pete

[nvprasad]

HI,

We can create one calculated table using "calanderauto" function ( to get dates between min and max date from the entire model) and both directional relationships with all fact tables.

It should work.

Thanks & Regards,

Venkata Nalla

[Anonymous]

I already have a calendar table (Calendrier and also calendrier_lesioncategloco) with all dates for the model, why would it change to have another calculated table ? the error will be the same for the ambiguous path, wouldn't it ?

Here is part of the structure of my actual calendar table :

let me know if you think the calendarauto function can have a different behaviour and i will try. Thanks a lot