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

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"

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

Thanks, both, I will give them both a try

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])RETURNIF (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.

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])RETURNIF (COUNTROWS ( alltraining ) <= COUNTROWS ( currentuserpasstraining ),"YES","NO")

checking if multiple training modules are complete and mark as completed

11 Replies

amitchandak
Super User
5 years ago
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
  Helper I
  5 years ago
  Thanks, both, I will give them both a try
Jihwan_Kim
Super User
5 years ago
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
  Helper I
  5 years ago
  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.
  - drwinny
    Helper I
    5 years ago
    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"
    )