checking if multiple training modules are complete and mark as completed

[drwinny]

Hi All,

I need to check if a user has passed multiple training courses or not and then return yes/no, true/false answer 

In the example joe bloggs1 did not complete all the training so that would be false, joe bloggs2 did pass all the training so I need to return true.

Not really sure of the best way to do this, I have been looking for the answer but I am just confusing myself.

Any guidance on how to achieve this would really help. (sample file below)

Many thanks,

Sean.

Sample Files 

[Jihwan_Kim]

Hi, @drwinny 

Please check the link down below.

Pass All Training Measure =
VAR alltraining =
CALCULATETABLE ( VALUES ( Sheet1[Training Code] ), ALL ( Sheet1 ) )
VAR currentuserpasstraining =
SUMMARIZE (
FILTER (
ALL ( Sheet1 ),
Sheet1[User Name] = MAX ( Sheet1[User Name] )
&& Sheet1[Passed] = TRUE ()
),
Sheet1[Training Code]
)
RETURN
IF (
COUNTROWS ( alltraining ) <= COUNTROWS ( currentuserpasstraining ),
"YES",
"NO"
)

https://www.dropbox.com/s/sqipj7ljvhof7ja/Training%20Example.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[drwinny]

@Jihwan_Kim thank you very much the suggested code does work very well with my test data but it seems to have trouble with my actual data. I have updated my sample data with a few false and not started rows to try and show what's happening.

This is the measure:

Pass All Training Measure =

VAR alltraining =

CALCULATETABLE ( VALUES ( Sheet1[Training Code] ), ALL (Sheet1[Training Name]) )

VAR currentuserpasstraining =

SUMMARIZE (

FILTER (

ALL ( Sheet1 ),

Sheet1[User Name] = MAX ( Sheet1[User Name] )

&& Sheet1[Passed] = "True"

),

Sheet1[Training Code]

)

RETURN

IF (

COUNTROWS ( alltraining ) <= COUNTROWS ( currentuserpasstraining ),

"YES",

"NO"

)

For some reason, people have completed the training twice for different companies and can have different values in the passed field (not assigned, false, true.

Any ideas to solve this extra problem?

Many thanks for taking the time to help me, it's really appreciated

Sean.

[Jihwan_Kim]

Hi, @drwinny 

Thank you for your feedback.

Please share your sample pbix file's link here, then I can try to look into it to find out what is the difference between the previous sample and the current sample and to find out which part to rewrite in the measure.

[Jihwan_Kim]

Hi, @drwinny 

Thank you for sharing.

I am not sure how your desired outcome looks like, but please check the below picture and the link down below.

https://www.dropbox.com/s/ubxkzfnr67rioom/Training%20Example%20%282%29.pbix?dl=0 

[drwinny]

Hi @Jihwan_Kim 

In my real data, I need to know if each user has passed 10 specific training courses so they are can move forward to additional training. But the data from the training platform is messy sometimes the same training has multiple values in the passed column it can be not assigned, passed, or failed.

In my sample data:

In this case, everything is OK: the user has passed all training courses:

In this case: the user has worked for two companies, for company C everything is ok.

Company B the user has passed all 7 training courses but "training A" has a "Not Assigned" and "passed" condition so this should be marked as yes or passed in the measure but it is shown as no or not passed

Does this make sense?

Thanks

Sean.

[Jihwan_Kim]

Hi, 

is it the same sample?  I cannot see the user name BOX JUMP.

[drwinny]

Hi @Jihwan_Kim it should be but I have uploaded it again just to make sure

[drwinny]

Hi @Jihwan_Kim thank you the sample files are below:

Sample Files 

[drwinny]

Sorry, the measure is: 

Pass All Training Measure =

VAR alltraining =

CALCULATETABLE ( VALUES ( Sheet1[Training Code] ), ALL (Sheet1) )

VAR currentuserpasstraining =

SUMMARIZE (

FILTER (

ALL ( Sheet1 ),

Sheet1[User Name] = MAX ( Sheet1[User Name] )

&& Sheet1[Passed] = "True"

),

Sheet1[Training Code]

)

RETURN

IF (

COUNTROWS ( alltraining ) <= COUNTROWS ( currentuserpasstraining ),

"YES",

"NO"

)

[amitchandak]

@drwinny , Try a new column like

New column =
if( countx(filter(Table, [UserName] =earlier([UserName])), [Training Code]) = countx(filter(Table, [UserName] =earlier([UserName]) && [passed] =True()), [Training Code]) , true(), false())

true() is for boolean true. else you can use = "True"

[drwinny]

Thanks, both, I will give them both a try