Default values for field parameter with language support?

martinriddar — Thu, 29 Feb 2024 08:40:05 GMT

I've got a field parameter to which I've added language support using the method describere here: Synchronize multiple field parameters - Power BI | Microsoft Learn

_p_location_resolution = { //english ("region", NAMEOF('dim terminal'[region]), 0, 1), ("country", NAMEOF('dim terminal'[country]), 1, 1), ("city", NAMEOF('dim terminal'[city]), 2, 1), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 1), //swedish ("region", NAMEOF('dim terminal'[region]), 0, 2), ("land", NAMEOF('dim terminal'[country]), 1, 2), ("stad", NAMEOF('dim terminal'[city]), 2, 2), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 2), //spanish ("región", NAMEOF('dim terminal'[region]), 0, 3), ("país", NAMEOF('dim terminal'[country]), 1, 3), ("ciudad", NAMEOF('dim terminal'[city]), 2, 3), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 3) }

A problem with this is that if I add a language to my model but forget to update all field parameters there is no fallback. I use this field parameter in a slicer and If I filter for a language that is not supported by the field parameter the slicer becomes empty.

My question is if it is possible to modify this in order to add a default definition?

I tried letting ChatGPT be creative and it came up with this:

_p_location_resolution = VAR SelectedLanguage = SELECTEDVALUE('languages'[ISO abbreviation], "en") RETURN SWITCH( SelectedLanguage, "en", { ("region", NAMEOF('dim terminal'[region]), 0, 1), ("country", NAMEOF('dim terminal'[country]), 1, 1), ("city", NAMEOF('dim terminal'[city]), 2, 1), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 1) }, "sv", { ("region", NAMEOF('dim terminal'[region]), 0, 2), ("land", NAMEOF('dim terminal'[country]), 1, 2), ("stad", NAMEOF('dim terminal'[city]), 2, 2), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 2) }, "se", { ("región", NAMEOF('dim terminal'[region]), 0, 3), ("país", NAMEOF('dim terminal'[country]), 1, 3), ("ciudad", NAMEOF('dim terminal'[city]), 2, 3), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 3) }, // default to english { ("region", NAMEOF('dim terminal'[region]), 0, 1), ("country", NAMEOF('dim terminal'[country]), 1, 1), ("city", NAMEOF('dim terminal'[city]), 2, 1), ("terminal", NAMEOF('dim terminal'[terminal]), 3, 1) } )

But this gives the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".

Unfortunately I'm too new to Power BI and DAX to determine if it's just a tiny fixable error or if I'm not allowed to write DAX code like this in a field parameter. I'm guessing that the field parameter definition is not reloaded when changing the language filter so it isn't possible to do something like this but hey, I got to ask.

Re: Default values for field parameter with language support?

123abc — Thu, 29 Feb 2024 14:22:57 GMT

The error you're encountering, "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value," indicates that one or more expressions in your DAX code are returning multiple values where a single value is expected. This typically happens when you're trying to return a table or multiple values in a context where a single value is required, such as a measure or a variable.

In your case, the issue seems to be related to the fact that you're trying to return multiple rows of values within the SWITCH function, which expects a single scalar value for each case.

To resolve this issue, you need to restructure your DAX code so that it returns a single value for each case in the SWITCH function. One approach could be to return a single concatenated string that represents all the fields for a given language. Here's how you can adjust your code:

_p_location_resolution =
VAR SelectedLanguage = SELECTEDVALUE('languages'[ISO abbreviation], "en")
RETURN
SWITCH(
SelectedLanguage,
"en", CONCATENATEX({"region", "country", "city", "terminal"}, [Value], ", "),
"sv", CONCATENATEX({"region", "land", "stad", "terminal"}, [Value], ", "),
"se", CONCATENATEX({"región", "país", "ciudad", "terminal"}, [Value], ", "),
// default to english
CONCATENATEX({"region", "country", "city", "terminal"}, [Value], ", ")
)

In this code:

CONCATENATEX is used to concatenate the values of each field into a single string.
The {} syntax is used to create an array of field names for each language.
[Value] represents the value associated with each field.

This way, your DAX expression returns a single string for each language, which can be used as the default definition for your field parameter. Make sure to adjust [Value] with the appropriate expression that retrieves the values you want to concatenate for each field.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

topic Re: Default values for field parameter with language support? in DAX Commands and Tips

Default values for field parameter with language support?

Re: Default values for field parameter with language support?