Forum Discussion

Oomsen's avatar
Oomsen
Helper III
4 years ago
Solved

Lookup within table

I'm using a tabl with items.  For demonstration reasons we copied item codes and added "-demo". The value of demo item is 0 for accounting reasons, so the cost price is €0. But the product itself d...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Oomsen ,

    You can create a calculated column as below, please find the details in the attachment.

    Column = 
VAR _position =
    IFERROR ( SEARCH ( "-Demo", 'Table'[code], 1, 0 ), 0 )
VAR _curcode = 'Table'[code]
RETURN
    IF (
        _position = 0,
        'Table'[costpricestandard],
        CALCULATE (
            MAX ( 'Table'[costpricestandard] ),
            FILTER ( ALL ( 'Table' ), LEFT ( _curcode, _position - 1 ) = 'Table'[code] )
        )
    )

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Oomsen ,

You can create a calculated column as below, please find the details in the attachment.

Column = 
VAR _position =
    IFERROR ( SEARCH ( "-Demo", 'Table'[code], 1, 0 ), 0 )
VAR _curcode = 'Table'[code]
RETURN
    IF (
        _position = 0,
        'Table'[costpricestandard],
        CALCULATE (
            MAX ( 'Table'[costpricestandard] ),
            FILTER ( ALL ( 'Table' ), LEFT ( _curcode, _position - 1 ) = 'Table'[code] )
        )
    )

Best Regards

Oomsen's avatar
Oomsen
Helper III
4 years ago

Thanks, this is perfect!