Forum Discussion

anonymoususer92's avatar
anonymoususer92
Icon for Helper I rankHelper I
2 years ago
Solved

Understanding Headcount (Dax for Allexcept, All)

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...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
2 years ago

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's avatar
anonymoususer92
Icon for Helper I rankHelper I
2 years ago

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