How to populate a virtual column with values in the user's selected language that is based on an EnumList

[pawann]

Hello there!

I got stuck with a challenge I hope you guys have an idea on how to accomplish.

It’s for an app where we offer the user to choose a preferred language. Based on the selected language, all content should be translated, including EnumList values.
Translation already works for column names etc using a large ‘Translations’ table and a corresponding expression for ‘Display Name’

The problem I face is around offering the user a drop down field (EnumList) that is

1. made up on values that should be displayed in the user’s language
2. pre-filtered based on other user’s preferences
3. and that are concatenated with other column values.

The current situation:
Table “Users” holding all information about the registered app users:
note: there is only one row per user

• the current users language is stored in Users[Language] as Text
• the current users items is stored in Users[MyItems] as EnumList

Table “Contacts” holding information for each Item.
note: table contains multiple rows per ItemID

• the id of an Item: Contacts[ItemID] as Text
• the contact email address for an Item: Contacts[Contact_Email] as Email
• the type of contact for an Item: Contacts[Contact_Role] as Enum

Table “Translations”:

• has one column per Enum value from Contacts table.
• row is selected by users language string [DE,FR,IT,EN]

What I want to achieve:
Goal 1: Create a virtual column that translates the Contacts[Contact_Role] into the users language.

I’m using this formula to translate a column name into the users language which works flawlessly:
ANY(SELECT(Translations[COLUMN_NAME],([Language] = LOOKUP(USEREMAIL(),Users,"Email","Language"))))

Because for the situation at hand, COLUMN_NAME actually refers to an Enum value from Contacts[Contact_Role], I thought I could replace Translations[COLUMN_NAME] with a SWITCH statement that switches the columns based on the Contact_Role in the SELECT statement above, similar to Steve’s solution to this question

ANY(SELECT(
	SWITCH([Contact_Role],
		"Plant Manager", Translations[PlantManager],
		"Maintenance", Translations[MaintenanceManager],
		"Supervisor Plant Manager", Translations[SupervisorPlantManager],
		Translations[No_Contact_Role]
	),
	([Language] = LOOKUP(USEREMAIL(),Users,"Email","Language"))
))

But this results in an error saying “Unable to find column ‘Language’”.

When replacing [Language] with Translations[Language], it results in the following error:
Cannot compare List with Enum in (Translations[Language] = ANY(SELECT(Users[Language],([Email] = USEREMAIL()))))

What am I missing here? Any guidance, ideas or hints are highly appreciated!

Goal 2: Concatenate the virtual column with other information.
Once goal 1 is achieved, I guess it should be easy using CONCATENATE() formula to add other info, either directly in the expression of the virtual column from goal 1 or as a new virtual column à la CONCATENATE([Goal1VirtualColumn],[otherColumnsOrText])