Search calendar table to multiple fact tables date/date exit problem

[LUCASM]

I have 75 all SQL tables that I don't have access to, most of which are linked and some have a date field and some have a datetime field

I have also created a lookup table [calendar] which for example I want to return the start of the week date - there might be other fields that I want to return.

The problem I'm having is that if 2 tbles are linked by an [ID] key and I want to use the calendar to return the start date of the week based on a field created in each Power BI table will not allow me to activate it.

This becomes more complex, because there is no datetime field in the [Calendar] table.

So I need to find a way to have a lookup table and convert the date and time stamp to a date during the search.

Any idea.

As this is quite complex I have linked a much simpler pibx file.

Example pbix

[v-easonf-msft · Accepted solution]

Hi， @LUCASM 

In fact ,DAX allows you to override the active relationship and use the inactive relationship in your formulas instead.  To do this, you must use the DAX function USERELATIONSHIP inside a CALCULATE function.

For more details ,you can refer to this post.

multiple-relationships-between-tables-in-dax

Best Regards,
Community Support Team _ Eason

[Greg_Deckler]

I get this when trying to access the PBIX

[LUCASM]

Thats strange

I'll try again

Example file 

....my bad it was a typo. The problem with copying the link by eye and not cut and pasting.

I'll hang my head in shame.