Create 2 one to many relationships to same table?

[studley]

My transaction table has columns for sending location, receiving location, and quantity. I need to calculate location balance by summing the quantities for all transactions where the location is the receiver and subtracting the sum of all transactions where the location is the sender. I was able to do this in SQL server by referencing the transaction table twice, once with an alias.

[studley · Accepted solution]

Thanks for the replies. I decided to use Power Query to make two partial copies of the table: one for the sender point of view and one for the receiver point of view. 

[Anonymous]

Thanks for the reply from @Greg_Deckler , please allow me to provide another insight:

Hi @studley ,

There can be only one active relationship between two tables; if you want to create multiple relationships, you can create multiple inactive relationships.
The inactive relationship can be activated in the formula by the USERELATIONSHIP function. Refer to the following formula:

MEASURE =
CALCULATE (
    MAX ( 'Table'[Value] ),
    USERELATIONSHIP ( 'Date'[Date], 'Table'[EndDate] )
)

For more details, you can refer to below document:

Active vs inactive relationship guidance - Power BI | Microsoft Learn

Best Regards,
Adamk Kong

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

[Greg_Deckler]

@studley 

You can do this by creating an inactive relationship and then using USERELATIONSHIP. If that doesn't help...

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.