Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need sum the number of holiday hours for employees. Employees can work on multiple projects at one time, but they will only get paid for 8 holiday hours regardless of how many project they work on.
The data table below shows that John works on Project A and Project B at the same time and there's a holiday that overlaps both projects on 12/24/2021. If you summed all his holiday hours for the dates shown in the table, it would equal 24 hours. In reality, we are only paying him for 16 holiday hours.
Date | Name | Project | Hours | Holiday |
12/17/2021 | John | A | 8 | 0 |
12/18/2021 | John | A | 0 | 0 |
12/19/2021 | John | A | 0 | 0 |
12/20/2021 | John | A | 8 | 0 |
12/21/2021 | John | A | 8 | 0 |
12/22/2021 | John | B | 4 | 0 |
12/23/2021 | John | B | 6 | 0 |
12/24/2021 | John | B | 0 | 8 |
12/25/2021 | John | B | 0 | 0 |
12/26/2021 | John | B | 0 | 0 |
12/27/2021 | John | B | 7 | 0 |
12/28/2021 | John | B | 6 | 0 |
12/29/2021 | John | B | 6 | 0 |
12/22/2021 | John | A | 8 | 0 |
12/23/2021 | John | A | 7 | 0 |
12/24/2021 | John | A | 0 | 8 |
12/25/2021 | John | A | 0 | 0 |
12/26/2021 | John | A | 0 | 0 |
12/27/2021 | John | A | 4 | 0 |
12/28/2021 | John | A | 6 | 0 |
12/29/2021 | John | A | 6 | 0 |
12/30/2021 | John | A | 7 | 0 |
12/31/2021 | John | A | 5 | 0 |
1/1/2022 | John | A | 0 | 0 |
1/2/2022 | John | A | 0 | 0 |
1/3/2022 | John | A | 4 | 0 |
1/4/2022 | John | A | 0 | 8 |
1/5/2022 | John | A | 5 | 0 |
1/6/2022 | John | A | 5 | 0 |
I need to create a measure that respects those rules and only sums once per distinct date and employee. Then I will put it into a table or chart that contains month and looks something like this:
Month | Hours | Holiday |
December '21 | 96 | 8 |
January '22 | 14 | 8 |
Total | 110 | 16 |
I have a calendar table that connect to this data table. Both Hours and Holiday are in the same data table.
Please let me know if you have any thoughts. I'm guessing SUMX will be involved but not sure exactly how to structure this.
Thank you!!
Solved! Go to Solution.
Hi @itsme
Please try
Holiday Hours =
SUMX (
SUMMARIZE (
'Table',
'Table'[Name],
'Date'[Date],
"@Hours", MAX ( 'Table'[Holiday] )
),
[@Holiday]
)
Hi @itsme
Please try
Holiday Hours =
SUMX (
SUMMARIZE (
'Table',
'Table'[Name],
'Date'[Date],
"@Hours", MAX ( 'Table'[Holiday] )
),
[@Holiday]
)
Thank you @tamerj1 ! I believe the last line should be [@Hours] instead of [@Holiday].
User | Count |
---|---|
51 | |
38 | |
20 | |
14 | |
13 |
User | Count |
---|---|
96 | |
71 | |
29 | |
20 | |
13 |