Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter Column X based on Column Y

I have one table that contains the following (example) data.  Approximately 8000 unique Employee IDs, approximately 70,000 total records:

 

Employee IDCourse TitleCourse Record StatusLogged Date
0001example_courseCompleted_Successfully01-20-2019
0001example_courseCompleted_Unsuccessfully01-20-2019
0001example_courseIn_Process01-19-2019
    

 

I need to create a visual that compares the number of employees that have successfully completed a course vs unsuccessfully or in process.  As you can see from the example data, a single employee could have a record of all three types for a single course, and when I create the visual I don't want to count completed unsuccessfully or in process records if the completed successfully record exists for the same employee.  I'm relatively new to Power BI, and I know I should be creating either a new measure or a new calculated column, but I'm not sure where to begin.

Need Help
  • rubinboer's avatar
    rubinboer
    6 years ago

    compares the number of employees that have successfully completed a course vs unsuccessfully or in process

    measures

    number of results = COUNTROWS('YourTable')

    employees that have successfully completed = COUNTROWS(FILTER('YourTable','YourTable'[status] = "Completed_Successfully"))

    employees that have unsuccessfully completed = COUNTROWS(FILTER('YourTable','YourTable'[status] = "Completed_Unsuccessfully"))

    courses that are in progress = COUNTROWS(FILTER('YourTable','YourTable'[status] = "In_Process"))

     

    I need to create a visual that compares

    percentage successfully completed = DIVIDE(employees that have successfully completed , number of results)

    percentage unsuccessfully completed = DIVIDE(employees that have unsuccessfully completed , number of results)

    percentage in progress = DIVIDE(courses that are in progress, number of results)

     

     

     

2 Replies

  • rubinboer's avatar
    rubinboer
    Resolver II
    6 years ago

    compares the number of employees that have successfully completed a course vs unsuccessfully or in process

    measures

    number of results = COUNTROWS('YourTable')

    employees that have successfully completed = COUNTROWS(FILTER('YourTable','YourTable'[status] = "Completed_Successfully"))

    employees that have unsuccessfully completed = COUNTROWS(FILTER('YourTable','YourTable'[status] = "Completed_Unsuccessfully"))

    courses that are in progress = COUNTROWS(FILTER('YourTable','YourTable'[status] = "In_Process"))

     

    I need to create a visual that compares

    percentage successfully completed = DIVIDE(employees that have successfully completed , number of results)

    percentage unsuccessfully completed = DIVIDE(employees that have unsuccessfully completed , number of results)

    percentage in progress = DIVIDE(courses that are in progress, number of results)

     

     

     

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi Anonymous ,

    I have created this sample table:

    Let me confirm it first you don't want to count completed unsuccessfully or in process records if the completed successfully record exists for the same employee. If the employee has completed unsuccessfully or in process status they should be counted repeatedly, right?

    If so, you can create the following measures:

    Successful completed =
COUNTROWS (
    FILTER ( 'Table', 'Table'[Course Record Status] = "Completed_Successfully" )
)
    Unsuccessful completed =
COUNTROWS (
    FILTER (
        'Table',
        'Table'[Course Record Status] = "Completed_Unsuccessfully"
            && CALCULATE (
                COUNT ( 'Table'[Employee ID] ) <= 2,
                ALLEXCEPT ( 'Table', 'Table'[Employee ID] )
            )
    )
)
    In Process =
COUNTROWS (
    FILTER (
        'Table',
        'Table'[Course Record Status] = "In_Process"
            && CALCULATE (
                COUNT ( 'Table'[Employee ID] ) <= 2,
                ALLEXCEPT ( 'Table', 'Table'[Employee ID] )
            )
    )
)

    The result should be like this:

     

    Sample file is attached that hopes to help you, please check and try it: Filter Column X based on Column Y.pbix 

     

    Best Regards,
    Yingjie Li

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