DISTINTCOUNT when applying filter

[esulli_]

Hi, 

I am building a dashboard to display turnover within an organisation but having problems when applying certain filters on my dashboard.

I have a simple DAX formula to count the number of employees which works as intended. 

No. of Employees = DISTINCTCOUNT('Capacity Mock'[Emp No.]) 

However when I apply a filter through a drop down slicer on my dashboard to view what department they were from the count does not alter. A filter works to show me the count of no. of employees from each Location but it does not give me a filtered count for a specific department. Both Location and Department are coming from different data sets so I am unsure if it is because my relationships are incorrect, or is there another DAX formula I can use?

I have attached copies of my data set in the comments below 

Any help would be great, thanks.

[selimovd]

Hey @esulli_ ,

your data model seems to be a little bit confusing. Can you share the file or at least the formulas?

It's important to know from which tables you try to count and in which table you are using for the analysis.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[esulli_]

Capacity Mock

Emp No.	Date	Hours	Dept at time	Year	Month	Day
1	01/04/2015	9	Hairdresser	2015	04	01
1	02/04/2015	4	Hairdresser	2015	04	02
1	03/04/2015	9	Hairdresser	2015	04	03
1	04/04/2015	0	Hairdresser	2015	04	04
1	05/04/2015	0	Hairdresser	2015	04	05
1	06/04/2015	4	Hairdresser	2015	04	06
1	07/04/2015	9	Hairdresser	2015	04	07
1	08/04/2015	9	Hairdresser	2015	04	08
1	09/04/2015	4	Hairdresser	2015	04	09
1	10/04/2015	9	Hairdresser	2015	04	10
1	11/04/2015	0	Hairdresser	2015	04	11
1	12/04/2015	0	Hairdresser	2015	04	12
1	13/04/2015	4	Hairdresser	2015	04	13
1	14/04/2015	9	Hairdresser	2015	04	14
1	15/04/2015	9	Hairdresser	2015	04	15
1	16/04/2015	4	Hairdresser	2015	04	16
1	17/04/2015	9	Hairdresser	2015	04	17
1	18/04/2015	0	Hairdresser	2015	04	18
1	19/04/2015	0	Hairdresser	2015	04	19
1	20/04/2015	4	Hairdresser	2015	04	20
1	21/04/2015	9	Hairdresser	2015	04	21
1	22/04/2015	9	Hairdresser	2015	04	22
1	23/04/2015	4	Hairdresser	2015	04	23
1	24/04/2015	9	Hairdresser	2015	04	24
1	25/04/2015	0	Hairdresser	2015	04	25
1	26/04/2015	0	Hairdresser	2015	04	26
1	27/04/2015	4	Hairdresser	2015	04	27
1	28/04/2015	9	Hairdresser	2015	04	28
1	29/04/2015	9	Hairdresser	2015	04	29
1	30/04/2015	4	Hairdresser	2015	04	30
2	01/04/2015	7	Grocers	2015	04	01
2	02/04/2015	7	Grocers	2015	04	02
2	03/04/2015	7	Grocers	2015	04	03
2	04/04/2015	0	Grocers	2015	04	04
2	05/04/2015	0	Grocers	2015	04	05
2	06/04/2015	7	Grocers	2015	04	06
2	07/04/2015	7	Grocers	2015	04	07
2	08/04/2015	7	Grocers	2015	04	08
2	09/04/2015	7	Grocers	2015	04	09
2	10/04/2015	7	Grocers	2015	04	10
2	11/04/2015	0	Grocers	2015	04	11
2	12/04/2015	0	Grocers	2015	04	12
2	13/04/2015	7	Grocers	2015	04	13
2	14/04/2015	7	Grocers	2015	04	14
2	15/04/2015	7	Grocers	2015	04	15
2	16/04/2015	7	Grocers	2015	04	16
2	17/04/2015	7	Grocers	2015	04	17
2	18/04/2015	0	Grocers	2015	04	18
2	19/04/2015	0	Grocers	2015	04	19
2	20/04/2015	7	Grocers	2015	04	20
2	21/04/2015	7	Grocers	2015	04	21
2	22/04/2015	7	Grocers	2015	04	22
2	23/04/2015	7	Grocers	2015	04	23
2	24/04/2015	7	Grocers	2015	04	24
2	25/04/2015	0	Grocers	2015	04	25
2	26/04/2015	0	Grocers	2015	04	26
2	27/04/2015	7	Grocers	2015	04	27
2	28/04/2015	7	Grocers	2015	04	28
2	29/04/2015	7	Grocers	2015	04	29
2	30/04/2015	7	Grocers	2015	04	30
3	01/04/2015	7	Florist	2015	04	01
3	02/04/2015	7	Florist	2015	04	02
3	03/04/2015	7	Florist	2015	04	03
3	04/04/2015	0	Florist	2015	04	04
3	05/04/2015	0	Florist	2015	04	05
3	06/04/2015	7	Florist	2015	04	06
3	07/04/2015	7	Florist	2015	04	07
3	08/04/2015	7	Florist	2015	04	08
3	09/04/2015	7	Florist	2015	04	09
3	10/04/2015	7	Florist	2015	04	10
3	11/04/2015	0	Florist	2015	04	11
3	12/04/2015	0	Florist	2015	04	12
3	13/04/2015	7	Florist	2015	04	13
3	14/04/2015	7	Florist	2015	04	14
3	15/04/2015	7	Florist	2015	04	15
3	16/04/2015	7	Florist	2015	04	16
3	17/04/2015	7	Florist	2015	04	17
3	18/04/2015	0	Florist	2015	04	18
3	19/04/2015	0	Florist	2015	04	19
3	20/04/2015	7	Florist	2015	04	20
3	21/04/2015	7	Florist	2015	04	21
3	22/04/2015	7	Florist	2015	04	22
3	23/04/2015	7	Florist	2015	04	23
3	24/04/2015	7	Florist	2015	04	24
3	25/04/2015	0	Florist	2015	04	25
3	26/04/2015	0	Florist	2015	04	26
3	27/04/2015	7	Florist	2015	04	27
3	28/04/2015	7	Florist	2015	04	28
3	29/04/2015	7	Florist	2015	04	29
3	30/04/2015	7	Florist	2015	04	30
4	01/04/2015	7	Gym	2015	04	01
4	02/04/2015	7	Gym	2015	04	02
4	03/04/2015	7	Gym	2015	04	03
4	04/04/2015	0	Gym	2015	04	04
4	05/04/2015	0	Gym	2015	04	05
4	06/04/2015	7	Gym	2015	04	06
4	07/04/2015	7	Gym	2015	04	07
4	08/04/2015	7	Gym	2015	04	08
4	09/04/2015	7	Gym	2015	04	09
4	10/04/2015	7	Gym	2015	04	10
4	11/04/2015	0	Gym	2015	04	11
4	12/04/2015	0	Gym	2015	04	12
4	13/04/2015	7	Gym	2015	04	13
4	14/04/2015	7	Gym	2015	04	14
4	15/04/2015	7	Gym	2015	04	15
4	16/04/2015	7	Gym	2015	04	16
4	17/04/2015	7	Gym	2015	04	17
4	18/04/2015	0	Gym	2015	04	18
4	19/04/2015	0	Gym	2015	04	19
4	20/04/2015	7	Gym	2015	04	20
4	21/04/2015	7	Gym	2015	04	21
4	22/04/2015	7	Gym	2015	04	22
4	23/04/2015	7	Gym	2015	04	23
4	24/04/2015	7	Gym	2015	04	24
4	25/04/2015	0	Gym	2015	04	25
4	26/04/2015	0	Gym	2015	04	26
4	27/04/2015	7	Gym	2015	04	27
4	28/04/2015	7	Gym	2015	04	28
4	29/04/2015	7	Gym	2015	04	29
4	30/04/2015	7	Gym	2015	04	30

