Forum Discussion

rayinOz's avatar
rayinOz
Helper III
6 years ago
Solved

Custom column, measure or table

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

 

Need Help
Show and Tell

7 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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's avatar
      rayinOz
      Helper III
      6 years ago

      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"))

       

  • luisat's avatar
    luisat
    Frequent Visitor
    6 years ago

    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
     
     

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      rayinOz
      Helper III
      6 years ago

      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