Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Hi,
In the last Table, please show the the projects in each rows as well.
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 |
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.
Thanks.
Needed to do some tweaking to the code, but it appears to work like it should.
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
78 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
84 | |
70 |