Help with select formula

[Neal_MF_Harper]

So I have this formula that I need a hand with:

ANY(SELECT(Checkout Log[Checked out to], AND([Barcode]=[_THISROW].[Barcode], [Checked Out?]=Yes)))

The above works as expected, however, I would like to setup further so that when “[Checked Out?]=No” it returns static text “EQ ROOM”

Tried playing with IF(), OR() and drinking too much coffee but could not figure out

Thanks in advance my wizard friends!
Neal

[LeventK · Accepted solution]

@Neal_MF_Harper
Try with this:

IF(
	LOOKUP([_THISROW].[Barcode],"Checkout Log","Barcode","Checked Out") = "No",
	"EQ ROOM",
	LOOKUP([_THISROW].[Barcode],"Checkout Log","Barcode","Checked out to")
)

[Neal_MF_Harper]

Thanks so much Levent, it worked!!
Have a wonderful day Sir.
smiley:

[LeventK]

You’re welcome

[Neal_MF_Harper]

So I hit a snag…

The checkout log will contain multiple entries, can I use MAX() or MAXROW() somehow to only look for the latest entry in the log?

Right now, if I have two entries in the log, the first record being “Checked Out” NO (returned) and the second entry YES, it returns the “EQ ROOM” text from the first entry, while I need it to be based on the most recent entry. Make sense?

Thanks in advance for your time!
Neal

[MultiTech]

Try and replace the [_thisrow].[barcode] with the maxrow formula:

maxrow(Checkout Log, DATETIME_COLUMN, [Barcode] = [_THISROW].[Barcode])

You’ll need to enter a column name where I put DATETIME_COLUMN, and maybe tweek the condition at the end - but replace the [_thisrow].[barcode] part of each lookup with the maxrow.

(I would test the maxrow formula in the editor, just add a new virtual column and see if the formula works - then delete the column like it was never there.)

[LeventK]

@Neal_MF_Harper
You can enhance the formula with this one:

IF(
	ANY(SELECT(Checkout Log[Check Out],AND([Barcode]=[_THISROW].[Barcode],[_RowNumber]=MAX(SELECT(Checkout Log[_RowNumber],[Barcode]=[_THISROW].[Barcode]))))) = "No",
	"EQ ROOM",
	ANY(SELECT(Checkout Log[Checked out to],AND([Barcode]=[_THISROW].[Barcode],[_RowNumber]=MAX(SELECT(Checkout Log[_RowNumber],[Barcode]=[_THISROW].[Barcode])))))
)