cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karlosdsouza
Helper II
Helper II

Distinct Count after filtering table based on values

Capture.JPG

 

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

3 ACCEPTED SOLUTIONS
sreenathv
Solution Sage
Solution Sage

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.

 

 

EmplNoLWD
76130-Jul-18
47228-Jun-18
816-Jun-18
200 
250 

 

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

 

LWDMonth = MONTH(LWDTable[LWD])
EmplNoLWDLWD Month
76130-Jul-187
47228-Jun-186
816-Jun-186
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.

EmpCount.png

Hope this solution helps. Thanks.

 

 

View solution in original post

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.

 

  

 

View solution in original post

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.

 

 

View solution in original post

15 REPLIES 15
sreenathv
Solution Sage
Solution Sage

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.

 

 

EmplNoLWD
76130-Jul-18
47228-Jun-18
816-Jun-18
200 
250 

 

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

 

LWDMonth = MONTH(LWDTable[LWD])
EmplNoLWDLWD Month
76130-Jul-187
47228-Jun-186
816-Jun-186
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.

EmpCount.png

Hope this solution helps. Thanks.

 

 

View solution in original post

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

Relationship.JPG

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.

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

error.JPG

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

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.

 

  

 

View solution in original post

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

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

Ok, Anyworkarounds?

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.

Thanks so much. Will be real help !

 

This is the link to Dummy Data

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.

 

 

View solution in original post

Thanks so much ! Really helpful !

Let me try this today and will confirm

meanwhile can you share your pbix

Awesome ! Works like a charm !

 

Thanks so much !

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.

 

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!