DAX formula in a table to return nth value based on multiple criteria

[Anonymous]

Hey folks,

Revising this topic to be less confusing in the hopes someone may have a solution.

I have a data set with  a huge list of users and their completion status for a wide variety of training courses. Training courses are grouped by Program (so for example, Program 1 can have 7 courses in it, Course 1, Course 2, etc.).

What I'm trying to do is display 2 things:

1.) User completion % by Program (e.g. Program 1 has 7 courses, if User A completed all 7 they're 100% complete for the program, if User B completed 6 of 7 courses they're 86% complete for the program, etc.)

2.) Incomplete course list by user (User B has completed 6 of 7 courses in Program 1, so I want to display the 1 course they haven't completed so their manager knows what to have them finish)

I actually got this to work great in Excel, just not sure what the DAX or Power BI equivalent would be. Here's a screenshot of my Excel file with the code that works below that in case it helps:

=IFERROR(INDEX(data!B:B,AGGREGATE(15,6,ROW(data!$B$2:$B$100)/((data!$E$2:$E$100=$B$2)*(data!$G$2:$G$100<>"Complete")),ROWS($1:1))),"")

Hopefully that's a bit clearer and isn't as heavy handed. If any Excel gurus who are familiar with Power BI know how to help me take what I've made work in Excel and migrate it to PBI, I'd be eternally grateful! 

[Anonymous]

Bumping this one time only with heavily revised main post in the hopes clarity helps produce an answer.

[parry2k]

@Anonymous does something like this will work

[Anonymous]

Yes! This looks nearly perfect!

Correct me if I'm wrong, but the top table you have here would show the % completion, so User A has completed all courses in Program 1 (so let's say 10/10) but only 71% of the courses in Program 2 (so let's say 7/10). If that's what it's showing, then yes sir, that's perfect!

For the 2nd table, it looks like it would show that User A has 2 incomplete courses in Program 2, and those courses are Course 1 and Course 2. It shows that for all users. If that's what the 2nd table is doing, that's also perfect 🙂

How would I go about doing what you've put together here? 

This is exciting, I didn't know if I was explaining correctly but your solutions look like they're spot on.

[parry2k]

@Anonymous I don't know how your data model is but this is what I did but you can always tweak as per your model.

Add following measures and it will do it.

Count Course = COUNTROWS ( Course ) //base measure

Count Course Completed = CALCULATE ( [Count Course], Course[Status] = "Completed" )

Count Course Outstanding = CALCULATE ( [Count Course], Course[Status] <> "Completed" ) 

% Completed = DIVIDE ( [Count Course Completed], [Count Course Completed] + [Count Course Outstanding] ) 

[Anonymous]

Thanks for your help on this, I really REALLY appreciate it.

I'm having some trouble with the data not showing everyone. Here's a screenshot:

I added the measures you provided and updated the range references for my data set. When I filter by Program (at the top via slicer), the bottom table shows a list of all the users in that Program who Not yet started. As you can see in the Employee Name column, it's a wide variety of users (all named Aaron since it's filtered alphabetically, but they're all different Aarons). 

But in the table I put together with these new DAX measures, it only shows two people at less than 100% complete, and none of them are any of the Aarons.

I'm assuming I'm doing something wrong here, so here are the DAX measures I modified:

% Completed = DIVIDE ( [Count Course Completed], [Count Course Completed] + [Count Course Outstanding] ) 

Count Course Completed = CALCULATE ( [Count Course], course_completions_report[Completion Status] = "Complete" )

Count Course Outstanding = CALCULATE ( [Count Course], course_completions_report[Completion Status] <> "Complete" ) 

Count Course = COUNTROWS ( course_completions_report ) //base measure

The name of my data set (imported via Excel) is course_completions_report.xlsm. I'm not sure if that last measure is wrong or not.

Also, in case it matters, my Excel file has 2 tabs, course_completions_report and CP. CP has all of the Programs and associated courses, and I made the connection in the Model tab of Power BI.

Thanks so much for your help, hopefully you or someone else can help me figure out why this is being weird, but if not I'll continue troubleshooting!

[v-chuncz-msft]

@Anonymous 

Learn more about CALCULATE and try to check the formula with fewer data by using slicers and filters.