Calculated column based on another table with conditions

[byr10112]

I have two tables. One table has the list of completed deals and the other table has the list of accounts. I'm trying to add a column to the accounts table that shows whether an account has a completed deal. The completed deals table has multiple occurences for each account ID. Below is a sample of tables:

Completed Deals

Account ID	Deal Name	Completed
1001	xxx	1
1001	xyz	1
1001	xyy	0
1002	yyz	0
1002	aad	1

Accounts

Account ID

1001

1002

1003

Below is what I need the Accounts table to look like

Accounts

Account ID	Completed Deal
1001	Completed Deal
1002	Completed Deal
1003	No Completed Deals
1004	No Completed Deals

How can I achieve this? 

[v-henryk-mstf · Accepted solution]

Hi @byr10112 ,

Try formula like below:

Result =
VAR row_ =
    CALCULATE (
        COUNTROWS ( 'Completed Deals' ),
        ALL ( 'Completed Deals'[Account ID] )
    )
RETURN
    IF ( row_ > 0, "Completed Deal", "No Completed Deals" )

If the problem is still not resolved, please point out the errors and give further examples to describe your needs so that I can answer them for you as soon as possible.

Best Regards,
Henry

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