DAX to pick a given 'RAG' in a loop

Hi navedkhan ,

You may wish to use a Switch(), seems simpler than nested If statements for debugging and reading. I broke this solution into three columns for ease of understanding and debugging. Changed the color into a number to assist in ordering these.  I would point out that in your logic, you don't mention whether an Item could have all three colors for the Child RAG.  I went on the assumption that there could be that situation, in which case I built the logic to be: If any red, then red, if no red and any amber then amber, if only green then green. Below is the table that I generated, the code, and the visual.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Child Rag # = SWITCH(RAG[Child RAG],"Red",1,"Amber",2,"Green",3) // Turn the color into a number so that we can select the min

Parent Rag # = var _item = RAG[Item]

return CALCULATE( MIN(RAG[Child Rag #]),(Filter('RAG',RAG[Item] = _item))) // Select for each Item

Parent Rag Color = Switch(RAG[Parent Rag #],1,"Red",2,"Amber",3,"Green") //Turns the number back to a color