Understanding Headcount (Dax for Allexcept, All)

[anonymoususer92]

Hi All,

I have been tasked with creating a report that tracks headcount, attendance, etc. for employees. 

The first phase is understanding headcount - ie, how many people are coming onsite for a given time period. 

I'm sharing a subset for 3 employees for the month of Aug, 2024. Needless to say, there are 1000s of employees but I am focusing on these 3 to make sure my measures are working right (which aren't, which is why I am asking for help on this group ;(  ) 

Here is what I want to find and the dax funtions I used- 

1. UniqueBadgeIns Per Day: 

UniqueBadgeInsPerDay_Final 3 =

CALCULATE(

    DISTINCTCOUNT('Badging Data'[EmployeeID]),

    KEEPFILTERS(VALUES('Badging Data'[EnterBadgeDate])),

    KEEPFILTERS(VALUES('Badging Data'[Employee Full Name]))

)

2. UniqueBadgeIns Per Week: 

UniqueBadgeInsPerWeek =

CALCULATE(

    DISTINCTCOUNT('Badging Data'[EmployeeID]),

    ALLEXCEPT('Badging Data','Badging Data'[EnterBadgeDate]),

    VALUES('Badging Data'[WeekNumber])

)

3. UniqueBadgeIns Per Month:

UniqueBadgeInsPerMonth =

CALCULATE(

    DISTINCTCOUNT('Badging Data'[EmployeeID]),

    ALLEXCEPT('Badging Data', 'Badging Data'[EnterBadgeDate]),

    VALUES('Badging Data'[EnterBadgeDate].[Month])

)

4. UniqueBadgeIns Per Quarter: 

UniqueBadgeInsPerQuarter =

CALCULATE(

    DISTINCTCOUNT('Badging Data'[EmployeeID]),

    ALLEXCEPT('Badging Data', 'Badging Data'[EnterBadgeDate]),

    VALUES('Badging Data'[EnterBadgeDate].[Quarter])

)

Employee Full Name	EmployeeID	EnterBadgeTime	Sum of TotalMinutes	EnterBadgeBuilding
E	2	8/6/2024 8:25	389	229
M	1	8/6/2024 8:37	434	223
E	2	8/7/2024 8:41	482	229
M	1	8/7/2024 9:04	461	223
D	3	8/7/2024 9:08	457	223
M	1	8/8/2024 8:35	452	229
E	2	8/8/2024 8:42	459	229
D	3	8/8/2024 9:11	359	223
M	1	8/12/2024 7:28	62	223
M	1	8/21/2024 7:41	532	223
E	2	8/26/2024 9:41	320	229
M	1	8/27/2024 13:35	164	223
M	1	8/28/2024 8:29	474	229
E	2	8/29/2024 9:54	155	229

Based on the dataset above, if I filtered for 8/6/2024, my measure for UniqueBadgeIns Per Day is returning a count of 3 instead of 2. 
I feel so disheartened that I find it hard to move forward with other requirements.

Can someone tell me what I'm doing wrong? 
Also, what If I were to slice this data by not just EnterBadgeData, but also - Business Group, Employee Name, Division, Unit, etc, ?

Eagerly awaiting guidance on this topic.

Thanks in advance to the community 🙂 

[v-tangjie-msft · Accepted solution]

HI @anonymoususer92 ,

Thanks rajendraongole1  for the quick reply. I have some other thoughts to add:

1. This is my test data.  

2.We can create a column.

EnterBadgeDate = DATEVALUE([EnterBadgeTime])

3.We can create a date table.

Date = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),"quarter",QUARTER([Date]),"month",MONTH([Date]),"weeknum",WEEKNUM([Date],2))

4. We can create a measure.

UniqueBadgeIns = 
CALCULATE(
    DISTINCTCOUNT('Badging Data'[EmployeeID]),ALLSELECTED('Badging Data'))

5.Then the result is as follows.

If I've misunderstood you please point it out in a follow-up reply.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Ashish_Mathur]

Hi,

In the Power Query Editor, seperate the time stamp in another column so that you have only the date data type in a column.  Create a Calendar Table with calculated column formulas for Year, Month name, Month number and Quarter.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Date column (created as per the first sentence i wrote) to the Date column of the Calendar Table.  To any visual, drag any date dimension from the Calendar Table.  Drag this measure

Measure = distinctcount('Badging data'[EmployeeID])

Hope this helps.

[v-tangjie-msft · Accepted solution]

HI @anonymoususer92 ,

Thanks rajendraongole1  for the quick reply. I have some other thoughts to add:

1. This is my test data.  

2.We can create a column.

