Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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:
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
Hi,
is it the same sample? I cannot see the user name BOX JUMP.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Sorry, the measure is:
@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"
Thanks, both, I will give them both a try
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |