parkmodelsandcabins.com

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.

Visual representation of Power BI data model

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:

Diagram of a multi-language data model

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:

Extract from a multilingual Date table

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”:

Steps to add a new field parameter

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:

Defining fields to be included as parameters

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:

Field parameter created in Power BI

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:

Editing field parameter names

Figure 6 — Edited Field Parameter (Figure by the Author)

Next, incorporate a Visual into your report and include the newly created Field parameter:

Field parameter used in a report

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:

Month name displayed in Italian

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:

Expanding the field parameter table for multiple hierarchies

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:

Adding a language column to the field parameter table

Figure 10 — Expanded Calendar Field Parameter Table (Figure by the Author)

This additional column can then be renamed and included in the Slicer:

Using the Calendar hierarchy in a report

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:

Additional product columns for different 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:

Field parameter table with additional hierarchy column

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:

Field parameter with hierarchy selection in the report

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.

Visual representation of Power BI's capabilities

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.

Share the page:

Twitter Facebook Reddit LinkIn

-----------------------

Recent Post:

Exploring SparseGPT: Enhancing Language Models Through Pruning

A look into SparseGPT, a method for pruning language models while maintaining accuracy and efficiency.

Leadership Insights: The Value of Circle Meetings and Speaking Last

Explore the benefits of circle meetings and the importance of being last to speak in leadership contexts.

Inspiration: The Source of My Drive and Motivation

Discover the heartfelt sources of inspiration that drive personal growth and creativity through family and love.