Course Enrolments - IF or Measure?

[rayinOz]

Hello,

I have a table that contains user records for courses they have enrolled in. I need to somehow flag a user that has completed all three courses. Maybe course name = x and status = completed AND course name = y and status = completed AND course name = z and status = completed THEN create a table with the Employee name and Username?

I don't know of the best way to do this.

Thanks,

rayinoz

[vanessafvg]

@rayinOz have you looked at the switch statement, more info here (although i am not sure if you can pass it that many arguments in one condition)

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

another way of doing it could be using this logic I think

http://sqlblog.com/blogs/marco_russo/archive/2014/08/18/possible-switch-optimization-in-dax-powerpiv...

[rayinOz]

Thanks for the reply!

How about if i simplify it another way... 

I first filter the table to show only completions. Then do a count on username.

If a username is listed 3 times (completed 3 courses), the username is added to a new table. If not 3 times (2 or 1 time) then it doesn't get added to the new table.

I'm thinking this will eliminate the course name variable and the course status variable.

How would i do this? 

Rayinoz

[Anonymous · Accepted solution]

Do you have a separate table for the users, where we can make them as "Wildly Complete" ?

In general, I want to think about this as "How many distinct courses are there?"  and "How many distinct courses did this student complete" -- a bit o' comparison between those to flag.

Maybe... given these measures:

Distinct Courses := DISTINCTCOUNT(MyTable[Course Name])

Completed Courses := CALCULATE([Distinct Courses], MyTable[Course Status] = "Completed")

Total Courses := CALCULATE([Distinct Courses], ALL(MyTable))

We can create a calc column, that is wildly untested but an interesting idea...

VAR TotalCourses = [Total Courses]
VAR MyCompletions = [Completed Courses]
RETURN
   IF (MyCompletions = TotalCourses, 1, 0)