how to model columns repeated in multiple languages in FACT table?

[Anonymous]

Hi 

I am trying to model survey responses that can be displayed in two languages - for example English and German name.

One of the columns in the FACT table indicates the Sector associated with this response.  There is one column for the sector's english name and one column for the sectors German name .  The pattern is repeated a number of times in the FACT table - one column for english and one column for German.

Response_ID	Sector_English_name	Sector_German_name	...
10	Agriculture	Landwirtschaft	...
20	Finance	Finanz	..
30	..	..	..

One approach I have considered: extract the two columns representing the Sector (English and Gernan) and creating a Dimension table called DIMSector. I could then unpivot these colums so I have 2 rows for each sector listed (one english row and one German row) . I could then filter the the DIMSector table on English/German (only showing either German rows or English rows at a time) and still maintain a one-to-many relationship with the FACT table .  I would repeat this approach for each time the pattern occurs in the FACT table - creating seperate DIM tables.

It this an appropriate approach to use or is there a better "power BI" way?

Thank you in advance

pgf

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

In my opinion,this is  an appropriate approach to use.

refer :https://radacad.com/many-to-one-or-many-to-many-the-cardinality-of-power-bi-relationship-demystified 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Anonymous]

Hi Lucien, Thank you