Forum Discussion
Distinct Count of Projects Containing Specific Team Members
Hi All, I have Roster table that has a column of Projects (repeating values) and I need to count how many Projects have been assigned multiple Job Titles. For Instance: PROJECT JOB...
Hi,
the code:
Measure = var __Filter = CALCULATETABLE(FILTER('Table';[JOB TITLE] in {"Manager"; "Tech"});ALLEXCEPT('Table';'Table'[PROJECT])) return IF( HASONEVALUE('Table'[PROJECT]); if(COUNTROWS(__Filter)>=2;1;0); COUNTROWS( FILTER( CALCULATETABLE(SUMMARIZE(__Filter;'Table'[PROJECT];"c"; DISTINCTCOUNT('Table'[JOB TITLE])));[c]>=2 ) ) )As seen here:
File.
Pls marks as solution if this works for you. Thumbs up for the effort would be great.
Kind regards, Steve.
mahoneypat
Microsoft Employee
Microsoft EmployeePlease try this expression in your measure. It returns 2 for your example data. Replace ProjectRoles with the name of your actual table.
Projects with Manager and Tech =
COUNTROWS (
FILTER (
DISTINCT ( ProjectRoles[PROJECT] ),
CALCULATE (
DISTINCTCOUNT ( ProjectRoles[JOB TITLE] ),
ProjectRoles[JOB TITLE] IN { "Manager", "Tech" }
) = 2
)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
stevedep
Memorable Member
Memorable MemberHi,
the code:
Measure =
var __Filter = CALCULATETABLE(FILTER('Table';[JOB TITLE] in {"Manager"; "Tech"});ALLEXCEPT('Table';'Table'[PROJECT]))
return
IF(
HASONEVALUE('Table'[PROJECT]);
if(COUNTROWS(__Filter)>=2;1;0);
COUNTROWS(
FILTER(
CALCULATETABLE(SUMMARIZE(__Filter;'Table'[PROJECT];"c"; DISTINCTCOUNT('Table'[JOB TITLE])));[c]>=2
)
)
)As seen here:
File.
Pls marks as solution if this works for you. Thumbs up for the effort would be great.
Kind regards, Steve.
Other than a few syntax issues (maybe I have an older version or something) this worked perfectly, thank you!