DAX calculated column to find if record exist in another table with 1:many relationship

[YingKai]

Hi, need some help here.

I have two tables, table A and B, relationship is 1:many, both and they are linked by key A[SO Doc] and B[SO Number].  Would like to created a calculated column called "Found in Table B" in Table A to show if A[SO Doc] is found in Table B.  Table B must first be filtered to remove Ticket type <> F&B or Resubmit = Yes before comparing the two tables.

Table A

SO Doc	Block Type	Price
1234	Food	10
1235	Non Food	30
1236	No block	20
1237	Food	40
1238	Food	10

Table B

Ticket	SO Number	Ticket type	Resubmit
1	1234	F&B	Yes
2	1236	Logistic	No
3	1237	F&B	No
4	1237	F&B	No
5	1234	F&B	No
6	1238	F&B	No
7	1239	F&B	No

Complimentary information:  After filtering table B, it should look like the following:

Ticket	SO Number	Ticket type	Resubmit
3	1237	F&B	No
4	1237	F&B	No
5	1234	F&B	No
6	1238	F&B	No
7	1239	F&B	No

Finally, result in Table A should look like the below:

SO Doc	Block Type	Price	Found in Table B
1234	Food	10	Found
1235	Non Food	30	Not found
1236	No block	20	Not found
1237	Food	40	Found
1238	Food	10	Found

Hope someone can help me on this.

Thank you.

[parry2k]

@YingKai can you share the expression you used? I don't get this error.

[YingKai]

@parry2k , I relooked and this time the error is gone.  Sorry about this.  With your help, I managed to edit the code to get to the result.  Thanks again.

Column = VAR __table = CALCULATETABLE ( RELATEDTABLE ( TableB ), Not(TableB[Ticket type] <> "F&B" || TableB[Resubmit] = "Yes" ))
RETURN IF ( NOT ISBLANK ( COUNTROWS ( __table ) ), "Found", "Not Found" ) 

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a calculated column.

Found in Table B CC =
IF (
'Table A'[SO Doc]
IN SUMMARIZE (
FILTER (
'Table B',
NOT ( 'Table B'[Ticket type] <> "F&B"
|| 'Table B'[Resubmit] = "Yes" )
),
'Table B'[SO Number]
),
"Found",
"Not found"
)

[YingKai]

@Jihwan_Kim , thank you very much for the clear precise presentation of solution.  As a young learner, I can understand the solution easily.

[parry2k]

@YingKai add a new column in TableA using the following DAX expression, you can change the logic as you see fit.

Column = 
VAR __table = CALCULATETABLE ( RELATEDTABLE ( TableB ), TableB[Ticket type] <> "F&B" || TableB[Resubmit] = "No" )
RETURN IF ( NOT ISBLANK ( COUNTROWS ( __table ) ), "Found", "Not Found" ) 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[YingKai]

Hi Parry2k, thank you first of all.  I got error "A table of multiple values was supplied where a single value was expected."  To bring back, my table A to B is 1:many and both direction.  Can you please relook?  Thank you.