Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Distinct Count against multiple criteria in the same column

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 com...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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
Uzi2019's avatar
Uzi2019
Icon for Community Champion rankCommunity Champion
1 year ago

Hi Anonymous 

 

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!