Lookup within table

[Oomsen]

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 does have a value. 

For example item 10.143 has a cost price of € 2700 (code and costprice column in the table).

I would like to add a custom column which generates the costprice for 10.143-demo.

My idea is to create a lookup for items with demo in the code. 

In that case item 10.143-demo suppost to get the cost price of item 10.143.

[Anonymous · Accepted solution]

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]

Thanks, this is perfect!

[amitchandak]

@Oomsen , Not very clear

You can append and create in DAX and you need convert Number to text in case of power Query

Power query

"Demo-" & Number.ToText([Cost])

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[Oomsen]

@amitchandak see the example below. 
I would like to get the 2700 for12.042-demo in a new column.

code

costpricestandard

description

enddate

id

isondemanditem

ispurchaseitem

issalesitem

isserialitem

isstockitem

itemgroupcode

itemgroupdescription

startdate

stock

12.042-Demo

0

Left drive 70Vdc PU-air tires assembly

null

4C493AE1-EAA2-4464-AA0F-3D581FF152E3

0

FALSE

TRUE

FALSE

TRUE

DE

Demo Robots

01/10/2020 00:00:00

1

12.042

2700

Left drive 70Vdc PU-air tires assembly

null

B802F624-B442-4B3E-8D05-43B38F7ABA6C

1

FALSE

TRUE

FALSE

TRUE

AS

Assemblies

01/11/2014 00:00:00

0