Forum Discussion
Join Dimension to Multiple Facts using Date Between
Super UserAnonymous,
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?
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.
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.