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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

I have added the files here: Example Files

Any giudeance on how to acheive this would be great.

Thanks,

Sean.

1 ACCEPTED SOLUTION
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:

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.

8 REPLIES 8
Community Support

Hi @drwinny ,

Any updates?

Best Regards,
Eyelyn Qin

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.

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:

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.

Helper I

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

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!

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

Helper I

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

Super User

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors