cancel
Showing results for 
Search instead for 
Did you mean: 

Transform ‘report language’ based on buttons

Scenario: 

With the development of economic globalization, users’ demands for the PBI report to be translated into their familiar language are increasing. 

  

However, Power BI doesn’t support the conversion of basic data language since PBI cannot touch your data. In this case, how can we fulfill this demand? 

 

Sample data: 

Name 

Chinese 

Math 

English 

zhao 

100 

85 

70 

qian 

95 

95 

85 

sun 

90 

85 

85 

li 

85 

60 

85 

zhou 

85 

85 

85 

wu 

85 

85 

90 

zheng 

85 

85 

90 

wang 

85 

90 

85 

 

Operations: 

  1. Go to Power Query Editor and transform your data by yourself. My operations are simple: 
    • Select the column “name” in Table Score and UnPivot other columns; 
    • Rename columns. 
     
  2. Close and Apply. 

   

     3. Create measures and visuals based on your requirements. Create report pages named “Contents”, “Basic Information” and “Score and Reward”. 

 

1.jpg

2.jpg

3.jpg

 

  1. Duplicate your measures with different names, such as: 
    • Measures (English) 
      Average Score = AVERAGE(Score[score]) 
      Number of students = DISTINCTCOUNT(Score[name]) 
     
    • Measures (français) 
      Nombre d'étudiants = DISTINCTCOUNT(Score[name]) 
      score moyen = AVERAGE(Score[score]) 
     
    • Measures (中文) 
      平均分 = AVERAGE(Score[score]) 
      学生数量 = DISTINCTCOUNT(Score[name]) 
    4.jpg   
  2. Duplicate your report pages, rename them separately and then hide them. 5.jpg

     3. Create a “LanguageTable” by “Enter Data”. 

 

Language 

English 

français 

中文 

 

     4. Create a Home page named “Home Page / Page d'accueil / 主页”. 

  • Add a slicer visual based on “Language” column from “LanguageTable” and set the slicer title as “Please choose the language. / Veuillez choisir la langue. / 请选择语言。”. 

 

  • Add a button. 

7.jpg8.jpg

 

    

Page Text Measure =  
SWITCH ( 
    SELECTEDVALUE ( LanguageTable[Language] ), 
    "English", "Click to page: Contents", 
    "français", "Cliquez sur la page: Contenu", 
    "中文", "点击转到页面: 目录" 
) 

Page Navigation Measure = 
SWITCH ( 
    SELECTEDVALUE ( LanguageTable[Language] ), 
    "English", "Contents", 
    "français", "Contenu", 
    "中文", "目录" 
) 

 

     5. In this way, we can jump between pages. 

 

     6. For the visuals, we can use measures to set titles or just manually enter titles in different languages. For example:  

9.jpg

 

Table visual title = 
SWITCH ( 
    SELECTEDVALUE ( LanguageTable[Language] ), 
    "English", "Average Score", 
    "français", "score moyen", 
    "中文", "平均分" 
) 

 

     7. For some columns that inevitably need to be displayed in visuals, it is recommended to duplicate it like below:

10.jpg11.jpg

 

 

     8. I have published the report to web: Transform ‘report language’ based on buttons. 

And the .pbix file is also attached at the end. Hope it works for you. 

 

 

Certainly perfecting transformation should still have the consideration of performance issues. Based on the operations above, more measures, calculated columns, visuals and report pages are included in the report. There’s one problem with this – will this affect the performance?  

  

There will only be a slight impact, which is caused by the repeated calculated columns. Since all pages except the homepage are hidden, when the report is viewed on the service, only the visuals and measures related to the page jumped to will affect performance. Therefore, there is not much difference in performance in this regard. 

12.jpg

 

Hope this article helps everyone with similar questions here. 

 

Author:  Icey Zhang

Reviewer: Ula Huang, Kerry Wang