Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Join Dimension to Multiple Facts using Date Between

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.startDat...
Need Help
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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 :

    IDStartDateEndDate
    1108101/01/201931/12/9999
    984302/05/201731/12/9999
    1180401/01/201931/12/9999
    984201/09/201731/12/9999
    984601/01/201831/12/9999
    984902/05/201731/12/9999
    1105301/10/202031/12/9999
    984802/05/201731/12/9999
    984502/05/201731/01/2020
    1105302/06/202030/09/2020
    985301/09/201731/12/9999
    984111/05/202030/11/2020
    984011/05/202031/05/2020
    985002/05/201731/01/2020

    Fact B :

    IDMesureDate
    110816991623/11/2020
    98436020220/01/2020
    118041346122/07/2020
    75827095610/02/2020
    106548689417/01/2020
    75827095626/10/2020
    90226991625/03/2021
    88304457929/03/2021
    88304457927/08/2020
    84468398924/12/2020
    81581346112/01/2021
    64308075107/02/2020
    90226991610/02/2020
    90226991604/03/2020
    89266103615/04/2021
    84468398912/08/2020
    84468398927/08/2020
    81581346126/10/2020
    75827095601/04/2020
    71986118706/05/2020
    71986118723/11/2020

     

    Fact C :

    IDMesure1Date
    95631029/12/2020
    8411103/09/2020
    8795206/11/2020
    8027721/12/2020
    11867329/12/2020
    10523815/01/2021
    9563609/03/2020
    63951003/09/2020
    8027617/08/2020
    6491121/12/2020
    9848103/12/2020
    9845107/01/2020
    11053326/01/2021
    6395128/04/2020
    6491328/08/2020

     

     

    Thanks in advance,

     

    • DataInsights's avatar
      DataInsights
      Icon for Super User rankSuper User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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,