Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Calculated Measure Multiple Criteria?

Hello Community,

 

In the table below, I'd like to count the user only if all of their required courses have been completed. The user is assigned to an allotment of courses, but I only want to count the user as "completed" if they have completed all of their required courses. This is just a small cut of the data, so when you consider all of the data, each user is responsible for completing all of their required courses. Upon completion of those required courses, the planned user completion should show 1 for the individual user and the actual user completion should also show 1. However, if the user shows "in progress" or "not attempted" for any of the required courses, the actual user completion card should show 0. 

 

I am using a calculated measure (as shown below), to display the actual count in a card, but I can't figure out the logic to not count the user as completed if they have a status of "in progress" or "not attempted" for a required course. Can you all help? 

Need Help

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    1. The formula evaluates completions while the [Course Completion Status] is "In Progress". It should be "Completed".

    Actual User Completions =
CALCULATE (
    DISTINCTCOUNT ( LearningPlanDetails[Username] ),
    LearningPlanDetails[Course Type] = "Online Course",
    LearningPlanDetails[Optional/Required] = "Required",
    LearningPlanDetails[Course Completion Status] = "In Progress"
)

    2. I guess we only can say one course of one user is completed. So we need to specify the course name in the formula or in a slicer. 

     

    Best Regards!

    Dale

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    Try this

     

    =COUNTROWS(FILTER(SUMMARISE(VALUES(LearningPlanDetails{UserName]),[UserName],"ABCD",CALCULATE(COUNTROWS(LearningPlanDetails),LearningPlanDetails[Course Completino Status]="Completed")),[ABCD]=COUNTROWS(LearningPlanDetails)))

     

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Ashish_Mathur & v-jiascu-msft - Thank you for your assistance!

       

      Ashish, I tried your solution but did not get it to work.

       

      Dale, I appreciate your reply but it came off a bit too cryptic for me so I didn't understand it.

       

      That said, I was able to figure out how to get my desired result and it works too good lol. I adjusted the calculated measure the now subtract the incomplete and in progress statuses from the completed statuses in order to get a count of the usernames who only have completed all of their required courses. My issue now is that if there is more than one class that is incomplete or in progress, the count goes below 0 to either -1 or -2. My fear is that these negative numbers are affecting the overall sum of users who have actually completed their training. Is it possible to have the number display as 0 instead of a negative number?

      Updated Calculated Measure Logic

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi Anonymous,

         

        Share the link from where i can download your file.