DAX Sematic/Syntax Error

[Timi99]

Hello All,

Could you please help with the below issue.

Issue: Semantic Error. The sysntax for SWITCH is INCORRECT.

Total_RD_MTM_Amount_CCY:=SUMX(TradeTransactionFinTrade, TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY]/TradeTransactionFinTrade[Traded_CCY] )
SWITCH (TradeTransactionFinTrade[Traded_CCY] =
"JPY", SUMX (TradeTransactionFinTrade, TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY]/TradeTransactionFinTrade[RD_Rate_SettleCCY_JPY]) ,
"USD", SUMX (TradeTransactionFinTrade, TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY]/TradeTransactionFinTrade[RD_Rate_SettleCCY_USD]) ,
"GBP", SUMX (TradeTransactionFinTrade, TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY]/TradeTransactionFinTrade[RD_Rate_SettleCCY_GBP]) ,
SUMX (TradeTransactionFinTrade, TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY])
)

I appreciate your kind help.

[v-jiewu-msft · Accepted solution]

Hi @Timi99 ,

If I understand correctly, your issue is the syntax for SWITCH is incorrect. Based on the information, you can follow these steps:

1.The syntax for SWITCH is as follows:

         DAX

         SWITCH(<expression>, <value>, <result>[ , <value>, <result> ]…[ , <else> ])

2.You can use the SWITCH function as follows: 

Total_RD_MTM_Amount_CCY :=
SUMX (
    TradeTransactionFinTrade,
    TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY] / TradeTransactionFinTrade[Traded_CCY]
)
    + SWITCH (
        TRUE (),
        TradeTransactionFinTrade[Traded_CCY] = "JPY",
            SUMX (
                TradeTransactionFinTrade,
                TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY] / TradeTransactionFinTrade[RD_Rate_SettleCCY_JPY]
            ),
        TradeTransactionFinTrade[Traded_CCY] = "USD",
            SUMX (
                TradeTransactionFinTrade,
                TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY] / TradeTransactionFinTrade[RD_Rate_SettleCCY_USD]
            ),
        TradeTransactionFinTrade[Traded_CCY] = "GBP",
            SUMX (
                TradeTransactionFinTrade,
                TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY] / TradeTransactionFinTrade[RD_Rate_SettleCCY_GBP]
            ),
        SUMX (
            TradeTransactionFinTrade,
            TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY]
        )
    )

[123abc]

I think there is a syntax error in your DAX:

The SWITCH function is being used incorrectly.

Please try this:

SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, ..., <else_result>)

In your code, it looks like you are trying to use SWITCH to conditionally sum values based on the currency type. Here's the corrected code:

Total_RD_MTM_Amount_CCY :=
SUMX (
TradeTransactionFinTrade,
SWITCH (
TRUE (),
TradeTransactionFinTrade[Traded_CCY] = "JPY", TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY] / TradeTransactionFinTrade[RD_Rate_SettleCCY_JPY],
TradeTransactionFinTrade[Traded_CCY] = "USD", TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY] / TradeTransactionFinTrade[RD_Rate_SettleCCY_USD],
TradeTransactionFinTrade[Traded_CCY] = "GBP", TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY] / TradeTransactionFinTrade[RD_Rate_SettleCCY_GBP],
TradeTransactionFinTrade[RD_MTM_Amount_Period_End_CCY]
)
)

In this corrected code, I've used the SWITCH function with the TRUE() condition to evaluate multiple conditions. Make sure to replace this corrected code with your existing code and test it to see if it resolves the syntax error.