Select value of column from table with most recent timestamp and matching key

[Paras_Sood]

Hi

I have a use case, where I have a table called Machines, Departments. Products and Daily Log along with a slice filtering data only for today (Today Operations) 

In the table daily log, we capture the status of a machine - What time it started (DateTime), what product is supposed to run on the machine, whether the machine is "Running", "Not Running", "Cleaning" or "Finished".

Our operators record the product, machine on "Daily Log" and record status as 'Running' when the line starts in the morning.

I want to view a list of all machines and the current status of the machine, which I could get from generating a view of the machine and then adding a virtual column to capture the most recent entry in the daily log (or today operations) table. I tried the following expression by adding a virtual column on the machine tablebut I get blank results:

SELECT(TODAY OPERATIONS[STATUS],(MAXROW(“Today Operations”, “DateTime Start”).[MACHINE ID])=[_THISROW].[MACHINE ID])

Can anyone help me with the expression?

Thank you!

[TeeSee1 · Accepted solution]

You still need to select the latest entries.

Try

SELECT(
 TODAY OPERATIONS[STATUS],
 [key col of TODAY OPERATION] = 
  MAXROW(
   “Today Operations”, 
   “DateTime Start”,
   IN(
    [_THISROW].[MACHINE ID],
    [MACHINE ID]
   )
  )
)

[Paras_Sood]

Trying out this expression is also futile for me:

MAXROW("TODAY OPERATIONS", "DateTime Start",[_THISROW].[MACHINE ID]=[MACHINE ID])

I get an error which says "Cannot compare Text with List in ([_THISROW].[Machine ID] = [MACHINE ID])"

[Paras_Sood]

Okay I understand why this does not work:

In Daily Log, the Machine ID is of ENUM LIST type (I want the operators to be able to update the status of multiple machines at the same time). I will try to update my expression to match this

[TeeSee1]

I m slow today..

I thought this would work (forget the last expression I deleted)

SELECT(
 TODAY OPERATIONS[STATUS],
 [key col of TODAY OPERATION] = 
  MAXROW(“Today Operations”, “DateTime Start”,[MACHINE ID]=[_THISROW].[MACHINE ID])
)

 But ...  "Cannot compare Text with List in ([_THISROW].[Machine ID] = [MACHINE ID])" is really incomprehensible.

[Machine ID] in both Machine and Operations tables are not ENUMLIST type, correct?

[Paras_Sood]

Hi @TeeSee1 

Thank you for your reply

You are correct, the machine ID in daily log table is of enum list type, that is why I am facing this error. I apologise for missing that out earlier.

[TeeSee1]

Oops, delayed response on my part.. Understand now.

Give me a minute

[TeeSee1]

I think you know what to do..

[Paras_Sood]

No you're alright

Appreciate the prompt responses, thank you for taking time out and helping me

[Paras_Sood]

I have made use of the "IN" statement

SELECT(
  TODAY OPERATIONS[Status],
  IN([_THISROW].[Machine ID],[Machine ID])
)

This gets me multiple status updated though, Not quite sure how I will be able to pick up the status of the machine at the moment.

[Paras_Sood]

This is how my data looks like in the backend

Ideally what I want to be doing is that find the latest entry for PAN-1 in the machine ID enumlist type and find the status of that. Similarly, for all other machines.

[TeeSee1 · Accepted solution]

You still need to select the latest entries.

Try

SELECT(
 TODAY OPERATIONS[STATUS],
 [key col of TODAY OPERATION] = 
  MAXROW(
   “Today Operations”, 
   “DateTime Start”,
   IN(
    [_THISROW].[MACHINE ID],
    [MACHINE ID]
   )
  )
)

[Paras_Sood]

You are a legend mate

[TeeSee1]

Glad to be of help.

[Paras_Sood]

I'm still trying to work out how that expression works hahaha

[Paras_Sood]

Hey @TeeSee1, I have another use case in this same table

I am using a bot to log all changes in a separate table called "change log" where I record the before and after values for the comments and status field if either are changed. What I am trying to achieve is this:

Just like above we showed the current status of the machine based on the entries in "today operations", I want to show all the records from the change log table relevant to that particular machine.

The link between the tables is:

Change Log contains a ref to the slice of daily log (called today operations), and today operations has a ref enum list to the machines table. I want to get change log for all the work orders in today operations and club them together to show what all changes have happened today for each machine. I tried the following:

SELECT( Daily Log[Related Change Logs], AND( IN( [_THISROW].[MACHINE ID], [MACHINE ID] ), ISNOTBLANK(Daily Log[Related Change Logs]) ) )

 But I get blanks in the output as well, and I also only get the "key" value for the change log table, but I want all the rows of that table to be displayed

[TeeSee1 · Accepted solution]

In the machine table, create a VC with this expression (I called it [Related clogs]

FILTER(
 "clog",
 IN(
  [dlog_id], 
  FILTER(
   "dlog", 
   IN([_THISROW].[m_id], [m_id])
  )
 )
)

Then you should see related clog records in the detail view of machines as an in-line table

[Paras_Sood]

Thanks mate but i tried this and I do not get any rows in the output. It's completely blank.

FILTER(
 "Change Log",
 IN(
  Today Operations[Work Order ID], 
  FILTER(
   "Today Operations", 
   IN([_THISROW].[Machine ID], [Machine ID])
  )
 )
)

I am trying to do something like this:

SELECT(
Today Operations[Related Change Logs],
IN([_THISROW].[Machine ID], [Machine ID])
)

Problem with this statement is that it only gets me the key columns and not the entire rows

[TeeSee1]

Today Operations[Work Order ID]

should be

[Work Order ID]

[Paras_Sood]

As always, you're a legend.