Maxrow help

[G0ldsoul]

Hi everyone,

I have two tables Parent "TECNICO" and child "MOVIMIENTOS 2". I want to record in a virtual column in TECNICO's table the latest date when an operator recived Personal Protective Equipment (PPE).

I used this formula, where the validation box says it's correct, but it returns a null value:

EOMONTH(
 LOOKUP
  (MAXROW(“MOVIMIENTOS 2”,“FECHA_MOVIMIENTO”,and([NOMBRES]=[_THISROW].[NOMBRE],[TIPO]="GUANTE OPERADOR")),"MOVIMIENTOS 2",ID_MOVIMIENTO,"FECHA_MOVIMIENTO"),ANY(SELECT(TIPO[TIEMPO], ([ID TIPO]="GUANTE OPERADOR"))) - 1)
+
DAY(
 LOOKUP(
   MAXROW(“MOVIMIENTOS 2”,“FECHA_MOVIMIENTO”,and([NOMBRES]=[_THISROW].[NOMBRE],[TIPO]="GUANTE OPERADOR")),
  "MOVIMIENTOS 2",ID_MOVIMIENTO,"FECHA_MOVIMIENTO"))

 I split the formula into the different expressions it contains to see wich one failed, wich led me to realiza the MAXROW formula is returnig the null value, leading the rest of the formula to retun an empty date value (31/1/2001).

MAXROW(“MOVIMIENTOS 2”,“FECHA_MOVIMIENTO”,AND([NOMBRES]=[_THISROW].[NOMBRE],[TIPO]="GUANTE OPERADOR"))

I dont undestand what's worng with the formula, it just returnas an empty value in the test window:

For reference here are the tables and columns used in the expresions.

TECNICO

MOVIMIENTOS 2

The [NOMBRES] column is a deference from another parent table to MOVIMIENTOS 2, "VALE DE ALMACEN" where it calls the [NOMBRE] value from table TECNICO.

Thanks in advance community!

[Tommy62]

Um in eine virtuellen Spalte in der Tabelle von TECNICO das späteste Datum einzutragen, sieht meiner Ansicht nach Ihr Ausdruckteil 

LOOKUP(
   MAXROW("MOVIMIENTOS 2","FECHA_MOVIMIENTO",and([NOMBRES]=[_THISROW].[NOMBRE],[TIPO]="GUANTE OPERADOR"))

richtig aus. (nicht getestet)

[G0ldsoul]

Yes, it´s correct in theory. But the result's still null.

[Sirfyaad · Accepted solution]

following expression:

MAX(
SELECT(
MOVIMIENTOS 2[Fecha],
AND(
ISNOTBLANK([PPE]),
[TECNICO ID] = [_THISROW].[TECNICO ID]
)
)
)

You can use this expression in the virtual column formula of the "TECNICO" table to display the latest date when an operator received PPE.

IF(
ISBLANK(
MAX(
SELECT(
MOVIMIENTOS 2[Fecha],
AND(
ISNOTBLANK([PPE]),
[TECNICO ID] = [_THISROW].[TECNICO ID]
)
)
)
),
"",
MAX(
SELECT(
MOVIMIENTOS 2[Fecha],
AND(
ISNOTBLANK([PPE]),
[TECNICO ID] = [_THISROW].[TECNICO ID]
)
)
)
)