Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
For the below data set if I want to find the distinct number of employees that have passed the test , I use the below command
IF(ISBLANK(CALCULATE(DISTINCTCOUNT(Events_Table_R[Pass_Employee]),Events_Table_R[Pass_Status] = "Pass")),0,(CALCULATE(Events_Table_R[Percentage_of_total],Events_Table_R[Pass_Status] = "Pass")))
It gives the result 2 even if the 1 employee has 1 course in which he has not passed.
Can anyone help me with the modified query so that I get the result 1 and it should disregard the employer if it has failed atleast 1 course test.
Here is the sample dataset I am using : -
Employee_No | Auto_Assign_Date | Status | Enrollment_Date | Complete_By | Date_Class_Completed | Pass_Status | Legacy_Status | Course_Number |
10000096 | 4/13/2016 | Completed | 4/27/2016 | 5/13/2016 | 4/27/2016 | Pass | Completed | 0 |
10000096 | 4/27/2016 | Completed | 5/17/2016 | 5/27/2016 | 5/17/2016 | Pass | Completed | 30 |
10000096 | 5/5/2016 | Completed | 9/15/2015 | 6/4/2016 | 9/15/2015 | Pass | Completed | 25 |
10000096 | 5/5/2016 | Completed | 11/27/2015 | 6/4/2016 | 11/27/2015 | Pass | Completed | 13 |
10000096 | 5/5/2016 | Completed | 10/16/2015 | 6/4/2016 | 10/16/2015 | Pass | Completed | 24 |
10000096 | 4/19/2016 | Completed | 9/1/2015 | 5/19/2016 | 9/1/2015 | Pass | Completed | 15 |
10000096 | 4/15/2016 | Completed | 3/2/2016 | 5/15/2016 | 3/2/2016 | Pass | Completed | 1 |
10000096 | 4/15/2016 | Completed | 2/23/2016 | 5/15/2016 | 2/23/2016 | Pass | Completed | 17 |
10000096 | 12/9/2015 | Completed | 12/23/2015 | 1/8/2016 | 12/23/2015 | Pass | Completed | 4 |
10000096 | 7/7/2015 | Completed | 7/28/2015 | 8/6/2015 | 7/28/2015 | Pass | Completed | 14 |
10000096 | 6/10/2015 | Completed | 6/29/2015 | 7/10/2015 | 6/29/2015 | Pass | Completed | 16 |
10000096 | 9/3/2015 | Completed | 9/29/2015 | 10/3/2015 | 9/29/2015 | Pass | Completed | 18 |
10000096 | 3/31/2015 | Completed | 4/30/2015 | 4/30/2015 | 4/30/2015 | Pass | Completed | 26 |
10000096 | 3/4/2015 | Completed | 4/1/2015 | 4/3/2015 | 4/1/2015 | Pass | Completed | 2 |
10000096 | 5/19/2015 | Completed | 6/18/2015 | 6/18/2015 | 6/18/2015 | Pass | Completed | 28 |
10000096 | 12/2/2014 | Completed | 12/30/2014 | 1/1/2015 | 12/30/2014 | Pass | Completed | 31 |
10000096 | 5/3/2016 | Not Enrolled | 6/2/2016 | Not Passed | Overdue | 27 | ||
10000132 | 4/13/2016 | Completed | 4/28/2016 | 5/13/2016 | 4/28/2016 | Pass | Completed | 0 |
10000132 | 4/27/2016 | Completed | 5/11/2016 | 5/27/2016 | 5/11/2016 | Pass | Completed | 30 |
10000132 | 12/9/2015 | Completed | 12/21/2015 | 1/8/2016 | 12/21/2015 | Pass | Completed | 4 |
Solved! Go to Solution.
@abhishekpati86 Its actually very easy if you understand the principle. Basically you want count of employees where they have all passed. So what you need to do is first distinct count all employees that have all "Pass" then count distinct all employees that have "Not Passed" and then just subtract later from the former which will give you the distinct count of all that have only "Pass".
So create calculated measure as below,
Measure = CALCULATE(DISTINCTCOUNT(sam[Employee_No]), FILTER(sam, sam[Pass_Status] = "Pass"))
-
CALCULATE( DISTINCTCOUNT(sam[Employee_No]), FILTER(sam, sam[Pass_Status] = "Not Passed"))
@abhishekpati86 Its actually very easy if you understand the principle. Basically you want count of employees where they have all passed. So what you need to do is first distinct count all employees that have all "Pass" then count distinct all employees that have "Not Passed" and then just subtract later from the former which will give you the distinct count of all that have only "Pass".
So create calculated measure as below,
Measure = CALCULATE(DISTINCTCOUNT(sam[Employee_No]), FILTER(sam, sam[Pass_Status] = "Pass"))
-
CALCULATE( DISTINCTCOUNT(sam[Employee_No]), FILTER(sam, sam[Pass_Status] = "Not Passed"))
Hey Mr.Abhisek,
I will give the two solution for your prob ,
1. create a new measure
Dist Count of Emp. = CALCULATE(DISTINCTCOUNT(Table1[Employee_No]),
FILTER(ALL(Table1[Pass_Status]),Table1[Pass_Status]="Pass"))
eg :
2. Selecting filter in Visual level
let me know if it is not solve your issue ,
Okay lets try this one.
1. Create one conditional column
Now again two way 🙂
1. create Calculated Measure
Dist Count of Employee = CALCULATE(DISTINCTCOUNT(Table1[Employee_No]),FILTER(ALLSELECTED(Table1[Pass Condition ]),SUM(Table1[Pass Condition ]) = 0))
2. Using Visual filter
ley me know, am in online only
Just to clarify ---
Events_Table_R - Name of the Table
Pass_Employee = CONCATENATE(Events_Table_R[Employee_No],Events_Table_R[Pass_Status])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |