Checking if multiple trainings are passed

[drwinny]

Hi All,

I have asked this question before, but I have not been able to solve the problem I have.

I need to calculate if each person is certified for Product A, Product B, Product C, Etc

To be certified you need to have passed all the trainings for that product, so for Product A the user would need to pass 3 training courses (this varies depending on the product)

Some people have worked for multiple companies and completed the same training, which causes me problems when using my real data.

When I have calculated who is certified and for what products I will then need to show:

Total people Product A certified = 1

Total people Product B certified = 2

Total people Product C certified = 1

I have added the files here: Example Files 

Any giudeance on how to acheive this would be great.

Thanks,

Sean.

[v-eqin-msft]

Hi @drwinny ,

Any updates?

Best Regards,
Eyelyn Qin

[drwinny]

Thank you to everyone that has taken the time to try and help me solve this problem, I will try the suggestions and reply, thanks again.

Sean.

[v-eqin-msft · Accepted solution]

Hi @drwinny ,

1. Extract Training type from Training ID column:

Training Type = LEFT([Training ID], FIND("Training",[Training ID])-1)

2.Create a flag measure—— if passed then 1 else 0：

Flag = 
var _score=SUMX(FILTER('Data',[User Name]=MAX('Data'[User Name]) && [Training Type]=MAX('Data'[Training Type])),[Training Passed])
var _count=CALCULATE(DISTINCTCOUNT('Data'[Training ID]),ALLEXCEPT(Data,Data[User Name],Data[Training Type]))
RETURN IF(_count=_score,1,0)

3. Sum the Flag measure of each Training Type:

Passed Person Count = var _t=SUMMARIZE(ALL('Data'),[Training Type],Data[User Name],"Flag",[Flag])
return SUMX(FILTER(_t,[Training Type]=MAX('Data'[Training Type])),[Flag])

Final output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[drwinny]

@v-eqin-msft and @amitchandak Both of your suggestions helped but Eyelyn9's solution gave me the "certified count" that I was looking for.

I just need to breakdown each step of this process so I can understand how to solve these types of questions for myself.

Thanks again for taking time to help me with this request.

[tamerj1]

Hi @drwinny 
You should have a separate table that contains all possible Traning ID's. The a calculated column or measure that satisfies you requirement can be generated. I have two questions:
1. Do yo want a calculated column or a measure? 
2. Please elaborate on the company's issue. If the person has results in two different companies how shall we proceed? 
Please copy and paste the same sample data so we can use it for the analysis. Thank you!

[amitchandak]

@drwinny , Try like

countrows(filter(summarize(Table, Table[user Name], Table[Training ID],"_1" ,Countrows(Table), "_2",Countrows(filter(Table, Table[Training Passed])=1)), [_1] =[_2]))

[drwinny]

@amitchandak 

Thanks for the suggestion, I get the following error, not sure if I have done something wrong or not?

[amitchandak]

@drwinny , Sorry, My Mistake. try that

countrows(filter(summarize(Table, Table[user Name], Table[Training ID],"_1" ,Countrows(Table), "_2",Countrows(filter(Table, Table[Training Passed]=1))), [_1] =[_2]))