Count qualified employees based on their distinct or common training

[Anonymous]

Hi everyone,

I have two tables as the screenshot below. I have created a measure that counts the qualified employees based on their training results.

The measure to count the qualified employees

Qulaified Employees = CALCULATE(DISTINCTCOUNT(Table1[Employee]),FILTER(Table1,[Pass or not]="Pass"))

My issue is whenever there is a common training between 2 different qualifications, the measure doubles the counts of the qualified employees.

As you can see in the screenshot, I have no employee who is qualified for ( qualification4 ) though it's counting as one due to the common training between qualification1 and qualification4, I'm trying to make a measure which does this dynamically and not hard coded.

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

What Greg suggested works,here is the related .pbix file you can refer to .

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

Perhaps something like this:

Qualified Employees = 
  VAR __Table = FILTER('Table1',[Pass or not]="Pass")
  VAR __Trainings = SELECTCOLUMNS(__Table,"__Training",[Training name])
  VAR __Employees = SELECTCOLUMNS(__Table,"__Employees",[Employee])
  VAR __Qualifications = 
    ADDCOLUMNS(
      'Qualifications'
      "__In",IF('Qualifications'[Training Name] IN __Trainings,"Yes","No")
    )
RETURN
  IF(
    COUNTROWS(FILTER(__Qualifications,[__In] = "No"))>0,
    BLANK(),
    COUNTROWS(DISTINCT(__Employees))

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490