Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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.

 

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.