C L on 27 Mar 2022 17:25:45
To streamline error handling, create operators or a wrapper function to force BLANKs to propagate through addition & subtraction as they do multiplication/division. Error-tolerant functions like DIVIDE are extremely helpful in creating measures, but addition & subtraction outside these functions can lead to undesidred results.
This new behavior would allow the following measures to evaluate to BLANK if no Australia data is in scope by propagating the intentional BLANK of the CALCULATE or DIVIDE function through.
Pct Difference Vs Australia Total =
DIVIDE(
[Cost],
CALCULATE(
[Cost],
[Country] = "Australia"
),
MINUS 1
Total Difference Vs Australia Total =
[Cost]
MINUS
CALCULATE(
[Cost],
[Country] = "Australia"
)
Whereas variables or other complexity is currently required:
Total Difference Vs Australia Total =
VAR AusCost =
CALCULATE(
[Cost],
[Country] = "Australia"
)
IF(
NOT(ISBLANK([COST]) && NOT(ISBLANK(AusCost)),
[Cost] - AusCost
)
MORE DETAIL:
Behavior of + and - would not change:
2 + BLANK = 2
BLANK - 2 = -2
2 + BLANK - 1 = 1
The new operators would function as follows. In order of operations, these new operators would be treated at the same level as + and - and evaluated left to right.
2 PLUS BLANK = BLANK
BLANK MINUS 2 = BLANK
2 PLUS BLANK - 1 = (1 PLUS BLANK) - 1 = BLANK - 1 = -1
2 + BLANK MINUS 1 = (2 + BLANK) MINUS 1 = 2 MINUS 1 = 1
2 + (BLANK MINUS 1) = 2 + BLANK = 2
Or if a wrapper function is preferred over new operators:
STRICT(2 + BLANK) = BLANK
STRICT(BLANK - 2) = BLANK
STRICT(2 + BLANK - 1) = BLANK
2 + STRICT(BLANK - 1) = 2 + BLANK = 2
STRICT(2 + BLANK) - 1 = BLANK - 1 = -1