cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Impactful Individual

## Count total working days

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
1 ACCEPTED SOLUTION
Super User

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" )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Community Support

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.

Super User

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" )
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.