Comparing Required Courses vs. List of Completed Courses

[CBloome77]

Greetings!

I am trying to put together a report that will help managers review specific courses to see who has not completed them yet.

I am able to get data from an excel file (which I have set up to be refreshed every day) that shows all courses completed, by course title, course code, user id, name, etc..  I also have a list of all courses we'd like to track.  

While I'm able to use a slicer to pull up which students completed, and on what date, but I'm trying to see the opposite.  I'd like to show a roster, and get a blank return if a completion date isn't present on the other list.

I've tried putting together a list of all required courses by name, and a field "Required? Y/N"  but keep getting "many many" warnings, and end up getting the completion dates for the whole team for all people on the roster.

Really feeling dumb here, and I'm sure it's a simple sollution.

Would really like to have a permanent list of team members, and then be able to use a slicer to select a specific course, and have complete Y/N or a listing of completion date or Null.

Thanks in advance, all.

Christopher

[CBloome77]

Good afternoon, Paul.  Sorry about the delay.  In between other items today, I realize that there is probably a way to use the Lookupvalue function in the data tab, but I'm having a problem searching by two criteria.  I have the two tables.  One is a list of each employee and what courses are assigned to them, and the other table, is a list of all coursework completed in the past year.

This is the Table with the completion data:

And here is the Table with the required coursework data:

I would like to add a column to the required coursework table, that searches for Employee ID and Course Code in the completion data table, and returns a completion date, or null return in the required coursework table.

This would allow me to create a report that would allow me to select a course, and see who has completed, and who has not.

I'm sure this is relatively easy, but I'm having a hard time tracking down how to do it.  Thanks!

[V-lianl-msft]

Hi @CBloome77 ,

According to your description, lookupvalue may not meet your needs. If a course corresponds to many students, you may need to create a measure. Please provide a sample pbix and the results you expect after deleting sensitive data.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CBloome77]

I'd like to add a sample PBX file, but like an idiot, I'm not sure how?

[PaulDBrown]

You can upload the file to a cloud service (OneDrive, Google Drive, Dropbox...) and share the public link from there

[PaulDBrown]

Can you please post dummy data samples or PBIX file?