Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count total working days

I have 2 tables:

 

attendance table:

IdNameDateNew id attendance Clock inClock OutAbsent
1Lebron6/1/2022 1-06 00:00:00 00:00 TRUE
1Lebron6/2/2022 1-06  07:00 15:00 Present
1Lebron6/3/2022 1-06  07:00 15:00 Present
1Lebron6/4/2022 1-06  07:00 15:00 Present
1Lebron7/3/2022 1-07  07:00 15:00 Present
1Lebron7/4/2022 1-07  07:00 15:00 Present
1Lebron7/5/2022 1-07  07:00 15:00 Present
1Lebron7/6/2022 1-07  07:00 15:00 Present
1Lebron8/1/2022 1-08  07:00 15:00 Present
1Lebron8/2/2022 1-08  07:00 15:00 Present
1Lebron8/3/2022 1-08  07:00 15:00 Present
1Lebron8/4/2022 1-08  07:00 15:00 Present
2Ari6/1/2022 2-06  07:00 15:00 Present
2Ari6/2/2022 2-06  07:00 15:00 Present
2Ari6/3/2022 2-06  07:00 15:00 Present
2Ari6/4/2022 2-06  07:00 15:00 Present
2Ari7/3/2022 2-07  07:00 15:00 Present
2Ari7/4/2022 2-0700:00:00 00:00 TRUE
2Ari7/5/2022 2-0700:00:00 00:00 TRUE
2Ari7/6/2022 2-07  07:00 15:00 Present
2Ari8/1/2022 2-08  07:00 15:00 Present
2Ari8/2/2022 2-08  07:00 15:00 Present
2Ari8/3/2022 2-08  07:00 15:00 Present
2Ari8/4/2022 2-08  07:00 15:00 Present

 

And employee table :

IdNameDatePayment per hourNew id
1Lebron6/1/2022  11-06
1Lebron7/1/2022  1.251-07
1Lebron8/1/2022  1.751-08
2Ari6/1/202222-06
2Ari7/1/20222.52-07
2Ari8/1/20222.52-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:
 
IdNameDatePayment per hourNew idTotal days worked
1Lebron6/1/2022  11-063
1Lebron7/1/2022  1.251-074
1Lebron8/1/2022  1.751-084
2Ari6/1/202222-064
2Ari7/1/20222.52-072
2Ari8/1/20222.52-084
DAX
Help Request
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

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

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    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.