Forum Discussion
navedkhan
Helper III
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', th...
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.
Ashish_Mathur
Super User
Super UserHi,
That is a confusing question. Why should the answer for US and UK be green? Please clarify.
navedkhanHelper III
Helper IIIapologies 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.