Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | Logged Date |
0001 | example_course | Completed_Successfully | 01-20-2019 |
0001 | example_course | Completed_Unsuccessfully | 01-20-2019 |
0001 | example_course | In_Process | 01-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.
Solved! Go to Solution.
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)
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.
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)
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |