Format string measures derrived from numbers based on local user settings

[heikomf]

Hi all,

I have a question regarding localization of Power BI report formats.

My goal is to use browser settings specific formatting for the users. For instance the numbers are formatted based on the language settings of the users browser (e.g. 1000 separator is "," for US users and "." for german users)

This works good for numbers in the report getting formated specific to the users.

However I also create String measures derived from numbers and here the formatting is not changed based on users browser settings (see screenshot: language setting is german, left measure is a string formated using "," using FORMAT(..., "###,#") option, but number measures on the right are formated using "." as 1000 separator based on user settings).

Is there a way to apply this dynamic formatting also to strings that are based on numbers (e.g. making the 1000 separator in strings dynamically change based on the user settings)?

Thanks for the help,

Heiko

 

 

[iBusinessBI]

I have a column that allows to switch between Currencies: USD $ or NIS ש"ח

In the Dynamic format I have a condition that returns the relevant symbol before the number shown:

VAR c = SELECTEDVALUE('Show Currency'[Currency for Calc])

RETURN

IF(c="USD","$ #,0","₪ #,0")

All works great in the Power BI but when I use Analyze in Excel - it changes the dollar sign $ to my ש"ח sign because of the Regional Settings.

I don't want to change the Regional Settings, I want to make it show exactly the symbols that I have written in the Dynamic Format.

I tried using UNICHAR:

IF(c="USD",UNICHAR(36)&" #,0",UNICHAR(8362)&" #,0")

But still the same issue.

Please help

[heikomf]

Thanks othy_bi for your answer.

That would be one solution, yes. However lets assume I need to use (string) concatenation for some values/visuals. What kind of function would I use to achieve the localized formating? 

I saw there is a function USERCULTURE() which can be used to find out the language settings of the user. However my FORMAT() function only accepts two parameters, the optional locale parameter mentioned here  (https://docs.microsoft.com/en-us/dax/format-function-dax) is not supported it seems. Does someone know why is that?

What are my other options? 

Thanks and best regards

[v-easonf-msft]

Hi, @heikomf 

I did not find more information about the USERCULTURE() function in the official document. I can't call this function in measure, how is it used?

I agree with @DataVitalizer  and I think that currently the locale cannot be dynamically identified through Dax function. In the format function, we usually manually enter the third locale parameter ("en-us", "de-De"...) to convert the string.

Best Regards,
Community Support Team _ Eason

[DataVitalizer]

Hi @heikomf 

In the left measure you are concatenating values in a card visual, in order to bypass the issue related to the browser language and as long as there is no function that helps retrieving that information in your calculation, I suggest playing around visuals so instead of formatting values try displaying values right from your fields in separated cards.

Did it work ? 👌Mark it as a solution to help spreading knowledge 👉A kudos would be appreciated