Trying to set up a complicated report for a training program.

[LaneLourcey]

I'm trying to set up a report for a training program. From the training system I can pull a series of reports for each class, and join them together into the following structure

Class	Student	Completion %
Class 1	Adam	75%
Class 1	Suzie	100%
Class 2	Suzie	100%
Class 3	Adam	0%
Class 4	Suzie	100%

The complicated part is that we're trying to show who has completed a particular program, of which we have three. Each of these programs requires some of the classes, and the programs overlap. I set up a table to map the programs to the course completion data as follows.

Program	Class
Program A	Class 1
Program A	Class 2
Program A	Class 4
Program B	Class 1
Program B	Class 2
Program B	Class 3

Now I'm thinking I need to be able to show an individual's number of courses completed in a program, and their overall % completion of a program, but I'm not sure the measures I could use to do that.

[amitchandak · Accepted solution]

@LaneLourcey , One of the ways is to merge these two tables into one in power query. Post that you can have new column in DAX like (use 1 or 100 as per need)

if(Sumx(filter(Table, [Student] =earlier([Student]) && [Program] =earlier([Program])),[Completion %]) =1, "Done", "Pending")