Use Active and Inactive Relationships in same measure

[inesj]

Hello everyone,

I am quite new to PowerBI and I would need some help on active/inactive relationships.

There are two main tables, "Customer Master" and "Installed Base", linked through an active relationship on PARTY_ID <--> OWNER_PARTY_ID using the bridging table "Party IDs" to avoid many-to-many relationships. This relationship basically allows me to see which elements in the Installed Based are owned by each Customer. 

However, I would also like to see all the elements that are installed at each Customer site, and for this I have created an inactive relationship between the two tables on PARTY_SITE_ID <--> INSTALLED_AT_SITE_ID. 

The model looks as follows:

The user should be able to select a customer, and then see two tables:
1. Elements owned by the customer.

2. Elements installed at all the customer's sites BUT not owned by the customer. 

I have already created the first table without any issue, but I am having some problems with the second one. I was able to successfully show ALL elements installed at the customer's sites using the following measure:

COUNT_INSTALLED_AT =
CALCULATE(

DISTINCTCOUNT(Install_Base[INSTANCE_ID]),

USERELATIONSHIP(Customer_Master[PARTY_SITE_ID],Install_Base[INSTALLED_AT_SITE_ID])

)

This basically assigns "1" to an Instance ID which is installed at one of the customer's sites, and BLANK() to others. All I need to do is then to create a table and leave out the Instance IDs where the measure outputs BLANK(). 
However, I now only want to include in my table the Instance IDs where the owner is not the selected customer - that is, where the OWNER_PARTY_ID is not part of the selected Customer PARTY_IDs.

I have tried to change the measure to the following: 

COUNT_INSTALLED_AT =

VAR Owner =

CALCULATE(

SELECTEDVALUE(Customer_Master[PARTY_ID]),

ALL(Customer_Master)

)

VAR Counted =

CALCULATE(

DISTINCTCOUNT(Install_Base[INSTANCE_ID]),

USERELATIONSHIP(Customer_Master[PARTY_SITE_ID],Install_Base[INSTALLED_AT_SITE_ID])

)

RETURN

IF(

Counted <> BLANK(),

IF(

CONTAINS(Customer_Master, Customer_Master[PARTY_ID], Owner),

BLANK(),

Counted,

BLANK()

)
)

Unfortunately, this is not working. All it does is exclude the instances for which the OWNER_PARTY_ID = INSTALLED_AT_PARTY_ID. I am under the impression that it does not quite understand that I am using both the active and the inactive relationship in the same measure... 

For more clarity, you can see below what I am trying to achieve:

Any help would be greatly appreciated!! 😊
Please let me know if you need any further information. 

Many thanks in advance.
IJ

[Anonymous]

@inesj 

The problem with this model is that when you switch on the inactive relationship in a measure.... well, the model becomes ambiguous since there are now 2 paths that lead from PARTY_IDs to Install_Base. Such a setup should be avoided at all costs. You either have to rethink your model or make sure that every time you switch on the inactive relationship, you deactivate some other in order to prevent ambiguity.

Look for a vid on YT where Alberto Ferrari talks about model ambiguity at length. You'll then understand that what you're doing is just plain wrong.

[amitchandak]

@inesj , What is the role partysId table palying here. You should  have inactive join between customer party id and owner party id.

No need of partids table as per me 

refer https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[inesj]

Hello @amitchandak ,
Thanks a lot for your reply. 
The PartyIDs Table is there because the Customer Table and the Installed Base Table both contain duplicate PARTY_IDs, and I do not want any many-to-many relationship.

Thank you for the link, but unfortunately I have not found anything in there that could help me. I am already using the USERELATIONSHIP function, but it only solves part of my problem.
Do you have any other idea of how I could approach this?