Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
soldous
Advocate II
Advocate II

Dynamic number of decimal places and thousand separator

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.

 

1 ACCEPTED SOLUTION

Hi @soldous,

 

Check this file: Download PBIX 

 

Capture.PNG

 

Capture 0.PNG

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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
Community Champion
Community Champion

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

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

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. 

Hi @soldous,

 

Check this file: Download PBIX 

 

Capture.PNG

 

Capture 0.PNG

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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

@soldous ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors