Column value bases on match in another table

[davidtoyne]

Table 1- Name = ‘AADUsers’

UserPrincipalName	DisplayName
User1@domain.com	User One
User2@domain.com	User Two
User3@domain.com	User Three
User5@domain.com	User Five

Table 2 – Name = ‘InScope’

UserPrincipalName	DisplayName
User1@domain.com	User One
User5@domain.com	User Five
User10@domain.com	User Ten

I’d like to make a column on Table 1 (AADUsers) that is boulean Yes/No field if the ‘UserPrincipalName’ exists in table 2 (InScope)

Like this: -

Table 1 – ‘AADUsers’

UserPrincipalName	DisplayName	IsInScope
User1@domain.com	User One	Yes
User2@domain.com	User Two	No
User3@domain.com	User Three	No
User5@domain.com	User Five	Yes

Can anyone assist with the DAX expression for this?

[Dangar332 · Accepted solution]

HI, @davidtoyne 

just adjust tablename and column name

try below for column code

Column = 
var a = LOOKUPVALUE(U2[user name],U2[user name],U1[username])
RETURN
IF(a<>BLANK(),"yes","no")

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

[v-yaningy-msft · Accepted solution]

Hi, @davidtoyne 

@Dangar332 gives a solution that you can try to use, and below there is a different solution you can refer to it as well

New Column:

IsInScope =

IF (

    COUNTROWS (

        FILTER ( InScope, InScope[UserPrincipal] = AADUsers[UserPrincipal] )

    ) > 0,

    "Yes",

    "No"

)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Dangar332 · Accepted solution]

HI, @davidtoyne 

just adjust tablename and column name

try below for column code

Column = 
var a = LOOKUPVALUE(U2[user name],U2[user name],U1[username])
RETURN
IF(a<>BLANK(),"yes","no")

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