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

[GilbertQ]

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.

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

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] ) )

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

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()

                                )

 

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

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

[Anonymous]

hi @sajarac  look like your conditional is not ok

[Sales Amount]<=0   
[Sales Amount] >=[Previous Month Sales]

[Sales Amount] <=[Previous Month Sales]

What is going to happen when [Sales Amount] = [Previous Month sales] ?

Switch evaluate the condition from the top to the bottom so in this case is going to valuate UNICHAR(9650)  

I thing you must modify one of your condition from "<=" or ">="  to  "="   to get the conditions in logical sequence. 

[sajarac]

Hi there,

Thank you very much for your prompt reply.

I was usign your sample, but I got stuck applying this part of the formula:

"....No unicode for green arrow up  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. ."

Thanks in advance for your help.

[Anonymous]

@sajarac check this

click the green and is all

[rsathish26]

Hi,

This works fine when the report is viewed on desktop or web but when exported as pdf it does not export colours as expected, red and green. The default colour is shown. Any ideas?

Thank you

[GilbertQ]

Hi @rsathish26

I think that this might currently be a limitation on the export features.

Did you change the colours based on the Conditional formatting in the table?

[rsathish26]

Thank you @GilbertQ. I have used the Card Visual and hence could not do any conditional formatting

[GilbertQ]

Hi @rsathish26

Yes unfortunately that will not happen with the card visual

[deltoro05]

This URL https://unicode-table.com/en/sets/arrows-symbols/ has changed to https://symbl.cc/en/sets/arrows-symbols/

[deltoro05]

This URL https://unicode-table.com/ has changed to https://symbl.cc/