Localizing the Data Model - Part 3

[Greg_Deckler]

Introduction

OK, so as I mentioned in Part 1, a user in my user group recently asked me about localizing a Power BI data model including table names, column names, measure names as well as titles for visualization, etc. I figured I'd break this subject up into chunks so there will at least be two parts to this blog article, maybe three or more. In Part 1, we localized the names of tables, columns and measures in our data model. In Part 2, we managed to get testing working using Power BI Premium and used a localization table and measures to solve for localized titles. However, because axis titles and legends do not have the magical fx button, we couldn't localize those things and we identified an issue with the actual translation of things like month names that were in the data itself. Let's pick up where we left off.

The Easy Way

Browser translation. You see, all modern browsers have the capability of translating a page in a foreign language. So, to test this, I created a completely unlocalized copy of my report, which looks like this in English:

So I created custom axis titles, legend, visual title, etc. Now I simply asked Edge to translate the page to German and here is the result:

I don't know much, if any German, but this looks reasonable to me. And hey, it even translated the names of my months to German as well. This means that the data within my model can be translated to other languages automatically! That's pretty fancy! Better yet, when I edit my report, even my Visualizations and Fields panes are translated to German:

And mind you, this works in either Pro or Premium so no need for Premium at all if you use the browser's translation feature!

So, are we done? I'm going to say "yes" for all practical purposes because if you don't use this technique, things start to become infinitely more difficult. Besides, this solution is "elegant". Why reinvent the wheel when people have already put so much time and effort into translation technology that simply requires a bit of user education to learn how to use? No need to translate anything and no need for Premium!

The Hard Way

OK, to demonstrate just how difficult this becomes if we don't use browser translation, let's first tackle what options we have for those text elements that do not include the fx button. Well, we could theoretically use our localization table and measures to create Card visuals that displayed the correct localized text and overlay the appropriate visual elements. But, that's not exactly an elegant solution. And, since you can't rotate Card visuals, you really don't have any options for your y-axis title. And how exactly do you account for things like Legend text that is significantly shorter or longer in one language versus another?

And what about translating the actual data like the weekday and month names? That's even more of a pain. The first thought I had was what if I built a simple table of just the month names with additional columns for each localization. I could relate this to my Dates table and then build a hiearchy for the x-axis of each of the month name columns and then use Object Level Security (OLS) so that only one of the columns could be read... Let me be clear, this does not work in the slightest. Oh OLS, you useless, useless, trash feature you. OLS, you have failed me for the last time!! You see, the minute Power BI attempts to display a visual for which a column "doesn't exist", it does not handle the issue gracefully, you just get a broken visual. And thus, why OLS is essentially a useless "feature".

Oh well, let's try something else and to make this just a tad more realistic, let's create a quick Facts table and set the Value column to Currency (Currency General):

Facts = 
    VAR __Table = ADDCOLUMNS('Dates',"Value",YEAR([Date])+MONTH([Date])+DAY([Date]))
RETURN
    UNION(__Table, __Table, __Table)

We now create a "Dates2" table like this:

Dates2 = 
    UNION(
        ADDCOLUMNS(
            'Dates',
            "Language","en-US"
        ),
        SELECTCOLUMNS(
            ADDCOLUMNS(
                'Dates',
                "Language","de-DE",
                "German Month",
                    SWITCH([Month],
                        "January","Januar",
                        "February","Februar",
                        "March","Marz",
                        "April","April",
                        "May","Mai",
                        "June","Juni",
                        "July","Juli",
                        "August","August",
                        "September","September",
                        "October","Oktober",
                        "November","November",
                        "December","December"
                    )
            ),
            "Date",[Date],
            "Month",[German Month],
            "Weekday",[Weekday],
            "Year",[Year],
            "Language",[Language]
        )
    )

So now our Dates2 table has 2 rows for every date that either has an English language month name or a German language month name as well as a Languae column that specifies which language is used in the row. We create the relationship between the two tables and it is Many-to-Many. Ok, Many-to-Many is not the end of the world, we setup two RLS roles, English and German which essentially have RLS rules like:

[Language] = "de-DE"

And we test and it works. Sure, it looks a little strange if you don't use an RLS rule with both English and German month names being in the axis but we could just enforce the rule that everyone needs to be a member of an RLS role. But, it works for an extremely simple data model. I can't imagine trying to do this technique for any kind of complex data model.

Conclusion

Browser translation is your friend. 

[Anonymous]

Hi Folks,

I tried to use the browser translation, but curiously enough this is not an option for a power BI service report page. The translation icon does not appear. How to you implement the browser translation in Edge or Google Chrome ?

[Greg_Deckler]

@Anonymous So what I have found to be the most consistent is if I am in the Service and viewing a report, over in the Filters pane, right-click and choose "Translate to xxx".

[Anonymous]

@Greg_Deckler ? OK I can see it now, it translate to the single local language the user has declared (Translate in French for me).  Thank you very much.