Count if DAX Help :)

[Anonymous]

Hello all,

I have two tables, fBrazilBadge (badge swipes for brazil) and fHeadcount (global employee records). fBrazilBadge is linked to a date table. So far I have validated that I am getting the correct counts by date.

I am looking to create create a bar chart visual. I need to create a measure(s) that says, if "employee ID equals selected "location ID" count as "1" because I am only looking to calculate the attendance data of people who are pointed to one of the sites in Brazil.

e.g. January 7th, I have 10 people badging in. 3 of them have location ID that is not one of the sites in Brazil, the other 7 are. So the 7 people would be counted as having had been on site to their designated work location.

[Mikelytics · Accepted solution]

so I assume that in the headcoun table each employee has only one assgigned location. The measure could look like this

Batch-In Count of employees =
SUMX(
   BatchTable,
   CALCULATE(
      IF(SELECTEDVALUE(BatchTable[Location ID] = SELECTEDVALUE(HeadCountTable[Location ID],1,0)
   )
)
   

But for my current understanding you would nee to creat the relation via the employee table because you want to check whether the employee batches withint the home location.

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[Mikelytics]

Hi  @Anonymous,

First questions which arises for me is: Does the table fHeadcount have an EmployeeID? What is the granularity of this table? Because it seems not right to creat a relation over the location ID.

Best regards

Michael

[Anonymous]

Yes, the  fHeadcount does have employeeID. I did a merge query to do a fuzzy match using name on both fHeadcount and fBrazilBadge to bring EmployeeID to fBrazilBadge as it does not have that data.