cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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

Accepted Solutions
Super User
Super User

Re: DAX query to find the distinct count

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

8 REPLIES 8

Re: DAX query to find the distinct count

Just to clarify --- 

 

Events_Table_R - Name of the Table 

 

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

Re: DAX query to find the distinct count

Tagging @Greg_Deckler

Re: DAX query to find the distinct count

Tagging @ankitpatira
Super User
Super User

Re: DAX query to find the distinct count

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 , 

Re: DAX query to find the distinct count

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.
Super User
Super User

Re: DAX query to find the distinct count

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 

 

Super User
Super User

Re: DAX query to find the distinct count

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

Re: DAX query to find the distinct count

Thanks @ankitpatira and @Baskar. It works now.