Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Link Date Table with table 'In between'

Hi all,   There are 2 tables that I like to link together, however the date column I want to use is in another table. The setup is like this:    Date Table: This is the generic date table that li...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi mahoneypat 

 

I'm able to create the relationship, but whenever I do a calculation based on the date, it doesn't seem to recognize the date table properly. Maybe I've set it up wrongly? 

 

 

Regards,

 

Justin

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Please make sure the data types are the same between your two tables, and that both are Date data type (not DateTime or text).

Regards,

Pat

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi mahoneypat.

     

    Thanks again for your help. I've tried your suggestion but don't get any closer than the following result, which shows the connection isn't quite working yet. (I only expect values in 2020.)

     

    I'm afraid the issues might be caused because my main date column is in the following format datetime format: 

    I've created an similar column in the monitor_export table and link it together, but to no avail. Is the 'time' part the issue? Any way to fix it in that case?

     

    Thanks again for thinking along.

     

    Regards,

     

    Justin

     

    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      5 years ago

      The data type and values on both sides of each relationship have to match.  You can highlight the column in Data View and in the ribbon change it from DateTime to Date.  However, if they are now both DateTime, that should work too.  If you can share your pbix (or a representative mock one), I can look at it directly.  You can upload it to Google Drive or OneDrive and share the link.

      Regards,

      Pat