cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rachel_g Frequent Visitor
Frequent Visitor

Join multiple tables to date table

Hi all

 

I'm having trouble with table joins to a Date Table and wondered if anyone can assist. I need to use the Date Table to create filter slicers that will filter multiple table visualisations, so I need direct relationships from the Date Table to these multiple tables. The problem is I am getting the "ambiguous" message when I try to connect the Date Table to one of the additional tables as it is forcing an indirect relationship.

 

Here are my tables and existing relationships. The Date Table and indirect relationship are marked in green:

 

 

PBI date issue.png

 

Here is the message I get when I try to make the relationship between DateTable and Marine_GL_Trans direct:

 

 

PBI error ambiguity.PNG

 

 

My date fields are all Date, the DateTable is marked as a Date Table, and I don't want to use the USERELATIONSHIP dax as this is for a slicer so I can filter across tables.

 

It was working fine without issue until I added the additional table SOP10106 with joins to Marine_GL_Trans and Visit, but these are needed to get revenue.

 

Can anyone help, please?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ross73312 Super Contributor
Super Contributor

Re: Join multiple tables to date table

So the question that needs to be asked is what about the data inside the 2 other tables inbetween are causing you to lose rows.

 

If you had a more simple structure of simply the Date table and the GL table, you might have very different results.  Since those other 2 tables are part of your structure, their anomolies compound.  You can't have the direct link, because then your model becomes ambigious  (as there are 2 different possible outcomes from a single date filter).

 

For example, if you had a date of the 1st of January in your GL and Date table, but not within the Visit and SOP tables, then data for the 1st will not be shown from your GL table, as the other tables caused it to be filtered out.

 

If that is your current problem, your inactive link you have created is your plan B.  Inside DAX you can use a formula called "Use Relationship" to force a particular measure to make a more specific calculation using your inactive relationship.  This video discusses the concept in a very easy to understand way:

 

https://www.youtube.com/watch?v=2BxaUXlx3K4

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

3 REPLIES 3
Ross73312 Super Contributor
Super Contributor

Re: Join multiple tables to date table

Because of the way the table relationships are already set up, the Date Table already has a relationship to your Marine GL Trans table.  The very nature of your 1-* relationship chains means that the relationship already exists.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


rachel_g Frequent Visitor
Frequent Visitor

Re: Join multiple tables to date table

Hi there

 

Thanks for the reply. That is what I thought, and yet as soon as I add date fields from my date table, I lose my $ amounts which come from the Marine_GL_Trans table.

 

PBI no data.PNG

When the slicer is cleared, the table reverts to all data, with amounts. So the existing relationships aren't sufficient to connect it to the DateTable.

 

PBI date issue.png

 

Ross73312 Super Contributor
Super Contributor

Re: Join multiple tables to date table

So the question that needs to be asked is what about the data inside the 2 other tables inbetween are causing you to lose rows.

 

If you had a more simple structure of simply the Date table and the GL table, you might have very different results.  Since those other 2 tables are part of your structure, their anomolies compound.  You can't have the direct link, because then your model becomes ambigious  (as there are 2 different possible outcomes from a single date filter).

 

For example, if you had a date of the 1st of January in your GL and Date table, but not within the Visit and SOP tables, then data for the 1st will not be shown from your GL table, as the other tables caused it to be filtered out.

 

If that is your current problem, your inactive link you have created is your plan B.  Inside DAX you can use a formula called "Use Relationship" to force a particular measure to make a more specific calculation using your inactive relationship.  This video discusses the concept in a very easy to understand way:

 

https://www.youtube.com/watch?v=2BxaUXlx3K4

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors