Forum Discussion

PBILix's avatar
PBILix
Frequent Visitor
1 year ago
Solved

Need help with modeling relational Values

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)

 

ContractValue
A100
B200
C500
D800

 

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

 

ContractSuccessor Contract
AB
BD
CF
DG
DH

 

 

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. 

 

 

 

DAX
Modeling
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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]
    )
    )

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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]
    )
    )

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    1 year ago

    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.

  • PBILix's avatar
    PBILix
    Frequent Visitor
    1 year ago

    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?