topic Re: Dynamic number of decimal places and thousand separator in DAX Commands and Tips

Dynamic number of decimal places and thousand separator

soldous — Mon, 11 May 2020 16:13:56 GMT

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.

Re: Dynamic number of decimal places and thousand separator

camargos88 — Mon, 11 May 2020 19:54:08 GMT

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.

Re: Dynamic number of decimal places and thousand separator

soldous — Mon, 11 May 2020 20:36:15 GMT

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.

Re: Dynamic number of decimal places and thousand separator

Anonymous — Mon, 11 May 2020 20:44:24 GMT

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

Re: Dynamic number of decimal places and thousand separator

Anonymous — Mon, 11 May 2020 20:47:50 GMT

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

Re: Dynamic number of decimal places and thousand separator

camargos88 — Mon, 11 May 2020 21:06:00 GMT

soldous ,

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

Re: Dynamic number of decimal places and thousand separator

camargos88 — Tue, 12 May 2020 00:16:23 GMT

Hi soldous,

Check this file: Download PBIX

Re: Dynamic number of decimal places and thousand separator

soldous — Thu, 14 May 2020 12:26:16 GMT

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