Signed in as:
filler@godaddy.com
Custom Fonts in Power BI
One thing that many BI developers will encounter is the request to add a corporate font to a report. It's fairly well known that report makers can edit the JSON theme in a Power BI report to gain access to custom fonts, and this can work if, for example, the organisation automatically installs the font on all company m
Flaws with Theme Fonts
Even with a 100% install base for the font in question, there are some scenarios where fonts may be problematic:
- If a subscription is generated from the report, the exported file won't have the font and will default to something like Times New Roman (yuck!)
- External users will most likely not have the font, so a carefully designed report will be thrown out of balance as all the fonts change when viewed externally (say hello to a dozen tiny scrollbars!)
- We are (currently) unable to specify a fallback font in the theme file. This would be a great solution as we could use something neutral like Segoe if our custom font is not found. Hopefully this can be added as a feature soon.
An Alternative Way...
This technique has some burden attached to it, and I would not recommend building a report that is heavy with it, as you will end up in some technical debt. But having said that, I did find a use for this on a daily mailout report where changing text would have looked odd being off-brand. I hope you enjoy this, even if only for the demonstration!
Download Required Fonts
First, we need to grab our font files. For this demonstration, I am using fonts from Google Fonts, with the addition of Helvetica Neue as it is a popular font. Note that Helvetica Neue is licences for free personal use, I use it here as a demonstration only. You should ensure you have the correct licences for fonts before using them for commercial purposes. Here are some nice fonts that I liked the look of:
Create CSS/Base64 Conversions for Fonts
Fonts can be used in websites in multiple ways, such as by linking to the font file in CSS. They can also be embedded in the CSS using Base64, which can get around some obstacles like security blocks on external files and different devices and browsers. Base64 strings are around 30% larger than the original file, so this method is not always advisable and can lead to increased load in websites.
As we cannot link to external font files in Power BI, we are going to store Base64 representations of fonts in Power Query parameters and then use functions to encode them into something that can be rendered visually.
To get our Base64 strings, we can use a website called Font Squirrel to convert our fonts. To do this, we upload out font, select Woff2 as the format and Base64 Encode. The output includes a CSS file containing the @font-face object that we can apply to entities in our site to style them with the font.
We aren't done just yet, as custom CSS doesn't work in Power BI. We need to write our text within an SVG (Scalable Vector Graphics) image which we can then encode to be displayed in our report.
Create SVG Image
Jumping over to Power Query, let's set up our parameters. In the example, I have created 6 parameters to hold the Base64 for 6 different fonts. I have also created a table manually in Power Query with a row for each font:
I use a simple 'if' statement to map the parameter to the correct font. The parameters help me debug or update the Base64 without modifying the main query.
Next we need to add our SVG code. In our SVG we create the @font-face reference and then add some text to render in the image, assigning it the custom font. Using Text.Combine(), we insert our Font Size and Base64 values too.
Text.Combine({
"<svg viewBox='0 0 1500 200' xmlns='http://www.w3.org/2000/svg'>
<style>
@font-face {
font-family: 'DynamicFont';
src:url(data:application/font-woff2;charset=utf-8;base64,", [FontBase64], ") format('woff2');
font-weight: normal;
font-style: normal;
}
.custom { font:", [FontSize], "px DynamicFont; fill:#000000; text-align:center; }
</style>
<text x='0' y='120' class='custom'>", [TextContent], "</text>
</svg>"}, "")
)
'DynamicFont' is the name we assign to our custom font - this could be anything we want. The viewBox is also important as this is the 'canvas' we draw our SVG image on. If the image appears cut off once rendered then we should modify these numbers.
Now we need to encode our SVG image, we do this with the Uri.EscapeDataString() function which gives us illegible code:
Finally, we prefix our encoded text with "data:image/svg+xml," which tells the browser to render this as an SVG image. We have one final problem before we load our data.
Power BI columns have a maximum length of 32,766 characters when storing strings. It is very likely that our encoded images breach this limit. The good news is that Power BI measures do not subscribe to this limit. So what we need to do is break up our image string for the purposes of loading it into columns, and then join it back together in a measure.
There is probably an easier way to do this, but for the purposes of demonstration, I use substring functions to break up the text into blocks of 32,766 characters. I then Unpivot these columns, putting the substrings onto rows, and filter out any blank rows. This gives me something like this:
Lastly I take the number from the Attribute column and use this as an index to show which order the substrings run in:
Making the Visuals
Now we can load! Once finished, we make a simple measure using the following DAX:
Viewable Font =
CONCATENATEX(
Images,
Images[Value],,
Images[Index], ASC
)
This joins the substrings back together into one string, making sure we use our Index column to get them in the correct order. All we need to do now is categorise this measure as an Image URL and apply it to some visual that can render images. The below is the output using a Matrix:
In Summary
I hope this has been interesting, it certainly is more of a 'proof of concept' than something to put into production in lots of projects, but it has been useful to me on occasion. Power Apps also supports this method but has encoding functions baked into Power FX and so can generate these images on the fly, instead of requiring a refresh like Power BI.
If the topic is interesting, here is a good article on this (from a general perspective, not just Power BI):
Using embedded Base64 Fonts. I recently stumbled upon an easy and… | by ChiefORZ | Medium
Also, feel free to download my PBIX below if you're having trouble getting things going yourself.
Dynamic Text (zip)
Download