Filter time series data based on other time series

[Danny_Stultiens]

Hi all,

I'm looking for a solution for the following issue;

I have 2 tables;

- Data from an production machine that is showing messages when there is a breakdown (Machine has some error whereby it stops working). This data is including timestamp for start and end of the breakdown. Data is a few months history now.

- Data from the same machine when it's being cleaned (including timestamp for start and end of the cleaning). Cleaning is done once per day, but not always at the sime time.

I would like to exclude all breakdown error messages from the machine when it is being cleaned. 

For that, I'm looking for a solution whereby I can add a column in the error messages table where I can print whether the machine is in cleaning or in production.

With this column, I would like to be able to provide a filter to only show the breakdown messages during production.

Below some dummy data to make my question more visible;

Cleaning data;

Timestamp start	Timestamp end	Status
5-6-2022 01:30:25	5-6-2022 03:50:52	Cleaning
5-6-2022 23:57:07	6-6-2022 02:10:05	Cleaning
7-6-2022 00:01:55	7-6-2022 02:50:29	Cleaning
7-6-2022 23:47:15	8-6-2022 02:04:46	Cleaning
9-6-2022 00:38:23	9-6-2022 03:01:31	Cleaning
10-6-2022 01:49:53	10-6-2022 03:59:38	Cleaning

Breakdown data, the last column (Status) is the one I would like to generate based on the cleaning table;

Timestamp start	Timestamp end	Breakdown message	Status
5-6-2022 12:38:52	5-6-2022 12:42:58	FC446ST1	Production
8-6-2022 06:37:14	8-6-2022 06:39:20	FC446ST1	Production
9-6-2022 02:43:34	9-6-2022 02:47:40	FC440ST5	Cleaning

I really appreciate some help with this.

Thanks in advance!

With kind regards,

Danny

[v-yiruan-msft]

Hi @Danny_Stultiens ,

Are you trying to create a calculated column [Status] in 'Breakdown data'  table based on the data of 'Cleaning data' table? Based on the two table data you provided, it seems that the two tables are not specifically related (same field information) and cannot be compared horizontally over time. Could you please provide more details about the results you want (calculation logic, more data and screenshots, etc.). It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Danny_Stultiens]

Hi Rena,

I'm sorry for the long delay. In the meantime I tried to resolve this issue in the source data, but that turned out to be impossible. So the question still remains to solve it in PowerBI.

I made a simplified pbix file. The instruction you send indicated to provide a Gdrive link and modified the access to "everyone". I hope this works;

https://drive.google.com/file/d/1Bo0xs6g1em8UPxwkD-7rmTebYTAh9q50/view?usp=drivesdk 

Thanks again!

With kind regards,

Danny