Forum Discussion

cjcj's avatar
cjcj
Icon for Helper I rankHelper I
4 years ago
Solved

Switch with multiple conditions from 2 tables

Hi, is there a way to set swich with mutiple conditions from 2 tables? follwowing works fine for 1 condition from 1 table.

 

TEST = 

sumx(
  'table1', 

    Switch (

      True(),

         'table1'[color] = "RED", 2345,    (i would like to modify:  'table1'[color] = "RED" && 'table2'[temp] = "WARM", 2345,)

         'table1'[color] = "BLUE", 1345, (i would like to modify:  'table1'[color] = "BLUE" && 'table2'[temp] = "Hot", 1345,)

    )

)

Thank you!

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi cjcj ,

    If you can't use the RELATED function, you can modify the formula like this:

    TEST =
VAR _T =
    ADDCOLUMNS (
        'table1',
        "temp",
            MAXX (
                FILTER ( 'table2', 'table2'[ID] = EARLIER ( table1[ID] ) ),
                'table2'[temp]
            )
    )
RETURN
    SUMX (
        _T,
        SWITCH (
            TRUE (),
            [color] = "RED"
                && [temp] = "WARM", 2345,
            [color] = "BLUE"
                && [temp] = "HOT", 1345
        )
    )

    It can also get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi cjcj ,

    According to your description, I create a sample.

    table1:

    table2:

    The two tables are related with the ID column. Here's my solution.

    Create a measure.

    TEST =
VAR _T =
    ADDCOLUMNS ( 'table1', "temp", RELATED ( 'table2'[temp] ) )
RETURN
    SUMX (
        _T,
        SWITCH (
            TRUE (),
            [color] = "RED"
                && [temp] = "WARM", 2345,
            [color] = "BLUE"
                && [temp] = "HOT", 1345
        )
    )

    As you can see, only the ID1 and ID2 match the condition. Get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • cjcj's avatar
      cjcj
      Icon for Helper I rankHelper I
      4 years ago

      v-yanjiang-msft Thank you for your response!  I am getting an error saying the Parameter is not the correct type.  Since I am using DirectQuery, I am not sure how or if the 2 tables are related.

       

      ADDCOLUMNS ( 'table1', "temp", RELATED ( 'table2'[temp] ) )

       

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi cjcj ,

        If you can't use the RELATED function, you can modify the formula like this:

        TEST =
VAR _T =
    ADDCOLUMNS (
        'table1',
        "temp",
            MAXX (
                FILTER ( 'table2', 'table2'[ID] = EARLIER ( table1[ID] ) ),
                'table2'[temp]
            )
    )
RETURN
    SUMX (
        _T,
        SWITCH (
            TRUE (),
            [color] = "RED"
                && [temp] = "WARM", 2345,
            [color] = "BLUE"
                && [temp] = "HOT", 1345
        )
    )

        It can also get the correct result.

        I attach my sample below for your reference.

         

        Best Regards,
        Community Support Team _ kalyj

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.