topic Re: checking if multiple training modules are complete and mark as completed in DAX Commands and Tips

checking if multiple training modules are complete and mark as completed

drwinny — Mon, 24 May 2021 12:49:20 GMT

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

Re: checking if multiple training modules are complete and mark as completed

amitchandak — Mon, 24 May 2021 13:11:33 GMT

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"

Re: checking if multiple training modules are complete and mark as completed

Jihwan_Kim — Mon, 24 May 2021 13:18:15 GMT

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

Re: checking if multiple training modules are complete and mark as completed

drwinny — Mon, 24 May 2021 13:23:57 GMT

Thanks, both, I will give them both a try

Re: checking if multiple training modules are complete and mark as completed

drwinny — Fri, 28 May 2021 12:54:00 GMT

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.

Re: checking if multiple training modules are complete and mark as completed

drwinny — Fri, 28 May 2021 12:56:36 GMT

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"

)

Re: checking if multiple training modules are complete and mark as completed

Jihwan_Kim — Fri, 28 May 2021 13:11:06 GMT

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.

Re: checking if multiple training modules are complete and mark as completed

drwinny — Fri, 28 May 2021 13:14:15 GMT

Hi Jihwan_Kim thank you the sample files are below:

Sample Files

Re: checking if multiple training modules are complete and mark as completed

Jihwan_Kim — Fri, 28 May 2021 13:34:03 GMT

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

Re: checking if multiple training modules are complete and mark as completed

drwinny — Fri, 28 May 2021 13:54:32 GMT

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.

Re: checking if multiple training modules are complete and mark as completed

Jihwan_Kim — Fri, 28 May 2021 14:41:10 GMT

Hi,

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

Re: checking if multiple training modules are complete and mark as completed

drwinny — Tue, 01 Jun 2021 14:49:20 GMT

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