Truncating decimal numbers in dimensions
I have a custom model that has currency exchange rates; the rates are decimal numbers with 4 places, in the sql query I have them truncated to only show 4 places
In some of my discoveries, I need to show the rate in rows rather than in measures so I have it set both as a dimension and as a measure. When added to a discovery, the dimension in rows is showing with trailing zeroes as in the attached picture; in the measures it shows correctly as I have formatted it that way in the model columns
Is there a way to format the dimension so it shows the same as in the sql query?
1 reply
-
Hi Sophie,
In your data flow in the model, add a calculated field into the table that contains the Rate field, Format it using the Format() function. This will format the decimal to the required decimal places and convert it to a string. Use the string version as a Dimension and the numeric version as the Measure:
Hope that helps.
Ian