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!
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:
As can be seen, we have 2 tasks that, at first glance, do not seem feasible to perform in Power BI:
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().
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!
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:
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):
Now it is time to understand the proposed solution to perform text formatting with DAX. The following is the data table we originally had:
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:
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]
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.
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?
* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.