Forum Discussion

mgallacher's avatar
mgallacher
Frequent Visitor
8 years ago
Solved

Mapping Dates Through Bridge Table

I have one many to many table AS orders, one bridge table AS Account Manager ID and the third many to many table AS Master Data(2).   I created a bridge table so my many to many tables could connec...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi mgallacher,

     

    You can create a date dimension table (suppose it's named as 'DimDate') using CALENDAR() function. Then this new table will contain a column with unique date values. Then, you can create a one to many relationship between 'orders' and 'DimDate' based on date field. Also, create a one to many relationship between 'Master Data(2)' and 'DimDate'. In that case, 'DimDate' works as a bridge table.

     

    Regards,

    Yuliana Gu

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi mgallacher

 

Why not have a separate date table that you connect to both your Orders and Master Data(2) table and use that as your filter table?

 

 

  • mgallacher's avatar
    mgallacher
    Frequent Visitor
    8 years ago

    Could you give a brief explanation or visual as to how you would set that up? I'm just a marketer who plays an analyst at work. I appreciate the help. 

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      8 years ago

      Hi mgallacher,

       

      You can create a date dimension table (suppose it's named as 'DimDate') using CALENDAR() function. Then this new table will contain a column with unique date values. Then, you can create a one to many relationship between 'orders' and 'DimDate' based on date field. Also, create a one to many relationship between 'Master Data(2)' and 'DimDate'. In that case, 'DimDate' works as a bridge table.

       

      Regards,

      Yuliana Gu

      • mgallacher's avatar
        mgallacher
        Frequent Visitor
        8 years ago

        Thank you! That solution worked well and I can create a filter from the new date table.