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

by guavaq Super Contributor 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 @guavaq,

 

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 @guavaq

 

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 Regular Visitor
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 Regular Visitor
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 guavaq Super Contributor
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 guavaq Super Contributor
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 guavaq Super Contributor
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 @guavaq

 

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..