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

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?