Dax LOOKUPVALUE for duplicates values and different length values

[Anonymous]

Hi 

I need help to enhance my DAX commans below. Below the Dax lookup command works initally what i need but there was changes to the values. O

1)  of the changes was with duplicate values. When i run the dax lookupvalue command it gives an error of - A table of multiple values was supplied where a single value was expected

2) The second change was that the current Dax command was loking at the length which was all 7 in length. Now the part number could be 4 to 14 charters long.

All suggestion for both or 1 of the items is helpful

[Anonymous]

Hi  @Anonymous ,

It seems that the table contains duplicate data causing the error to occur:

You can change the lookupvalue formula to the following form:

CALCULATE(FIRSTNONBLANK('PNDETAIL'[Primary Item Number], 1), FILTER(ALL(' PNDETAIL '),' PNDETAIL '[Enginneering item number]= 'CQMSPN'[CPARTN] ) )

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , Try like

new column =

maxx(filter(Table2, Table1[Engineering Item Number] = Table1[CPARTN]), Table2[Primary Item Number ])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Anonymous]

Hi Amitchandak

I tried to use the syntx but got an error message .

new column = maxx(filter(CQMSPN, PNDETAIL[Engineering Item Number] = CQMSPN [CPARTN]), PNDETAIL [Primary Item Number ])