Forum Discussion
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 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.
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
4 Replies
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.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
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
Thanks, this is perfect!
