Best way to lookup condition of machine based on date & time

[Anonymous]

About me: I'm a Powert BI newbie but I have a moderate amout of experience in Tableau. Please be patient with me.  Thanks.

What I'm trying to do: my objective is to trend the operating temperatures of a machine but I'm only interested in those temperatures when the machine is in a certain configuration.  I have a few hundered thousand lines of data and two seperate tables in Excel.  I cannot use the temperatures value to assume a condition because the temperature ranges for each condition sometimes overlap.

The first table has a line every time the machine changes condition with a date and time (including seconds).  Sometimes it has a line even when the condition of the machine didnt change.  Here is a sample of the data:

Timestamp	Status
8/12/18 5:01:34 PM	Condition A
8/12/18 5:03:29 PM	Condition A
8/12/18 5:31:29 PM	Condition B
8/12/18 5:49:29 PM	Condition A
8/12/18 6:13:29 PM	Condition B
8/12/18 6:31:29 PM	Condition A

The second table has a bunch of timestamps (that rarely match the time stamps in the first table) which show the the machines temperatures at that point in time.  It looks like this:

Timestamp	Temperature
8/12/18 5:18:16 PM	382
8/12/18 5:26:57 PM	377.06
8/12/18 5:40:04 PM	149
8/12/18 6:15:18 PM	131
8/12/18 6:36:30 PM	128

I'm looking for help on the best way to lookup the machine's condition at the timestamp of the temperature so I can ultimately trend the temperatures but only when the machine is in a certain condition.  Any help is appreciated.  Please let me know if you need any more information to assist.  Thanks.

[Anonymous]

Hi @Anonymous 

You can have a preliminary understanding of DAX by following this link . It contains explanations of DAX, video explanations, function introductions, etc., I hope it will be helpful to you.

https://docs.microsoft.com/en-us/dax/

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

WHat exact result do you expect to see in the third column of Table2.  Please show clearly.

[Anonymous]

@Ashish_Mathur thanks for the followup question. I am aiming for the third column of table two to show the corresponding condition (condition A, condition B, etc.) of the machine at the timestamp of the respective temperature measurement.  In short, I need to infer from table one what the condition is of the machines is and then transpose that condition to the corresponding temperature.  

For simplicity, lets pretend that Condition A = ON and Condition B = OFF.  From Table one, I could derive the following information:

From	To	Machine Condition
8/12/2018 5:01:34 PM	8/12/2018 5:03:28 PM	Machine is ON (i.e. condition A)
8/12/2018 5:03:29 PM	8/12/2018 5:31:28 PM	Machine is ON (i.e., condition B)
8/12/2018 5:31:29 PM	8/12/2018 5:49:28 PM	Machine is OFF
8/12/2018 5:49:29 PM	8/12/2018 6:13:28 PM	Machine is ON
8/12/2018 6:13:29 PM	8/12/2018 6:31:28 PM	Machine is OFF
8/12/2018 6:31:29 PM	present	Machine is ON

Now I can take the timestamps in table two and deduce what the condition of the machine is for the timestamps shown:

Timestamp	Temperature	Machine Condition
8/12/18 5:18:16 PM	382	ON (i.e., condition A)
8/12/18 5:26:57 PM	377.06	ON
8/12/18 5:40:04 PM	149	OFF (i.e., condition B)
8/12/18 6:15:18 PM	131	OFF
8/12/18 6:36:30 PM	128	ON

Does this help show clearly what I am trying to do?  Sorry for the delayed response.  Please let me know if I need to expplain further.  Thanks.

[Ashish_Mathur]

Sorry but i still do not understand your question.  I do not know what is the inout and what is the output you want.  I'm sure someone will understand your question and help you.

[Greg_Deckler · Accepted solution]

@Anonymous I'm thinking something like this:

Column =
  VAR __Timestamp = [Timestamp]
  VAR __MaxTimestamp = MAXX(FILTER('StatusTable','StatusTable'[Timestamp]<__Timestamp),'StatusTable'[Timestamp])
RETURN
  MAXX(FILTER('StatusTable',[Timestamp]=__MaxTimestamp),'StatusTable'[Status])

[Anonymous]

@Greg_Deckler Thanks for the reply.  Like I said in the OP, I am very new to Power BI...I do not [yet] know Dax but I am learning.  Can you help me understand what this code is doing?  I'm trying to reverse engineer it but not having much luck yet.  

[Greg_Deckler]

@Anonymous Sure:

Column =
  VAR __Timestamp = [Timestamp]
//This gets the Timestamp of the current row in the table.

  VAR __MaxTimestamp = MAXX(FILTER('StatusTable','StatusTable'[Timestamp]<__Timestamp),'StatusTable'[Timestamp])
/* The FILTER statement filters the other table, for rows where the timestamp column in that table is less than the timestamp in the current table. MAXX iterates over this filtered table to determine the maximum value of the timestamp. In essence, this finds the last time just prior to the timestamp in the current row when the machine last changed status and thus this is the timestamp that corresponds to the status of the machine at the timestamp in question. */

RETURN
  MAXX(FILTER('StatusTable',[Timestamp]=__MaxTimestamp),'StatusTable'[Status])
/* Having determined the correct timestamp, we again filter the table but this time for the exact timestamp we found in __MaxTimestamp and use MAXX to simply return the Status column at that timestamp and hence, the Status of the machine in question.*/

[Anonymous]

@Greg_Deckler sorry for the delayed response.  I wanted to ask one more question about your proposed solution: is it working under the assumption that there are exact matching timestamps between both tables?  This is not the case but I just wanted to verify because I couldnt be 100% sure based on your generous description of the DAX code.

[Greg_Deckler]

@Anonymous No such assumption. However, you would want this modification just in case there are:

VAR __MaxTimestamp = MAXX(FILTER('StatusTable','StatusTable'[Timestamp]<=__Timestamp),'StatusTable'[Timestamp])

[Anonymous]

@Greg_Deckler Thank you for the updated DAX.  I'm still trying to figure out how to substitute the correct table names and column names from my spreadsheets into the approriate areas in the code you suggested.  Any recommendations on a good YouTube video for a beginner?  Right now I cannot get the last 

'StatusTable'[Status]

of the RETURN line to populate with the correct table/column.  I'm sure I am just overlooking something but I cannot figure out.  Sorry if this is something obvious...I'm very new to Power BI which--at this point-- is totallly different than Tableau to me.

 

[Anonymous]

@Greg_Deckler Please disregard my last comment...somehow I was able to get the correct table/column entered.  I'm still not sure why sometimes they prepopulate as I'm typing and sometimes I have to manually try to enter them.  Guess it's part of the learning curve?!

[Greg_Deckler]

@Anonymous Sometimes it depends on whether you have the right syntax and are thus in the right "context" to have items pre-populate using type ahead technology.