Forum Discussion
Connecting startimes/endtimes with a timedate table
Hi all,
I am currently running into a tricky task where I want to link 2 tables. Table 1 contains start times, stop times and duration for each machine. We have a total of 12 machines, which could possibly be on for the same period of time. Table 2 consists of seconds rows of minutes in the period 2018 to now. Now I would actually like to see in that 2nd table in a binary system which machine is on per minute. See example below. Anyone suggestions on how I can accomplish this?
The result:
Thank you in advance
Hi Martijn2 ,
If you don't want to change Table1, you can also try creating the following measure:
Measure = VAR __cur_datetime = SELECTEDVALUE('Table2'[DateTime]) VAR __result = COUNTROWS( FILTER('Table1','Table1'[StartTimeNew]<=__cur_datetime && 'Table1'[EndTimeNew] >=__cur_datetime) ) RETURN __resultmatrix row -- 'Table2'[DateTime]
matrix column -- 'Table1'[MachineCode]
matrix value -- [Measure]
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
3 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - Omid_Motamedise
Combine the first two column to get her and provide allt the dates based on your time steps and then merge it.
Hi Martijn2 ,
If you don't want to change Table1, you can also try creating the following measure:
Measure = VAR __cur_datetime = SELECTEDVALUE('Table2'[DateTime]) VAR __result = COUNTROWS( FILTER('Table1','Table1'[StartTimeNew]<=__cur_datetime && 'Table1'[EndTimeNew] >=__cur_datetime) ) RETURN __resultmatrix row -- 'Table2'[DateTime]
matrix column -- 'Table1'[MachineCode]
matrix value -- [Measure]
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
