powerbi-text-formatting-dax
Javier Ríos

Javier Ríos

Business Intelligence Consultant | Marketing Expert | Data Specialist

Other Articles:

Text formatting with DAX

Share on linkedin
LinkedIn
Share on facebook
Facebook
Share on twitter
Twitter
Share on whatsapp
WhatsApp

Power BI is a great tool for data visualisation. It has many different visualisations that adapt to practically any need and periodically introduces improvements to implement new features often required by the immense user community.

However, sometimes we come across a feature needed for our designs that has not yet been implemented for which there is apparently no short-term solution. It is at these moments that creativity and knowledge of the tool and DAX can help us overcome these obstacles.

Today I present a particular case where a client needed a specific format for part of the text in a table. Among the formatting options offered by Power BI for this visualisation, we do not have the option of establishing a specific formatting for a part of the text in certain boxes. Nor is there a DAX for formatting. So… can it be done? Now we’ll see!

Understanding the need

First, let’s look at the need. The client needs us to create a visualisation where he can see the weekly planning of their teaching staff. He needs to get something like that:

Power BI DAX Text Formatting Target

As can be seen, we have 2 tasks that, at first glance, do not seem feasible to perform in Power BI:

  • The box has part of the text in bold and part of the text in normal format.
  • The box distributes the text on 2 separate rows (timetable above, subject below).

If we look through the formatting possibilities of the visual, we will obviously not find anything of the sort.

The first possible solution that came to my mind was to insert HTML tags like <b>, <i>, <p>, <br /> in the text box but it did not work as Power BI does not interpret them as code but as text.

Then I also tried using special web language character codes within the text like o à but that didn’t work either. It was at this point that I remembered a DAX function capable of fetching me almost any Unicode character which, virtually, would open the door to a solution: UNICHAR().

What is Unicode?

Unicode is a universal character encoding standard that defines each character or symbol of almost any alphabet by a unique numeric identifier. Therefore, the character “A” in Unicode would be “41”. Unicode also includes a specific code for all Emojis, statistical symbols, mathematical symbols… and this is where it comes to our rescue.

Between the coded symbols there are mathematical series that simulate bold or italic characters and numbers in serif and sans serif styles… and that’s all we need!

So... what is the solution?

What we are going to do is to replace each “normal” character we want with its Unicode Bold equivalent, which is the format the client needs. For this we will use the SUBSTITUTE() and UNICHAR() functions together. That is, we are going to make a text format with DAX.

But one thing that will be essential is to know which characters we want to replace and their Unicode equivalent. For this purpose, there are sites such as https://yaytext.com/es/ which allow you to enter a text and see the possible variants of it:

Power BI DAX Yaytext Text Formatting

In this case, the style I am interested in is “Bold (serif)”. To find out which Unicode code corresponds to which character, copy each character from the result and paste it into a Unicode code search engine such as https://unicodelookup.com/, where the code we are interested in is “Dec” (for decimal):

Power BI DAX Unicode-Lookup Text Formatting

Practical implementation

Now it is time to understand the proposed solution to perform text formatting with DAX. The following is the data table we originally had:

Power BI DAX Text Formatting Initial Table

There are many ways to approach the solution but, in my case, as I only need to replace the numbers from 0 to 9, I have chosen to create a column and use a simple code that, iteratively, searches one by one for the different characters that we want to replace and changes them for their bold equivalents using Unicode code:

				
					SUSTITUIR = 
VAR S0 = SUBSTITUTE(Principal[HORARIO],"0",UNICHAR(120812))
VAR S1 = SUBSTITUTE(S0,"1",UNICHAR(120813))
VAR S2 = SUBSTITUTE(S1,"2",UNICHAR(120814))
VAR S3 = SUBSTITUTE(S2,"3",UNICHAR(120815))
VAR S4 = SUBSTITUTE(S3,"4",UNICHAR(120816))
VAR S5 = SUBSTITUTE(S4,"5",UNICHAR(120817))
VAR S6 = SUBSTITUTE(S5,"6",UNICHAR(120818))
VAR S7 = SUBSTITUTE(S6,"7",UNICHAR(120819))
VAR S8 = SUBSTITUTE(S7,"8",UNICHAR(120820))
VAR S9 = SUBSTITUTE(S8,"9",UNICHAR(120821))
RETURN
S9
				
			

Our table now looks like this:

Power BI DAX Formatted Text Format

Obviously, if we needed to modify many more characters, the approach would be different in order to simplify the code as much as possible.

Now that we have the timetable in bold, we want to have both the timetable in bold and the subject in a single field, in this case with standard formatting and on a separate line. To do this, we create a new column where we join both columns and use the UNICHAR(10) character between them, which generates a line break:

				
					Texto-Formateado = Principal[SUSTITUIR] & UNICHAR(10) & Principal[CONCEPTO]
				
			
Power BI DAX Formatting Result

We can see that the result shows us everything in the same line but, once we take this field to the table or matrix, it will show the information as we need it.

Power BI DAX Formatting General Situation

Conclusion

As indicated above, UNICHAR() can not only be used to insert special characters but also Emojis and others. That is, we could use lists like the ones found here   to obtain the code of flags of any country, arrows, signage, faces … the possibilities are almost endless and allow you to give a very visual original touch to any design without having to prepare and load each of the images. If this resource is used, it is important to remember that the codes we use in UNICHAR() must be decimal, so in case of Hexadecimal codes it is necessary to convert them.

UNICHAR() and a little imagination can certainly open the door to more advanced and complete design solutions for our developments. What use can you think of?

Deja un comentario

Your email address will not be published. Required fields are marked *

Business Data Master Logo

No te pierdas el

WEBINAR
Gratuito

Explicaremos en detalle los contenidos y objetivos del Business Data Master

29/11/2021

18:30 (GTM+1)

Online

BUSINESS DATA MASTER

* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.