Zero in DAX: Understanding When Adding 0 is Redundant and How to Avoid It

                                                                                                               Check list of all posts

Please s. the screenshot below

Data Model

Dimension - Request: Contains a list of requests (R1, R2, R3).

Fact - Work Order: Contains work orders associated with requests, tasks, and the number of hours for each task.

Sum of Hours = SUM('Work Order'[Hours])

This table shows the sum of hours grouped by Request, WorkOrder, and Task without any modification. The result for R1 is 3 hours.

Hours1 = SUM('Work Order'[Hours])

This calculation is the same as the Simple Sum and produces the same result.

Hours2 = SUM('Work Order'[Hours]) + 0

Adding 0 to the sum doesn't change the individual row values but highlights a practice that might seem redundant. The result shows hours as expected, but the addition of 0 is unnecessary and can be misleading or confusing.

Hours3 = CALCULATE(SUM('Work Order'[Hours])) + 0

This is similar to Hours2, where the CALCULATE function is used, but adding 0 again is redundant. The calculation yields the same results but demonstrates a poor practice of modifying sums unnecessarily.

Conclusion

The diagram illustrates that adding 0 to a DAX sum calculation does not alter the result. However, this practice is discouraged because it introduces unnecessary complexity and can confuse those reviewing the code. It offers no benefits and might cause misunderstandings about the calculation's logic. In composite models, this practice can lead to significant performance issues.

DAX query behind the scene:

DEFINE
    VAR __DS0FilterTable =
        TREATAS ( { "R1" }, 'ChangeRequest'[Request] )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL (
                ROLLUPGROUP (
                    'WorkOrder'[Request],
                    'WorkOrder'[WorkOrder],
                    'WorkOrder'[Task],
                    'ChangeRequest'[Request]
                ),
                "IsGrandTotalRowTotal"
            ),
            __DS0FilterTable,
            "Hours3", 'WorkOrder'[Hours3]
        )
    VAR __DS0PrimaryWindowed =
        TOPN (
            502,
            __DS0Core,
            [IsGrandTotalRowTotal], 0,
            'WorkOrder'[Request], 1,
            'WorkOrder'[WorkOrder], 1,
            'WorkOrder'[Task], 1,
            'ChangeRequest'[Request], 1
        )

EVALUATE
__DS0PrimaryWindowed
ORDER BY
    [IsGrandTotalRowTotal] DESC,
    'WorkOrder'[Request],
    'WorkOrder'[WorkOrder],
    'WorkOrder'[Task],
    'ChangeRequest'[Request]