Relationship between two tables with multiple dates and names

[Anonymous]

Hello,

I'm new to the forum and looking for some help on a problem I've been researching for a few weeks now. Not sure if there is a post out there already answering this, but I haven't found one yet for this specific problem. Perhaps I'm not wording it correctly and that is why, so I apologize if this question has already been answered.

Goal: I am trying to build a client facing dashboard/report that pulls information from 3 different datasets. The report needs to be able to have two slicers that will filter all the charts displaying data to represent a specific client and a specific date range. 

Problem: On each independent dataset there are client names in a column and dates in a column; the other columns are each filled with different data points. The relationship manager is not letting me make both a date and client relationship active between all 3 datasets. It will allow 1 type of relationship (client or date), but says that the second relationship can't be active as it influences the other relationship and can 'cause ambiguity'.

For additional context: Each dataset has multiple instances of the same date and same client in each column which I know impacts the relationship type. I tried making a specific, separate client table and date table to then link them together through a sort of 'third party' but even that didn't work. 

I would appreciate any help or guidance! I can provide additional information if that would be helpful to solve this problem. Thanks in advance!

[Mishra-Sama]

Hi 
@Anonymous 

As I understood you were looking for , multiple relationship in your model 
there is a way through DAX function USERELATIONSHIP

using this function you can activate required relationship as per situation . And in modeling whichever relation you have connected will remain activated and this dax will be get activated when you use this in any visual else it will remain deactivated.

let me know if this is helpful . 

[AntonioM]

Your separate table(s) idea is the way to go here. As you've said, it won't let you link multiple columns between the tables, so you need to create a client table and a date table, then link both of those to each of your three tables.

If you have a list of all the possible clients you could use that, otherwise you could create the table using something like

Clients =
DISTINCT (
    UNION (
        VALUES ( Table1[Client] ),
        VALUES ( Table2[Client] ),
        VALUES ( Table3[Client] )
    )
)

There are lots of different ways to make a date table but the simplest is probably to use 

Date Table =
CALENDARAUTO ()

Once you've made these and linked them, you can use the columns from these two tables in your slicers and it will filter all three data tables

[Anonymous]

Thanks for this suggestion, but I'm getting a message saying that when I try to create the second relationship it won't go active due to 'creating ambiguity'.

For reference:

Email data table relationship with client table

Reply data table relationship with client table

Email data table relationship with date table

Reply data table NO relationship with date table due to ambiguity

How do I overcome this ambiguity error message?

[AntonioM]

Please could you send a screenshot of the relationships in the model view?

Ambiguity means that one table could filter another table in more than one way (along different paths). It sometimes happens if you have bidirectional relationships.

[Anonymous]

Sure! Here are the relationships:

The dates to linkedIn feed won't go active due to this error message:

Do these screenshots help? 

[AntonioM · Accepted solution]

Are any of these set up bi-directional, with the filter going both ways? That's the only reason I can think of. 

You'll just want the filter direction from clients and dates to the other tables, which shouldn't cause any problems.

[Anonymous]

This worked! I was able to change the directions and it allowed the relationships and fixed the charts.

Thank you so much for your help!