Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to 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]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
98 | |
89 | |
73 | |
63 | |
61 |