Forum Discussion

MalgorzataF's avatar
MalgorzataF
New Member
9 months ago
Solved

MdxScript(Model) (37,5) Calculation error in measure

Hi,

I created a measure that is supposed to be used in a slicer. However inserting it in the slicer field, I get the following error: "MdxScript(Model) (37,5) Calculation error in measure. 'table1'[WA_LIBOR_Spread]: Column[Value] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

WA_LIBOR_Spread =
SWITCH(
    SELECTEDVALUE('Value'[Value]),
    "Facility Commitment Amount P1",
        DIVIDE(
            SUMX(
                'table1',
                'table1'[LIBOR_SPREAD] *
                'table1'[P1_COMMITMENT_USD]
            ),
            SUM('table1'[P1_COMMITMENT_USD])
        ),
    "Facility Commitment Amount P2",
        DIVIDE(
            SUMX(
                'table1',
                'table1'[LIBOR_SPREAD] *
                'table1'[P2_COMMITMENT_USD]
            ),
            SUM('table1'[P2_COMMITMENT_USD])
        ),
    "Facility Commitment Amount Change",
        DIVIDE(
            SUMX(
                'table1',
                'table1'[LIBOR_SPREAD] *
                (COALESCE('table1'[P2_COMMITMENT_USD], 0) -
                 COALESCE('table1'[P1_COMMITMENT_USD], 0))
            ),
            SUMX(
                'table1',
                COALESCE('table1'[P2_COMMITMENT_USD], 0) -
                COALESCE('table1'[P1_COMMITMENT_USD], 0)
            )
        ),
    "Outstanding Balance P1",
        DIVIDE(
            SUMX(
                'table1',
                'table1'[LIBOR_SPREAD] *
                'table1'[P1_FAC_NET_OS_USD]
            ),
            SUM('table1'[P1_FAC_NET_OS_USD])
        ),
    "Outstanding Balance P2",
        DIVIDE(
            SUMX(
                'table1',
                'table1'[LIBOR_SPREAD] *
                'table1'[P2_FAC_NET_OS_USD]
            ),
            SUM('table1'[P2_FAC_NET_OS_USD])
        ),
    "Outstanding Amt Change",
        DIVIDE(
            SUMX(
                'table1',
                'table1'[LIBOR_SPREAD] *
                (COALESCE('table1'[P2_FAC_NET_OS_USD], 0) -
                 COALESCE('table1'[P1_FAC_NET_OS_USD], 0))
            ),
            SUMX(
                'table1',
                COALESCE('table1'[P2_FAC_NET_OS_USD], 0) -
                COALESCE('table1'[P1_FAC_NET_OS_USD], 0)
            )
        ),
    BLANK()
)

where
Value = {
    ("Facility Commitment Amount P1", NAMEOF([P1_COMMITMENT_USD]), 0),
    ("Facility Commitment Amount P2", NAMEOF('table1'[P2_COMMITMENT_USD]), 1),
    ("Outstanding Balance P1", NAMEOF('table1'[P1_FAC_NET_OS_USD]), 2),
    ("Outstanding Balance P2", NAMEOF('table1'[P2_FAC_NET_OS_USD]), 3),
    ("Facility Commitment Amount Change", NAMEOF('table1'[Commitment Amt Change]), 4),
    ("Outstanding Amount Change", NAMEOF('table1'[Outstanding Amt Change]), 5)
}

I'm using direct query mode, and Value is a separate table, that is also used in another slicer, to allow the users to filter the data of the report.

Any idea on how can I fix this?

  • Hi MalgorzataF , Thank you for reaching out to the Microsoft Community Forum.

     

    You’re getting that error because the slicer is based on a field parameter (the Value table), which internally uses a composite key made up of both [Value] and [Value Fields]. Since your measure only references one part of that key ([Value]), the engine throws the error. Instead of using a measure directly in the slicer, create a disconnected table with a single column (for example, Selector[Option]) listing the same options and use SELECTEDVALUE(Selector[Option]) inside your measure to switch logic. Power BI slicers can’t take measures directly, they must be bound to columns.

3 Replies

  • v-hashadapu's avatar
    v-hashadapu
    Community Support

    Hi MalgorzataF , Thank you for reaching out to the Microsoft Community Forum.

     

    You’re getting that error because the slicer is based on a field parameter (the Value table), which internally uses a composite key made up of both [Value] and [Value Fields]. Since your measure only references one part of that key ([Value]), the engine throws the error. Instead of using a measure directly in the slicer, create a disconnected table with a single column (for example, Selector[Option]) listing the same options and use SELECTEDVALUE(Selector[Option]) inside your measure to switch logic. Power BI slicers can’t take measures directly, they must be bound to columns.

  • v-hashadapu's avatar
    v-hashadapu
    Community Support

    Hi MalgorzataF , 

    hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

     

  • v-hashadapu's avatar
    v-hashadapu
    Community Support

    Hi MalgorzataF , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.