topic Re: Filtering data on selected language in DAX Commands and Tips

Filtering data on selected language

Boeboey — Tue, 20 May 2025 07:44:35 GMT

Hi,

I'm trying to create a multi-language report based on the USERCULTURE().

I started by creating a measure: Selected_Language = LEFT(USERCULTURE(),2)

This returns the language: en, nl, fr, ...

I have an overview of events, each containing a code for department, function, ...

I imported an excel containing "codes" and their translations for certain tables:

Department
Function

Based on the selected_language measure, I would like to display the correct value for department and function for each event.

I created un unpivoted table "Safety codes" containing all codes:

I created a parameter "Language" that I used to filter on role level:

This seems to be working fine:

However, to be able to use this approach, the user needs to be a viewer, not a member or contributer.

But as a viewer, the user is not able to pin certain visuals to a dashboard.

Any idea how to fix this? Or is there another approach?

Without the RLS filtering, the values in all languages are shown:

Thanks for some feedback on this.

Re: Filtering data on selected language

Boeboey — Tue, 20 May 2025 07:43:48 GMT

I also tried another approach without using the parameter "language" and row level security.

I have added a slicer "language" which is referring to the unpivoted table "Safety codes":

But when selecting an item froml Department, the visual Function is not filtered at all:

The relationships are looking like this:

Re: Filtering data on selected language

V-yubandi-msft — Tue, 20 May 2025 11:05:26 GMT

Hi Boeboey ,

Thanks for reaching out to the Fabric community. You’re good, and We appreciate you sharing details along with snapshots they make troubleshooting much easier. We wanted to build on your approach and address the challenges you mentioned regarding RLS and filtering.

1. Since RLS prevents users from pinning visuals to dashboards, you might want to reconsider using it for this scenario. Instead, a better approach would be to dynamically filter translations without RLS.

2. To do this, you can create a measure or calculated column that pulls the correct translation from the Safety Codes table based on the Selected Language measure. This method ensures flexibility and keeps your reports dynamic.

3. Your data relationships are structured correctly! Keeping Department and Function separate enables independent lookups for each, which works perfectly with your unpivoted Safety Codes table.

I hope this helps..

Re: Filtering data on selected language

Boeboey — Tue, 20 May 2025 12:56:49 GMT

Thanks V-yubandi-msft for the advice!

I got rid of the RLS approach.

I have extended the original overview table:

with the columns language and.

Then I have duplicated the row for each language ("amount of languages" times row 1, ...).

And for each row, I took the translation in the language of that row.

E.g.

In this way, I had the language, all codes and the translations in this language for the codes for each event in 1 table. The language column of this table is now used as slicer across all pages.