Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count if DAX Help :)

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.

 

 

Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    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.

3 Replies

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    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.