Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChPetru
Helper I
Helper I

Lookupvalue() Returns Multiple Values Vetically in SAME table

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. 

ChPetru_0-1659648491296.png

 

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!

1 ACCEPTED SOLUTION
changqing
Resolver II
Resolver II

Hi @ChPetru ,

 

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

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

vcgaomsft_0-1660110896753.png

Here's the result:

changqing_0-1660111088176.png

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

 

changqing_1-1660112057859.png

3. The PBIX file is attached for reference.

PBIX file 

 

Best Regards,
changqing

View solution in original post

7 REPLIES 7
changqing
Resolver II
Resolver II

Hi @ChPetru ,

 

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

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

vcgaomsft_0-1660110896753.png

Here's the result:

changqing_0-1660111088176.png

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

 

changqing_1-1660112057859.png

3. The PBIX file is attached for reference.

PBIX file 

 

Best Regards,
changqing

tamerj1
Super User
Super User

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] ) )

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

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]

I have many more lines:

ChPetru_0-1659642900374.png

 

@ChPetru 

Try

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

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

ChPetru_0-1659647768940.png

 

Here is the PBIX file

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors