previous article
Increasingly frequently, Power BI is used not only as a self-service tool, but also forms the basis of operationalized analytics applications at many internationally active companies and even in the B2B sector. This, in particular, requires Power BI reports to be displayed in multiple languages. Here you have to decide: Do I create one report per language or try to make my report filterable by language? This article considers the latter approach.
Power BI allows premium users to translate metadata (e.g. table and column names). But as regards the data to be displayed, we as Power BI developers need to become creative. Here we are faced with the challenge of having to translate tabular data as well as headings and texts.
Data modelling for multilingualism
The core of multilingualism is the data model. Considering a simple model which only contains a fact table and a date dimension, we here want to display months in German as well as English.
To achieve this, we need the date dimension in a structure comprising one row per language and date value. However, this violates the one-to-many relationship between dimension and fact.
We can restore this implicitly via a bridge table in combination with a language filter. The central challenge now is to transfer the filter from the "Language" table to the "Date" table, but without creating an ambiguity in the model. We can achieve this with virtual relationships in our DAX measures (Relationships in Power BI and Tabular models - SQLBI). Here it is important to know that virtual relationships act as an inner join.
To be able to also translate the titles later, we need a table similar to the date dimension. This is not linked to the facts, and can therefore be physically related.
ETL for the multilingual model
The translated texts of the data and titles can be stored centrally in a "Translation" table. Suitable for this is a structure which, in addition to the translation, also includes the language, the table, the column and its original expression of the column value. This table can then be further transformed, e.g. in Power Query. Transformation takes place in two steps.
For the date dimension, "Translation" is first filtered to TableName = "Date", and then connected to the date table's month column via "ColumnName". This expands each row of the date table by the number of languages. Due to the virtual relationships here, we have to keep in mind that each value is translated in each language (referential integrity).
If this cannot be guaranteed, possible contingency solutions must be considered. As an example, expressions without translations can be determined here by means of a right anti-join, and a default value can be defined by means of a cross-join with the language table.
Transformation for titles is comparatively simple. For the title table, the "Translation" table is referenced and filtered through TableName = "Title".
Multilingualism in reports
In reports, we now benefit from the preparatory data modelling and transformation.
We can use the month column, language filter and DAX measure for sales (including virtual relationship) to display a translated chronology. For the heading, we use the function of dynamic titles.
By simply changing the language filter, we can now set titles as well as month names on the axes to English.
Considerations & limitations
The presented approach shows how to use data and headings in Power BI in a scalable way in multiple languages. However, there are some aspects to consider during implementation.
Firstly, it must be borne in mind that virtual relationships result in a loss of performance compared to physical relationships. Furthermore, we increase the cardinality of the dimensions, which worsens compression in the data model. It becomes larger, and some operations such as non-additive calculations slow down disproportionately. This can sometimes be counteracted by optimized distribution of the load from the storage and form engines (Analyzing the performance of DISTINCTCOUNT in DAX - SQLBI).
Secondly, the options of dynamic labels are limited. Axis titles, legend titles, column names and measure names cannot be translated using this technique. However, metadata translation can be of help here.
Multilingualism in Power BI includes translations of metadata as well as the data and text to be displayed. Because this is currently not available "out-of-the-box", multilingualism of data requires intelligent data modelling. Though this raises the degree of complexity, it offers a scalable and high-performance solution.
