Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am currently working on 2 tables. The 1st table has the regular hours and the 2nd one has the OT hours.
What I am trying to achieve is to have the OT hours in a new column in the Regular Hours table. I already tried LOOKUPVALUE but it gave me an error.
Attached is the xlsx file.
Thank you in advance.
Solved! Go to Solution.
hi, @PatrickLamoste
For your case, you need to create a new table by this formula:
Table =
SUMMARIZE (
Table1,
Table1[Employee ID],
Table1[Date],
"Regular Hours", CALCULATE ( SUM ( Table1[Hours] ) ),
"OT Hours", CALCULATE (
SUM ( Table2[Hours] ),
FILTER (
Table2,
Table2[Employee ID] = Table1[Employee ID]
&& Table2[Date] = Table1[Date]
)
)
)
Result:
and here is sample pbix file, please try it.
Regards,
Lin
hi, @PatrickLamoste
You could try these two formulas as below:
Column = LOOKUPVALUE ( Table2[OT Hours], Table2[ID], Table1[ID], Table2[Date], Table1[Date] )
Column 2 = CALCULATE ( SUM ( Table2[OT Hours] ), FILTER ( Table2, Table2[ID] = Table1[ID] && Table2[Date] = Table1[Date] ) )
Result:
and here is sample pbix file, please try it.
Best Regards,
Lin
Hello Lin @v-lili6-msft ,
I tried the LOOKUPVALUE but it does not work for me it gave this error. It seems that there are some entries with repeated values (EID and Date). Below is my actual list. I also tried the 2nd formula and it gave incorrect values.
Thank you so much for your help.
Table 1 | |||
Employee ID | Hour Type | Date | Hours |
110290 | Regular Hours | 01/04/2019 | 10.00 |
110290 | Regular Hours | 02/04/2019 | 7.00 |
110290 | Regular Hours | 03/04/2019 | 8.00 |
110290 | Regular Hours | 04/04/2019 | 3.00 |
110290 | Regular Hours | 04/04/2019 | 6.00 |
110290 | Regular Hours | 05/04/2019 | 3.00 |
110290 | Regular Hours | 05/04/2019 | 3.00 |
110290 | Regular Hours | 08/04/2019 | 12.00 |
110290 | Regular Hours | 09/04/2019 | 11.00 |
110290 | Regular Hours | 10/04/2019 | 0.00 |
110290 | Regular Hours | 10/04/2019 | 10.00 |
110290 | Regular Hours | 11/04/2019 | 7.00 |
110290 | Regular Hours | 11/04/2019 | 0.00 |
110290 | Regular Hours | 12/04/2019 | 10.00 |
110290 | Regular Hours | 15/04/2019 | 2.00 |
110290 | Regular Hours | 15/04/2019 | 4.00 |
110290 | Regular Hours | 15/04/2019 | 4.00 |
110290 | Regular Hours | 16/04/2019 | 2.00 |
110290 | Regular Hours | 16/04/2019 | 5.00 |
110290 | Regular Hours | 17/04/2019 | 2.00 |
110290 | Regular Hours | 17/04/2019 | 4.00 |
110290 | Regular Hours | 17/04/2019 | 2.00 |
110290 | Regular Hours | 18/04/2019 | 1.00 |
110290 | Regular Hours | 18/04/2019 | 1.00 |
110290 | Regular Hours | 18/04/2019 | 7.00 |
110290 | Regular Hours | 22/04/2019 | 5.00 |
110290 | Regular Hours | 23/04/2019 | 2.00 |
110290 | Regular Hours | 23/04/2019 | 3.00 |
110290 | Regular Hours | 23/04/2019 | 3.00 |
110290 | Regular Hours | 24/04/2019 | 1.00 |
110290 | Regular Hours | 24/04/2019 | 3.00 |
110290 | Regular Hours | 24/04/2019 | 2.00 |
110290 | Regular Hours | 25/04/2019 | 2.00 |
110290 | Regular Hours | 25/04/2019 | 2.00 |
110290 | Regular Hours | 25/04/2019 | 5.00 |
110290 | Regular Hours | 26/04/2019 | 1.00 |
110290 | Regular Hours | 26/04/2019 | 4.00 |
110290 | Regular Hours | 26/04/2019 | 3.00 |
110290 | Regular Hours | 29/04/2019 | 3.00 |
110290 | Regular Hours | 30/04/2019 | 1.00 |
Table 2 | |||
Employee ID | Hour Type | Date | Hours |
110290 | OT Hours | 01/04/2019 | 10.00 |
110290 | OT Hours | 02/04/2019 | 7.00 |
110290 | OT Hours | 03/04/2019 | 8.00 |
110290 | OT Hours | 04/04/2019 | 9.00 |
110290 | OT Hours | 05/04/2019 | 6.00 |
110290 | OT Hours | 08/04/2019 | 12.00 |
110290 | OT Hours | 09/04/2019 | 11.00 |
110290 | OT Hours | 10/04/2019 | 7.00 |
110290 | OT Hours | 15/04/2019 | 10.00 |
110290 | OT Hours | 16/04/2019 | 7.00 |
110290 | OT Hours | 17/04/2019 | 8.00 |
110290 | OT Hours | 18/04/2019 | 9.00 |
110290 | OT Hours | 23/04/2019 | 8.00 |
110290 | OT Hours | 24/04/2019 | 2.00 |
110290 | OT Hours | 24/04/2019 | 3.00 |
110290 | OT Hours | 24/04/2019 | 2.00 |
110290 | OT Hours | 25/04/2019 | 7.00 |
110290 | OT Hours | 29/04/2019 | 10.00 |
110290 | OT Hours | 30/04/2019 | 8.00 |
hi, @PatrickLamoste
Yes, the problem is that there are some entries with repeated values (EID and Date), So you could not use LOOKUPVALUE in this case.
For your requirement, you have to aggregate [Hours] value by SUM/MIN/MAX as 2nd formula, otherwise, what is the expected output for [Employee ID] 110290 and [Date] 24/04/2019?
Regards,
Lin
Hi @v-lili6-msft Lin,
Thank you for your reply. I tried to tweak the formula a bit but still a dead end.
I was wondering if it's possible to summarize Table2 [OT] and will be put into Table1 [RegularHours]. But since Table 1 has duplicate dates I need to only display 7 and not the sum of the whole row when I put it in a visual. Like this.
Table1 | ||||
Employee ID | Hour Type | Date | Hours | OT Hours Sum |
110290 | Regular Hours | 24/04/2019 | 1 | 7 |
110290 | Regular Hours | 24/04/2019 | 3 | 7 |
110290 | Regular Hours | 24/04/2019 | 2 | 7 |
Table2 | |||
Employee ID | Hour Type | Date | Hours |
110290 | OT Hours | 24/04/2019 | 2 |
110290 | OT Hours | 24/04/2019 | 3 |
110290 | OT Hours | 24/04/2019 | 2 |
So when I put in a table visual it would look like this:
Visual: Table | |||
Employee ID | Date | Regular Hours | OT Hours |
110290 | 24/04/2019 | 6 | 7 |
Hi,
If your end objective is to show Regular and OT hours in a single Table visual, then you need not bring over OT hours from Table2 into Table1. If my approach looks suitable to you, then post back and I will share my solution with you.
hi, @PatrickLamoste
For your case, you need to create a new table by this formula:
Table =
SUMMARIZE (
Table1,
Table1[Employee ID],
Table1[Date],
"Regular Hours", CALCULATE ( SUM ( Table1[Hours] ) ),
"OT Hours", CALCULATE (
SUM ( Table2[Hours] ),
FILTER (
Table2,
Table2[Employee ID] = Table1[Employee ID]
&& Table2[Date] = Table1[Date]
)
)
)
Result:
and here is sample pbix file, please try it.
Regards,
Lin
ID | Date | Regular Hours |
1 | 01/04/2019 | 7.00 |
1 | 02/04/2019 | 7.00 |
2 | 04/04/2019 | 7.15 |
2 | 05/04/2019 | 6.85 |
3 | 08/04/2019 | 8.00 |
3 | 09/04/2019 | 7.00 |
3 | 10/04/2019 | 8.50 |
3 | 11/04/2019 | 8.50 |
4 | 12/04/2019 | 3.00 |
4 | 15/04/2019 | 7.00 |
4 | 16/04/2019 | 4.50 |
5 | 17/04/2019 | 8.00 |
5 | 18/04/2019 | 7.50 |
5 | 21/04/2019 | 4.50 |
5 | 23/04/2019 | 9.50 |
6 | 24/04/2019 | 9.00 |
6 | 25/04/2019 | 9.50 |
6 | 30/04/2019 | 7.00 |
ID | Date | OT Hours |
1 | 01/04/2019 | 2.00 |
2 | 04/04/2019 | 4.00 |
2 | 05/04/2019 | 3.00 |
3 | 08/04/2019 | 2.00 |
3 | 09/04/2019 | 2.00 |
3 | 10/04/2019 | 4.00 |
3 | 11/04/2019 | 3.00 |
4 | 12/04/2019 | 2.00 |
4 | 16/04/2019 | 1.00 |
5 | 18/04/2019 | 1.50 |
5 | 21/04/2019 | 2.50 |
5 | 23/04/2019 | 3.50 |
6 | 24/04/2019 | 4.00 |
6 | 25/04/2019 | 3.00 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |