Combining models together that have identical tables

[masplin]

sorry asking this again as stil lcompeltely stuck and got no answers last time

We have 2 systems one a transactional system and one a CRM system.  I have built seperate models for these. Both have a date table, client table and branch hierachy.   So in my Imported model i have DateTable T/DateTable CRM, Client T/Client CRM  etc.

to allow me to Use a single date structure to report measures form both models I created a new dateTable and then relationships bewtween the source datetables  and this new table. i used many to many and set direction as Datetable filter DateTableT/DateTable CRM.  This seeme dot work fine and i coudl make one visual with measures from both sources.

One issue we have is the start date in CRM has to be manually entered by checking what it is in the transactional system. This means it is often wrong as it changes, gets cancelled or simply no one does it. So I wanted to write a report showing start date in CRM and start date in Transactional to spot any differneces.  I took the same approach creating a new table "Client" which is just the account no.   i then created many to one relationship single from Client T/Client CRM to Client . 

This sort of works but not what I expected.  If I first select an account number from the master Clinet table and then data from the Client CRM table it works fine.  If I do the same and select data from client T table it also works fine so the relationships seem to be fine.  However if i try to select data from both T and CRM it errors saying cannot determine relationship between fields.  I'm compeltely foxed ???   Often this is because there isnt a measure that uses the relationships so tried creating an account count specifying the client relationships even thoguh they are active , but for one account from master client table it just lists every account in the other 2 tables so filtering isnt working. 

Map of relationships looks fine

I guess my first question is am I doing the right hing creating a duplicate table above that can filter the similar tables coming from the 2 models?  the only other way is pick one of the source tables as master and link the other one, but i tried this for dates and just got in a mess. 

If this is the right approach why cant i pick up column data from the both of the source tables at the same time only one at a time? 

Model contains sensitive data and dont know how I can chop it odwn to be able to share. 

Appreciate any ideas of how to resolve this

Thanks

Mike

[sturlaws]

Hi @masplin,

are all tables imported?

assuming this it the case, I cannot see a reason why you should have more than 1 date table. So either create a generic date table with DAX or Power Query, or merge the two date tables in Power Query to create 1 date table. 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[masplin]

So each moel has measures where the relationships in that model are being used. I cant edit the relationships in the comabined model so effectively you have to lin k the datetables together so you cnause a single date that gets pushed odwn to each date table ot allow you combine measure form different reports in one visiual

What i am saying is this works fine for measures. What doesn't seem to workis to pull different column data from each model in one visual. 

[sturlaws]

What doesn't seem to workis to pull different column data from each model in one visual. 

From your screen shot you have provided, I can see that your table Client PP has the column Business. Business does not exist in Client or Client CRM(as far as I can tell from your screen shot). If you add Business to a visual, the filtering effect will not propagate from Client PP -> Client -> Client CRM, because the relationships are set up with single direction, and single direction filters flow from the 1-side to the many-side. 

So when you pull in 'Client PP'[Business], tables on the CRM-part of your modell is not aware of any filtering done on this column.

The obvious solution is to change relationships to bi-directional, so that filters also can flow from the many-side to the 1-side. You could try it, but with the model you have, I think you will run into errors with ambiguity pretty quickly. Also, there are some hidden dangers with bi-directional filtering:
Bidirectional relationships and ambiguity in DAX - SQLBI

So what you really should do is to put some work in and merge the two Client-tables (and any other table you want filter to both "sides" of your model), this would allow you to keep the single direction relationship. The difficulty of this depends on what your tables look like. 

If you run into trouble and need more help, it is probably a good idea to create a sample report with sample data, reproducing the issues you have in your report/model. This would make it much easier to help you.

Cheers,
Sturla