How to return the value of a third field from...

[Michael2]

How to return the value of a third field from a select statement where 2 other criteria have been met?

Criteria 1 Matching [KEY] fields Criteria 2 The MAX [DATE] from within the list Return [NUMBER]

I have tried the following statement as well as several others. This fails because the MAX statement returns a date when I need it to return a yes/no.

AND(

SELECT(Child Table[NUMBER],[KEY]=[_THISROW].[KEY]),

MAX(

SELECT(Child Table[DATE],[KEY]=[_THISROW].[KEY])

)

)

[Michael2]

NUMBER(ANY(SELECT(Child Table[Number],[KEY]=MAXROW(“Child Table”,“DATE”,[KEY]=[_THISROW].[KEY]))))

[Bellave_Jayaram]

Try this:

ISNOTBLANK (SELECT(Child Table[NUMBER], AND([KEY]=[_THISROW].[KEY],

[DATE] = MAX(

Child Table[DATE])

)

) )

[Aleksi_Alkio]

If you write a separate virtual column with your MAXROW, you can read the number with a deref expression.

[skinee_dog]

Thanks@Aleksi_Alkio but, the expression in my previous post returns the value I need and does so with one virtual column so, I’m going to go with that.

[Michael2]

Thanks @Bellave_Jayaram

I’m afraid I wasn’t clear about the value I was after. I need to return the value of [Number] rather than just yes/no.

The following statement works well. Any suggestions on ways to improve would be appreciated. ANY(SELECT(Child Table[Number],[KEY]=MAXROW(“Child Table”,“DATE”,[KEY]=[_THISROW].[KEY])))