Count Distinct job by Day and employee

[Thenalter]

Hello,

New to Power BI and have done quite a bit of searching on this subject. Found a lot of similar questions and responses and tried to implement quite a few of the responses I found with no success on my part so far. What I am looking for is a calulated field that counts the distinct job numbers in a day for each employee in my data set. I need these counts to then roll up in a hierarchy from employee level of detail to manager to location over time without losing an accurate count of number of jobs because two employees worked the same job the same day or an employee worked the same job two days in a row

Here is a dummy sample of what I am dealing with to help explain:

In the example above, both John and Steve work the same job (project 1) on the same day. I need my calculated field to count 1 for both of them for 1/2/2021, even though John is listed twice because he has a second entry for his overtime. On 1/3/2021 you can see they both work project 1 again, but Steve goes to project 2 for the second half of the day. John should be showing 1 job for the day and Steve would show 2 (based on two different job numbers in the same day). If they didn't work another day in the month or in the year, the totals would be John with 2 and Steve with 3.

Really appreciate the help

[mahoneypat · Accepted solution]

You can try this expression, replacing Table with your actual table name.  And, if you want your "Total" to be correct, you can add the Date column into the SUMMARIZE too.

Count Job and Name = COUNTROWS(SUMMARIZE(Table, Table[Name], Table[Project Number]))

Pat

[Thenalter]

Hi Pat,

Your solution got me most of the way there - Awesome thank you!! When I put that in as the measure on my list of names it returned the total job numbers worked in a given time period, but did not account for employees working multiple different job numbers in a day. The final piece was adding the date field from my data source to your suggestion. The final measure looks like:

Count Job and Name = COUNTROWS(SUMMARIZE(Table, Table [Name], Table [Project Number], Table [Date] ))

Thank you again for your help!!

[Thenalter]

I just re-read your post again @mahoneypat and only now realized you had said to use date. I looked for the facepalm emoji but couldn't find it...

Thanks Again!!

[Ashish_Mathur]

Hi,

You should be able to use a simple Distinctcount() function.

Measure = distinctcount(Data[Name])

Hope this helps.

[Thenalter]

Hi Ashish,

Thank you for reaching out. That does not seem to work in cases where employees work multiple different job numbers in a single day. In my example, Steve works two different jobs in a day, his count should be two for that day. If you simply distinctcount by name, don't you only get one for that day? And no cumulative count beyond that day?

Thanks Again!!