cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jvanmeter Frequent Visitor
Frequent Visitor

Lookup Multiple Rows Return Yes or No

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Lookup Multiple Rows Return Yes or No

Hi @jvanmeter

 

Please try this Column in Users Table

 

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

Re: Lookup Multiple Rows Return Yes or No

Hi @jvanmeter

 

Please try this Column in Users Table

 

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