Need help with modeling relational Values

[PBILix]

I have a contract Table at a contract granularity level. Each Contract has a certain Value

I have another Table which displays the succesor of a contract ( I could also integrate the predecessor in the future)

Contract Table (holds all contracts)

Contract	Value
A	100
B	200
C	500
D	800

Relation Table (shows which contracts emerged from the contracts) One Contract can become one or many new contracts

Contract	Successor Contract
A	B
B	D
C	F
D	G
D	H

I need to display

1. Which contracts emerged from a certain contract

2. Which values these contracts hold

3. Dynamic, when i filter a contract, i need to display what are the precedor and succesor contracts of the filtered ones. 

[PBILix]

This worked fine! Thanks for your fast and helpful input. 

I implemented your solution and am now able to see all succesor and preceding contracts. 

Do you have an idea how I could use these contracts as a filter? E.G. I select a Succesor contract and the contract Table is filtered for this succesor contract?

[V-yubandi-msft]

Hi @PBILix ,

We haven't heard back from you yet, so we're checking in to see if the solution provided by @bhanu_gautam  resolved your issue. If you have any further questions or need additional assistance, please don't hesitate to reach out.

Your feedback is very important, and we look forward to hearing from you soon.

Thanks.

[bhanu_gautam · Accepted solution]

@PBILix 

Create relationships between the tables:

Contract Table[Contract] -> Relation Table[Contract]
Contract Table[Contract] -> Relation Table[Successor Contract]

Create a calculated column in the Relation Table to get the value of the successor contracts:

Successor Value =
LOOKUPVALUE(
'Contract Table'[Value],
'Contract Table'[Contract], 'Relation Table'[Successor Contract]
)

Create a measure to get the predecessor contracts:

DAX
Predecessor Contracts =
VAR SelectedContract = SELECTEDVALUE('Contract Table'[Contract])
RETURN
CALCULATE(
CONCATENATEX(
FILTER(
'Relation Table',
'Relation Table'[Successor Contract] = SelectedContract
),
'Relation Table'[Contract],
", "
)
)

Create a measure to get the successor contracts:

DAX
Successor Contracts =
VAR SelectedContract = SELECTEDVALUE('Contract Table'[Contract])
RETURN
CALCULATE(
CONCATENATEX(
FILTER(
'Relation Table',
'Relation Table'[Contract] = SelectedContract
),
'Relation Table'[Successor Contract],
", "
)
)

Create a measure to get the values of the successor contracts:

DAX
Successor Contract Values =
VAR SelectedContract = SELECTEDVALUE('Contract Table'[Contract])
RETURN
CALCULATE(
SUMX(
FILTER(
'Relation Table',
'Relation Table'[Contract] = SelectedContract
),
'Relation Table'[Successor Value]
)
)