Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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")) )))))
Solved! Go to 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.
Proud to be a PBI Community Champion
@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])
Proud to be a PBI Community Champion
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.
@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.
Proud to be a PBI Community Champion
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |