Forum Discussion

drwinny's avatar
drwinny
Icon for Helper I rankHelper I
5 years ago

checking if multiple training modules are complete and mark as completed

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 woul...
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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's avatar
    drwinny
    Icon for Helper I rankHelper 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's avatar
      drwinny
      Icon for Helper I rankHelper 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"
      )
    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      5 years ago

      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.