This post is also available in:

Hits: 21664

As of today (October 22, 2018), it is not possible to automatically create multilingual reports in Power BI.
This feature is mentioned in the Dynamics365 Autumn Update Notes (Link), but it is not yet clear, at least not for me, when the feature will be released. I hope of course soon…
That’s why I show in this article how multilingual reports can still be created with a small workaround.

If you want to read more about Power BI, subscribe to our newsletter.
 

Name

Vorname / First Name

Email address:

Leave this field empty if you're human:

 

Starting point

It may be a customer request that the reports have to be displayed in either English or German. Both the column headings in the tables and the measures should be displayed in the required language.
However, the contents of the individual fields do not have to be changed. This means that the product name “RoadBike” can remain unchanged. If you are looking for a solution for changing the values in a column, I can recommend you the Youtube video by Ruth Pozuelo (curbal.com) and the one by Adatis .

Challenges

In order to meet the above-mentioned customer needs, the following challenges must be mastered:

• Depending on the selected language, the column headings have to be adjusted
• Furthermore, the measures must be translated (for example, “revenue” must become “Umsatz”).
• The visuals should be displayed depending on the selected language.
• The report recipient should be able to change the language with as little effort as possible.

Create language parameter

The first challenge is easy to solve. What we need is a parameter in Power Query.
I named the parameter pmt_Language and have two choices: “German” and “English”.

Parameters can be easily created in Power Query:

The parameter needs a name (1) as well as a selection option (2). In this case this will be a list which is defined below (3) with “Deutsch” and “English”.
As default value (4) I have defined “Deutsch”, which also corresponds to the current value (5).

Adjust column headers

For this example I have created a data set consisting of two tables “Sales” and “Customers”.

With the created parameter we can now also easily adjust the column headings.
To do this, we enter an if-then condition in the Advanced Editor. This condition checks which language was selected in the parameter. In the case of English, the table remains unchanged, since the source language of the table is English.
If Deutsch is selected, the column names must be adapted. For Sales it looks like this:

Language = if pmt_Language = "english" then #"Changed Type" else
Table.RenameColumns(#"Changed Type",
{{"OrderDate", "Bestelldatum"}, {"CustomerKey", "KundenNr"}, {"SalesTerritoryKey", "VerkaufsgebietNr"}, {"SalesOrderNumber", "BestellNr"}, {"SalesOrderLineNumber", "BestellPositionsNr"}, {"UnitPrice", "Stückpreis"}, {"ExtendedAmount", "Verkaufspreis"},
{"ProductStandardCost", "ProduktStandardKosten"}, {"TotalProductCost", "TotalProduktKosten"}, {"SalesAmount", "Verkaufsbetrag"}, {"TaxAmt", "Steuerbetrag"}, {"Freight", "Frachtkosten"}
})

The result for German is as follows:

The same is done for the table “Customers”:

Language = if pmt_Language = "english" then #"Removed Other Columns1" else
Table.RenameColumns(#"Removed Other Columns1",{{"CustomerKey", "KundenNr"}, {"Fullname", "Name"}})

Measures

The sales measure should also change the name depending on the language. To do this, we create two measures:

Umsatz = sum(Sales[Verkaufsbetrag])

Revenue = sum(Sales[SalesAmount])

When writing the “Revenue” measure, Power BI immediately responds with an error message. Since the language is set to “Deutsch”, DAX does not find the column Sales[SalesAmount].

We’re ignoring that mistake right now.

Create visuals

Next, at the center of the report layout, we create a normal bar graph consisting of the Name column and the “Umsatz” measure.

Changing the parameter

The report user should be able to change the language as easily as possible. All we have to do is change the value of the parameter in the user interface.

English” is selected as the new language.

Power BI tells us that we still have to activate the change.

Now we immediately receive an error message in the visual.

We can ignore the message and create a new bar chart on the left, this time with the English terms.

At the moment the report looks like this:

Create bookmarks

Now we create two bookmarks. The first bookmark “bm_english” hides the German visual, the bookmark “bm_deutsch” shows only the German visual and hides the English visual.

Here are the settings for the English bookmark. The red mark shows the hidden status of the German visual.

And here’s the same for the German bookmark:

Control bookmarks via the GUI

Unfortunately at the moment bookmarks cannot be selected depending on values. The selection of the selected bookmark is done via the user interface. Therefore we have to create two new buttons and specify the bookmark you want to use.

The bookmark buttons can be created as follows.

The assignment of a label for the button is still missing, here on the basis of the English example:

.. and the associated action for the button, activating the bookmark “english”.

The same settings must also be made for the German button.

User interactions

The user only needs to change the parameter and select the desired bookmark to change the language of the report.

The two diagrams could also be overlayed so that the user does not see any difference.

Conclusion

With a few simple steps, the report language can also be adapted in Power BI. Of course, the work with more extensive data sets with a large number of tables is bigger.
But until Power BI can handle multilingual reports, this is a viable workaround.

You can download the file of the post here.

Find out more about Power BI here.

Happy Querying!