Forum Discussion
Two Relationships Between the Same Two Tables
I have an odd data structure in my ERP. A/R Transactions (invoices, credit memos, etc) is a parent table, and A/R Applications is a child table. However, the applications are linked to TWO records in the transactions table.
| ARTransNum | Amount |
| I-001 | 500 |
| I-002 | 600 |
| CR-001 | -1100 |
| ARApplicationNum | ARTransNum | ApplyToARTransNum | Amount |
| I-001-001 | I-001 | 500 | |
| I-002-001 | I-002 | 600 | |
| CR-001-001 | CR-001 | I-001 | -500 |
| CR-001-002 | CR-001 | I-002 | -600 |
I need two links:
ARTransaction.ARTransNum (O) <- (M) ARApplication.ARTransNum
ARTransaction.ARTransNum (O) <- (M) ARApplication.ApplyToARTransNum
But when I try to create two links in the semantic model, I get the following error:
Is it possible to set up two relationships like this?
You can only setup a single active relationship between two tables. You can concatenate the two fields in each table to make a single field for the join.
Alternatively you can use calculate( something,( treatas( ARTransaction[ARTransNum], ARApplication[ApplyToARTransNum])) to create a virtual relationship for a given measure.
The exact implementation will depend on what you are trying to do
4 Replies
Hello,
Deku posted some really useful information - just want to expand on some points as it may be useful.
You can only make 1 active relationship between 2 tables but you can actually make more than one (the others would be inactive). In order to use the other relationships, you can use the USERELATIONSHIP function within your dax code.
The last thing to mention is that you can also duplicate the table and then make one relationship to each of them (this is bad practice though as you're duplicating the data).
You can only setup a single active relationship between two tables. You can concatenate the two fields in each table to make a single field for the join.
Alternatively you can use calculate( something,( treatas( ARTransaction[ARTransNum], ARApplication[ApplyToARTransNum])) to create a virtual relationship for a given measure.
The exact implementation will depend on what you are trying to do
It's a little crazy to me that that limitation exists when something like 4D has been able to do multiple named relationships since 2001. Power BI is supposed to be top of the line, lol. Regardless, thank you for the answer.
True and no worries. As an actual solution for what you're trying to do - I'd probably recommend making 2 actual reports instead. Double the maintenance but it would be the cleanest UX.
