Setting number format to German-Swiss in Fabric semantic model or Power BI

[SalvaC]

Dear all

I'm struggling with the definition of the number format in a Fabric Semantic model.

As my clients are in Switzerland, the number format should be like this:

But even with the correct number format in the Windows regional settings, the numbers still show like this in Power BI:

I learned that the number format is set by the model language.

I then tried setting the Model language to German-Swiss, but there is only German (Germany):

The German number format is not as needed in Switzerland.

I then tried another setting (French (France)), which has a similar number format, but it didn't work.

The numbers are still shown in the same format.

I then tried to set a custom-format string, but it wasn't possible to set a format string, which works in any case.

This is not a new issue, as this post suggests:

Regional Settings has no effect on number formats - Microsoft Fabric Community

This post shows that it's possible to solve:

Incorrect locale (regional number formating) when ... - Microsoft Fabric Community

But it looks like, that it's impossible to change the language of an existing sematic model.

Do you have an idea what I can try next?

Kind regards

Salvatore

[SalvaC]

I had to add additional checks to account for negative numbers:

SWITCH(TRUE()

        ,Result < -999999999.99

            ,FORMAT(Result, "#'###'###'###.0")

        ,Result < -999999.99

            ,FORMAT(Result, "#'###'###.0")

        ,Result < -999.99

            ,FORMAT(Result, "#'###.0")

        ,Result < 0

            ,FORMAT(Result, "0.0")

        ,Result < 1000

            ,FORMAT(Result, "0.0")

        ,Result < 1000000

            ,FORMAT(Result, "#'000.0")

        ,Result < 1000000000

            ,FORMAT(Result, "#'###'###.0")

        ,Result <= 1000000000000

            ,FORMAT(Result, "#'###'###'###.0")

        ,"#0.0"

        )

Now it works perfectly.

[v-echaithra]

Hi @SalvaC ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[SalvaC]

Hi @v-echaithra 

Sorry for the delay.

I was able to adapt your approach to the final solution.

The fixed format string doesn't work for all numbers:

I then added a SWITCH() to check for the result and define the correct format:

Online Sales (By Order Date) =

VAR Result = SUMX('Online Sales'

                    ,('Online Sales'[SalesQuantity] * 'Online Sales'[UnitPrice]) - 'Online Sales'[DiscountAmount]

                    )

                    * [Scaling (With Default)]

RETURN

SWITCH(TRUE()

        ,Result < 1000

            ,FORMAT(Result, "0.0")

        ,Result < 1000000

            ,FORMAT(Result, "#'000.0")

        ,Result < 1000000000

            ,FORMAT(Result, "#'###'###.0")

        ,Result <= 1000000000000

            ,FORMAT(Result, "#'###'###'###.0")

        ,"#0.0"

        )

The performance is good.
The DAX query needs only 20ms with one Measure.
I will see how it works with multiple Measures.

The drawback of the approach is, that I must add this logic in each Measure.

I would prefer to add the same expression as a Dynamic format expression to all separate Measures, but this didn't work:

VAR CurValue = SELECTEDMEASURE()

RETURN

SWITCH(TRUE()

        ,CurValue < 1000

            ,"0.0"

        ,CurValue < 1000000

            ,"#'000.0"

        ,CurValue < 1000000000

            ,"#'###'###.0"

        ,CurValue <= 1000000000000

            ,"#'###'###'###.0"

        ,"#0.0"

        )

This is very strange.
Why doesn't the same expression work as a dynamic format expression?

Regards

Salvatore

[ExcelMonke · Accepted solution]

Hello,

Unfortunately it is not a common format, and therefore not standard in Power BI. However, there is a workaround using the FORMAT function. See the screenshot below. In the DAX, you can plug in your [measure] in the VAR. 
Do note, that I recommend doing this only for your final result. The result you see here in the card, is technically a "text" value. In other words, you cannot use it as a number and apply mathematical equations to it. 

Here is the DAX sample:

Formatted Result = 
VAR _Result = [Measure]

RETURN
FORMAT ( _Result, ("#'###'###.##"))

[SalvaC]

Hi,

Thanks for the Tip.

I already tried this.

The issue is that when using this format string and the number is below 1,000,000, it will display as 'xxx'xxx

Therefore, I would need to set dynamic formatting, checking the length of the number, and setting the format string accordingly.

I would do this only as a last resort.

KInd regards

Salvatore