Forum Discussion
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...
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 , 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 |