cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Course Enrolments - IF or Measure?

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.

2017-06-01_16-25-03.png

 

Thanks,

 

rayinoz

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: Course Enrolments - IF or Measure?

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)

View solution in original post

3 REPLIES 3
Highlighted
Super User I
Super User I

Re: Course Enrolments - IF or Measure?

 

@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...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper III
Helper III

Re: Course Enrolments - IF or Measure?

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

Highlighted
Memorable Member
Memorable Member

Re: Course Enrolments - IF or Measure?

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)

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors