cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abhishekpati86
Helper III
Helper III

DAX query to find the distinct count

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_NoAuto_Assign_DateStatusEnrollment_DateComplete_ByDate_Class_CompletedPass_StatusLegacy_StatusCourse_Number
100000964/13/2016Completed4/27/20165/13/20164/27/2016PassCompleted0
100000964/27/2016Completed5/17/20165/27/20165/17/2016PassCompleted30
100000965/5/2016Completed9/15/20156/4/20169/15/2015PassCompleted25
100000965/5/2016Completed11/27/20156/4/201611/27/2015PassCompleted13
100000965/5/2016Completed10/16/20156/4/201610/16/2015PassCompleted24
100000964/19/2016Completed9/1/20155/19/20169/1/2015PassCompleted15
100000964/15/2016Completed3/2/20165/15/20163/2/2016PassCompleted1
100000964/15/2016Completed2/23/20165/15/20162/23/2016PassCompleted17
1000009612/9/2015Completed12/23/20151/8/201612/23/2015PassCompleted4
100000967/7/2015Completed7/28/20158/6/20157/28/2015PassCompleted14
100000966/10/2015Completed6/29/20157/10/20156/29/2015PassCompleted16
100000969/3/2015Completed9/29/201510/3/20159/29/2015PassCompleted18
100000963/31/2015Completed4/30/20154/30/20154/30/2015PassCompleted26
100000963/4/2015Completed4/1/20154/3/20154/1/2015PassCompleted2
100000965/19/2015Completed6/18/20156/18/20156/18/2015PassCompleted28
1000009612/2/2014Completed12/30/20141/1/201512/30/2014PassCompleted31
100000965/3/2016Not Enrolled 6/2/2016 Not PassedOverdue27
100001324/13/2016Completed4/28/20165/13/20164/28/2016PassCompleted0
100001324/27/2016Completed5/11/20165/27/20165/11/2016PassCompleted30
1000013212/9/2015Completed12/21/20151/8/201612/21/2015PassCompleted4
1 ACCEPTED SOLUTION
ankitpatira
Community Champion
Community Champion

@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"))

View solution in original post

8 REPLIES 8
ankitpatira
Community Champion
Community Champion

@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"))

View solution in original post

Thanks @ankitpatira and @Baskar. It works now.

Baskar
Resident Rockstar
Resident Rockstar

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.PNG

 

 

2. Selecting filter in Visual level 

 

1.PNG

 

 

 

let me know if it is not solve your issue , 

Hi Baskar,

Thanks for your reply. I am getting the same count but I want the result count to be only 1.

It should show only the 2nd row employee in your pivot table and not the 1st one as the employee hasn't passed one of the course.

Okay lets try this one.

 

1. Create one conditional column 

 

1.PNG

 

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)) 

 

 

1.PNG

 

 

2. Using Visual filter

2.PNG

 

 

 

ley me know, am in online only 

 

abhishekpati86
Helper III
Helper III

Just to clarify --- 

 

Events_Table_R - Name of the Table 

 

Pass_Employee = CONCATENATE(Events_Table_R[Employee_No],Events_Table_R[Pass_Status])

Tagging @Greg_Deckler

Tagging @ankitpatira

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors