topic Attendance or count based on 2 fields in DAX Commands and Tips

Attendance or count based on 2 fields

rains_23 — Mon, 21 Oct 2024 14:16:30 GMT

Hi,

I have a table, as below and I need to count the employee attendance for the whole month. I want the answer as ABC =3, PQR=4, CDF=3. If I use the count of (Login date) in a matrix it gives correct, but the total does not come as correct. Also I want it based on Division for each month, or week. How do I use this. I used the sumx function, but it is not working.

Division	Employee Name	Login Date	Time	Day of Week	Location
1	ABC	24/01/2023 00:00	50:17.0	Tuesday	Atrium Door RHS
1	ABC	25/01/2023 00:00	33:52.0	Wednesday	Atrium Door RHS
1	ABC	31/01/2023 00:00	54:14.0	Tuesday	Atrium Door RHS
1	PQR	11/01/2023 00:00	07:44.0	Wednesday	Atrium Door RHS
1	PQR	31/01/2023 00:00	25:03.0	Tuesday	2nd Floor Lift Lobby Door LHS
1	PQR	11/01/2023 00:00	24:06.0	Wednesday	Atrium Door RHS
2	PQR	12/01/2023 00:00	12:56.0	Thursday	Atrium Door RHS
2	CDF	13/01/2023 00:00	12:43.0	Friday	Atrium Door RHS
2	CDF	16/01/2023 00:00	12:08.0	Monday	Atrium Door RHS
2	CDF	17/01/2023 00:00	13:19.0	Tuesday	Atrium Door RHS

Re: Attendance or count based on 2 fields

Selva-Salimi — Mon, 21 Oct 2024 14:45:02 GMT

Hi rains_23

first add a column to your table as follows:

year_month= format( date , 'YYYYMM')

and then write a measure as follows:

measure employee_count := var tbl = summarize (table , division, employee , year_month ,"cnt", count (employee))

return

sumx(tbl , cnt )

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Attendance or count based on 2 fields

Anonymous — Tue, 22 Oct 2024 01:40:21 GMT

Hi rains_23

When using the count of “Login Date” in the matrix, the total is 10, isn't that what you want? May I ask what you expect the total to be?

If I use the count of (Login date) in a matrix it gives correct, but the total does not come as correct.

Here is my test result:

Create new columns with the following DAX:

MonthYear = FORMAT([Login Date], "YYYY-MM")

WeekNumber = WEEKNUM([Login Date])

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

Re: Attendance or count based on 2 fields

rains_23 — Tue, 22 Oct 2024 08:48:11 GMT

The total does not come correct for me ,

Re: Attendance or count based on 2 fields

Anonymous — Tue, 22 Oct 2024 09:19:54 GMT

Hi rains_23

There is too little information to see from this screenshot, could you please create a matrix visual showing us the problem you are experiencing using the sample data from the initial post?

And describe all the relevant fields in the matrix and the measures that might be used.

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

Re: Attendance or count based on 2 fields

rains_23 — Tue, 22 Oct 2024 13:50:00 GMT

Sorry, I was going to post the data I used. All are simple columns. Month is a colum duplicated from Login date. with month format. What it is doing is it is taking the total as 21 in above example, that is number of employees in the month of jan. and not no. of occurence. So If an employee is in month of jan 3 times, it is counting as 1 and not 3

Re: Attendance or count based on 2 fields

rains_23 — Tue, 22 Oct 2024 13:50:44 GMT

Card details - is employee name

Re: Attendance or count based on 2 fields

Anonymous — Wed, 23 Oct 2024 07:11:32 GMT

Hi rains_23

Based on your description, I think you were expecting the totals to be count of Login Date, but the actual result in your matrix looks like count of employee, is that correct? Please correct me if I have misunderstood.

However, I am not able to reproduce the problem using sample data.

Please check the following first:
1. Check the field settings:
Make sure the Employee, Division and Month fields are not incorrectly set as row labels or key column.

2. Data Model Relationships:
Make sure that the Login Date column has the correct relationship to the other tables and that there are no many-to-many relationships.

If the problem persists, please create a simple sample Power BI file with sample data to reproduce the problem and share your pbix file here.

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

Re: Attendance or count based on 2 fields

rains_23 — Wed, 23 Oct 2024 11:08:02 GMT

Hi,

Unfortunately I am not able to find the Row Label and Key Column properties in my version.

Re: Attendance or count based on 2 fields

Anonymous — Thu, 24 Oct 2024 05:53:18 GMT

Hi rains_23

In the model view, select your table to see the Row lable and Key column.

As stated in the previous reply, if possible, please create a Power BI file using simple sample data and reproduce the problem, then share that Power BI file here and we'll try to find the cause of the probelm and solve it.

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