I have heard this question come up now and then where someone wants to separate the names and columns seen by users from the names and users used in SSAS and in the reports. The main benefit this has is that name changes will not result in broken reports.
Turns out there is a neat trick that will allow you to do this. The trick is for you to add a translation to the model in the same language as the culture of the model is set to. As you know at the time of writing Power BI doesn’t support translations (it is planned though: https://docs.microsoft.com/en-us/business-applications-release-notes/october18/intelligence-platform/power-bi-service/dataset-metadata-translations) but when you add a translation for the default language (again the same as the model) Power BI will pick it up automatically 
.
Lets take a look. First you need SQL Server Tabular 2016 and up or Azure Analysis Services to support translations. Next I create a new model in SSDT and to make sure I check the culture by looking at the BIM file (using code view):
Now we need to add a translation for the same language. Unfortunately the tool doesn’t allow you to add the same language as the model (oops I was the PM who added this feature..) but it is easy to change later. Close the bim file and double click the model again to open it in the visual editor and select manage translations under Model in the top toolbar. Now add any language, select the language and press export.
Now open the JSON file with your favorite editor:
And change the name to the same string as the culture of your model and add the translations.
Save the file and load it back in, go to Model, Translations, Load translation and select the JSON file again.
Now you see the translations is added:
and now when I connect to that model using Power BI I see the translated names:
If I now use them in a visual it shows the translated name:
If I now rename the field using translations again to something else the visual still works and now has the same name (after a refresh):
Nifty trick
This is such an amazing feature as it will give so much flexibility. Too bad I didn’t see this article earlier. However, I have a question. The exported file need to be part of the same solution and needs to be included when promoting to other environments ? I guess I am looking for advice on best practices on where to store this translation json file.
Best,
SN
You don’t have to store this translation file, the translations themselves will become part of the model after you commit them (you can see them in the bim file). The json file is just a convenience feature to make editing easier.
Hi Kasper,
Does this trick also work for Excel?
it does !
Hello,
This does not create a default translation doesn’t it ?
If a user ha a different (from the cube one) locale identifier, without any translation defined for, I think he obtains the technical labels and not the default translation labels.
Can you confirm ? Is there any solution for that ?
Hi Kasper,
this is interesting, but what I really need is to be able to have member caption translations. One of the main reasons why we cannot use Tabular models in our line of work is that the UID of the member is completely tied to the description. This means that if the descriptions change, our queries / reports will break.
I was hoping that translation columns would allow me to have one column in the model language, and then display a different column in the users’ language to get around this issue. Do you know if this feature (or the ability to set a key/description pair) is on the roadmap?
Cheers,
D.
Hoi Kasper, zou het kunnen dat dit nu niet meer werkt? Zou het ook kunnen zijn dat dit komt omdat ik SQL 2017 gebruik en jij in dit artikel SQL 2016 gebruikt?
Groeten
Hi,
Reports in existing power model breaking after pointing to an ssas with translation although translated names are exactly similar to the ones used in the report. Can you please help
Hello ! I followed all the steps above, however I do not see the “translated” names on Power BI, only on Excel. Is there an extra step I need to do to see the translations on Power BI interface ?
Thank you!
Make sure you set up the model in the right default locale (same as the model)
Hi Kasper,
Is it possible to take this approach with a strictly PowerBI model (July 2020 version). I.e. one that does not connect to a separate SSAS tabular model. I saved my model as a PowerBI Template (*.pbit) and opened it in Tabular Editor. I then exported the translation file (en-US) as a JSON file, but it doesn’t have the the same elements as an SSAS tablular model, especially after the cultures section.
Taking a leap, but I am going to try and replicate my PowerBI model in SSAS, create the translation JSON file and see whether I can import it into my PowerBI model.
Thanks,
Kelly
You cannot do it with Power BI yet in a supported way. (you can do it but dont call MS if it breaks..). You’ll have to be a bit more patient.