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.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

 

 

 

 

Need Help

6 Replies

  • DataInsights's avatar
    DataInsights
    Super 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
        Super 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.