topic Count unique employees per month in DAX Commands and Tips

Count unique employees per month

michellepace — Thu, 27 Aug 2020 12:02:51 GMT

Hi there,

I am struggling to count unique employees per month. I can do it in excel, but not in power bi. Please see the data, PeopleCount1 formula and excel pivot (showing the correct result). Could somebody please help me with the DAX formula for PeopleCount? I suspect it is because of the way I have structured my data model.

Q.08 Data.xlsx
Q.08 Varying count of people over time.pbix

Thanks in advance, Michelle

Re: Count unique employees per month

AntrikshSharma — Thu, 27 Aug 2020 12:11:40 GMT

michellepace Use this:

PeopleCount1 = CALCULATE ( DISTINCTCOUNTNOBLANK ( Employees[Employee #] ), CROSSFILTER ( EmployeeHours[Index], Employees[Index], BOTH ) )

Also, that date table is incorrect, make sure you have all the dates for calendar/fiscal year to avoid any further issues.

Re: Count unique employees per month

michellepace — Thu, 27 Aug 2020 16:24:36 GMT

Hi AntrikshSharma ,

Thank you very much for such an eloquent solution.
Question 1: Do you perhaps have a link where I could read up on how/when to use crossfilter?

> that date table is incorrect, ensure you have all the dates for calendar/fiscal year to avoid issues.

Thanks for that. With my dates table, I only actually populate a date row where I have data existing (for example, I never have employee hours on a Sunday so many of the Sunday rows will not be in my Dates table). It may be an obvious answer, but just to be sure:
Question 2: should a Date table always contain consecutive dates (even if I don't have data for those dates)?

I am really new to "designing" data models. I have become suspicious that my model is incorrectly designed as it does not seem right that creating a measure of a simple employee count needs to be that sophisticated.
Question 3: in your opinion is there anything inherently flawed in my design?

Question 4: (just a little one) What format did you choose when writing your reply to get the DAX code to display so nicely? Did you choose perhaps "python"

Thank you once again.. and especially for your patience.
Michelle

Re: Count unique employees per month

AntrikshSharma — Thu, 27 Aug 2020 16:52:07 GMT

michellepace

A1 - Refer to this page and the articles at the bottom: https://dax.guide/crossfilter/

A2 - Yes, date table should always be complete calendar or fiscal year, think of it as a ladder, if you want to go 4 days back or 5 days in future you would need each step to be there otherwise you won't be able to ascend or descend, and time intelligence functions won't work.

A3 - The data model looks fine, the only reason we have to use CROSSFILTER is because you are counting DISTINCT in Employees table and then trying to filter it by both company and dates, for company it works but since Dates is not directly related to Employees we have to make the filter from date travel to EmployeeHours and then further make it travel to Employees, think of it as a bridge that we opened only for the duration of calculation. You can achieve the same result with using bi-directional filters and Expanded table filtering

1. Bi-directional - Edit the relationship between Employees and EmployeeHours ( Not a recommended method but will help in visualizing CROSSFILTER, don't use this ever!! as the filters from Dates will always be able to Filter Employees table and as the model grows you will get confuse why a measure doens't returns the required result. )

2. Expanded tables - Disable bi-directional filtering from the previous point and make it single directional and then use this code.

PeopleCount1 = CALCULATE ( DISTINCTCOUNTNOBLANK ( Employees[Employee #] ), EmployeeHours )

EmployeeHours in the above code is where table expansion happens and all the table that are related to EmployeeHours with Many: 1 relationship are joined to EmployeeHours with a LEFT JOIN, read this article. https://www.sqlbi.com/articles/expanded-tables-in-dax/

A4 - I have used https://www.daxformatter.com/ to format and indent & then PHP from the options

Re: Count unique employees per month

michellepace — Thu, 27 Aug 2020 18:24:22 GMT

Antriksh this is an incredibly useful answer which I will refer back to many times. Thank you so much for the clear and (very) educational explanation. A really top reply, thank you once again.

Re: Count unique employees per month

AntrikshSharma — Thu, 27 Aug 2020 18:33:19 GMT

You're welcome! 🙂