How to create an Overall RAG Measure from 6 other RAG Measures

[CaveOfWonders]

Hi All,

I have 6 distinct RAG measures formatted as whole numbers. They are coming from different tables and have different logic but the output is the same.

The output for all of them will be:

0
1
20
300

0 represents No Data
1 represents Green,
20 represents Amber
300 represents Red.

I need to create a measure which looks at these 6 measures and provides an overall RAG based on the following logic:

A red status is generated by two or more reds, or four or more ambers.

An amber status is generated by one red, or two or more ambers

Otheriwse Green

I have tried an failed many times. Could someone please help me figure the DAX out.

[Anonymous · Accepted solution]

@CaveOfWonders , I think the DAX for your OverallRAG measure would be something like this. It assumes your six RAG measures are named [REG1] thru [RAG6]

RAGOverall = 
    VAR vRed = 300
    VAR vAmber = 20
    VAR vGreen = 1
    VAR vRedCount = 
        IF([RAG1] = vRed, 1) 
        + IF([RAG2] = vRed, 1) 
        + IF([RAG3] = vRed, 1)
        + IF([RAG4] = vRed, 1) 
        + IF([RAG5] = vRed, 1) 
        + IF([RAG6] = vRed, 1)

    VAR vAmberCount = IF([RAG1] = vAmber, 1) 
        + IF([RAG2] = vAmber, 1) 
        + IF([RAG3] = vAmber, 1) 
        + IF([RAG4] = vAmber, 1)
        + IF([RAG5] = vAmber, 1) 
        + IF([RAG6] = vAmber, 1)

    RETURN
        SWITCH(TRUE()
            ,vRedCount >= 2, vRed        //RED because 2 or more reds
            ,vAmberCount >= 4, vRed      //RED because 4 or more ambers
            ,vRedCount = 1, vAmber       //AMBER because 1 red
            ,vAmberCount >= 2, vAmber    //AMBER because 2 or more ambers
            ,vGreen                      //GREEN if none of the above apply
        )

[CaveOfWonders]

Amazing. It works, thank you so much.