Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
eliasayy
Impactful Individual
Impactful Individual

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

 

Untitled.png

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.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
v-rongtiep-msft
Community Support
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"
    )
)

vpollymsft_0-1658888023433.png

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

 

Untitled.png

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.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.