Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
rubinboer
Resolver II
Resolver II

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)

 

example.PNG

 

 

View solution in original post

2 REPLIES 2
v-yingjl
Community Support
Community Support

Hi @Anonymous ,

I have created this sample table:

sample table.png

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:

table result.png

 

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.

rubinboer
Resolver II
Resolver II

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)

 

example.PNG

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.