Forum Discussion
How to structure multilingual dimension table with repeated PKs — normalize or unpivot?
Hi Monsieboy,
Thank you for reaching out to the Microsoft fabric community forum.
After thoroughly reviewing the details you provided, I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.
outcome:
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
Super UserHi Monsieboy ,
In Power BI, the cleanest and most scalable approach for multilingual dimension tables like yours is to normalize the data rather than unpivoting into wide columns.
Here’s why:
- Keeping Language and TypeID as separate columns allows you to filter and translate dynamically using slicers or user preferences.
- You can create a composite key using EntityNumber & Language & TypeID to ensure uniqueness in your dimension table.
- This structure works well with row-level security and localization logic, especially if you want to show the UI in the user’s preferred language.
- Unpivoting into wide columns (e.g. NameEN1Type1) becomes hard to maintain and doesn’t scale well when adding new languages or types.
So yes, go with the normalized structure:
EntityNumber | Language | TypeID | Denomination
Then in your model, you can use a measure or calculated column to pick the right translation based on the current language context.
Let me know if you need help building that logic.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
So basically snowflake/normalize the translations into a new table?
So I have fact table, then a dimension table with ID - type - language, and a snowflaked further normalized dimension table linked to the first dimension table with PK_label - language?
But do I have to force end-user who will select a company from fact table to also before that specify a language? Or set a default language and allow him to change it? Otherwise how will dimension tables know which of 4 languages to select for company name?
Thanks a lot!