As Power BI designers, we always put our focus on giving the best experience to the users that will interact with our dashboard and reports. Having said that, it is important to remember that small details can make a big difference. Let’s analyze the following scenario. We have a simple report that shows some useful insights about US states:

‍

So far so good, but it turns out that this report will be consumed by several people from around the globe, whose mother tongue isn’t necessarily English. Wouldn’t be nice to add multilanguage support to our visualization? Let’s see what we can do about it.

‍

First approach

A possible solution for this requirement would be to create a different copy of our table visualization for each of the languages that we want to support. These tables will have different header titles based on the required language, however, only one of those tables will be shown depending on the language selected by the user. This would also affect the visual titles. The hide/show effect can be achieved using bookmarks:

‍

‍

Nevertheless, this method has a big downside. As the list of supported languages grows, more visualizations and bookmarks will need to be created, making it hard to scale in the long run. Just think about having to support 20 different languages, the report would rapidly turn into a nightmare.

On top of that, title translations will end up being hardcoded into the visualizations, instead of using information provided, for example, from a database.

‍

Custom visualization

Fortunately, there’s a better option to the one described above. What if we could have just one single table with dynamic header titles that change depending on the selected language? Sadly, the built-in table visualization provided by Power BI does not support this feature, since the header titles can only have a static value:

‍

‍

Even so, there’s a way to get this feature done and that’s by creating our own custom visual. If you don’t know what a custom visual is, you can check this introductory article where we talk about it.

Key features our custom visual will have:

• The visual will consist of a simple and plain table, allowing to have multiple columns and rows.
• The table columns can have either a static header title or a dynamic header title.
• Dynamic header titles text will be defined based on a value (the language) selected by the user on a slicer. Therefore, if that value changes, the header title will dynamically change as well.

Data roles

Data roles define the type of data that our visualization will receive as input. For this scenario, we will implement the following ones:

• Static column values: defines the columns that have a static title, just like a regular table.
• Dynamic column values: defines the table columns which their header title must be dynamic. Each dynamic column value must have a corresponding dynamic column header.
• Dynamic column headers: defines fields that work as the source of the titles for our dynamic columns:

The association between the dynamic column values and dynamic column headers will be done through their data role index. For example, the Dynamic column header with a data role index of 1 will be linked to the Dynamic column value that has a data role index of 1:

‍

‍

Using the custom visual in report

Once we create the custom visual, it’s time to go back to the report. Its current dataset consists of a single table with information about US states:

‍

‍

However, this is not enough. In order to add multilanguage support to our report, we need to have a record for each title translation that can be displayed.

The best way to maintain this data is to create a new table, which will be responsible of storing these translations. Each row will represent a different language:

‍

‍

Please, notice that this table includes translations not only for column titles but for visual titles as well. For this sample English, Spanish and French will be supported:

‍

Adding visualizations

First, we will add a slicer to let the users choose their language. The filtering is set to single select to avoid selecting multiple languages at once. The goal is to always keep the Internationalization table filtered by a single row/translation. This slicer will grow automatically as we include more languages to the Internationalization table, making it easy to scale:

‍

‍

Now we need to import and configure the custom visual. Let’s analyze the Visualizations pane:

Static column values data role

This data role will contain only a single field and that’s the ID. This is because this column is the only one that does not require multiple translations and therefore its header title will always remain static:

‍

‍

Dynamic column values data role

Here will be stored the columns that need to have a dynamic header title. These fields are retrieved from the States table:

‍

‍

Dynamic column headers data role

Finally, we need to determine the fields that will serve as the title of the dynamic column values. These are retrieved from the Internationalization table:

‍

‍

Remember that each title on the Dynamic column header data role will be associated to the Dynamic column column value field that shares the same data role index. That’s how we programmed our custom visual. For instance, State column will have its header defined by State Title field, since both share the same order, which is 1.

Now, when switching between the available languages, the header titles will change dynamically to the corresponding translation:

‍

‍

Last but not least, we need to add dynamic titles to both slicer and table visualization. The solution is simple, instead of using a static text box, like in the first approach, we’ll opt for a card. This visual allows us to generate the title based on a measure:

‍

Final result

And that’s it, the custom visual is ready to be tested. This is how the final product looks:

‍

Both the source code of the custom visual and the sample report can be found in this public repository along side with more technical details about how this demonstration was achieved.

Originally published by Hernan Demczuk for SOUTHWORKS on Medium 08 January 2021