When building an enterprise reporting solution with Power BI, a question that always comes up is how to handle translations. Large enterprises operate in various countries where people also speak different languages. So a report should be available in all frequently used languages. Ideally, you just create a report once and then a user can decide (or it is decided for him) in which language the report is displayed.
Power BI only partially supports this scenario and the closest we could get *before field parameters* were introduced is already very well described by Chris Webb’s blog post on Implementing Data (As Well As Metadata) Translations In Power BI – a must-read if you need to deal with translations in Power BI. Another good read on the topic is the blog post Multilingual Reports in Power BI from PBI Guy.
As you will quickly realize, the translation of metadata is already pretty easy as it is baked into the engine. Unfortunately this is not the case when you need to translate actual data values (e.g. product names, …). In the multidimensional version of Analysis Services this just worked like a charm as it was also a native feature but this feature never made it to Analysis Services Tabular Models, Azure Analysis Services or Power BI.
The current approaches when it comes to data and value translations are more workarounds than actual solutions. They probably work fine for small data models and very specific use-cases but usually fall short in performance, usability or maintainability when implemented on a larger scale enterprise models.
The recently introduced Field Parameters in Power BI give us a bit more flexibility here and another potential solution to implement data and value translations in Power BI.
Here is what we want to achieve:
- create a single report only
- support for multiple languages – metadata and column data
- only minor changes to the existing data model
How can Field Parameters help here?
Field Parameters allow you to select the columns you want to display in your report/visual on-the-fly. Based on the selection, the reporting engine decides which physical column(s) it needs to use in the query it generates and sends to the data model.
So we can create a Field Parameter for the different columns that hold the translated data values and already easily switch the language by changing the selection of our Field Parameter. This is how our Filed Parameter would be defined:
Translated ProductName = {
("product name", NAMEOF('DimProduct'[EnglishProductName]), 0, "en-US"),
("nom du produit", NAMEOF('DimProduct'[FrenchProductName]), 1, "fr-FR"),
("nombre de producto", NAMEOF('DimProduct'[SpanishProductName]), 2, "es-SP")
}
I did this for all the fields for which translated values are actually provided. Usually this is just a very small subset of all the available columns!
Translated MonthOfYear = {
("MonthName", NAMEOF('DimDate'[EnglishMonthName]), 0, "en-US"),
("mois de l'année", NAMEOF('DimDate'[FrenchMonthName]), 1, "fr-FR"),
("mes del año", NAMEOF('DimDate'[SpanishMonthName]), 2, "es-SP")
}
Translated DayOfWeek = {
("Day Of Week", NAMEOF('DimDate'[EnglishDayNameOfWeek]), 0, "en-US"),
("jour de la semaine", NAMEOF('DimDate'[FrenchDayNameOfWeek]), 1, "fr-FR"),
("día de la semana", NAMEOF('DimDate'[SpanishDayNameOfWeek]), 2, "es-SP")
}
As you can see, Field Parameters allow you to translate the metadata (first value) and also to define the column to use for the data values (second value, using NAMEOF() function).
To change all field parameters at once I introduced an additional 4th column that holds the culture/language of the current row which is then linked to another static DAX table that is defined as follows:
Language = DATATABLE("Culture", STRING, {{"en-US"}, {"fr-FR"}, {"es-SP"}})
Then relationships are set up between these tables:
In your report you can now simply use the column from the field parameters and add a slicer for the Language table to control which language is displayed. Note: this must be a single-select slicer as otherwise Power BI will build a hierarchy of the different languages!
Here is the final result:
(please use Full Screen mode from bottom right corner)
As you can see, we just created a single report that supports multiple languages for both, metadata and data values, allows you to easily switch between them and provides similar performance as if you would have built the report for a single language only!
There are still some open questions when it comes to translating all the labels used on the whole report which is already partially covered in the other blog posts referenced above but this approach brings us another step further to a fully translatable report.
Another nice feature of this approach is that you can also put security on top of the Language/Culture table so a user only sees exactly one row – the one with the language/culture of his choice or country. So a user would not even need to select the language but it would be selected for him automatically!Ideally you could even use the USERCULTURE() DAX function but unfortunately this is currently not supported in the PBI service. There is already an open idea for which you can vote if this is important to you.
USERCULTUER() DAX function is now finally general available also in the service: https://powerbi.microsoft.com/en-us/blog/userculture-dax-function-now-supported-in-power-bi-premium/
The .pbix file can be downloaded here: PBI_Translations.pbix
Thank you for this very intresting article Gerhard
Pingback: Language Translation via Power BI Field Parameters – Curated SQL
This is a great advance … but …
Not all visuals support dynamic labels/titles. Notably table (not matrix) visuals don’t for column headers, so their labels are fixed. This can be seen on the second page of your example report. That’s possibly the last of the commonly-used standard visuals (caveat emptor), but it is used in almost every Power BI solution.
Hopefully those remaining static labels/titles will be addressed in future updates to Power BI. Until then we’ll have to keep maintaining multiple PBIX files to achieve a fully multi-lingual solution (sighs).
I know, there is still some work to do by the Power BI team to make fully multi-lingual reports possible.
regarding labels/titles: names for measures, columns, tables, etc. can be translated using metadata translations which are already supported – but they not necessary play 100% together with this solution until USERCULTURE is also fully supported in the Power BI service
static labels/titles are another story but can also be addressed as shown in the two blog posts I referenced in the beginning
-gerhard
Wow, real cool. Is there a way to also switch measure names multilingual? In some Visuals or Matrix the Measurename appears in the Legend or Axis….
for translations of measure names and other meta-data you can use regular translations as described in the linked blog posts at the beginning of this blog post
another good source is also https://www.kasperonbi.com/setting-up-translations-for-power-bi-premium/
however, this does not yet work seamless in combination with what I described in this blog post
once USERCULTURE() works properly also in the PBI service, we could combine these two approaches
Great Thanks. It seems but that this than has to be premium…
thats unfortunately true but the only option at the moment
Hi Gerhard, fortunately the USERCULTURE function is now available (https://powerbi.microsoft.com/en-us/blog/userculture-dax-function-now-supported-in-power-bi-premium/). I was trying to find a way to use it together with field parameters in order to be able to translate the column data accordingly with the customer preferred language, but no success till now, since the USERCULTURE function can be used only in measures, so I’m not able to use it to try to filter automatically the report.
Did you find a way to use it dynamically in order to get a report 100% translated without using a manual parameter to select the language?
the idea I was referring to is to use RLS to limit the rows in the Field Parameter tables based on the users culture using USERCULTURE()
Oh I see! Now I got it, it’s a perfect solution! Thank you
Hi Gerhard, great solution! I was just thinking of this now that Field Parameters are published by Power BI. Will check your solution as month + day names are the last thing of my report to be translated.
Two quick questions:
– Do you know if this affects the performance of the report/ datamodel?
– Is this also possible with Week Start Day? I.E. change week start from Monday to Sunday.
Thanks!
performance impact should be neglectable as its basically just changing the referenced columns under the hood before actually running the query
regarding Week Start Day – it depends where you use it
if its in a measure you may be able to add a new column to your field parameters definition and reference it in your measure
Hi Gerhard,
Thank you for you quick reply.
The Week Start Day is currently setup in columns, and has date definitions for each possible Week Start, which has a one to many relationship with the regular date table. Therefor most of the work will be done in Power Query and when the model will be refreshed.
I will try out to create a similar setup regarding the Week Start as you have also done with the Month + Day Names. Creating a Field Parameter which the users can switch between, so that the week start will also change with this.
Hi Gerhard,
great solution. I am using Field Parameters in combination with Userculture (RLS). Do you know if it’s possible to change the Name of the Field Parameter (first column) depending on the language used with metadata translation? I cannot just make an additional row for each language since I am using the Field Parameter combined with a bookmark. Thank you in advance for your reply!
dont think thats possible but I think it should still work the way i described it in the blog using multiple rows, one for each language.
due to RLS it will filter down to on row anyway. In your visual you would just use the field parameter then – not sure how this would conflict with your bookmarks?
you might be able to add an additional column to your field parameter to group your rows and then set the bookmark on that new column?
Very interesting but I don’t want the user to have to choose his language Is there a way to derive it from the URL in case of a report in PowerBI Embedded?
If I am not mistaken you can pass filters from PBI Embedded to the report – this should work similar to a slicer on the page
You can set the language via embedConfig property, see https://learn.microsoft.com/en-us/javascript/api/overview/powerbi/configure-report-settings
Gerhard is one of
the leading technical experts around Microsoft BI in Europe