Rules / Formatting

Visible decimal column with no format string

info DECIMAL_COLUMN_WITHOUT_FORMAT_STRING · built in · model layer · scope: Column, CalculatedColumn, CalculatedTableColumn

What it checks

Visible Decimal number and Fixed decimal number columns with no format string.

Each finding names the column, as 'Sales'[Discount Rate]. A column counts as visible when it and its table are not hidden.

Example

Fires the rule
table Sales
	column 'Discount Rate'
		dataType: double
		summarizeBy: none
		sourceColumn: Discount Rate
After the fix
table Sales
	column 'Discount Rate'
		dataType: double
		formatString: 0.0%
		summarizeBy: none
		sourceColumn: Discount Rate

Why it matters

A format string says what a column's numbers are. Without one, a share, an amount, and a plain measurement look alike, or as Tabular Editor's guidance for its version of this rule puts it, "Users can't tell if values are currency, percentages, or plain numbers" (Provide format string for numeric and date columns (opens in a new tab)). Left at General, Power BI Desktop shows the column's values as plain numbers: in a table visual, a discount amount reads 20.30 with no currency symbol, and a share of 0.25 reads 0.25, not 25%. A format set on the column in the model applies wherever the column is used, "unless a visual or element level format string overrides it" (Use custom format strings in Power BI Desktop (opens in a new tab)), so setting it once spares every report author setting it visual by visual.

How to fix it

In Power BI Desktop, select the column in the Data pane and set Format under Column tools, or select it in Model view and set Format in the Properties pane (Add a model level format string (opens in a new tab)). In the column's TMDL file, add a formatString: line under the column, such as formatString: 0.0% for a ratio or formatString: #,0.00 for an amount.

When to ignore it

When no reader sees the column as a number: a decimal kept for a relationship or read only by measures is better hidden than formatted, which clears the finding too. Coordinates are the other case: a latitude or longitude column is there to place points on a map, not to be read as a number, so leaving it at General is fine. Otherwise a visible decimal column is one a report author can drop into a visual, where it shows with no format of its own.

To ignore this rule on one object, add annotation pbiplint.ignore = DECIMAL_COLUMN_WITHOUT_FORMAT_STRING under the object in its TMDL file. Power BI Desktop keeps the annotation. pbiplint also honors Tabular Editor's BestPracticeAnalyzer_IgnoreRules annotation: on an object it ignores the rule there, and on the model it ignores the rule on every model object. To turn the rule off for a whole project, set "DECIMAL_COLUMN_WITHOUT_FORMAT_STRING": "off" under rules in pbiplint.config.json.

Quirks

Check a model for this Improve this page (opens in a new tab)