Count unique employees per month

[michellepace]

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

[AntrikshSharma · Accepted solution]

@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. 

[michellepace]

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. 

[AntrikshSharma]

@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

[michellepace]

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.

[AntrikshSharma]

You're welcome! 🙂