DAX expression to look values between 2 tables

[rsrajkumar]

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 · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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

[rsrajkumar]

Thanks a lot @SamsonTruong 

Both the solutions worked. The first one did not work initially due to a typo.