Help with a complex DAX query for a traffic light system

[TS-Callum]

Hi all,

I am creating a report that will feature a traffic light system for the first page of the report that management can easily see at a glance how various sections of the business are doing with a green, orange or red circle (in this case, the support team)

In order to create this, I decided a new table would may be required and I will need to write some DAX that outputs an image URL based on whether there were 6 breached or warning incidents that month for the selected customer.

Please see the below example data for my source table -

Essentially, only for the current month and only for a selected customer (as per synced slicer selection) I am wanting to do a count of EscalationLevel per category. My new table should ideally look similar to the below -

I am then going to write another quick DAX query in another new table that will grab these values, sum Breach and Warning together, and display an image URL which is a green circle if the result is 0, orange circle if the result is 1-3, and a red circle if the result is over 6.

Am I going about this the wrong way, or is there an even easier alternative?

I hope this makes sense, I'm pretty terrible at explaining sometimes so please feel free to ask me for further information.

Any guidance would be hugely appreciated. Thanks!

[v-chuncz-msft · Accepted solution]

@TS-Callum,

You just need to take advantage of measure below.

Measure =
COUNTROWS (
    FILTER ( Table1, Table1[EscalationLevel] IN { "Breach", "Warning" } )
)