Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello fellow Power BI users,
I hope you all doing great!
I have come for help, because I really don't know how to progress with my project. I have been stuck to this problem for almoust two weeks now. I have very comlex table, which is appended 3 tables as such rawData, serviceRequest and ProductionPlan. I wanted to "kill two flies by one round" when I came to the problem with Shifts and ShiftsUsage. Basicly I can't tell ftom Timestamp if the time was during 8h shift or 12 hour shift. By comparing to the plan I should be able to tell, which one of those it was as well how many hours should be the working time for the ShiftUsage, which is telling me how much time the Operater actually worked of his Shift.
Sample there (very chopped) :
I can't share the file as it is because of firm rules about data, but if really needed, I can upload this excel later on.
The ShiftPlan is calculated like this:
Solved! Go to Solution.
Hi @Chocohammer
For your plan, if a plan is deleted, then filter the table is FALSE in Power Query. This will show the real Plan
I presume your Plan table has a deviceID column?
Load the Plan and the Actual table into the Report. Make sure that the dates in both tables are in Date/Time format before loading and the device id is a Whole number
After loading go to the Fact table and add new column enter something similar into the measure field
Shift = MAXX(FILTER(Plan, 'Fact'[deviceid] = Plan[DeviceID] && 'Fact'[timestamp] >= Plan[start] && 'Fact'[timestamp] < Plan[end]), Plan[Shiftplan])
This is looking at the device id and then asking if the timestamp is between the startand end dates in the plan table and retruning the Shift where the conditions are met.
I have created a file that you can download and check.
https://drive.google.com/file/d/1ht-FbYCQ3y0wXL25FxIBlF4JjA3zGOvy/view?usp=sharing
Thanks
Joe
If this post helps, then please Accept it as the solution
Hi , @JoeBarry
I have one additional question. I found out that I need shifts even for services. I added conditions for that, but the code is so non optimal. It takes almost an hour to run, and refresh almost 2 hours.
Do you think I have an error in the code, is it overloaded or does it need another approach?
I did this:
Shift =
MAXX (
FILTER (
poductionPlan_stations,
'Data'[deviceID] = poductionPlan_stations[deviceID]
&& ('Data'[timestamp] >= poductionPlan_stations[start]
&& Data[timestamp] < poductionPlan_stations[end])
|| (Data[requestTime] >= poductionPlan_stations[start]
&& Data[requestTime] < poductionPlan_stations[end])
),
poductionPlan_stations[ShiftPlan]
)
Thank you so much for any suggestions.
Best regards
Choco
Hi @Chocohammer
Is the plan for an individual person or is it a general shift plan for each day? Is it possible that an 8 or 12 hour shift overlap on the same times? Example 8pm - 4am or 8pm - 6am on the same dates?
If it's for an individual person is there a UserID in both the plan and the RawData table available?
let me know these tings and I will try to help you
Kind rgeards
Joe
Hi @JoeBarry
Thank you for your reply!
The plan is individual for every deviceID. I have more than 500 deviceID, every deviceID is an individual machine that operator works on.
So for your question, it's not possible for the plan to overlap, because one machine can't work multiple time at one. The only possible way is, when changes have been made at the Plan and the "Old" Plan is deleted (TRUE statement).
I hope I answerd your question right. I will gladly answer more.
Best regards
Choco
Hi @Chocohammer
For your plan, if a plan is deleted, then filter the table is FALSE in Power Query. This will show the real Plan
I presume your Plan table has a deviceID column?
Load the Plan and the Actual table into the Report. Make sure that the dates in both tables are in Date/Time format before loading and the device id is a Whole number
After loading go to the Fact table and add new column enter something similar into the measure field
Shift = MAXX(FILTER(Plan, 'Fact'[deviceid] = Plan[DeviceID] && 'Fact'[timestamp] >= Plan[start] && 'Fact'[timestamp] < Plan[end]), Plan[Shiftplan])
This is looking at the device id and then asking if the timestamp is between the startand end dates in the plan table and retruning the Shift where the conditions are met.
I have created a file that you can download and check.
https://drive.google.com/file/d/1ht-FbYCQ3y0wXL25FxIBlF4JjA3zGOvy/view?usp=sharing
Thanks
Joe
If this post helps, then please Accept it as the solution
Hi @JoeBarry
Thank you one more for your fast reply!
I'm very grateful, I didn't think about that option if I didn't have a relationship between the Plan table and the Fact table. I can use the MAXX FILTER. Tbh. I can't use MAXX or MAX very efficiently, so maybe I was intently not used in it. I know at least I need more training now, about MAX function.
Also I'm sorry, it's true that I'm already filtering the Plan in Power Query that only false is loaded. DeviceID is always a whole number and both tables had the format of timestamp DATE/TIME.
You are a life saver. It works perfectly. Here's the proof.
Now I can continue on my project.
Thank you so much and have a wonderful day!
Best regards
Choco
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
98 | |
90 | |
71 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |