cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pschweiss-bhgh Frequent Visitor
Frequent 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
Super User
Super User

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
Super User
Super User

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 Frequent Visitor
Frequent 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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 309 members 2,941 guests
Please welcome our newest community members: