cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

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

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

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?

Anonymous
Hi @GilbertQ @Cercue, i can't download your files, it appears somekind of certification error. Can you make them available again? Thanks and regards
Anonymous

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

Hi @Anonymous

 

Could you please put in the entire measure.

 

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

Anonymous

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

Hi @Anonymous

 

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

Anonymous

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

Anonymous

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

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.

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. 

Hi @wcalcagno

 

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

 Thank you @GilbertQ !

 

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?