Distinct Count after filtering table based on values

[karlosdsouza]

I have to calculate the Total No of employees who had LWD in that month. Please guide
I tried different things e.g. creating a temp table with filtered but nothing is working out..

Pls. guide

(Desired outcome : Answer is Jun - 2 people left, July - 1 people left)

https://drive.google.com/file/d/1OToMGrcga2CcenKD6nUpBN-jcQrucEti/view?usp=sharing

[Anonymous · Accepted solution]

Hi,

You may create a calculated table with the following expression...

LWDTable = ALL(Employees[EmplNo],Employees[LWD])

This will give the following output into a new table named LWDTable.

EmplNo	LWD
761	30-Jul-18
472	28-Jun-18
81	6-Jun-18
200
250

Then add a calculated column to this table with the following formula

LWDMonth = MONTH(LWDTable[LWD])

EmplNo	LWD	LWD Month
761	30-Jul-18	7
472	28-Jun-18	6
81	6-Jun-18	6
200
250

Now you will be able to use the LWDMonth field in a Matrix visualisation (both as a row header as well as count of LWDmonth in values) to get the desired output.

Hope this solution helps. Thanks.

[Anonymous · Accepted solution]

Hi,

When I suggested that solution, I assumed that there won't be any duplicates in both the tables because there can be only one record for every employee in AllEmp table as well as LWD table. If that is not the case, you may alternatively modify the formula for creating the LWD Table as follows...

LWDTable = ALL(Employees[EmplNo],Employees[LWD],Employees[Department])

By including the department in LWDTable, you will be able to slice/filter by departments also.

But ideally, there should be one employee master without any duplicates. In your case, the Employees table has records of every month. But in case if one employee is in "Sales" Department in "April" and he gets transferred to another department in "May", then you will might have a problem.

To resolve this, you have to figure out a method to maintain a employee master (without the month field) where each employee is reflected only once and the department field shows the current department of the employee.

[Anonymous · Accepted solution]

Hi,

You may create a calculated table with the following expression...

LWDTable = ALL(Employees[EmplNo],Employees[LWD])

This will give the following output into a new table named LWDTable.

EmplNo	LWD
761	30-Jul-18
472	28-Jun-18
81	6-Jun-18
200
250

Then add a calculated column to this table with the following formula

LWDMonth = MONTH(LWDTable[LWD])

EmplNo	LWD	LWD Month
761	30-Jul-18	7
472	28-Jun-18	6
81	6-Jun-18	6
200
250

Now you will be able to use the LWDMonth field in a Matrix visualisation (both as a row header as well as count of LWDmonth in values) to get the desired output.

Hope this solution helps. Thanks.

[karlosdsouza]

Thanks, this works

However in the table when i also want to filter it by Department (another column, not shown in my picture), it doesnt work. What's the solution ?

Other relationship is as below

[Anonymous]

Hi,

Try this.

1. Delete the direct relationship between Calendar and LWDTable.
2. Create a relationship between the ECode in AllEmpIDs and ECode in LWDTable.
3. Let the Calendar to LWDTable relationship follow the path Calendar -> AllEmpIDs -> LWDTable.
4. This way you will be able to filter by department also.

[karlosdsouza]

Thanks Sreenath, on 2nd step given - Its giving the error as 

I checked LWD Table - It doesnt have any duplicates on E.Codes but have duplicates on LWD Date

[Anonymous · Accepted solution]

Hi,

When I suggested that solution, I assumed that there won't be any duplicates in both the tables because there can be only one record for every employee in AllEmp table as well as LWD table. If that is not the case, you may alternatively modify the formula for creating the LWD Table as follows...

LWDTable = ALL(Employees[EmplNo],Employees[LWD],Employees[Department])

By including the department in LWDTable, you will be able to slice/filter by departments also.

But ideally, there should be one employee master without any duplicates. In your case, the Employees table has records of every month. But in case if one employee is in "Sales" Department in "April" and he gets transferred to another department in "May", then you will might have a problem.

To resolve this, you have to figure out a method to maintain a employee master (without the month field) where each employee is reflected only once and the department field shows the current department of the employee.

[karlosdsouza]

Duplicates will always exist  2 employees can have same LWD or same departments.

What I have done is I have created a unique list of all departments and linked it with Employees and LWD table

But it has got me into another problem that i now can not use hierarchy Function > Department > Group

[Anonymous]

Duplicates in department or month or LWD is okay. What is causing the problem is duplicates in Employee itself.

[karlosdsouza]

Ok, Anyworkarounds?

[Anonymous]

Please share the PBIX file. This is not complicated. I will be able help. 

If that is not possible, you can send me all the relevant tables with few sample records.

[karlosdsouza]

Thanks so much. Will be real help !

This is the link to Dummy Data

[Anonymous · Accepted solution]

Hi,

I have downloaded your sample data and created a PBIX. For your requirement, You don't even need to create a calculated table I suggested earlier.

You have to just create two relationships betwen the AllEmpIDs table and the Calendar table and keep only one active.

Relationship 1 (Active) : AllEmpIDs[DOJ] -> Calendar[Date]

Relationship 2 (Inactive) : AllEmpIDs[LWD] -> Calendar[Date]

Once you have defined the two relationships as suggested above, you may proced to create the following measure.

CountEmpLWD = CALCULATE(DISTINCTCOUNT(AllEmpIDs[Empl No]),USERELATIONSHIP(AllEmpIDs[LWD],'Calendar'[Date]))

Using the "USERELATIONSHIP" formula, the system will use the inactive relationship betwen the LWD in your original table and the date field in Calendar table during the evaluation of the measure. Because of this, whenever you choose any month or year from calendar, the measure will show the distinct count of employees.

All other relationships will work as it is. i.e. any other filter like department etc.. will continue to work.

[karlosdsouza]

Thanks so much ! Really helpful !

Let me try this today and will confirm

meanwhile can you share your pbix

[karlosdsouza]

Awesome ! Works like a charm !

Thanks so much !

[Anonymous]

Hi,

If my solution helped you, don't forget to click the thumbs up / Kudos button. It will help others looking for similar solution to find the post under Kudoed posts.

[karlosdsouza]

Thanks so much ! Have accepted all 3 solutions.  All of them helped a lot !