DAX to replace value with another in same column depending on 2nd column

[donaldo]

Hey all,

I have a curious problem due to the way data I am using is stored.

I have the table below .And I need to replace the ID (column a) of all the rows where Level is = 5, with the ID of the same Idea (column b) where the level is = 4. So the unique ID of Ideas at Level 4 is linked to some data I also need for Level 5.

I've tried creating a new column to use for this purpose with the DAX below; but it gives me "A table of multiple values was supplied where a single value was expected". I've tried wrapping it all in FIRSTNONBLANK with same result. 

IL5 to IL4 = 
 FIRSTNONBLANK(ImplementationLevel[ID]; IF(
    VALUE(ImplementationLevel[Level]) = 1;
    LOOKUPVALUE(ImplementationLevel[ID];ImplementationLevel[Idea];ImplementationLevel[Idea];ImplementationLevel[Level];"1");
    IF(VALUE(ImplementationLevel[Level]) = 2;
    LOOKUPVALUE(ImplementationLevel[ID];ImplementationLevel[Idea];ImplementationLevel[Idea];ImplementationLevel[Level];"2");
    IF(VALUE(ImplementationLevel[Level]) = 3;
    LOOKUPVALUE(ImplementationLevel[ID];ImplementationLevel[Idea];ImplementationLevel[Idea];ImplementationLevel[Level];"3");
    IF(VALUE(ImplementationLevel[Level]) = 4;
    LOOKUPVALUE(ImplementationLevel[ID];ImplementationLevel[Idea];ImplementationLevel[Idea];ImplementationLevel[Level];"4");
    IF(VALUE(ImplementationLevel[Level]) = 5;
    LOOKUPVALUE(ImplementationLevel[ID];ImplementationLevel[Idea];ImplementationLevel[Idea];ImplementationLevel[Level];"4"))
    )))))

[PattemManohar]

@donaldo Please try this as a New Column

NewID = 
VAR _CurrentIdea = Test178CondReplace[Idea]
VAR _Level4ID = LOOKUPVALUE(Test178CondReplace[ID],Test178CondReplace[Idea],_CurrentIdea,Test178CondReplace[Level],4)
RETURN IF(Test178CondReplace[Level]=5,_Level4ID,Test178CondReplace[ID])

[donaldo]

Hey @PattemManohar

Thanks for the suggestion. I'm getting the same error. 

NewID = 
VAR _CurrentIdea = ImplementationLevel[Idea]
VAR _Level4ID = LOOKUPVALUE(ImplementationLevel[ID];ImplementationLevel[Idea];_CurrentIdea;ImplementationLevel[Level];"4")
RETURN IF(ImplementationLevel[Level]="5";_Level4ID;ImplementationLevel[ID])

I've put the Levels in "" as they are formatted as Text (elsewhere I don't want them to be treated as numbers).

Why does it return multiple values? ID column is unique values.

[PattemManohar · Accepted solution]

@donaldo Could you please check and confirm... whether the data contains same idea (duplicates) at same level (in this case it is 4). Even though it has a different ID.