Replacing null with zero brings extra rows to the matrix

Hi Anonymous 

You're facing a similar problem to the one discussed here.

The key thing do define is the conditions under which blank should be forced to zero, which may be dependent on the layout of the particular visual.

In your example, it looks like the condition for forcing zero to blank in a given cell of the matrix is:

• "In both the current row and current column, at least one of Metric 1 and Metric 2 is nonblank."

I'm going to assume that a nonblank value for either of the metrics in a particular cell of the matrix implies a nonblank value when that metric is calculated in the context of the entire row or column. (If that's not true, it's a bit more complicated.)

With these assumptions, you could write measure like this (and similarly for Metric 2):

Metric 1 Zero = 
VAR RowNonEmpty =
    CALCULATE ( 
        NOT ISBLANK ( [Metric 1] ) || NOT ISBLANK ( [Metric 2] ),
        ALLSELECTED ( 'Date' )
    )
VAR ColumnNonEmpty =
    CALCULATE (
        NOT ISBLANK ( [Metric 1] ) || NOT ISBLANK ( [Metric 2] ),
        ALLSELECTED ( 'Dim 1' ),
        ALLSELECTED ( 'Dim 2' )
    )
VAR ForceZero =
    RowNonEmpty && ColumnNonEmpty
RETURN
    [Metric 1] + IF ( ForceZero, 0 )

ALLSELECTED is used to modify the filter context to the entire "row" or "column", which is dependent on which dimensions are included in the rows/columns.

The test of whether a row/column is nonempty then depends on the set of measures included in the visual.

Obviously, the code would change in a matrix with a different layout. I can't think of any generic solution that doesn't depend on visual layout.

Given the common logic, you could also use a Calculation Group to capture the logic in a Calculation Item and avoid modifying multiple measures.

Example PBIX attached.

Regards,

Owen