Dynamic number of decimal places and thousand separator

[soldous]

Hello, 

I need to achieve a dynamic number of decimal places (0 if the value is greater than 1 and e.g. 2 if the value is smaller than 1). That is an easy task with:

IF(value < 1; ROUND(value; 2); ROUND(value;0 ))

And it works in the visual if the value type is General and the number of the decimal places Auto. But I also need to have a thousand separator for values greater than 1k. When I apply the thousands separator the value changes from General to Decimal number and the number of the decimal places from Auto to strict number. To change the value to text is not applicable because I need to visualize it as a bar chart.

Is there any option how to set the thousands separator by DAX?

Thank you very much for help.

[Anonymous]

I'm afraid what you want to do is not possible. Dynamic formatting is only possible with calculation groups and these are not yet available in Power BI (only on SSAS 2019 and Azure).

Best

D

[camargos88]

Hi @soldous ,

Check this link: https://docs.microsoft.com/en-us/dax/custom-numeric-formats-for-the-format-function

Maybe you can use the FORMAT function.

[Anonymous]

Dear @camargos88.

Suggesting the FORMAT function that returns a STRING is not the wisest idea in view of the requirements stated in the initial question...

Best
D

[soldous]

Hi @camargos88 ,

I can't. Format function returns always text. This could be used in table/matrix in some cases but not in bar chart or gauge. I also can't use Data bars conditional formating with text. 

[camargos88 · Accepted solution]

Hi @soldous,

Check this file: Download PBIX 

[soldous]

Hi @camargos88 

Your solution works perfectly!! I only changed the formatting a little bit because in your suggestion there is always a zero for numbers smaller than 100. So I changed it to #,0.##.

Best regards,

soldous

[camargos88]

@soldous ,

As our master @Anonymous  said, it wasn't a good idea. I missed the part of bar chart.