cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pschweiss-bhgh
Regular Visitor

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

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

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

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.

UserCounts.jpg

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)

usercountstable.jpg

Hope that helps.

 

 

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

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

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.

UserCounts.jpg

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)

usercountstable.jpg

Hope that helps.

 

 

View solution in original post

pschweiss-bhgh
Regular Visitor

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

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

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors