topic Re: Checking if multiple trainings are passed in DAX Commands and Tips

Checking if multiple trainings are passed

drwinny — Fri, 04 Mar 2022 16:13:45 GMT

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.

Re: Checking if multiple trainings are passed

amitchandak — Fri, 04 Mar 2022 17:09:14 GMT

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]))

Re: Checking if multiple trainings are passed

drwinny — Fri, 04 Mar 2022 17:31:00 GMT

amitchandak

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

Re: Checking if multiple trainings are passed

amitchandak — Sat, 05 Mar 2022 03:58:48 GMT

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]))

Re: Checking if multiple trainings are passed

tamerj1 — Sat, 05 Mar 2022 08:39:02 GMT

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!

Re: Checking if multiple trainings are passed

Anonymous — Tue, 08 Mar 2022 07:31:08 GMT

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.

Re: Checking if multiple trainings are passed

drwinny — Thu, 10 Mar 2022 15:35:47 GMT

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.

Re: Checking if multiple trainings are passed

Anonymous — Tue, 15 Mar 2022 09:42:32 GMT

Hi drwinny ,

Any updates?

Best Regards,
Eyelyn Qin

Re: Checking if multiple trainings are passed

drwinny — Tue, 15 Mar 2022 13:50:19 GMT

Anonymous 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.