Compare Multiple User lists and Return YES or NO if the data exists elsewhere

[pschweiss-bhgh]

Hello-
Fair warning this a novice PowerBI Question!

I know I have done this before but can't wrap my head around it.

Scenario:

I have 3 datasets (CSV of Users from O365, CSV of Users from Payroll, Live data source from SalesForce showing Users)

The "primary key" between all 3 data sets is the email address.

Desired result:

I essentailly want to compare all 3 data sets and return a table that has the following...

Column 1: email 

Column 2: Exists in all 3 Data sets (if yes return TRUE if no return FALSE)

Column 3: Exists in O365 ONLY (if yes return TRUE if no return FALSE)

Column 4: Exists in SalesForce ONLY (if yes return TRUE if no return FALSE)

Column 5: Exists in Payrol ONLY (if yes return TRUE if no return FALSE)

Thoughts Here?

[jdbuchanan71 · Accepted solution]

Hello @pschweiss-bhgh 

I put together the attached .pbix with a solution that should work for you.

First we create a table with the unique emails from all lists.

CombineTable = 
DISTINCT(
    UNION(
        DISTINCT( O365[email] ),
        DISTINCT( Payroll[email] ),
        DISTINCT( SalesForce[email] )
    )
)

We join that back to all three lists so we can do the counts.

The some measures to count the rows in each table.

CountRowsO365 = COUNTROWS(O365)

Then start adding the columns based on those counts.

All 3 = 
VAR O365CT = [CountRowsO365]
VAR PayrollCT = [CountRowsPayroll]
VAR SalesForceCT = [CountRowsSalesForce]
RETURN
IF ( 
    SalesForceCT > 0 
    && O365CT > 0 
    && PayrollCT > 0
    , TRUE, FALSE)

Hope that helps.

[pschweiss-bhgh]

@jdbuchanan71 This was incredibly helpfull and worked right away for me.