Power BI Desktop/DAX – Using Unicode (Arrows) as Measures in a Table

by Super User on ‎06-08-2017 10:36 AM

I was helping out in the Power BI Community and I came across the following requirement.

The user had sales figures and based on the Previous month, they wanted to have an arrow showing if it was up or down. As well as if there was no data to say that there was no data. So this is how I achieved this below.

 

Blog 1.png

Creating a Date Table to make PREVIOUSMONTH DAX measure easy

The first thing that I did was to create a Date table and link this to my table above.

This ensured that when I wanted to create my PREVIOUSMONTH DAX measure it would be simple and easy.

I did it using my blog post: Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Quer...

Once I had my Date Table I then created the relationship between my Sales Data table and my Date Table

 

Blog 2.png

Creating PREVIOUSMONTH DAX measure

Next I created my Previous Month measure with the following DAX Syntax

Previous Month Sales =

CALCULATE ( [Sales Amount], PREVIOUSMONTH ( 'Date'[Calendar Date] ) )

Blog DAX Formatter.png

 

Getting the Unicode numbers for Arrows

Now in order to get the Unicode Characters I followed the very interesting blog post by Chris Webb: The DAX Unichar() Function And How To Use It In Measures For Data Visualisation, in which I learnt how to use Unicode Characters in a measure.

Next I found the following website which contained the Unicode numbers for my arrows I required.

https://unicode-table.com/en/sets/arrows-symbols/

I then made a note of the following ones that I wanted to use

 

blog 3.png

   

Creating the Arrows measure

The final piece was where I created the Arrows measure.

Below is the DAX Syntax with an explanation afterwards

Arrows = SWITCH(

                                                TRUE(),

                                                                ISBLANK([Previous Month Sales]),BLANK(),

                                                                                || ISBLANK([Sales Amount]),BLANK(),

                                                                [Sales Amount] <= 0, "No Data",

                                                                [Sales Amount] >= [Previous Month Sales],UNICHAR(9650),
                                                                [Sales Amount] <= [Previous Month Sales],UNICHAR(9660),

                                                BLANK()

                                )

 Blog DAX Formatter.png

  • Due to having multiple conditions instead of having nested IF statements I found it easier to use the SWITCH DAX function.
  • I also know that with the SWITCH DAX function it evaluates from top to bottom.
    • So the first condition was to check for if the [Previous Month Sales] or the [Sales Amount] was blank meaning it was the start or end of the data to make it BLANK. It is highlighted in GREEN
      • This could be changed to anything value, but I prefer it being BLANK.
    • Next I put in a condition to see if the current Months [Sales Amount] is less than or equal to 0 to put in the value of “No Data” It is highlighted in PURPLE
    • Next is where I use the Unicode characters in my measure. Here I compare if the Sales for the [Sales Amount] is >= the [Previous Month Sales]and if it is then use the UP Arrow. This is highlighted in BLUE.
    • Next is where I use the Unicode characters in my measure. Here I compare if the Sales for the [Sales Amount] is <= the [Previous Month Sales], and if it is then use the DOWN Arrow. This is highlighted in ORANGE.
    • And then finally if none of the above conditions are met then make the value BLANK

Table Output

Below is the outputted table, which is doing as what was required.

 

blog 4.png

Conclusion

As you can see from above being able to make sure of the Unicode Characters can make your dataset that much easier to read.

You can view it here: PBI – Unicode Measures

You can download the Sample File here: PBI – Unicode Measures.pbix

Comments
by powerrdl Frequent Visitor
on ‎06-09-2017 11:09 PM

Hi @GilbertQ,

 

Is there a way to get green & red up and down arrows?

 

Regards

powerrdl

by cerkue Visitor
on ‎06-10-2017 01:14 AM

powerbi.jpg

 Hi @powerrdl @GilbertQ

 

my way to get green & red up and down arrows and yellow diamond...

 

Keeping the example measure and changing unicodes

 

Arrows = SWITCH(
                          TRUE();
                              ISBLANK([Previous Month Sales])
                                      || ISBLANK([Sales Amount]);BLANK();
                   [Sales Amount] <= 0; UNICHAR(128310);
                   [Sales Amount] >= [Previous Month Sales];UNICHAR(9650);
                   [Sales Amount] <= [Previous Month Sales];UNICHAR(128315);
              BLANK()
             )

 

No unicode for green arrow up Smiley Sad but we can use (9650) grey arrow and switch to green under column formating, select the measure Arrows changue font color to  00B200  and activate all options except Header. Smiley Very Happy

 

regards,

 

 

 

 

 

by powerrdl Frequent Visitor
on ‎06-10-2017 01:25 AM

Wow ! That's cool, will try and revert

 

Many Thanks Smiley Happy

by vkomarag Member
on ‎06-12-2017 07:33 AM

Hi @cerkue

 

I tried the red arrow but it showing up in grey. Any option need to be enabled plaese let me know.

 

KVB

by cerkue Visitor
on ‎06-13-2017 06:59 AM

Hi @vkomarag,

 

