Join Dimension to Multiple Facts using Date Between

[Anonymous]

Hi all,

We have a dimesion table A linked to 3 fact tables (B, C, D) and used in the same report.

The link between the dimesion A and the fact B is  based on A.ID=B.ID and B.Date between A.startDate and A.StartDate,

The link between the dimesion A and the fact C is  based on A.ID1=C.ID1 and C.Date between A.startDate and A.StartDate,

The link between the dimesion A and the fact D is  based on A.ID2=D.ID2 and D.Date between A.startDate and A.StartDate

I found a scenario with one dimesion linked to one fact using date between, and the solution was to create a list of DatesBetweens and merge the two tables. But in our case the dimension is used with 3 facts in the same report, so I'm running out of idea on how to model it. 

Can you please share ideas on how would you model it in Power BI?

Thanks in advance,

Joseph

[DataInsights]

@Anonymous,

Would you be able to provide sample data? Your post states "between A.startDate and A.StartDate", which sounds like two StartDate columns. Does your dimension table have StartDate and EndDate?

[Anonymous]

Hi,

Thank you for your reply and sorry for the delay.

You are right, I just made a mistake, the date is between A.startDate and A.EndDate :

The link between the dimesion A and the fact B is based on A.ID=B.ID and B.Date between A.startDate and A.EndDate,

The link between the dimesion A and the fact C is based on A.ID=C.ID1 and C.Date between A.startDate and A.EndDate,

The link between the dimesion A and the fact D is based on A.ID=D.ID2 and D.Date between A.startDate and A.EndDate

Dimension A :

ID	StartDate	EndDate
11081	01/01/2019	31/12/9999
9843	02/05/2017	31/12/9999
11804	01/01/2019	31/12/9999
9842	01/09/2017	31/12/9999
9846	01/01/2018	31/12/9999
9849	02/05/2017	31/12/9999
11053	01/10/2020	31/12/9999
9848	02/05/2017	31/12/9999
9845	02/05/2017	31/01/2020
11053	02/06/2020	30/09/2020
9853	01/09/2017	31/12/9999
9841	11/05/2020	30/11/2020
9840	11/05/2020	31/05/2020
9850	02/05/2017	31/01/2020

Fact B :

ID	Mesure	Date
11081	69916	23/11/2020
9843	60202	20/01/2020
11804	13461	22/07/2020
7582	70956	10/02/2020
10654	86894	17/01/2020
7582	70956	26/10/2020
9022	69916	25/03/2021
8830	44579	29/03/2021
8830	44579	27/08/2020
8446	83989	24/12/2020
8158	13461	12/01/2021
6430	80751	07/02/2020
9022	69916	10/02/2020
9022	69916	04/03/2020
8926	61036	15/04/2021
8446	83989	12/08/2020
8446	83989	27/08/2020
8158	13461	26/10/2020
7582	70956	01/04/2020
7198	61187	06/05/2020
7198	61187	23/11/2020

Fact C :

ID	Mesure1	Date
9563	10	29/12/2020
8411	1	03/09/2020
8795	2	06/11/2020
8027	7	21/12/2020
11867	3	29/12/2020
10523	8	15/01/2021
9563	6	09/03/2020
6395	10	03/09/2020
8027	6	17/08/2020
6491	1	21/12/2020
9848	1	03/12/2020
9845	1	07/01/2020
11053	3	26/01/2021
6395	1	28/04/2020
6491	3	28/08/2020

Thanks in advance,

[DataInsights]

@Anonymous,

Try this approach.

1. In Power Query, create a column DimKey in table DimensionA that is a concatenation of ID and StartDate. If the data is structured properly, each row should have a unique combination of ID and StartDate.

Text.From([ID]) & "-" & Date.ToText([StartDate], "yyyymmdd")

2. In Power Query, create a column DimKey in each fact table that retrieves DimensionA[DimKey]. The example below is for table FactB.

Table.SelectRows(
      DimensionA,
      (LookupTable) =>
        [ID] = LookupTable[ID]
        and [Date] >= LookupTable[StartDate]
        and [Date] <= LookupTable[EndDate]
    )

After creating the column, click the Expand icon and select DimKey.

3. Create a relationship between DimensionA and each fact table based on DimKey.

[Anonymous]

Hi,

Thank you so much for your answer!

I'm trying to implement it, and when I expand the column and select DimKey, it's taking a lot of time to load it. It has been loading for 14 hours now for just 225 000 rows (for one fact table). This fact table is the smallest one and it contains more than 300 000rows. So, I'm wondering if I should wait for it to finish loading, if there will be a performance issues later for refreshes...

Thanks in advance,

[DataInsights]

@Anonymous,

Do you have the ability to write custom SQL? If so, you could move this logic to the database and likely get better performance.

Another option is to use a dataflow in Power BI Service (a dataflow is Power Query online). You would connect your pbix to the dataflow.

[Anonymous]

Thank you so much for your answer!

I don't have this ability yet.

We are working with PBI on Report Server, so I understand that the only way is to customise the model in the database.

Thanks again!