one table for two relationship

[AndrejZevzikov]

HI, Guys
I want optimize my reports and searching the solution. I have two tables:
1) Companies (company_title, id)
2)  Sales (Client_company_id, Responsible_company_id, amount)
I want have two slicers with company_title one slicer for client, other for responsible company. At this moment I just copy companies table and create two active relationships, and create two slicers from different companies tables.
But i searching other soluction without copying companies table, maybe create one active and one inactive relationship ant use DAX, with userelationship, but it dosen't work. Any ideas?

[v-eqin-msft · Accepted solution]

Hi @AndrejZevzikov ,

To my knowledge, if two tables are linked by more than one relationship, you can decide which relationship to activate by using USERELATIONSHIP().The relationship must already be in the model. You cannot use USERELATIONSHIP to create a relationship on the fly. USERELATIONSHIP can only activate an existing relationship.

And according to the official document:

• USERELATIONSHIP uses existing relationships in the model, identifying relationships by their ending point columns.

• In USERELATIONSHIP, the status of a relationship is not important; that is, whether the relationship is active or not does not affect the usage of the function. Even if the relationship is inactive, it will be used and overrides any other active relationships that might be present in the model but not mentioned in the function arguments.

• An error is returned if any of the columns named as an argument is not part of a relationship or the arguments belong to different relationships.

• If multiple relationships are needed to join table A to table B in a calculation, each relationship must be indicated in a different USERELATIONSHIP function.

Please refer to this which provide an example that explains the function in great detail.

If this post does not make sense, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

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