Distinct Count after filtering table based on values

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.

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

LWDMonth = MONTH(LWDTable[LWD])

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.

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.

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.