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.
I have had a couple posts around this each of which has gotten me a step closer and helped me to better understand my data, however, I am still a bit stuck. There is a fair amount of data below to show you what I am trying to contend with. Everytime I pare the data down the solution doesn't scale so I am showing the information for 2 projects. The Teams.Value contains teams that are assigned to the project but the way the data shows in the table (because of assigned resources) it shows extra rows with teams listed multiple times. When a team completes work they show up in the TeamWorkCompleted.Value column, again this column has many duplicates because of assigned resources. What I am trying to get out of this is a list of the teams that still have work to do.
Example: Based on the information in the data set, these 2 teams each have 1 project left to do. If Windows still had work to do on both projects then they would show up in the chart below with count of 2.
Team | Count |
AppTech | 1 |
Telecom | 1 |
DATA SET:
Project_Name | Teams.Value | TeamsWorkCompleted.Value |
Project A | Infrastructure | DataCenter |
Project A | Infrastructure | Desktop Services |
Project A | Infrastructure | Network |
Project A | Infrastructure | PMO |
Project A | Infrastructure | Windows |
Project A | Infrastructure | DataCenter |
Project A | Infrastructure | Desktop Services |
Project A | Infrastructure | Network |
Project A | Infrastructure | PMO |
Project A | Infrastructure | Windows |
Project A | Infrastructure | DataCenter |
Project A | DataCenter | DataCenter |
Project A | DataCenter | Desktop Services |
Project A | DataCenter | Network |
Project A | DataCenter | PMO |
Project A | DataCenter | Windows |
Project A | DataCenter | DataCenter |
Project A | DataCenter | Desktop Services |
Project A | DataCenter | Network |
Project A | DataCenter | PMO |
Project A | DataCenter | Windows |
Project A | Desktop Services | DataCenter |
Project A | Desktop Services | Desktop Services |
Project A | Desktop Services | Network |
Project A | Desktop Services | PMO |
Project A | Desktop Services | Windows |
Project A | Desktop Services | DataCenter |
Project A | Desktop Services | Desktop Services |
Project A | Desktop Services | Network |
Project A | Desktop Services | PMO |
Project A | Desktop Services | Windows |
Project A | Desktop Services | DataCenter |
Project A | Network | DataCenter |
Project A | Network | Desktop Services |
Project A | Network | Network |
Project A | Network | PMO |
Project A | Network | Windows |
Project A | Network | DataCenter |
Project A | Network | Desktop Services |
Project A | Network | Network |
Project A | Network | PMO |
Project A | Network | Windows |
Project A | PMO | DataCenter |
Project A | PMO | Desktop Services |
Project A | PMO | Network |
Project A | PMO | PMO |
Project A | PMO | Windows |
Project A | PMO | DataCenter |
Project A | PMO | Desktop Services |
Project A | PMO | Network |
Project A | PMO | PMO |
Project A | PMO | Windows |
Project A | Telecom | DataCenter |
Project A | Telecom | Desktop Services |
Project A | Telecom | Network |
Project A | Telecom | PMO |
Project A | Telecom | Windows |
Project A | Telecom | DataCenter |
Project A | Telecom | Desktop Services |
Project A | Telecom | Network |
Project A | Telecom | PMO |
Project A | Telecom | Windows |
Project A | Telecom | Desktop Services |
Project A | Telecom | Network |
Project A | Telecom | PMO |
Project A | Telecom | Windows |
Project A | Telecom | DataCenter |
Project A | Telecom | Desktop Services |
Project A | Telecom | Network |
Project A | Telecom | PMO |
Project A | Telecom | Windows |
Project A | Windows | DataCenter |
Project A | Windows | Desktop Services |
Project A | Windows | Network |
Project A | Windows | PMO |
Project A | Windows | Windows |
Project A | Windows | DataCenter |
Project A | Windows | Desktop Services |
Project A | Windows | Network |
Project A | Windows | PMO |
Project A | Windows | Windows |
Project B | AppTech | DBA |
Project B | AppTech | Unix |
Project B | AppTech | Windows |
Project B | AppTech | DBA |
Project B | AppTech | Unix |
Project B | AppTech | Windows |
Project B | AppTech | DBA |
Project B | AppTech | Unix |
Project B | AppTech | Windows |
Project B | AppTech | DBA |
Project B | AppTech | Unix |
Project B | AppTech | Windows |
Project B | AppTech | DBA |
Project B | AppTech | Unix |
Project B | AppTech | Windows |
Project B | AppTech | DBA |
Project B | AppTech | Unix |
Project B | AppTech | Windows |
Project B | DBA | DBA |
Project B | DBA | Unix |
Project B | DBA | Windows |
Project B | DBA | DBA |
Project B | DBA | Unix |
Project B | DBA | Windows |
Project B | DBA | DBA |
Project B | DBA | Unix |
Project B | DBA | Windows |
Project B | DBA | DBA |
Project B | DBA | Unix |
Project B | DBA | Windows |
Project B | DBA | DBA |
Project B | DBA | Unix |
Project B | DBA | Windows |
Project B | DBA | DBA |
Project B | DBA | Unix |
Project B | DBA | Windows |
Project B | Unix | DBA |
Project B | Unix | Unix |
Project B | Unix | Windows |
Project B | Unix | DBA |
Project B | Unix | Unix |
Project B | Unix | Windows |
Project B | Unix | DBA |
Project B | Unix | Unix |
Project B | Unix | Windows |
Project B | Unix | DBA |
Project B | Unix | Unix |
Project B | Unix | Windows |
Project B | Unix | DBA |
Project B | Unix | Unix |
Project B | Unix | Windows |
Project B | Unix | DBA |
Project B | Unix | Unix |
Project B | Unix | Windows |
Project B | Windows | DBA |
Project B | Windows | Unix |
Project B | Windows | Windows |
Project B | Windows | DBA |
Project B | Windows | Unix |
Project B | Windows | Windows |
Project B | Windows | DBA |
Project B | Windows | Unix |
Project B | Windows | Windows |
Project B | Windows | DBA |
Project B | Windows | Unix |
Project B | Windows | Windows |
Project B | Windows | DBA |
Project B | Windows | Unix |
Project B | Windows | Windows |
Project B | Windows | DBA |
Project B | Windows | Unix |
Project B | Windows | Windows |
Solved! Go to Solution.
Hi shawn3474,
To achieve your requirement, you can create a measure and use DAX formula below:
Count of Projects left = CALCULATE(DISTINCTCOUNT(Table1[Project_Name]), ALLEXCEPT(Table1, Table1[Teams.Value]))
Regards,
Jimmy Tao
Hi shawn3474,
To achieve your requirement, you can create a measure and use DAX formula below:
Count of Projects left = CALCULATE(DISTINCTCOUNT(Table1[Project_Name]), ALLEXCEPT(Table1, Table1[Teams.Value]))
Regards,
Jimmy Tao
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |