Is there a way to bypass the ambiguity between tables when creating relationships?

[Anonymous]

This is probably a silly question, however, I want to know if there's a less restrictive process? I currently have 13 tables in this Project. And as clunky and messy as it looks, this is the best current way I've been able to map/relate the tables so that it works and filters correctly. However, I can't get the Deals table to link to the Timesheets and Projects tables. As shown below:

In the bottom right, I am trying to set the relationship between Deal Id and Project Id. I can only set it to either Deals, or Timesheets/Projects, only one at a time, not both. I've tested on each and neither Deals or Projects filter towards each other. When I try to link Project Deal Mapping table to both tables, this is what happens:

I understand why ther ewould be ambiguity between the tables, but in SQL and other formats for instance, you can set multple foreign keys and relationships. In Power Bi you can't seem to do that, there is a clear relationship between Deals and Projects but I can't create it. 

And I can't start scrapping other relationships without the whole project falling apart. Any help would be appreciated. 

[amitchandak]

@Anonymous , There are two many to many , bi-directional join. You should prefer 1 to many single directional.

Also If needed Many to Many Single Direction (filter the fact/detailed) from master

[Anonymous]

@amitchandak Hi, I've made some adjustments and redued many to many relationships and bi-directional but I still can't get a relationship between the two. 

Is there another way to get around this? 

[v-kkf-msft]

Hi @Anonymous ,

Such a relationship is not allowed to exist in Power BI: A -> B -> C -> A. So there can be only one relationship active between Deals/Timesheets and Timesheets/Projects.

You can modify the direction of cross-filtering between other tables so that they cannot form a circle. For example, change the "Both" between table Deals (CRM) and table Project Deal Mapping to "Single(Project Deal Mapping filters Deals (CRM))". 

https://docs.microsoft.com/power-bi/transform-model/desktop-relationships-understand 

Or use the USERELATIONSHIP function to make the relationship active.

https://docs.microsoft.com/power-bi/guidance/relationships-active-inactive 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-kkf-msft Hi thanks for the response. 

First, I couldn't adjust those relationships to be the way you suggested they should be, but that was to be expected. However, I'm not sure how to insert the USERELATIONSHIP function in my context. 

Whenever I select a Deal, I want there to be Projects shown to that relevant Deal. So how would I use the USERELATIONSHIP function in this context? I've looked online but it appears to be only used for calculation only. 

[v-kkf-msft]

Hi @Anonymous ,

Create an inactive relationship between Deal table and Projects table.

Then use measures like the following:

Max_DealID = 
CALCULATE (
    MAX( Deal[Deal ID] ),
    USERELATIONSHIP ( Deal[Product ID], Projects[Project ID] )
)

SumValue1 = 
CALCULATE (
    SUM ( Projects[Value1] ),
    USERELATIONSHIP ( Deal[Product ID], Projects[Project ID] )
)

SumValue2 = 
CALCULATE (
    SUM ( Projects[Value2] ),
    USERELATIONSHIP ( Deal[Product ID], Projects[Project ID] )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.