Forum Discussion
DAX to display missing courses
Hello everyone!
I have a unique issue I could use some help with, but I'm not sure where to start. Basically, I need to put together a DAX formula that will look at a set of Completed courses within one course set and a set of Incomplete courses within another course set. Then I need it to compare the two course sets and "highlight" (doesn't actually need to highlight in the literal sense) which courses still need to be completed between the two course sets. I know that can be a bit confusing, so here's an example using sandwiches:
| Sandwich 1 ingredient list | Status | Sandwich 2 ingredient list | Status |
| Butter Knife | Complete | Bacon | Incomplete |
| Bread | Complete | Lettuce | Incomplete |
| Peanut Butter | Complete | Tomato | Incomplete |
| Jelly | Complete | Bread | Complete |
| Bananas | Complete | Butter Knife | Complete |
In the table above, I meet all necessary criteria/prerequisites to make a Peanut Butter, Banana, and Jelly sandwich. But as you can see for Sandwich 2, I'm missing some of the prerequisites (I don't have Bacon, Lettuce, or Tomato ready yet).
What I'm trying to do is choose what I AM able to make (a PBB&J sandwich), then choose what I WANT to make (a BLT), then have a table, card, or something else show me only what I'm missing (since the bread and butter knife are present in both and I'm marked as having a butter knife and bread already, I only need Bacon, Lettus, and Tomato).
The purpose of all of this is to identify missing courses for cross training in a large data set. Since a lot of the courses we offer are present in multiple course sets, I need to put something together that will allow users to choose which two course sets to compare, and have PBI spit out a "for the 2nd course set, you need to finish x, y, and z courses then you'll be fully cross trained on both course sets". Hopefully my weird example and subsequent explanation make sense.
Again, not sure where to begin (or if it's even possible?), so if anyone is able to help I'd be forever grateful. If not, then I at least appreciate you taking the time to read this! Thanks much everyone.
2 Replies
So, let's think about a data model.
If you have a table with the fields:
Student, Course Set, Course, Status
And you filter for "incomplete" as a statusYou will get a table with all the courses/coursesets the student still needs to complete
You might want to add a "date completed" fieldThis would be your fact table.
You would probably want a list of students in a "student" table, a list of courses in a courses table and a list of coursesets in a courseset table and a list of statuses in a status table.
Those would be your dimension tables, all with a 1 to many relationship to your fact table.
Having the values in dimension tables will make it a lot easier to do filtering on your fact table than just using the fields from the fact table directly.
If you add a slicer for students, and one for status, it should be easy to display all the courseset/courses that are still imcomplete for a student.If different students have different course sets, then you might need another fact table that lets you assign course sets to students.
I don't want to go into to many what ifs here... let me know if you'd like more information on this.Power BI is easy to learn, but it has hard parts.
I'm a personal Power Bi Trainer I learn something every time I answer a questionOn a question with this many ramifications it might help to do a screen share where we can have a discussion. Email me a day/time and I'll send you a Zoom invitation to a meeting. [email protected]
