Forum Discussion

byr10112's avatar
byr10112
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

Calculated column based on another table with conditions

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 IDDeal NameCompleted
1001xxx1
1001xyz1
1001xyy0
1002yyz0
1002aad1

 

Accounts

Account ID
1001
1002
1003

 

Below is what I need the Accounts table to look like

 

Accounts

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

 

How can I achieve this? 

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    3 years ago

    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.

1 Reply

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    3 years ago

    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.