Making multilingual reports in Power BI requires a lot of different elements. Translations can be added to PBIX files to translate column names, visual titles, etc. but these translations will not impact the data itself. One way to translate the dimensions themselves (star schema introduction) is to have a translation table that contains a display language and value column for each ID of the dimension. In the example below, the translation table would replace the original table and we could apply RLS to the Language column.
Data Model Edits
To make RLS work in this manner, that is to filter down one table and not the entire data model, there are a couple of things that need to be set up. First, you will need a bridge table for every translated table that has a unique list of the ID’s. Second, the RLS filter needs to be added to each of the tables that has a translation. Lastly, the relationships need to be re-established so that the translated tables are only related to their bridge table.
Add RLS Filters
In order to apply RLS dynamically in an embedded Power BI scenario, the locale needs to be passed through the embedded token as an argument within the UPN (dynamic RLS instructions ). Unfortunately, Power BI can only receive one value for each embedded token, so if you need true RLS on the report along with dynamic filtering for the translation, you will need to pass both arguments through the embedded token and separate them later. For example, if the UPN of the user was example@test.com, you can program the embedded token to send the default browser locale send by that user and the UPN of the signed in user in this format: UPN|locale.
It looks a bit goofy, but the pipe delimitator will allow us to separate out the two RLS arguments via the DAX function PATHITEM(). With PATHITEM(), we can tell the RLS filter to either choose what’s before the pipe (the UPN in this case) or what’s after (the locale) to pass through for the role filter. The filter for the example above would be:
[Language] = PATHITEM(USERPRINCIPALNAME(),2)
Once you have applied this filter to all of the tables with the Language field, you’ll need to make some adjustments to your data model.
Reconnect Relationships
Per the diagram below, you will need to connect the bridge table into your existing data model. Remember, the ONLY relationship your translation table should have would be to the bridge table. Every other fact table can join to the bridge table. The joins are as follows – Translation Table has ONE bidirectional join as * to 1 to the Bridge Table, Bridge table joins to every fact table single direction with Bridge Table filtering the fact tables in a 1 to * relationship.
NOTE: do not use CROSSFILTER() in measures with tables that have RLS applied. They will break in the online Power BI service.
And that’s all there is to it! Hopefully this saves you time when building a multilingual, embedded report. Happy coding!
Additional Resources
- https://pbi-guy.com/2022/01/31/multilingual-reports-in-power-bi/
- https://pbi-guy.com/tag/row-level-security/
- https://nineboards.com/power-bi-embedded-with-dynamic-rls/#:~:text=%20Power%20BI%20Embedded%20with%20Dynamic%20RLS%20%E2%80%93,for%20our%20next%20steps..%20Select%20T…%20More%20
- https://docs.microsoft.com/en-us/power-bi/guidance/star-schema