DAX to pick a given 'RAG' in a loop

[navedkhan]

Hi - I need to write a DAX function wherein if;

1) if all of the values in child column is 'Green'; then Parent Column would be 'Green', else;

2) if any of the given values is 'Red or Amber', then parent column is 'Red'; 'Amber' 

Please help as am unable to write this logic which should run in a loop.

@Ashish_Mathur  @Zubair_Muhammad 

[v-diye-msft]

Hi @navedkhan 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. thanks!

[Nathaniel_C]

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

[Ashish_Mathur]

Hi,

That is a confusing question.  Why should the answer for US and UK be green?  Please clarify.

[navedkhan]

apologies for confusion caused if any. 

I basically want to derive this;

- "if any of the child col. (col. RAG) is Red; parent col. would be 'Red'; if child column is  'Amber', then parent col. to 'Amber'.

trust now simplifies.

@Ashish_Mathur 

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Result = if(CALCULATE(COUNTROWS(Data),ALL(Data[RAG]),Data[RAG]="Green")=CALCULATE(COUNTROWS(Data),ALL(Data[RAG])),"Green",if(CALCULATE(COUNTROWS(Data),ALL(Data[RAG]),Data[RAG]="Red")>0,"Red",if(CALCULATE(COUNTROWS(Data),ALL(Data[RAG]),Data[RAG]="Amber")>0,"Amber",BLANK())))

Hope this helps.