This post is also available in: de

Hits: 324

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.
 


 

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:

Create new parameter
Create new parameter

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).

Parameter "pmt_Language"
Parameter “pmt_Language”

Adjust column headers

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

Table Sales
Table Sales
Table Customers
Table 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:

Sales in German
Sales in German

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"}})
Customers in German
Customers in German

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].

error message DAX
error message DAX

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.

Diagramm with name und measure in German
Diagramm with name und measure in German

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.

Parameterwert ändern
Parameterwert ändern

English” is selected as the new language.

Select parameter value "english
Select parameter value “english

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

Apply changes
Apply changes

Now we immediately receive an error message in the visual.

Error message missing column
Error message missing column

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

English Diagramm
English Diagramm 

At the moment the report looks like this:

Diagramms in English and German
Diagramms in English and German

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.

Settings Bookmark english
Settings Bookmark english

And here’s the same for the German bookmark:

Settings Bookmark deutsch
Settings Bookmark deutsch

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.

create button for bookmarks
create button for bookmarks

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

Button text english bookmark
Button text english bookmark

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

assign action
assign action

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.

Report in English language
Report in English language
Report in German language
Report in German language

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!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: