Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
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
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
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?
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
Thanks a lot @SamsonTruong
Both the solutions worked. The first one did not work initially due to a typo.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |