Localizing the Data Model - Part 2

[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. Now, where we left off in Part 1, we had localized the names of tables, columns and measures in our data model but had yet to actually test it. So we solved that little dilemma and will now move on to localizing visual titles.

Hooray! It Works!

OK, so I read a number of blog posts on this subject before deciding to tackle this series. But, I stuggled with testing. Many of the blog articles I read basically took you through the steps and then were like "And then you just switch your regional settings and tada!". Well, not so fast. There was an important nugget of information left out in a lot of those blog articles (not all, but many). The "tada!" works if you happen to publish to a Premium workspace. Luckily I have access to a Premium Power BI tenant (thanks @chass!) and once I did this then everything started working. So, note the table, column and measure translations will not work in Pro. I can be reasonably certain that they will work in Premium Per User (PPU) and I have no idea about Embedded. Anyone that knows and has tested, please let me know.

So, now that we know how to test, let's setup a test. I published my file out to the Premium tenant and created a new report based on a Live connection to the Power BI dataset. We will call this new Power BI file, LocalizationReport. Once I did this, when I went to Options and settings | Options | Regional Settings, switched the Application language to German (Germany) then restarted Power BI, my Fields list looked like this:

How Well Does This Actually Work?

Spiffy, let's build a visual. Only, I'm switching back to Englisch first because I don't know a lick of Deutsch. So, here is the wicked visual that I came up with and it's configuration.

I left everything default except the font sizes. And, here it is in German:

OK, other than my month names still being in Englisch (more on that later) this looks pretty decent. My title, legend and axis titles have all been translated to Deutsch. Even my data labels have been translated to...wait, that's not right... Actually it is in a way, if you have a visual that sums up something and have it set to auto-display units then it will come back in Tsd. (tausend) instead of K (thousand) for example. Anyway. I can also tell you that the tooltips also display in Deutsch as well even though I forgot to include that in my screen shots.

What About Custom Titles and Stuff?

So far so great. But, what if you want to have non-default titles, axis titles and legends for your visuals? How does that work? Well, if you guessed "it doesn't" you would be correct. The minute you change the title for the visual for example, it shows up with that title in either language. Hmm...what to do? Well, we can actually get around this by using an "unofficial" DAX function, USERCULTURE. 

Now, I call this function "unofficial" because it does not show up in the official Microsoft DAX function reference documentation and when you use it you get the red squiggly underscore line in the formular bar and for good measure type-ahead doesn't work:

Despite all that, as you can see in the image above, the function actually returns the language code for the current user's application language. That begs the question, what other undocumented DAX functions are lurking out there? 

Dang. Oh well, kudos to anyone who gets that reference. Moving right along, we can use this function to sort of solve our problem. What we can do is create a new table using an enter data query. I called mine Localization:

Now we just need a measure like the following: 

Count of Date Title = 
    LOOKUPVALUE(
        Localization[Localization],
        Localization[Text],"Count of Date",
        Localization[Language],USERCULTURE()
    )

You could also use CALCULATE or MAXX(FILTER(...),...) if you are so inclined. Then all you need to do is to the use the fx button for your Titeltext, use this Count of Date Title measure as your Basierend auf Feld (Based on Field) and tada! Now your custom title is works in Englisch, Deutsch and Spanisch!

OK, OK, maybe the "tada!" is a bit premature. Unfortunately that magical fx button doesn't exist yet for legends, axis titles and pretty much any other text. Oh but colors, oh, every color setting imaginable has the magical fx button. Because I really need to link the color of my axis titles to a formula...

Conclusion

Being able to tie axis title colors and legend colors to formulas is dumb. Well, OK, just not nearly as useful as if the time had been spent enabling that feature for the text-based fields. I can't imagine a use case for formula-based axis title colors and legend colors but I'm sure one exists somewhere on the planet. But, it would be really useful for localization purposes if axis title text and legend text had this feature.

Stay tuned, I have more coming in this series. Like how to solve those month name display issues.

[Greg_Deckler]

Part 3: Localizing the Data Model - Part 3 - Microsoft Power BI Community