EnterBadgeDate = DATEVALUE([EnterBadgeTime])

3.We can create a date table.

Date = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),"quarter",QUARTER([Date]),"month",MONTH([Date]),"weeknum",WEEKNUM([Date],2))

4. We can create a measure.

UniqueBadgeIns = 
CALCULATE(
    DISTINCTCOUNT('Badging Data'[EmployeeID]),ALLSELECTED('Badging Data'))

5.Then the result is as follows.

If I've misunderstood you please point it out in a follow-up reply.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[anonymoususer92]

Thank you @v-tangjie-msft Neeko! 

This measure works great when I used the data set you provided ! 

A few points -

1. When I used the above measure on my actual data, it is still giving a count of 3 for 6th of Aug. It is so weird. (See screenshots). And it is the employee with alias name 'D' that is causing this issue. Could this be because I am using another slicer  called 'Emp Full + ID' ?

2. Could it be because I changed the relationship between date table (I have a custom date table too) and Badging data to Bi-directional? 

3. If you look at your last screenshot, it is showing a count of 3 instead of 2! What do you think happened there? 

[v-tangjie-msft · Accepted solution]

Hi @anonymoususer92 ,

Try to:
1. Change the model relationship to a one-to-many relationship in one direction
2. Click the [employee name+id] slicer and set the interaction to "None" on the card visual.

Change how visuals interact in a report - Power BI | Microsoft Learn

If that doesn't help, please provide your PBIX file (please note the protection of private data).

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[anonymoususer92]

It worked! The issue I found was that the Sum of Total Minutes had blank values that prevented rows showing up in a table. I changed that in the Power Query from Blank to 0. That seemed to pull up the records on a table.

Let me ask you this now - 

If I use the unique badge in measure, I get count (Business Group, Division, even Quarters) as the same value (which is the total unique badge ins). See first image.

But I'd like for it to be like the below image - 

Thanks again Neeko! 

[v-tangjie-msft]

Hi @anonymoususer92 ,

Glad to hear that your issue has been resolved. Sorry for that the information you have provided is not making the problem clear to me. 

In order to solve your new problem , please create a new case with a detailed description ,we suggest a case to solve only one problem, because it can get good help and give a better reference for other users!

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[rajendraongole1]

Hi @anonymoususer92 -create a below measure calculate the distinct number of employees who badged in on a given day , i am using selectedvalue function. as we have slicer to report 

UniqueBadgeInsPerDay_Final =
CALCULATE(
DISTINCTCOUNT('Badging Data'[EmployeeID]),
FILTER(
'Badging Data',
'Badging Data'[EnterBadgeDate] = SELECTEDVALUE('Badging Data'[EnterBadgeDate])
)
)

Create measure for weekly, monthly, and quarterly calculations,instead of use of ALLEXCEPT and VALUES might be causing duplication or incorrect filtering

UniqueBadgeInsPerWeek =
CALCULATE(
DISTINCTCOUNT('Badging Data'[EmployeeID]),
FILTER(
'Badging Data',
'Badging Data'[EnterBadgeDate] IN DATESINPERIOD(
'Badging Data'[EnterBadgeDate],
MAX('Badging Data'[EnterBadgeDate]),
-7,
DAY
)
)
)

Monthly:

UniqueBadgeInsPerMonth =
CALCULATE(
DISTINCTCOUNT('Badging Data'[EmployeeID]),
FILTER(
'Badging Data',
MONTH('Badging Data'[EnterBadgeDate]) = MONTH(SELECTEDVALUE('Badging Data'[EnterBadgeDate]))
)
)

Quarterly:

UniqueBadgeInsPerQuarter =
CALCULATE(
DISTINCTCOUNT('Badging Data'[EmployeeID]),
FILTER(
'Badging Data',
QUARTER('Badging Data'[EnterBadgeDate]) = QUARTER(SELECTEDVALUE('Badging Data'[EnterBadgeDate]))
)
)

if you add a slicer for Business Group, the DISTINCTCOUNT measure will return the distinct badge-ins for employees in that group only.

please check on the EmployeeID and EnterBadgeDate are properly filtered so that only distinct badge-ins per day are counted.

Above measure helps in your scenerio.

[anonymoususer92]

Thanks @rajendraongole1 !

So I used the measure UniqueBadgeInsPerDay_Final that you created. 
While it is able to filter based on emp_id selected, it doesn't account for a particular 'date'
If you look at the data I shared, if i select 6th Aug from the slicer, and then slice for all 3 employees, it gives me a count fo 3 (In KPI Card) instead of 2. I think it the function is missing the ability to slice by date along with emp_id