Count total working days

[eliasayy]

I have 2 tables:

attendance table:

Id	Name	Date	New id attendance	Clock in	Clock Out	Absent
1	Lebron	6/1/2022	1-06	00:00:00	00:00	TRUE
1	Lebron	6/2/2022	1-06	07:00	15:00	Present
1	Lebron	6/3/2022	1-06	07:00	15:00	Present
1	Lebron	6/4/2022	1-06	07:00	15:00	Present
1	Lebron	7/3/2022	1-07	07:00	15:00	Present
1	Lebron	7/4/2022	1-07	07:00	15:00	Present
1	Lebron	7/5/2022	1-07	07:00	15:00	Present
1	Lebron	7/6/2022	1-07	07:00	15:00	Present
1	Lebron	8/1/2022	1-08	07:00	15:00	Present
1	Lebron	8/2/2022	1-08	07:00	15:00	Present
1	Lebron	8/3/2022	1-08	07:00	15:00	Present
1	Lebron	8/4/2022	1-08	07:00	15:00	Present
2	Ari	6/1/2022	2-06	07:00	15:00	Present
2	Ari	6/2/2022	2-06	07:00	15:00	Present
2	Ari	6/3/2022	2-06	07:00	15:00	Present
2	Ari	6/4/2022	2-06	07:00	15:00	Present
2	Ari	7/3/2022	2-07	07:00	15:00	Present
2	Ari	7/4/2022	2-07	00:00:00	00:00	TRUE
2	Ari	7/5/2022	2-07	00:00:00	00:00	TRUE
2	Ari	7/6/2022	2-07	07:00	15:00	Present
2	Ari	8/1/2022	2-08	07:00	15:00	Present
2	Ari	8/2/2022	2-08	07:00	15:00	Present
2	Ari	8/3/2022	2-08	07:00	15:00	Present
2	Ari	8/4/2022	2-08	07:00	15:00	Present

And employee table :

Id	Name	Date	Payment per hour	New id
1	Lebron	6/1/2022	1	1-06
1	Lebron	7/1/2022	1.25	1-07
1	Lebron	8/1/2022	1.75	1-08
2	Ari	6/1/2022	2	2-06
2	Ari	7/1/2022	2.5	2-07
2	Ari	8/1/2022	2.5	2-08

so i want to create a new column in the employee table of how many days an employee worked i want something like:

Total Days Worked = CALCULATE(COUNTROWS(Attendance'),  Attendance'[Absent] = "Present", '[New Id] = [New id Attendance]))

so the new employee table should look like:

Id	Name	Date	Payment per hour	New id	Total days worked
1	Lebron	6/1/2022	1	1-06	3
1	Lebron	7/1/2022	1.25	1-07	4
1	Lebron	8/1/2022	1.75	1-08	4
2	Ari	6/1/2022	2	2-06	4
2	Ari	7/1/2022	2.5	2-07	2
2	Ari	8/1/2022	2.5	2-08	4

[v-rongtiep-msft]

Hi @eliasayy ,

Maybe you can try to use a measure.

Measure =
CALCULATE (
    COUNTROWS ( attendance ),
    FILTER (
        ALL ( attendance ),
        attendance[New id attendance ]
            = SELECTEDVALUE ( attendance[New id attendance ] )
            && attendance[Name] = SELECTEDVALUE ( attendance[Name] )
            && attendance[Absent] = " Present"
    )
)

Best Regards
Community Support Team _ Polly

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

[Jihwan_Kim · Accepted solution]

Hi,

If you can create a relationship between two tables via New Id column and New Id Attendance column, please try using RELATEDTABLE DAX function for creating a calculated column.

Please check the below picture and the attached pbix file.

Total days work CC =
COUNTROWS (
    FILTER ( RELATEDTABLE ( Attendance ), Attendance[Absent] = "Present" )
)