Only a sample of this data as table was to large to post

[esulli_]

Turnover 

Emp No.Leave DateLeave ReasonYearMonthDayLeave Identifier

2	17/05/2015	Voluntary	2015	05	17	217052015
7	02/05/2015	Retirement	2015	05	02	702052015
8	10/04/2015	Early Retirement	2015	04	10	810042015
12	30/05/2015	Retirement	2015	05	30	1230052015
16	13/04/2015	Voluntary	2015	04	13	1613042015
18	30/04/2015	Voluntary	2015	04	30	1830042015
20	20/05/2015	Retirement	2015	05	20	2020052015

[esulli_]

Staff Details

LocationEmp No.SexStart Date

Blue City	1	M	07/06/2010
Red Town	2	F	03/06/2010
Blue City	3	F	14/06/2010
Blue City	4	F	14/06/2010
Red Town	5	F	01/07/2010
Blue City	6	M	01/07/2010
Blue City	7	F	26/07/2010
Blue City	8	F	02/08/2010
Blue City	9	F	20/09/2010
Blue City	10	F	20/09/2010
Red Town	11	M	01/04/2011
Red Town	12	F	01/04/2011
Red Town	13	F	01/04/2011
Red Town	14	F	01/04/2011
Red Town	15	M	01/04/2011
Red Town	16	F	01/04/2011
Red Town	17	M	01/04/2011
Red Town	18	F	01/04/2011
Red Town	19	F	01/04/2011
Red Town	20	M	01/04/2011

[esulli_]

'Capacity Mock' contains details of every day an employee has worked within a specific period and details of their employment, within this data is their department they work, which I would like to use as a filter.

Within this data set there is a DAX formula of 

No. of Employees = DISTINCTCOUNT('Capacity Mock'[Emp No.]) 

'Staff Details' which usually holds information on the employees (please see below). This is where the filter Location is being pulled from which seems to work fine with filters. 

The final data table is 'Turnover' (see below) this contains records of everyone who has left. Within this data table there are DAX formulas of 

Total No. of Leavers = DISTINCTCOUNT(Turnover[Emp No.])

No. of Vol Leavers = CALCULATE(COUNTROWS(Turnover),Turnover[Leave Reason]="Voluntary")

In my dashboard I would like to filter through different departments (please see drop box on left hand side of below screenshot i.e Arcade, etc) however it only seems to filter between both Locations 'Blue City' or 'Red Town' and not each department.