Forum Discussion
How to extract a location name from a Description text field using a "List of Locations"
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")
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.
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.
Hi Anonymous ,
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
2 Replies
Hi Anonymous ,
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