If you create a measure like  Red arrow=UNICHAR(128315)  and put on a Visual Card or Matrix you should see a red arrow, nothing special must be enable, check your PoweBi version.

 

 

 

 

 

by vkomarag Member
on ‎06-13-2017 09:56 AM

Hi @cerkue

 

I did the same

 

Red arrow = UNICHAR(128315)

 

i got a black triangle pointing down.

 

Am i missing anything?

by Super User
on ‎06-13-2017 02:05 PM

I think that it is due to the table or matrix not being able to change the colour of the text (because the unicode is a text character)

 

I also did see from the Microsoft Data Insights Summit that they are going to release a quick measure for Stars, so I am certain that there will be one coming soon that will do it for arrows?

by Imranraju_786 Occasional Visitor
on ‎06-28-2017 11:53 PM

Hi Dear All,

Am totaly new with power BI I want to start this amezing program for my company .I got some basic knowldge which is required fro this .I have one main issue that I want to work with my all fellow workers.Basically I want to be an admin for my company But i have no idea how can I handel this .I have in strating more tahn 20 persons I want to share data with them I try to distribute date through Row level Security but unfortunatly am not satisfied with this system .You guys have much experince with this software.It would be much appriciated if some body can guide me properly how can I handel this issue .Is ther any other system which I can use for security or I dont need.

Waiting for quick feedback.

Thanks

by Super User
on ‎06-29-2017 03:53 PM

Hi @Imranraju_786

 

I would suggest watching this video first by Guy in a Cube which explains what Row Level Security is

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

 

Then once you get an understanding you can then use the tutorial below to test it out and see how it works and pieces together.
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-rls/

 

That should get you going.

 

 

by Shubham Member
on ‎09-18-2017 03:39 AM

Hi @cerkue

 

Could you please share your .pbix file with Green and Red arrow.

by Super User
on ‎09-19-2017 06:52 PM

Hi @Shubham

 

It is part of the blog post right near the end.

 

Here is the link also: https://1drv.ms/u/s!Apxn-69XhcAmhqpv7owSIFn0ViS7ng

by Shubham Member
on ‎09-20-2017 04:23 AM

hi @GilbertQ

 

thanks for sharing but file does not has "Red" "Green" color arrows. if you got success to implement this in .pbix file then please share with us. We will levarge the same functionality.

 

-Shubham

by cerkue Visitor
on ‎09-25-2017 05:37 AM
by Shubham Member
on ‎09-25-2017 09:56 AM

all arrows(UP and DOWN) are in GREEN color.. 

 

 

by Baskar Super Contributor
on ‎02-20-2018 02:24 AM

How we can get red,yellow,green circle using unichar.like traffic signal.

by Super User
on ‎02-20-2018 03:10 PM

Unfortunately with the UniChar it is only in the default colors.

by Pezir Frequent Visitor
on ‎07-23-2018 10:38 PM

Thanks a lot, this works well! What if I want return certain unicode or blank if both columns are blank, not just one? 

by Super User
on ‎07-24-2018 07:44 AM

Hi @Pezir

 

What you could do is to alter the SWITCH conditions based on your requirements.

 

You could test to see if your measure is BLANK() and if it is then return either BLANK() or return another Unicode character?

by Ricardo Member
on ‎07-26-2018 07:40 AM
Hi @GilbertQ @Cercue, i can't download your files, it appears somekind of certification error. Can you make them available again? Thanks and regards
by Ricardo Member
on ‎07-26-2018 09:19 AM

Hi,

Im getting an error in a measure like Measurec = UNICHAR(9733) :

 

"Failed to resolve name 'Unichar'. It is not a valid variable, table, or function name."

 

Also: I would like to place all the logic on the tabular model (SSDT Tabular project), but the same error appears on Data Tools.

Why is this error happening?

 

Regards

by Super User
on ‎07-26-2018 02:22 PM

Hi @Ricardo

 

Could you please put in the entire measure.

 

As it currently stands with your example that will fail due to a syntax error

by Ricardo Member
on ‎07-26-2018 03:31 PM

Hi @GilbertQ,

 

after reading your post i've tested again and it works. It was failing, and in latest SSDT 2015 also, so i was thinking some problem with versions... it seems that it works with Tabular Models version 1400  (latest SQL Server and Azure)...

My doubts are:

- Tabular models 1200 are compatible with UNICHAR?

- why can't i simply drag a measure like test2 and see the arrow in what should be a text box?

 

2018-07-26_23h28_00.png

Regards

by Super User
on ‎07-26-2018 05:59 PM

Hi @Ricardo

 

It could possibly be due to the fact that it needs to be 1400

by Ricardo Member
on ‎07-27-2018 02:35 AM

New test, find the problem.

This is unbelievable, i tryed on the organization VM to use UNICHAR in a new PBIX, and it works, so my conclusion is that my report isn't working because i'm connected to an SSAS Tabular 1200 from SQL Server 2016.

 

So, i'm limited on Tabular and on Power BI.

Smiley Indifferent

by Ricardo Member
on ‎07-27-2018 03:42 AM

