Unicode Traffic Light indicator not showing on PBI desktop (Windows 7) and PBI Service

[omrdmr]

Hi,

I would like to display my measure as a unicode character to imitate traffic lights in my PBI report. I have two values to change the Unicode character between red, amber and green This can't be achieved by conditional formatting.

I've used the below formula in my measure but, can't get it display the colours properly. All I see is black squares.

Light = UNICHAR(if(AND([W_CY] >= 0, [W_CY] < 0.02), 128215, if(AND([W_CY] >= 0.02, [W_CY] < 0.025), 128210, 128213)))

where  W_CY is another measure.

I use windows 7. But a computer running windows 10 can display the colours properly.

PBI service also displays black squares.

What should I do to get this measure display colours as expected on Windows 7?

[nehajadhav166]

@TomMartens 

Where can I find Unicode value for orange lights?

Thanks,

Neha

[TomMartens]

Hey @nehajadhav166 ,

I get all the unicode I use from this site: HTML Unicode UTF-8 (w3schools.com)

Hopefully, this provides what you are looking for.

Regards,

Tom

[TomMartens]

Hey,

I have no idea about your Windows 7 question, but maybe I realized a little flaw regarding the UNICODE values. I think it should be:

128210 (yellow)

128213 (red)

128215 (green)

Regards

[omrdmr]

Yes, you are right, I am using the same values. Just corrected the post.

Thanks.

[v-chuncz-msft]

@omrdmr,

Instead, you may specify Image URL in the Data Category or use conditional formatting.

https://community.powerbi.com/t5/Desktop/Workaround-a-for-Measure-to-return-a-URL/m-p/223598

[omrdmr]

Hi, @v-chuncz-msft,

As I said earlier, conditional formatting is not working for me. I have two data limits and need to display 3 colours based on the limits. As PBI Con formatting doesn't offer the same functionality in Excel, it doesn't work for me.

I've looked at the post you shared. Yes, you can display images using calculated columns but I couldn't get it to work. 

I tried to create a calc column using a formula like below one:

Image = 
VAR val = IF(ISBLANK(CALCULATE(SUM(Fact_Table[Total]), dCalendar[YTD], Fact_Table[Service]="ABC")), 0, 
	CALCULATE(
		DISTINCTCOUNT(Fact_Table[No]), 
		Fact_Table[RecordType]="i", 
		dCalendar[YTD],
		Fact_Table[Flag],
		OR(
			Fact_Table[Code]=1, 
			Fact_Table[Code]=2
		), 
		Fact_Table[Biz Service]="ABC"
	)
	/
	CALCULATE(SUM(Fact_Table[Total]), dCalendar[YTD], Fact_Table[Service]="ABC")
)
RETURN 
    SWITCH (
        TRUE (),
        Val <= 0.02, "https://www.google.com/favicon.ico",
        "https://www.microsoft.com/favicon.ico"
    )

I want to use this column in my table where I display colours for each month. But as val variable returned 0- where the same formula in my measure returned 0.02- all displayed Google's favicon.

 

Do you think, I can get this to work with some changes? OR Cal column is not something I can use to display colours/images?

[v-chuncz-msft]

@omrdmr,

By selecting the Diverging box and specifying number -1, 0 and 1, you can just configure three colors for the current conditional formatting.

[omrdmr]

Hi @v-chuncz-msft,

I've tried this with a PBI consultant. We want to show certain colours: Red, Green and Amber. But when we select diverging it displays colours in the gradient style.

Regards

[v-chuncz-msft]

@omrdmr,

See the example attached.

[omrdmr]

Hi @v-chuncz-msft,

I've changed the conditional formatting as in the example. This is not I wanted it to display. Please see the image.

I want to make bg colour

• Green, when the measure <2% for the month
• Amber when the measure >=2% and <2.5% for the month
• Red when the measure >=2.5% for the month

Your example works well when you don't have any target to change the colour based upon. But in my case, it is not flexible enough.

Regards

[v-chuncz-msft]

@omrdmr,

Currently, there is no more elegant way. Otherwise, you'll need to create a custom visual.

[omrdmr]

Thanks, @v-chuncz-msft. Probably we will not go for it due to the time constraint.

I wonder if anyone tried to display UNICHARs (128215, 128210, 128213) as colours on WINDOWS 7 in a measure like:

Light = UNICHAR(if(AND([W_CY] >= 0, [W_CY] < 0.02), 128215, if(AND([W_CY] >= 0.02, [W_CY] < 0.025), 128210, 128213)))

 If anyone achieved, please let me know.

Regards

[kritchensBMcD]

I know this is an older post. But now powerbi allows for you to conditonally color items so you can use them for your RAG status. There are Unicode Differences between windows 7 and 8 and 10 so if you have a mixed environment this could be an issue and you would need to find a common ground between each of them.