cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!


   


Highlighted
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 377 members 3,781 guests
Please welcome our newest community members: