topic Re: Lookupvalue() Returns Multiple Values Vetically in SAME table in DAX Commands and Tips

Lookupvalue() Returns Multiple Values Vetically in SAME table

ChPetru — Thu, 04 Aug 2022 21:28:19 GMT

Hello,

I am strunggling to find a way for this to work for 3h with no luck, it seems so simple but proves to be impossible.

I just want to bring the Total Component Price from ANY Component Line (2 - 5) to the Parent Line (1) in a new calculated column.

Tried lookupvalue: retrieve Total Component Price by looking the Line ID into Parent ID but it returns the multiple values error.

Tried Calculate (Firstnonblank (Total Component Price, True()),FILTER(Table1,LineID=ParentID) returns blank since filter on Parent ID = Line ID is not true for any row.

Here is the PBIX file

Please help me with the syntax of calculating this in my table (no additional tables please).

Thank you!

Re: Lookupvalue() Returns Multiple Values Vetically in SAME table

tamerj1 — Thu, 04 Aug 2022 18:52:40 GMT

Hi ChPetru
If this is the only blank value in the [Total Component Price] column then
New Column = COALESCE ( TableName[Total Component Price], MAX ( TableName[Total Component Price] ) )

Re: Lookupvalue() Returns Multiple Values Vetically in SAME table

ChPetru — Thu, 04 Aug 2022 19:40:19 GMT

hi Tamerj, the table I have given as an example is just a small sample of a huge dataset with millions of rows, and I have multiple different Parent Products and Components. The Coalesce will not work

Re: Lookupvalue() Returns Multiple Values Vetically in SAME table

tamerj1 — Thu, 04 Aug 2022 19:50:45 GMT

Hi ChPetru

you are creating a calculated column, I don't understand what is the complexity you're talking about. Should be simple, IF [ID] = 1 then MAX ( [Total Component Price] ) otherwise [Total Component Price]

Re: Lookupvalue() Returns Multiple Values Vetically in SAME table

ChPetru — Thu, 04 Aug 2022 19:56:02 GMT

I have many more lines:

Re: Lookupvalue() Returns Multiple Values Vetically in SAME table

tamerj1 — Thu, 04 Aug 2022 20:03:28 GMT

ChPetru

Try

New Column = COALESCE ( TableName[Total Component Price], CALCULATE ( MAX ( TableName[Total Component Price] ), ALLEXCEPT ( TableName, TabeName[ParentID] ) ) )

Re: Lookupvalue() Returns Multiple Values Vetically in SAME table

ChPetru — Thu, 04 Aug 2022 21:26:43 GMT

tamerj1 , it doesnt work, it brings the price at component level, not header:

Here is the PBIX file

Re: Lookupvalue() Returns Multiple Values Vetically in SAME table

changqing — Wed, 10 Aug 2022 06:16:38 GMT

Hi ChPetru ,

I can't download the PBIX file because of the network.

1. How about using up-fill in Power Query Editor?

Here's the result:

2. Or new a calculated column:

Column = VAR _lookupvalue = LOOKUPVALUE ( 'Table 2'[Total Component Price], 'Table 2'[Parent ID], 'Table 2'[Line ID] ) VAR _result = IF ( 'Table 2'[Parent ID] <> BLANK (), 'Table 2'[Total Component Price], _lookupvalue ) RETURN _result

3. The PBIX file is attached for reference.

PBIX file

Best Regards,
changqing