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

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