Enhancing Multi-language Support in Power BI with Field Parameters
Written on
Chapter 1: Introduction to Field Parameters
The introduction of field parameters in Power BI marks a significant advancement in data visualization capabilities. This feature opens up new possibilities for implementing translations effectively. Below is a comprehensive solution utilizing field parameters to facilitate multi-language support.
Photo by Towfiqu Barbhuiya on Unsplash
Section 1.1: Power BI and Tabular Data Models
Power BI and SSAS Tabular models natively support the translation of metadata. However, they lack a built-in mechanism for translating data itself. For those looking for a solution to implement multi-language support for tabular models, SQLBI's SSAS Tabular Translator tool can be utilized. You can refer to the Microsoft documentation provided in the References section.
To illustrate, consider a Date table featuring multiple columns for month names, one for each language. Adapting the data model is essential for such scenarios. A potential structure for this model is shown below:
Figure 1 — Multilanguage Data Model (Figure by the Author)
In this model, a single Dimension table holds multiple entries for various languages, connected to a Bridge table that mitigates many-to-many relationships. Moreover, row-level security can be implemented to restrict user access to Dimension rows based on their language preference.
Despite its advantages, this model comes with challenges:
- Each Dimension table expands the original size (e.g., tripling for three languages).
- Complexity increases during the dimension table load process.
- Many-to-many relationships can create ambiguity.
- Row-level security must be configured for numerous tables.
- Each user’s preferred language must be stored.
- Users cannot switch their language preferences easily.
- This method may slow down reporting processes.
While alternatives for implementing multi-language support exist, they often involve creating separate reports for each language or developing multiple visuals. However, with the May 2022 release of Power BI Desktop, the field parameters feature emerged as a promising solution.
Section 1.2: Understanding Field Parameters
Field parameters allow users to determine which field (column or measure) is visible in their visuals. For example, you can add a Slicer to your report to manage which Measure appears in a Visual. Further details about this feature can be found in the Power BI May 2022 Feature Summary.
As of this writing (July 2022), field parameters are still in Preview, so caution is advised as the feature may undergo changes.
Chapter 2: Implementing Field Parameters
My dataset includes a Date table that contains columns for month and week names in multiple languages:
Figure 2 — Extract from the Date Table (Figure by the Author)
To enable Slicer functionality for selecting which column appears in my Visual, navigate to the Modeling pane and select “New parameter” followed by “Fields”:
Figure 3 — Add a New Field Parameter (Figure by the Author)
Here, you can choose the fields (columns) to include in the parameter, which will subsequently appear in the Slicer:
Figure 4 — Define Fields as Parameters (Figure by the Author)
Upon clicking Create, a new calculated table will be generated, and a Slicer will be added to the report:
Figure 5 — Field Parameter After Creation (Figure by the Author)
This calculated table comprises three columns: the first serves as the label for each row, the second employs the NAMEOF() function to retrieve the field’s name, and the third is the order index for sorting the first column. The latter two columns remain hidden.
You can modify the table name by adjusting the expression and rename the columns directly in the Data model. The displayed names in the Slicer and Visuals can be altered by changing the text in the first column:
Figure 6 — Edited Field Parameter (Figure by the Author)
Next, incorporate a Visual into your report and include the newly created Field parameter:
Figure 7 — Field Parameter in a Report (Figure by the Author)
When you adjust the selection in the Slicer, the content displayed in the Visual will also change accordingly:
Figure 8 — Month Name in Italian (Figure by the Author)
You have now successfully implemented a basic structure for multi-language support in your report.
Section 2.1: Expanding the Field Parameter Table
The next exciting phase involves expanding the Field parameter table. When multiple fields are added and several selections are made in the Slicer, Power BI generates a hierarchy.
SQLBI has created a video illustrating this functionality (see the References section). This feature can be leveraged to create multiple hierarchies—one for each language—within your calendar columns:
Figure 9 — New Field Parameters (Figure by the Author)
Although you cannot add multiple elements with the same name, you can reference the same column under different names. However, the current setup requires selecting multiple columns in the Slicer, which is not user-friendly.
To address this, you can manually add a language column to the table:
Figure 10 — Expanded Calendar Field Parameter Table (Figure by the Author)
This additional column can then be renamed and included in the Slicer:
Figure 11 — Use of the Calendar Hierarchy in a Report (Figure by the Author)
Section 2.2: Adding Multiple Hierarchies
Now that one hierarchy exists in the field parameter table, the next question is how to implement multiple hierarchies for different languages without overwhelming users with multiple Slicers.
For instance, if you have additional columns for product names in various languages:
Figure 12 — Additional Product Column (Figure by the Author)
You can incorporate these into your existing field parameter table by adding another column for the hierarchy:
Figure 13 — Field Parameter with Hierarchy Column (Figure by the Author)
When adding the field parameter to a Visual, remember to set a filter for the Hierarchy:
Figure 14 — Field Parameter with Hierarchy Selection in the Report (Figure by the Author)
Through these methods, you can continually expand your Field parameter table with multiple hierarchies, allowing users to select their language preferences seamlessly. Users can also create personal bookmarks for easy access.
Conclusion
Field parameters significantly enhance the Power BI feature set, providing a versatile tool for creating user-friendly reports.
Photo by Janko Ferli? on Unsplash
The possibilities with this feature are extensive, and in this article, I showcased just one way to improve report usability and user experience. Thank you for your attention.
References
SQLBI has published a video about field parameters that inspired the solutions presented in this article:
For further information, check Microsoft’s feature documentation: Let report readers use field parameters to change visuals (preview) — Power BI | Microsoft Docs. The Contoso sample dataset, utilized in this article, can be downloaded for free from Microsoft.