Count of latest entry

[Anonymous]

Having some problems getting my visualisation to show what I want it to show... Hopefully someone out there can help me out.

The story is:   A team with 5 account managers taking care of a number of projects for the company.

The teamleader wants a realtime updated report showing exact number of projects currently owned by each account manager... Rather simple... Just do a COUNT of projects for each account manager.

But sometimes more than one account managerhas been looking at a project, and the "ownership" of each project should only be counted towards the account manager that has last updated the project.

Might be easier if I show you how it looks....

The datatable looks more or less like this:

Account Manager	Project	Last Updated
AM 01	ABC123	4-12 2019
AM 02	DEF456	5-12 2019
AM 03	GHI789	6-12 2019
AM 02	ABC123	7-12 2019
AM 05	JKL012	8-12 2019
AM 01	OPQ345	10-12 2019
AM 04	RST678	11-12 2019
AM 02	OPQ345	13-12 2019
AM 05	UVW901	14-12 2019
AM 01	XYZ234	15-12 2019
AM 05	GHI789	20-12 2019
AM 03	OPQ345	23-12 2019
AM 04	JKL012	27-12 2019
AM 02	DEF456	28-12 2019

What the visualisation is showing me right now is:

Account Manager	Total projects	Distinct projects
AM 01	3	3
AM 02	4	3
AM 03	2	2
AM 04	2	2
AM 05	3	3
TOTAL	14	13

But what I want it to show me would be something like this:

Account Manager	Current projects
AM 01	1
AM 02	2
AM 03	1
AM 04	2
AM 05	2
TOTAL	8

I have searched for several days and the only possible sollutions to this would be some sort of variable DAX code to ensure that only the most recent procejt is counted... But when I try all the different codes I have found in the forum I get the same error message saying that the particular DAX code can't be used in Direct Query mode.

[Ashish_Mathur]

Hi,

In the last Table, please show the the projects in each rows as well.

[Anonymous]

Hi.

Not really sure what you mean, but the account manager that has ownership of each of the projects is the account manager that last updated the project.

And what i'm aiming to do is simply to make a table that looks like the last table I showed above...

A line for each of the account managers with the exact number of owned projects right now.

Current projects	Owner	Last updated
ABC123	AM 02	7-12 2019
DEF456	AM 02	28-12 2019
GHI789	AM 05	20-12 2019
JKL012	AM 04	27-12 2019
OPQ345	AM 03	23-12 2019
RST678	AM 04	11-12 2019
UVW901	AM 05	14-12 2019
XYZ234	AM 01	15-12 2019

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Current projects = COUNTROWS(FILTER(SUMMARIZE(VALUES(Data[Project]),Data[Project],"Last update by the manager",MAX(Data[Last Updated]),"Last update by any manager",CALCULATE(MAX(Data[Last Updated]),ALL(Data[Account Manager]))),[Last update by the manager]=[Last update by any manager]))

Hope this helps.

[Anonymous]

Thanks.

Needed to do some tweaking to the code, but it appears to work like it should.

[Ashish_Mathur]

You are welcome.