4

I have created cube using SSDT Tools 2017,which has below columns 

Sales Amount        Stock Amount
2000                  5000

I would like to change above column data type as comma separated as below

Sales Amount        Stock Amount
2,000                  5,000
MoazRub
  • 2,881
  • 2
  • 10
  • 20
saij
  • 57
  • 2
  • 7

2 Answers2

2

You need to adjust the [Data Format] property and the show [Thousand Seprator]. Within your project select your measure and you will be able to see the above in the properties window.

MoazRub
  • 2,881
  • 2
  • 10
  • 20
  • MoazRub Above are non measure columns. – saij Apr 09 '19 at 10:49
  • Even if they are dimension attributes you have to do the same. – MoazRub Apr 09 '19 at 10:52
  • MoazRub I have not found format property option for the dimension attributes.I see only Data Format option and thousand separator option(True\False). – saij Apr 09 '19 at 12:17
  • That is what i was refering to.instead of Data Format , I wrote Format. These two(Data Format and Thousand seprator) will solve your issue – MoazRub Apr 09 '19 at 12:27
  • MoazRub I have followed steps,which you have mentioned.it's working for thousands (for example:-99,227),for lakhs data it looks like (197,281) – saij Apr 09 '19 at 14:43
  • @saij yup that is how it should be, it is a thousand seprator. However the method explained below is better and gives you more flexibility. Try that too. May I also suggest you mark that as answer. – MoazRub Apr 09 '19 at 14:46
2

A calculated column with the DAX FORMAT function can be used to define a custom format. In this instance, "#,0" is used for the second parameter to create a thousand separator which is a comma and use no decimal or numbers to the right of it, as indicated in your question. Be aware a calculated column will add additional overhead to the tabular model. You avoid adding this by using the Decimal Number option for the Data Format property. This can be changed in SSDT by selecting the column and viewing the properties (press F4), finding the Data Format field and choosing the Decimal Number option, however this will still include a decimal, which from your question doesn't look like the desired format.

FORMAT(Fact Table[Sales Amount], "#,0")
userfl89
  • 4,610
  • 1
  • 9
  • 17