5 Status Indicator

[Amirul]

Hi,

Is there any way that I can create a traffic light status for 5 colour? I already watch the SQLBI using tabular editor, however, I could not get it right.

This is my formula

Status = 
VAR Ratio = [Sales to Salary Ratio]
VAR RatioTolerance = 2
VAR RatioGoal = 6

RETURN
    IF (
        NOT ISBLANK ( Ratio ),
        SWITCH (
            Ratio < RatioTolerance, -2,
            RatioTolerance < Ratio < RatioGoal - RatioTolerance, -1,
            RatioGoal - RatioTolerance < Ratio < RatioGoal, 0,
            RatioGoal < Ratio < RatioGoal + RatioTolerance , 1,
            Ratio > RatioGoal + RatioTolerance, 2
        )
     )

[Greg]

Sure … conditional formatting can do this, no problem. Much easier to describe the method specifically if you can attach your work-in-progress PBIX and an Excel mock-up of your desired outcome.
Greg

P.S.: if you include DAX in your posts, please format it using the built-in editor

[BrianJ]

@Amirul,

Absolutely. There are a number of different ways you can do this.

First, instead of using SWITCH here, I think what would work better is SWITCH(TRUE). See @sam.mckay’s excellent video below on how to set that up:

Using SWITCH True Logic In Power BI - DAX Concepts

For each true condition, instead of returning a number you will want to return a UNICHAR() code for your stoplight icon. Here’s a big list of codes – take a look at the geometric shapes section for colored circles you can use as stoplight indicators.

Huge List of Unicode Character Symbols

However, rather than using UNICHAR() codes, I think you can get a much better look by using hosted custom icons. Take a look at my Problem of the Week #5 solution for an example of this technique:

Problem of the Week #5 (DAX) - Dynamic Last N Weeks Trend

Attached is my solution to Problem of the Week #5. I’m getting a huge kick out of how people have started adding individual flair to their entries (@jbressan’s timeline, @quantumudit’s full dashboard, @Greg going Greg-style challenge entry, etc.). In keeping with the spirit, I used neither the UNICHAR() nor conditional formatting approach for the icons, instead going with custom flaticon.com icons hosted on imgbb.com, one of the techniques I discussed on last week’s members’ event with @sam.mc…

If you want to go this route, I explain in detail how to do it in my Data Challenge #7 write up:

Power BI Challenge 7 - Purchases, Inventory And Sales Entry from Brian J

@EnterpriseDNA, Pardon the length of this post. I took a somewhat unusual approach to the data modeling component of this challenge, and have gotten a number of questions about it, and the same is true for the timeline visual I included in my report. Thus, for those interested I have laid out the approach I used for each in detail, with a lot of screenshots to help explain the methodologies. DATA MODEL In looking at the data initially, all the tables had a many-to-many relationship to each ot…

I hope this is helpful.

• Brian

PS – conditional formatting is another way to do this, but you are very limited in the icon sets you can use. However, if you do like the icons available in the conditional formatting, that’s probably the easiest way to accomplish what you want to do.

[Amirul]

Thats lovely Brian. Thank you so much.

Thank you also Greg. For showing me how to use the textbox.

[EnterpriseDNA]

Hi @Amirul. I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!