Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Please help me with the syntax of calculating this in my table (no additional tables please).
Thank you!
Solved! Go to Solution.
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.
Best Regards,
changqing
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.
Best Regards,
changqing
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:
Try
New Column =
COALESCE (
TableName[Total Component Price],
CALCULATE (
MAX ( TableName[Total Component Price] ),
ALLEXCEPT ( TableName, TabeName[ParentID] )
)
)
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |