Forum Discussion

rsrajkumar's avatar
rsrajkumar
Icon for Helper I rankHelper I
1 year ago
Solved

DAX expression to look values between 2 tables

Hi,

I have 2 tables. If the Case Number from Table1 is found in Table 2, it should return a 1 in the Status Column, else it should return a 0 in the Status Column. Table2 can have duplicated rows for the same case number as they can have multiple Sale ID.

 

Table 1

Case Number
1234
1235
1236
1237
1238
1239
1240

 

Table2

Case Number  Sale ID
1234                A10
1235                A11
1235                A12
1236                A15
1237                A20
1240                A21

 

I need the output in Table1 as:

 

Case Number      Status
1234                      1
1235                      1
1236                      1
1237                      1
1238                      0
1239                      0
1240                      1

 

Request a DAX function for the Status column or any other alternate solution

 

Thanks

 

 

 

 

 

  • SamsonTruong's avatar
    SamsonTruong
    1 year ago

    Hi rsrajkumar ,

    This can be achieved through a calculated column in Table 1. Here's a DAX expression that will return 1 if the case number exists in Table2, and 0 otherwise:

    Status = 
IF(
    CONTAINS(Table2, Table2[Case Number], Table1[Case Number]),
    1,
    0
)

     

    If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

    Thanks,

    Samson





  • SamsonTruong's avatar
    SamsonTruong
    1 year ago

    Hi rsrajkumar , would you be able to send the DAX expression you are leveraging for the calculated column?

    Alternatively, you could use a lookup:

    Status = 
IF(
    NOT ISBLANK(
        LOOKUPVALUE(Table2[Case Number], Table2[Case Number], Table1[Case Number])
    ),
    1,
    0
)

     

    If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

    Thanks,

    Samson

4 Replies

  • SamsonTruong's avatar
    SamsonTruong
    Icon for Super User rankSuper User
    1 year ago

    Hi rsrajkumar ,

    This can be achieved through a calculated column in Table 1. Here's a DAX expression that will return 1 if the case number exists in Table2, and 0 otherwise:

    Status = 
IF(
    CONTAINS(Table2, Table2[Case Number], Table1[Case Number]),
    1,
    0
)

     

    If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

    Thanks,

    Samson





    • rsrajkumar's avatar
      rsrajkumar
      Icon for Helper I rankHelper I
      1 year ago

      Hi SamsonTruong ,

      Thank you for your response. When I tried your proposed solution, I get an error, "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value" . What could be the problem?

      • SamsonTruong's avatar
        SamsonTruong
        Icon for Super User rankSuper User
        1 year ago

        Hi rsrajkumar , would you be able to send the DAX expression you are leveraging for the calculated column?

        Alternatively, you could use a lookup:

        Status = 
IF(
    NOT ISBLANK(
        LOOKUPVALUE(Table2[Case Number], Table2[Case Number], Table1[Case Number])
    ),
    1,
    0
)

         

        If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

        Thanks,

        Samson