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

(Michael) #1

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])

)

)

(Michael) #2

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

(Bellave Jayaram) #3

Try this:

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

[DATE] = MAX(

Child Table[DATE])

)

) )

(Aleksi Alkio) #4

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

(skinee dog) #5

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.

(Michael) #6

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])))