Merge tables and summarize with conditions

[MichaelHutchens]

Hi everyone, I'm hoping someone can help. I'm looking to merge two tables in DAX. The first is based on a dataflow I made, the second uses DirectQuery and I don't have access to Power Query or transformation functions for it.

The first table (called 'Content') shows a list of users, their email addresses, and URLs to content they own on the intranet. There may be multiple rows for each person.

The second table (called 'Employees') shows a list of employee records in Active Directory, including their name, and email address. There may be multiple rows for each person as they can leave and-rejoin the organization. Each time they leave their record is updated in the 'Employee Status' column to 'Withdrawn' from 'Active'. Each time they re-join the organization a new 'Active' record is created for them, and they are given a new Employee ID. The Employee IDs always increment, so the highest Employee ID number is always the latest one, regardless of whether the employee is 'Active' or 'Withdrawn'.

What I need is a table that shows:

'Content'[User Name]
'Content'[Email Address]
'Employees'[Employee Status]

The merged table should use a relationship between the 'Content'[Email] and 'Employees'[Email Address] columns that I've created, but because the Employees table may contain multiple different rows for each person (which may include multiple copies of their email address), the merged table should only show the row from the Employees table with the highest 'Employee ID' number.

Also, occasionally the record in AD will be missing an email address or have a placeholder. In this case the merged table should show the user name and email address from the 'Content' table, and 'Withdrawn' as the Employee Status.

Last requirement: The email addresses in the 'Content' come from a variety of domains. For the purposes of this demo data I need the merged table to only show records from @hotmail.

'Employees' table:

Email Address	User Name	Employee Name	Employee Status	Employee ID
		Jimmy Jimson	Active	20008240
jimmy.jimson@outlook.com		Jimmy Jimson	Withdrawn	10011640
jimmy.jimson@hotmail.com	jimsonj14	Jimmy Jimson	Active	10017439
jimmy.jimson@hotmail.com	jimsonj13	Jimmy Jimson	Withdrawn	10016801
jimmy.jimson@hotmail.com	jimsonj21	Jimmy Jimson	Active	20006079
not.available@hotmail.com	old.noons3	Sam Samson	Withdrawn	20007653
Sam.Samson@hotmail.com	samsons1	Sam Samson	Withdrawn	20009157
Sam.Samson@hotmail.com	samsons3	Sam Samson	Active	20009068
Sam.Samson@hotmail.com	samsons2	Sam Samson	Withdrawn	20006172

'Content' table

User Name	Email Address	Content URL
Jimmy Jimson	jimmy.jimson@hotmail.com	www.google.com
Jimmy Jimson	jimmy.jimson@hotmail.com	www.google.com
Sarah Sarahson	s.sarahson@hotmail.com	www.bing.com
Peter Peterson	ppeterson@outlook.com	www.askjeeves.com
Sam Samson	Sam.Samson@hotmail.com	www.maps.co.nz
Sam Samson	Sam.Samson@hotmail.com	www.maps.co.nz
Sam Samson	Sam.Samson@hotmail.com	www.amazon.com

Example of the result I need:

User Name	Email Address	Content URL	Employee Status
Jimmy Jimson	jimmy.jimson@hotmail.com	www.google.com	Active
Jimmy Jimson	jimmy.jimson@hotmail.com	www.google.com	Active
Sam Samson	Sam.Samson@hotmail.com	www.maps.co.nz	Withdrawn
Sam Samson	Sam.Samson@hotmail.com	www.maps.co.nz	Withdrawn
Sam Samson	Sam.Samson@hotmail.com	www.amazon.com	Withdrawn

Hope that all makes sense, thanks in advance for your time 🙂

[VijayP]

@MichaelHutchens 

To Create DAX we need to have strong Data Model. These two Tables cannot be related each other and some business logic is required. If you look at your Data  Jimmy you want only Active and Samson you want as Withdrawn and you donot want others. What is the business logic here so that i Can do some thing

But I wonder how we can achieve using DAX . Let me have some understanding based on that I can draw  a conclusion for outcome

[MichaelHutchens]

Thanks @VijayP . 

The business logic would be:

1. SHOW the User Name and Email Address from 'Content' and the 'Employee Status' from 'Employees' in the new merged table
2. WHERE the email address in 'Content' matches the 'Email Address' in 'Employees'
3. AND where the 'Employee ID' in 'Employees' is MAX
4. AND where the 'Email Address' in 'Employees' contains "@hotmail"
5. IF there is no match between 'Email Address' in 'Employess' and 'Email Address' in 'Content' after the rules above have been applied, set 'Employee Status' in the merged table to 'Withdrawn'.

(Also I apologise, I had accidentally swapped the names of the two tables. I've edited the post again and corrected this now).

[v-easonf-msft]

Hi, @MichaelHutchens 

I'm a little confused. According to the logic, the employee status of the username "Jimmy Jimson" should also be "withdrawn", right?

Best Regards,
Community Support Team _ Eason

[MichaelHutchens]

I'm so sorry @v-easonf-msft , I made a mistake with "Jimmy Jimson's" status. I've edited the original post so that the 'example results' table makes more sense.

[v-easonf-msft · Accepted solution]

Hi, @MichaelHutchens 

Try measure codes as below:

Max Employee ID for each email address = 
VAR tab =
    FILTER (
        All(Employees),
        Employees[Email Address] in VALUES('Content'[Email Address])
            && CONTAINSSTRING ( Employees[Email Address], "@hotmail" )
            && Employees[Email Address]=MAX(Employees[Email Address])
    )
RETURN
    MAXX ( tab, Employees[Employee ID] )

M_Employee Status = 
CALCULATE (
    MAX ( Employees[Employee Status] ),
    FILTER (
        All(Employees),
        Employees[Employee ID] = [Max Employee ID for each email address]
            && Employees[Email Address] = MAX ( 'Content'[Email Address] )
    )
)

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[VijayP]

@MichaelHutchens 

In the Employee Table Jimmy has got only google.com but in the result table are you expecting newspaper.com also for jimmy , and what is the basis or business logic for that !

Otherwise you can achieve this using Merge option in Power Query

[MichaelHutchens]

Apologies @VijayP , I made a mistake with my example 'expected results'. Thanks for picking that up, I've edited the post now.

I don't have access to Power Query for one of these tables so I can't use merge, so I'm looking for a DAX solutions here.