Relationship Issue between two tables in Power BI (Ambiguity issue)

Question

Have the following tables in the Power BI data model:

1.       Transaction

Buyer	Manufacture	TransactionAmount
Harrison	Patrick	50
Harrison	Thomas	40
Harrison	Jack	90
Aliza	Patrick	82
Aliza	Thomas	70

2.       Manufacturer

Manufacturer	Regions
Patrick	Western Australia
Thomas	Northern Territory
Jack	New South Wales

3.       Buyer

Buyer	Regions
Harrison	Hunter Valley
Aliza	Barossa Valley

The requirement:

when I select the relevant region want to show the corresponding total transaction amount on the card.

The slicer should include all the regions that came from both buyers and manufacturers.

Created the other table Regions, using the following query and added the relationships as follows:

Regions =
CALCULATETABLE (
    UNION (
        SUMMARIZE ( Buyer, Buyer[Regions] ),
        SUMMARIZE ( Manufacture, Manufacture[Regions] )
    )
)

Problem:
Can't create an active relationship between Buyer and Region. Therefore, unable to filter the transaction amount correctly.

Therefore, I expected the correct way to solved this issue without changing the data model and relationships among the Buyer, manufacturer and Transaction tables

Answer 1

First of all, in your production model you should make sure your relationships from your Regions table are 1-to-many against your Manufacturer and Buyer table. Furthermore, the DAX expression you have used to construct your Regions table is not great, consider changing to:

Regions = 
DISTINCT ( 
    UNION ( 
        DISTINCT ( Manufacturer[Regions] ) , 
        DISTINCT ( Buyer[Regions] ) 
    )
)

Lastly we arrive at your question: How to get around the fact that only one of the relationships from Region can be active, to avoid model ambiguity issues, i.e. that filters from your Region table cannot have two possible filter propagation routes toward your Transaction table.

To solve this issue, without changing your datamodel as you request, we need to write a fairly advanced measure that introduces a couple of variables to store the Regions values that are active in your Buyer and Manufacturer tables, given a certain slicer selection on the column Regions[Regions]. Furthermore, the variables will be used to filter the Transaction table to ensure that we only evaluate each row in Transaction once.

The code below assumes that the table that has an inactive relationship with Regions is the Buyers table, as in your data model:

Transaction Amount =
VAR _buyers =
    CALCULATETABLE (
        VALUES ( Buyer[Buyer] ),
        USERELATIONSHIP ( Regions[Regions], Buyer[Regions] )
    )
VAR _manu =
    VALUES ( Manufacturer[Manufacture] )
RETURN
    SUMX (
        FILTER (
            ALL ( 'Transaction' ),
            'Transaction'[Buyer] IN _buyers
              || 'Transaction'[Manufacture] IN _manu
        ),
        'Transaction'[TransactionAmount]
    )

To allow for multiple selections (e.g. Hunter Valley and Northern Territory) we must calculate both buyers and manufacturers that are selected separately.

To calculate which buyers are active, we need to output the VALUES of buyers in the Buyers table that correspond to the Regions[Regions] selection. Since this relationship is inactive we need to use CALCULATETABLE to invoke the special USERELATIONSHIP function.

For manufacturers, this is much easier since we do not need to change our filtering context to use an inactive relationship, so here VALUES alone will produce the correct result.

To finalize, we use an iterator (SUMX) over the Transactions table, where we sum up the TransactionAmount for all rows where the corresponding Transaction[Buyer] value is in the list of selected buyers, OR the Transaction[Manufacturer] value is in the list of selected manufacturers.