Link Date Table with table 'In between'

[Anonymous]

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 links to all other tables (and I want to use to filter etc).

Monitor_export (This table holds an ID column and the execution_date which I want to use as date). 

Monitor_export_data(This table holds an ID column that links with monitor_export, but has no date column that I want to link to the 'Date Table'.

I want to be able to filter the values in monitor_export_data, based on the execution_date column. But however I structure the relationships, I'm not able to make it work. Should I create a direct connection between 'monitor_export_data' and 'Date'?

 3 Tables I want to Link

Hope someone is able to help! Thanks in advance. 

[mahoneypat]

Seems like you could relate Date and Monitor_export on Execution Date, and then connect Monitor Export and Monitor Export Data on the ID column.  Does that not work?

Regards,

Pat

[Anonymous]

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]

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]

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]

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