cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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 ,

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

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors