Distinct Count of Projects Containing Specific Team Members

[Anonymous]

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 TITLE
Alpha	Manager
Alpha	Member
Alpha	Member
Alpha	Tech
Beta	Manager
Beta	Tech
Delta	Manager
Delta	Member
Omega	Tech

I have been able to count how many PROJECTS contain one specific JOB TITLE using

Contains Manager = CALCULATE(DISTINCTCOUNT('Roster'[PROJECT]), FILTER('Roster', [JOB TITLE] = "Manager"))

However, I have not been able to count how many PROJECTS contain, say, "Manager" AND "Tech".

So in this instance I would need "2" to be returned, because only two projects, Alpha and Beta, have both a Manager and Tech assigned.

Thanks!

[mahoneypat]

Please 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 · Accepted solution]

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. 

[Anonymous]

Other than a few syntax issues (maybe I have an older version or something) this worked perfectly, thank you!

[AntrikshSharma]

Something like this?

Manger & Tech =
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Michael[Job Title] ),
        FILTER (
            ALL ( Michael[Job Title] ),
            Michael[Job Title] IN { "Manager", "Tech" }
        )
    )
RETURN
    IF ( HASONEVALUE ( Michael[Project] ), Result, BLANK () )

You can also use less verbose version.

Manger & Tech =
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Michael[Job Title] ),
        Michael[Job Title] IN { "Manager", "Tech" }
    )
RETURN
    IF ( HASONEVALUE ( Michael[Project] ), Result, BLANK () )

[Anonymous]

Not quite. It looks like your code returns how many Managers and/or Techs are assigned to each project.

I need my code to return how many projects have a Manager and Tech assigned to them, so I can put it on a card.

So in this instance I would need "2" to be returned, because only two projects, Alpha and Beta, have both a Manager and Tech assigned.

[AntrikshSharma]

is this how you want it?