How to extract a location name from a Description text field using a "List of Locations"

[LMcQ]

This is an example of the HistoryExtracts[Description]:
      Removed 50 from stockroom StockroomName, client Client-Name

_StockroomList would have about 25 names
this is the code I'm trying to work out....

AffectedStockroom =
VAR _SRname = FILTER('Locations', Locations[Site Category] = "E")

VAR _StockroomList = SELECTCOLUMNS(_SRname, "Stockroom", Locations[Location Name])

RETURN

    IF(HistoryExtracts[Type] = "Stockroom Update" && CONTAINSSTRING(HistoryExtracts[Description], _SRname, "Not Found")

Error message is: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

What am I missing?? do I have to do a switch for each Stockroom name?
Thanks for your help.

[LMcQ]

Thank you so much... I've spent days trying to figure it out on my own... Fantastic!!! Sorting and filtering have always been a weak spot for me

[Anonymous · Accepted solution]

Hi @LMcQ ,

Please update the formula of calculated column [AffectedStockroom] as below and check if it can return your expected result... Please find the details in the attachment.

AffectedStockroom = 
VAR _loc =
    CALCULATE (
        MAX ( 'Locations'[Location Name] ),
        FILTER (
            'Locations',
            IFERROR (
                SEARCH ( 'HistoryExtracts'[Description], 'Locations'[Location Name], 1, 0 ),
                0
            ) > 0
                && 'Locations'[Site Category] = "E"
        )
    )
RETURN
    IF (
        HistoryExtracts[Type] = "Stockroom Update"
            && NOT ( ISBLANK ( _loc ) ),
        _loc,
        "Not Found"
    )

Best Regards