Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
drwinny
Helper I
Helper I

Checking if multiple trainings are passed

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

 

Screenshot 2022-03-04 161248.png

 

 

I have added the files here: Example Files 

 

Any giudeance on how to acheive this would be great.

 

Thanks,

 

Sean.

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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:

Eyelyn9_0-1646724611554.png

 

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.

View solution in original post

8 REPLIES 8
v-eqin-msft
Community Support
Community Support

Hi @drwinny ,

 

Any updates?

 

Best Regards,
Eyelyn Qin

drwinny
Helper I
Helper I

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
Community Support
Community Support

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:

Eyelyn9_0-1646724611554.png

 

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.

@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
Super User
Super User

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
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak 

 

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

 

 

Screenshot 2022-03-04 172852.png

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.