Summarizecolumns with a filter that returns max of grouping by email address

[MichaelHutchens]

Hi folks,

I'm hoping someone can help. I have a table of user records and I need to create a SUMMARIZECOLUMNS-based table that FILTERS records where the Employee ID is the highest, grouped by each Email Address. Here's a sample of user records:

Email Address	Email Status	Employee Name	Org Unit	Employee ID
Jimmy.Jimson@emailaddress.co.nz	Active	Jimmy Jimson	Name 1	20007406
Jimmy.Jimson@emailaddress.co.nz	Active	Jimmy Jimson	Name 1	20011554
danny.danson@emailaddress.co.nz	Active	Danny Danson	Name 2	80000079
danny.danson@emailaddress.co.nz	Active	Danny Danson	Name 2	80000375
danny.danson@emailaddress.co.nz	Active	Danny Danson	Name 2	80000662
Sally.Salson@emailaddress.co.nz	Active	Sally Salson		10005247
Sally.Salson@emailaddress.co.nz	Active	Sally Salson		20000738
Sally.Salson@emailaddress.co.nz	Active	Sally Salson		20010319
Sally.Salson@emailaddress.co.nz	Active	Sally Salson	Name 4	91004059

And here are my expected results:

Email Address	Email Status	Employee Name	Org Unit	Employee ID
Jimmy.Jimson@emailaddress.co.nz	Active	Jimmy Jimson	Name 1	20011554
danny.danson@emailaddress.co.nz	Active	Danny Danson	Name 2	80000662
Sally.Salson@emailaddress.co.nz	Active	Sally Salson	Name 4	91004059

Any help would be greatly appreciated 🙂

[Jihwan_Kim · Accepted solution]

Hi, 

Please check the below picture and the attached pbix file.

It is for creating a new table.

New table =
CALCULATETABLE (
    Data,
    TREATAS (
        SUMMARIZECOLUMNS (
            Data[Email Address],
            "@maxemployeeID", MAX ( Data[Employee ID] )
        ),
        Data[Email Address],
        Data[Employee ID]
    )
)

[MichaelHutchens]

Thanks so much @Jihwan_Kim , that's perfect 🙂