DAX query to find the distinct count

[abhishekpati86]

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

[ankitpatira · Accepted 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]

Thanks @ankitpatira and @Baskar. It works now.

[Baskar]

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 , 

[abhishekpati86]

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.

[Baskar]

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 

[abhishekpati86]

Just to clarify --- 

Events_Table_R - Name of the Table 

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

[abhishekpati86]

Tagging @Greg_Deckler

[abhishekpati86]

Tagging @ankitpatira