Forum Discussion
Helper IIIDAX to pick a given 'RAG' in a loop
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.
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.
5 Replies
- Ashish_Mathur
Super User
Hi,
That is a confusing question. Why should the answer for US and UK be green? Please clarify.
navedkhanapologies 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
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.
- v-diye-msft
Community Support
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
Community Champion
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.
NathanielChild 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
