Distinct Count against multiple criteria in the same column

[n3mo]

Hi all,

Been trying to work out a distinct count but can't seem to get it work with the results I've found.

I am trying to calculate the number of personnel within the organisation that have completed course A & B from data stored within a single list. If A&B both appear in the course column then the person is competent. Sample data is below. 

Hopefully this makes sense. 

EmployeeID	Course	Competent
1	A	Yes
1	B	Yes
2	A	No
3	B	No
4	A	Yes
4	B	Yes

[johnt75 · Accepted solution]

You could create a calculated column like

Competent =
VAR RequiredCourses = { "A", "B" }
VAR CompletedCourses =
    CALCULATETABLE (
        VALUES ( 'Table'[Course] ),
        ALLEXCEPT ( 'Table', 'Table'[Employee ID] )
    )
VAR Matches =
    INTERSECT ( CompletedCourses, RequiredCourses )
VAR Result =
    IF ( COUNTROWS ( Matches ) = COUNTROWS ( RequiredCourses ), "Yes", "No" )
RETURN
    Result

[n3mo]

Thanks, however it is the 'Competent' I am trying to calculate. Maybe I went wrong by showing this in a column. The output would effectively be Competent=2 (empID 1&4), NotCompetent=2 (empID 2&3)

[Uzi2019]

Hi @n3mo 

Try this measure.

Count =

CALCULATE(DISTINCTCOUNT(Course[EmployeeID]),FILTER(ALL(Course),Course[Course]="A" || Course[Course]="B"),Course[Competent]="Yes")

I hope I answered your question!

[bhanu_gautam]

@n3mo , Create a measure using

DAX
DistinctCountCompetentEmployees =
CALCULATE(
DISTINCTCOUNT('Table'[EmployeeID]),
FILTER(
'Table',
'Table'[Course] = "A" || 'Table'[Course] = "B"
&& 'Table'[Competent] = "Yes"
)
)