Lookup Multiple Rows Return Yes or No

Question

I have two tables (Users) and (Groups).&nbsp;Users looks something like:&nbsp;DistinguishedName&nbsp; &nbsp; &nbsp;Type&nbsp; &nbsp; &nbsp;Status&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;Etc.Test1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; User&nbsp; &nbsp; &nbsp;Enabled&nbsp; &nbsp; &nbsp;Other InfoTest2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; User&nbsp; &nbsp; &nbsp;Enabled&nbsp; &nbsp; &nbsp;InformationTest3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; User&nbsp; &nbsp; &nbsp;Enabled&nbsp; &nbsp; &nbsp;More Info&nbsp;&nbsp;Groups looks something like:&nbsp;DistinguishedName&nbsp; &nbsp; &nbsp;Group&nbsp; &nbsp;&nbsp;Test1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Group1Test1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Group2Test1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Group3Test2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Group2Test2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Group3Test3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Group3&nbsp;&nbsp;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.&nbsp;The two tables have a relationship on DinstinguishedName&nbsp;I want to lookup from Users to Groups on Dinstinguishedname and tell me if they are a member of the specific group.&nbsp;&nbsp;I was trying&nbsp; Group1ISMember = LOOKUP('Groups'[Group],'Groups'[DistinguishedName],'Users'[DinstinguishedName])But i get the error of multiple values returned.&nbsp;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?&nbsp;&nbsp;Thank you for your help.&nbsp;

zubair_muhammad · Accepted Answer

Hi jvanmeter&nbsp;Please try this Column in Users Table&nbsp;Group 1 is Member =
IF (
    CALCULATE ( COUNT ( Groups[Group] ), Groups[Group] = "Group1" )
        &gt;= 1,
    "Yes",
    "No"
)

Forum Discussion

Lookup Multiple Rows Return Yes or No

1 Reply

Related content

Return multiple values from lookup

Return multiple values from lookup

Lookup help

Lookup for multiple columns

Multiplication with lookup()