I converted printscreens of the arrows in Base64 (using https://www.browserling.com/tools/image-to-base64), and tried something like this on Tabular

 

="data&colon;image/jpeg;base64, " & IF ( [Moving Average 3M]>1; "iVBORw0KGgoAAAANSUhEUgAAABwAAAAeCAYAAAA/xX6fAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAACbSURBVEhL7dFRCoAgEIThjlT3P5wxkGE66q7tKkQ/DPSgfg9tYXI/WO3Yj+tL1xAIbAk4gqrBFFsCalEVyDBMkwmISRODDEknzQzEJIlA9jibJFMQ69UF2aOt9TIHsVZNkD0mWSsXEKtVBdkjmtVyAzEWBdnlkbFcQSyvANmlN8tzB7G0B8gOWyxtCojFbpAdslys+IfefR0M4QQbPD4TaV+BpAAAAABJRU5ErkJggg==";

"iVBORw0KGgoAAAANSUhEUgAAAB4AAAAeCAYAAAA7MK6iAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAACTSURBVEhL7dFBDoAgDERRjyT3PxxmFiaIA7SFUhe+ZJb0x3jkIH94m0c4ncl1pdcXswerVtoWrtF/zB7OrrYlzNAwsAPWMe7hlmYY2CHtWlzDPd0wsIPS9biFR4ZhYIdHG3EJS4jCwAKtSSwPS4nDwEL1pJaGNVRhYMF7GsvCWuowfCZsYQpDeNjKHIaw8IygcM4XLBctMmT0kYQAAAAASUVORK5CYII=")

 

In Data category placed as image,. It isn't working on Power BI, i get an error:

 

xxx.PNG

by wcalcagno Frequent Visitor
Wednesday - last edited Wednesday

First of all, I want to say tanks for your tip.  works amazing with calculated columns and meassures.  

 

I have a situation here.  I use the Swith for the arrow with a meassure, who is coming from another 2 measures:

Measure1 = CALCULATE(SUM('Sales'[monto_pago]);
DATESBETWEEN(Calendar[Date];
date(year(TODAY());MONTH(today());1);
date(year(today());MONTH(TODAY());day(today()))
))

 

Measure2 =CALCULATE(SUM('Sales'[monto_pago]);
DATESBETWEEN(Calendar[Date];
date(YEAR(EDATE(TODAY();-1));MONTH(EDATE(today();-1));1);
EOMONTH(TODAY();-1))
)

 

Measure 3 = DIVIDE([Measure1]; [Measure2])-1

Arrow = SWITCH(
true();
ISBLANK([Measure3]);BLANK();
[Measure3]>1;UNICHAR(9650);
[Measure3]<1;UNICHAR(128315);
[Measure3]=1;UNICHAR(8649);
BLANK())

 

that works amazing... no problem with that.screen1.JPGHow the measures works

 

 

 

 

 

 

 

 

 

 

 

 

The issue is coming when I put the customer name as a filter,  All the measures changes, except the Arrow

how can I solve that?

screen2.JPGAll measures changes except the triangle

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks for your greater help.

by wcalcagno Frequent Visitor
Wednesday

I fix my previous post.. the error it was on the Switch measure.. I change for 0 and it works

---- original ----

Arrow = SWITCH(
true();
ISBLANK([Measure3]);BLANK();
[Measure3]>1;UNICHAR(9650);
[Measure3]<1;UNICHAR(128315);
[Measure3]=1;UNICHAR(8649);
BLANK())

 

----Fixed ----

Arrow = SWITCH(
true();
ISBLANK([Measure3]);BLANK();
[Measure3]>0;UNICHAR(9650);
[Measure3]<0;UNICHAR(128315);
[Measure3]=0;UNICHAR(8649);
BLANK())

 

I forgot that I'm working with positives and negatives percentages, so the limit is 0 no 1...   thanks anyway. 

by Super User
Wednesday

Hi @wcalcagno

 

Well done on resolving the issue. I was looking at your measure and it all made perfect sense.

by wcalcagno Frequent Visitor
Wednesday

 Thank you @GilbertQ !

 

by sajarac Regular Visitor
3 hours ago

Hi there,

 

I've got stuck I think in the easy part:

 

I can't figute out how to chenge the green triangle here:

 

my way to get green & red up and down arrows and yellow diamond...

 

Keeping the example measure and changing unicodes

 

Arrows = SWITCH(
                          TRUE();
                              ISBLANK([Previous Month Sales])
                                      || ISBLANK([Sales Amount]);BLANK();
                   [Sales Amount] <= 0; UNICHAR(128310);
                   [Sales Amount] >= [Previous Month Sales];UNICHAR(9650);
                   [Sales Amount] <= [Previous Month Sales];UNICHAR(128315);
              BLANK()
             )

 

No unicode for green arrow up Smiley Sad but we can use (9650) grey arrow and switch to green under column formating, select the measure Arrows changue font color to  00B200  and activate all options except Header. Smiley Very Happy

 

 

Could you please point me in the right direction?