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 ID Course Title Course Record Status Logge...
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)

     

     

     

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity 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.