Rules / DAX Expressions

Avoid using '1-(x/y)' syntax

warning AVOID_USING_'1-(X/Y)'_SYNTAX · ported · model layer · scope: Measure, CalculatedColumn, CalculationItem

What it checks

Expressions with a number, then plus or minus, then either SUM('Table'[Column]) followed by a division operator, or a call to DIVIDE. The common shape is 1 - SUM(Sales[Cost]) / SUM(Sales[Amount]).

Each finding names the object: a measure as [Margin %], a calculated column as 'Sales'[Margin %], a calculation item by its name.

Example

Fires the rule

table Sales
	column Amount
		dataType: decimal
		sourceColumn: Amount
	column Cost
		dataType: decimal
		sourceColumn: Cost
	measure 'Margin %' = 1 - SUM(Sales[Cost]) / SUM(Sales[Amount])
		formatString: 0.0%

After the fix

table Sales
	column Amount
		dataType: decimal
		sourceColumn: Amount
	column Cost
		dataType: decimal
		sourceColumn: Cost
	measure 'Margin %' = DIVIDE(SUM(Sales[Amount]) - SUM(Sales[Cost]), SUM(Sales[Amount]))
		formatString: 0.0%

Why it matters

Written that way the measure always returns a value. When there are no rows, the division is blank, one minus blank is one, and every empty cell in the matrix shows 100 percent. The visual fills with rows that should not be there, and the query does extra work to produce them. Written as a single DIVIDE over the difference, the measure is blank when the data is blank and the engine skips those rows.

How to fix it

Rewrite 1 - x / y as DIVIDE(y - x, y), and hold the shared denominator in a variable when it is used twice:

Margin % =
VAR TotalAmount = SUM ( Sales[Amount] )
RETURN DIVIDE ( TotalAmount - SUM ( Sales[Cost] ), TotalAmount )

In Power BI Desktop, select the measure and edit it in the formula bar. In the TMDL file, edit the expression after measure 'Margin %' =.

When to ignore it

There is no case where the original shape is the better one. If a visual relies on the measure showing 100 percent where there is no data, that is a display decision, and it belongs in a measure that returns the value on purpose, not in a subtraction that happens to produce it.

To ignore this rule on one object, add annotation pbiplint.ignore = AVOID_USING_'1-(X/Y)'_SYNTAX under the object in its TMDL file. Power BI Desktop keeps the annotation. To turn the rule off for a whole project, set "AVOID_USING_'1-(X/Y)'_SYNTAX": "off" under rules in pbiplint.config.json.

Quirks

The pattern needs SUM as the numerator, or DIVIDE right after the number. 1 - [Cost] / [Sales] and 1 - AVERAGE(...) / ... are not matched.
Table and column names must contain only letters, digits, spaces, and underscores for the SUM form to match.

USE_THE_DIVIDE_FUNCTION_FOR_DIVISION fires on the / in the same expression, and the rewrite clears both.

Ported from Microsoft's Best Practice Analyzer ruleset (opens in a new tab).

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