Custom column, measure or table

[rayinOz]

Hello community

I have a dataset that has two courses per employee (employeeID). Course A and Course B. Each course will have a status "complete" or "incomplete".

EmployeeID | Course Name | Course Status

001 | Course A | Incomplete
001 | Course B | Complete

002 | Course A | Complete
002 | Course B | Complete

003 | Course A | Incomplete
003 | Course B | Incomplete

1. I need to get a list of employees who have completed both courses
2. I need to get a percent of employees who have completed both courses

I'm sure there is an easy way to do this... any help would be GREATLY appreciated.

Kind regards
Ray

[V-lianl-msft]

Hi @rayinOz ,

Please try to create a calculated column and apply in visual level filter.

Y/N = CALCULATE(COUNT('Table'[Course Name]),FILTER(ALLEXCEPT('Table','Table'[EmployeeID]),'Table'[Course Status]="Complete"))

Then create a measure like this:

PERCENTAGE_ = DIVIDE(CALCULATE(DISTINCTCOUNT('Table'[EmployeeID]),FILTER(ALL('Table'),'Table'[Y/N]=2)),CALCULATE(DISTINCTCOUNT('Table'[EmployeeID]),ALL('Table')))

For more details,you can refer to the pbix.

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

[rayinOz]

YES... almost there. This solution is gonna work me thinks. 

Follow up. IF both courses are listed as incomplete, the field has a null value.. how do I make it a zero?

Y/N = CALCULATE(COUNT('MI and Cyber (2)'[Course Name]), FILTER(ALLEXCEPT('MI and Cyber (2)','MI and Cyber (2)'[Username]), 'MI and Cyber (2)'[UoM Status]="Completed"))

[Ashish_Mathur · Accepted solution]

Hello

You can download my PBI file from here.

I hope this helps.

[rayinOz]

Thank you! You have given me everything I need!! WHEW!!!! Boss happy now. 😄

Kind regards

Ray

[amitchandak]

@rayinOz ,

Create the measures like this and display against the employee ID/Name

total = calculate(distinctCOUNT(table[Course Name ]))
Complete =  calculate(distinctCOUNT(table[Course Name ]), table[Course Status]="Complete")

Flag = if([total]=[Complete],"Yes","No")

Appreciate your Kudos.

[rayinOz]

Hello

Thanks for helping me create the three measures. I've done that, but I am unsure how the display them in a list or graph.... thanks so much. 😄

Ray

[luisat]

Hey @rayinOz ,

at first id suggest to pivot the data so your data would look like this

let
Source = #table( {"Column 1", "Column 2", "Column 3"}, { {001 ,"Course A", "Complete"}, {001 ,"Course B", "Complete"}, {002 ,"Course A", "Incomplete"}, {002 ,"Course B", "Complete"}, {003 ,"Course A", "Complete"}, {003 ,"Course B", "Complete"}, {004 ,"Course A", "Incomplete"},
{004 ,"Course B", "Incomplete"} }),
#"Pivot" = Table.Pivot(Source, List.Distinct(Source[#"Column 2"]), "Column 2", "Column 3"),
#"Renamed" = Table.RenameColumns(#"Pivot",{{"Column 1", "Id"}})
in
#"Renamed"

Now just check where "Course A" and "Course B" is Complete.

CompletedBoth = Abfrage2[Course A] = "Complete" && Abfrage2[Course B] = "Complete"

Regards,

Luis