Forum Discussion

ChPetru's avatar
ChPetru
Icon for Helper I rankHelper I
4 years ago
Solved

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. 

 

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!

DAX
Help
LOOKUPVALUE
measure
  • changqing's avatar
    changqing
    4 years ago

    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

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    • ChPetru's avatar
      ChPetru
      Icon for Helper I rankHelper I
      4 years ago

      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

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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]

  • changqing's avatar
    changqing
    Icon for Resolver II rankResolver II
    4 years ago

    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