- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
measure to look up value and added to total and be dependent by date for fields with no data
Updates!!
Here's some sample data to better address my goal.
Roster rable
ID | Name |
0123456 | John |
0234567 | Mary |
0345678 | Greg |
0456789 | Kite |
0567890 | Peter |
Labor hour table
Name | Employee Num | Clock on | Clock off | Clock on Date only | Labor min | Labor Hour |
Greg | 05678 | 11/22/21 6:00 AM | 11/22/21 7:00 AM | 11/22/2021 | 60.00 | 1.00 |
Greg | 05678 | 11/22/21 7:05 AM | 11/22/21 8:10 AM | 11/22/2021 | 65.00 | 1.08 |
Kite | 06789 | 11/22/21 7:30 AM | 11/22/21 9:11 AM | 11/22/2021 | 101.00 | 1.68 |
Greg | 05678 | 11/22/21 8:25 AM | 11/22/21 2:10 PM | 11/22/2021 | 345.00 | 5.75 |
Mary | 02345 | 11/22/21 8:25 AM | 11/22/21 10:49 AM | 11/22/2021 | 144.00 | 2.40 |
John | 03456 | 11/22/21 9:06 AM | 11/22/21 10:06 AM | 11/22/2021 | 60.00 | 1.00 |
Kite | 06789 | 11/22/21 10:08 AM | 11/22/21 12:45 PM | 11/22/2021 | 157.00 | 2.62 |
John | 03456 | 11/22/21 10:15 AM | 11/22/21 12:45 PM | 11/22/2021 | 150.00 | 2.50 |
Mary | 02345 | 11/22/21 11:20 AM | 11/22/21 5:02 PM | 11/22/2021 | 342.00 | 5.70 |
John | 03456 | 11/22/21 12:56 PM | 11/22/21 5:00 PM | 11/22/2021 | 244.00 | 4.07 |
Kite | 06789 | 11/22/21 1:17 PM | 11/22/21 4:45 PM | 11/22/2021 | 208.00 | 3.47 |
Mary | 02345 | 11/22/21 5:20 PM | 11/22/21 7:07 PM | 11/22/2021 | 107.00 | 1.78 |
John | 03456 | 11/23/21 6:15 AM | 11/23/21 8:00 AM | 11/23/2021 | 105.00 | 1.75 |
Greg | 05678 | 11/23/21 7:00 AM | 11/23/21 11:06 AM | 11/23/2021 | 246.00 | 4.10 |
Mary | 02345 | 11/23/21 7:45 AM | 11/23/21 10:17 AM | 11/23/2021 | 152.00 | 2.53 |
John | 03456 | 11/23/21 8:06 AM | 11/23/21 10:26 AM | 11/23/2021 | 140.00 | 2.33 |
John | 03456 | 11/23/21 10:55 AM | 11/23/21 1:57 PM | 11/23/2021 | 182.00 | 3.03 |
Greg | 05678 | 11/23/21 2:06 PM | 11/23/21 3:06 PM | 11/23/2021 | 60.00 | 1.00 |
John | 03456 | 11/24/21 6:00 AM | 11/24/21 8:11 AM | 11/24/2021 | 131.00 | 2.18 |
Kite | 06789 | 11/24/21 6:06 AM | 11/24/21 8:06 AM | 11/24/2021 | 120.00 | 2.00 |
Mary | 02345 | 11/24/21 6:10 AM | 11/24/21 8:06 AM | 11/24/2021 | 116.00 | 1.93 |
Greg | 05678 | 11/24/21 6:16 AM | 11/24/21 8:01 AM | 11/24/2021 | 105.00 | 1.75 |
Greg | 05678 | 11/24/21 8:06 AM | 11/24/21 11:06 AM | 11/24/2021 | 180.00 | 3.00 |
Kite | 06789 | 11/24/21 8:20 AM | 11/24/21 10:55 AM | 11/24/2021 | 155.00 | 2.58 |
John | 03456 | 11/24/21 8:25 AM | 11/24/21 11:06 AM | 11/24/2021 | 161.00 | 2.68 |
Kite | 06789 | 11/24/21 11:00 AM | 11/24/21 11:55 AM | 11/24/2021 | 55.00 | 0.92 |
Greg | 05678 | 11/24/21 11:26 AM | 11/24/21 3:36 PM | 11/24/2021 | 250.00 | 4.17 |
Kite | 06789 | 11/24/21 1:00 PM | 11/24/21 3:00 PM | 11/24/2021 | 120.00 | 2.00 |
John | 03456 | 11/24/21 1:27 PM | 11/24/21 4:16 PM | 11/24/2021 | 169.00 | 2.82 |
Peter | 07890 | 11/24/21 6:54 AM | 11/24/21 8:32 AM | 11/24/2021 | 98.00 | 1.63 |
Peter | 07890 | 11/24/21 9:14 AM | 11/24/21 11:32 AM | 11/24/2021 | 138.00 | 2.30 |
Peter | 07890 | 11/24/21 1:54 PM | 11/24/21 5:12 PM | 11/24/2021 | 198.00 | 3.30 |
Shift change record table
Employee Num | Name | Shift Change hour | Chift change date |
02345 | Mary | 10 | 11/22/2021 |
05678 | Greg | 9 | 11/24/2021 |
vacation record table
Employee Num | Name | time off hours | vacation date |
06789 | Kite | 8 | 11/23/2021 |
02345 | Mary | 4 | 11/23/2021 |
05678 | Greg | 2 | 11/23/2021 |
02345 | Mary | 5 | 11/24/2021 |
What I want to achieve:
date filter: past __3__ Days
22-Nov | 23-Nov | 24-Nov | Total | |||||
Employee Num | Clock in hour | Availabel hour | Clock in hour | Availabel hour | Clock in hour | Availabel hour | Clock in hour | Availabel hour |
02345 | 9.88 | 10 | 2.53 | 4 | 1.93 | 3 | 14.35 | 17 |
03456 | 7.57 | 8 | 7.12 | 8 | 7.68 | 8 | 22.37 | 24 |
05678 | 7.83 | 8 | 5.10 | 6 | 8.92 | 9 | 21.85 | 23 |
06789 | 7.77 | 8 | 0.00 | 0 | 7.50 | 8 | 15.27 | 16 |
07890 | 0.00 | 8 | 0.00 | 8 | 7.23 | 8 | 7.23 | 24 |
Total | 33.05 | 42.00 | 14.75 | 26.00 | 33.27 | 36.00 | 81.07 | 104.00 |
I want the report to be able to dynamically capture actual clock in hour vs available hours and be able to show the total for both row and column.
Hope this makes it more clear to understand!!!
Thank you!
****************************************Original Post*****************************************************
Hello all,
I'm working on a labor hour report.
There are 4 tables.
Column with red cross is the primary key.
Roster:
Labor Table:
Shift schedule change:
Time off :
I'm trying to build a talbe where I can:
1. filter by date to look at the labor utilization rate throughout the week/month/year...etc.
2. show clock-in hour vs available hours for each employee.
3. total for the day of clock in hour and available hour.
Factors:
1. shift schedule is not always 8 hours.
so I create a table to submit shift change record.
2. need to consider time-off.
there's a table for time-off records too.
Problem:
1. I added a column in labor hour table to calculate available hours. So each clock in records has an available hours. But if the employee for some reason didn't clock in for the day that he is available, since there is no clock in records. The person doesn't have a availalbe hours calculated, thus it shows blank for "clock-in hour" and "available hours".(see belowpicture) This makes the Total available hour skewed, from below picture, if the 2 person are not off, the available hours should be 616 not 600.
I was able to make a measure to show 0 for "clock-in hour" but for "available hours" it gets tricky.
If I make another measure to check 1. if that person is off and 2. if his shift got changed, First of all, I don't even know if I could make this measure work. Second, the problem will be: even if I get this to work, the numbers won't be added to the total.
I'm totally lost now...maybe it is wrong to add the available hours to the clock in table?
I was trying to avoid making a big table to store every employees' available hours for every single day in a year...
Any suggestions would be greatly appreciated....!!!
Thanks!!!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @jessi82315
Yeah, I know. Because I did not consider the filter when calculating the total.
Try to add filters like below, you can get the correct result.
Result:
It is indeed close to three hours, but this will not be beyond your knowledge. There must be an easier way, but at the time this solution was the only one I thought of.
I have learned a lot about this case, thank you for bringing it up.😉
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @jessi82315
This logic seems to be correct. If it does not react, check whether filter is added to all VARs(needed for the calculation), and check whether there is a filtering relationship between the slicer and other fields
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hope you are doing well! I just found another question I never got it cleared. (and it may be a dumb question...Please bear with me )
Multiple variables in the measure like below this one: the filter for the date
var _sum=CALCULATE(SUM('vacation record table'[time off hours]),FILTER(ALL('vacation record table'),'vacation record table'[Employee Num]=MAX('Labor hour table'[Employee Num])&&'vacation record table'[vacation date]=MAX('ColumnOfMatrix'[Clock on Date only])))
When filtering through the data, how come using MAX can get us the correct result?
I understand measure is a aggregated function based and cannot be perform without using these MAX, SUM...etc. functions but is it not always looking at the Maximum date to match????
Thanks
Jessie
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @jessi82315
This is definitely not a dumb question, on the contrary it's a good one and a lot of people are struggling here.
I did an explanation in a previous thread, you can read it to help understand.
Question regarding syntac/ context transition cumulative sum
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @v-angzheng-msft ,
sorry for the late reply! I had been reading your post and learning all these stuff. probably got too excited and forgot to reply 🤣 But yes!! that is very helpful!!! Really appreciate your BIG HELP 💪
Thank you!
Jessie
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @jessi82315
This logic seems to be correct. If it does not react, check whether filter is added to all VARs(needed for the calculation), and check whether there is a filtering relationship between the slicer and other fields
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@v-angzheng-msft Thank you!! I added some relationship between the tables and it does work now.
Thanks again!!!! 😀
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @jessi82315
Could you please consdier sharing a simple sample file without any sesentive information and posting expected result so it is clear on what needs to be implemented? It makes it easier to give you a solution.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hmmm now I can't even see any replies...
I hope you can see my reply! @v-angzheng-msft !!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @jessi82315
Don't worry, I have already requested to remove the post from spam.
Okay, I have seen your reply. Please note: If the reply is edited too many times, the post will be marked as spam.
It took me close to three hours to process your case, and finally I got the solution.
Here is the solution:
First create three auxiliary tables:
ColumnOfMatrix = VALUES('Labor hour table'[Clock on Date only])
Shift All =
var _t1=SUMMARIZE('Labor hour table',[Employee Num],[Name])
var _t2=VALUES('Labor hour table'[Clock on Date only])
var _t=GENERATE(_t1,_t2)
var _except=EXCEPT(_t,SUMMARIZE('Shift change record table',[Employee Num],[Name],[Chift change date]))
var _add=ADDCOLUMNS(_except,"Shift Change hour",8)
var _name=SELECTCOLUMNS(_add,"Empoyee Num",[Employee Num],"Name",[Name],"Shift Change hour",[Shift Change hour],"Chift change date",[Clock on Date only])
var _union=UNION('Shift change record table',_name)
return _union
Vacation All =
var _t1=SUMMARIZE('Labor hour table',[Employee Num],[Name])
var _t2=VALUES('Labor hour table'[Clock on Date only])
var _t=GENERATE(_t1,_t2)
var _except=EXCEPT(_t,SUMMARIZE('vacation record table',[Employee Num],[Name],[vacation date]))
var _add=ADDCOLUMNS(_except,"time off hours",0)
var _name=SELECTCOLUMNS(_add,"Empoyee Num",[Employee Num],"Name",[Name],"time off hours",[time off hours],"vacation dadte",[Clock on Date only])
var _union=UNION('vacation record table',_name)
return _union
To create four measures:
_Clock in hour =
var _clock=CALCULATE(SUM('Labor hour table'[Labor Hour]),FILTER(ALL('Labor hour table'),'Labor hour table'[Clock on Date only]=MAX('ColumnOfMatrix'[Clock on Date only])&&'Labor hour table'[Employee Num]=MAX('Labor hour table'[Employee Num])))
var _if=IF(_clock=BLANK(),0,_clock)
var _column=CALCULATE(SUM('Labor hour table'[Labor Hour]),FILTER(ALL('Labor hour table'),'Labor hour table'[Employee Num]=MAX('Labor hour table'[Employee Num])))
var _row= CALCULATE(SUM('Labor hour table'[Labor Hour]),FILTER(ALL('Labor hour table'),'Labor hour table'[Clock on Date only]=MAX('ColumnOfMatrix'[Clock on Date only])))
var _total=CALCULATE(SUM('Labor hour table'[Labor Hour]))
var _switch=
SWITCH(
TRUE(),
not(HASONEVALUE('Labor hour table'[Employee Num]))&¬(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
// 4,
_total,
not(HASONEVALUE('Labor hour table'[Employee Num])),
// 3,
_row,
not(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
// 2,
_column,
// 1
_if
)
return _switch
_ShiftChange =
var _shiftChange=
CALCULATE(MAX('Shift change record table'[Shift Change hour]),
FILTER(ALL('Shift change record table'),'Shift change record table'[Employee Num]=MAX('Labor hour table'[Employee Num])&&'Shift change record table'[Chift change date]=MAX('ColumnOfMatrix'[Clock on Date only])))
var _if=COALESCE(_shiftChange,8)
var _column=CALCULATE(SUM('Shift All'[Shift Change hour]),FILTER(ALL('Shift All'),'Shift All'[Employee Num]=MAX('Labor hour table'[Employee Num])))
var _row= CALCULATE(SUM('Shift All'[Shift Change hour]),FILTER(ALL('Shift All'),'Shift All'[Chift change date]=MAX('ColumnOfMatrix'[Clock on Date only])))
var _total=CALCULATE(SUM('Shift All'[Shift Change hour]))
var _switch=
SWITCH(
TRUE(),
not(HASONEVALUE('Labor hour table'[Employee Num]))&¬(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
// 4,
_total,
not(HASONEVALUE('Labor hour table'[Employee Num])),
// 3,
_row,
not(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
// 2,
_column,
// 1
_if
)
return _switch
_timeOff =
var _sum=CALCULATE(SUM('vacation record table'[time off hours]),FILTER(ALL('vacation record table'),'vacation record table'[Employee Num]=MAX('Labor hour table'[Employee Num])&&'vacation record table'[vacation date]=MAX('ColumnOfMatrix'[Clock on Date only])))
var _if=COALESCE(_sum,0)
var _column=CALCULATE(SUM('Vacation All'[time off hours]),FILTER(ALL('Vacation All'),'Vacation All'[Employee Num]=MAX('Labor hour table'[Employee Num])))
var _row= CALCULATE(SUM('Vacation All'[time off hours]),FILTER(ALL('Vacation All'),'Vacation All'[vacation date]=MAX('ColumnOfMatrix'[Clock on Date only])))
var _total=CALCULATE(SUM('Vacation All'[time off hours]))
var _switch=
SWITCH(
TRUE(),
not(HASONEVALUE('Labor hour table'[Employee Num]))&¬(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
// 4,
_total,
not(HASONEVALUE('Labor hour table'[Employee Num])),
// 3,
_row,
not(HASONEVALUE('ColumnOfMatrix'[Clock on Date only])),
// 2,
_column,
// 1
_if
)
return _switch
_Available = [_ShiftChange]-[_timeOff]
Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you're amazing....!!!😲 I mean 3 hours to a solution like this is beyond my knowledge!!! (still have a lot to learn...)
I was studying your solution and got a question!
Which date field will be the right one to filter through these numbers?
I tried the "Clock on Date only" in "ColumnOfMatrix" table, it was working good but the Total is the same for filtering for 2 days and 3 days. See below:
It looks like Total for 2 days is not adding up correctly. Do you know why?
Thank you so much,
Jessie
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @jessi82315
Yeah, I know. Because I did not consider the filter when calculating the total.
Try to add filters like below, you can get the correct result.
Result:
It is indeed close to three hours, but this will not be beyond your knowledge. There must be an easier way, but at the time this solution was the only one I thought of.
I have learned a lot about this case, thank you for bringing it up.😉
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Could I ask 1 more question?
I have other columns in the "Labor Hour" table as "Job Number".
I was trying to add filter to the measures so it would respond to it when I have a "Job Number" filter active but it doesn't seem to react? I did it same way as how you add the filter for the date: var _row = CALCULATE(SUM('Labor hour table'[Labor Hour]),FILTER(ALL('Labor hour table'),'Labor hour table'[Clock on Date only]=MAX('ColumnOfMatrix'[Clock on Date only]&&'Labor hour table'[Clock on Date only] in ALLSELECTED(ColumnOfMatrix[Clock on Date only]&&'Labor hour table'[Job Number] in ALLSELECTED('Labor hour table'[Job Number])))
I'm still learning all the DAX funcitons you used in the solution and wondering maybe there's something that I'm missing?
Any helps is appreciated!!!
Thanks
Jessie
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You are AMAZING!!!!! I'm learning a lot from you too!
There are still a lot about Power BI I haven't learned and the way you do it definitely brings a new perspective to solve problems!
Thanks again for your help!!
Much appreciated!
Thanks
Jessie
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello!! I'm sorry I'm not sure why but when I hit submit after editing the post and it marked your reply as spam....!!!
I didn't do it on purpose and don't know how to undo it either...! 😂
if you would please look at my updated post again!! any help is greatly appreciated!!
sorry again about accidentally delete your reply...!
thanks!!!
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
05-24-2024 02:57 PM | |||
02-08-2024 12:19 PM | |||
08-20-2024 06:51 PM | |||
02-27-2024 09:28 AM | |||
02-26-2024 01:29 PM |
User | Count |
---|---|
128 | |
81 | |
59 | |
57 | |
44 |
User | Count |
---|---|
184 | |
111 | |
82 | |
66 | |
52 |