Dax Measure: Count occurrence of text and If statement

[Aimeeclaird]

Hi, looking for some help please.

I have data from an MS forms assessment we have created. The user will complete the assessment and a RAG status will be assigned based on their response. 

I need to write a measure that calculates an overall RAG status for the Executive Summary page of the report. 

Exmaple of data: PBI Community Example Data.xlsx

How can I calculate based on the logic included in the file what the overal RAG status for the section of questions is?

[Fowmy]

@Aimeeclaird 

Your logic is not clear, can you explain with the help of your sample data:

[Aimeeclaird]

Hi @Fowmy thanks for the reply. I've tried to explain my logic better in the file. Let me know if this helps. Also open to other suggestions of better logic to apply if you have anything!

[amitchandak · Accepted solution]

@Aimeeclaird , Try a new column like

new column =
var _all= countx(filter(table,[Question section] =earlier([Question section])),[Question section])
var _red = countx(filter(table,[Question section] =earlier([Question section]) && [Response] ="Red" ),[Question section])
var _amber = countx(filter(table,[Question section] =earlier([Question section]) && [Response] ="Amber" ),[Question section])
var _green = countx(filter(table,[Question section] =earlier([Question section]) && [Response] ="Green" ),[Question section])
return
switch( true() ,
_red >=2, "RED"
_amber >=2 , "AMBER "
_green = _all , "GREEN",
_green >=1 && _amber=1 , "GREEN",
"Other"
)

[Aimeeclaird]

Hi @amitchandak thanks for the reply.

I've used the above to create a new column but unfortunately everything is calculted as 'other'. Not sure if (as the comment above mentions) the problem is with the logic I am trying to use?

[Aimeeclaird]

Hi @amitchandak Sorry, ignore that! I had made a mistake. I've resolved now but have identified a couple of scenarios not covered by my logic. I will adjust this using your suggested formula. Thanks for the help!