Lookup Multiple Rows Return Yes or No

[jvanmeter]

I have two tables (Users) and (Groups).

Users looks something like:

DistinguishedName     Type     Status        Etc.

Test1                            User     Enabled     Other Info

Test2                            User     Enabled     Information

Test3                            User     Enabled     More Info 

Groups looks something like:

DistinguishedName     Group    

Test1                            Group1

Test1                            Group2

Test1                            Group3

Test2                            Group2

Test2                            Group3

Test3                            Group3

I want to add a column on the Users Table that essentially is Titled "Group 1 is Member" and the row for that column will be yes or No.

The two tables have a relationship on DinstinguishedName

I want to lookup from Users to Groups on Dinstinguishedname and tell me if they are a member of the specific group. 

I was trying  Group1ISMember = LOOKUP('Groups'[Group],'Groups'[DistinguishedName],'Users'[DinstinguishedName])

But i get the error of multiple values returned. 

How do I do a lookup from Table Users to Table Groups and Tell me if they are a member of Each Specific Group and return a Yes or No? 

Thank you for your help. 

[Zubair_Muhammad · Accepted solution]

Hi @jvanmeter

Please try this Column in Users Table

Group 1 is Member =
IF (
    CALCULATE ( COUNT ( Groups[Group] ), Groups[Group] = "Group1" )
        >= 1,
    "Yes",
    "No"
)