Replace blank with 0 in matrix visual

The "show items with no data" setting won't help in this instance.

There are 2 key steps in SUMMARIZECOLUMNS which are relevant here. Firstly, it calculates the group by tuples, the combinations of values from the rows & columns of the matrix for which it needs to calculate the measures.

Secondly it implements the non-empty filter, so that it only returns the combinations for which at least one measure returns a non-blank value. 

The "show items with no value" setting applies to the second step, to prevent the non-empty filter being applied. However the problem you are facing is the calculation of the group by tuples. If a combination of the values from the rows and columns of the matrix does not exist in the underlying data then it will never be returned, and the calculation of any measures will not happen. That is why adding a 0 to the measure result, and your other attempts, don't have the desired result - the measures are simply not being calculated for those cells where you see a blank.

The only way around it, I think, is to make sure that the underlying data has an entry, even if it is 0, for every combination of the values on the rows and columns of the matrix. This is made even more difficult by the fact that you are in Direct Query, as you would need to alter the underlying source rather than being able to perform some trickery in Power Query.

I think you may have to live with the blanks I'm afraid.

The reason +0 and COALESCE are not fixing it is that the matrix is not evaluating the measure for those row/column combinations at all. The underlying DirectQuery returns no rows for those pairs, so the cell stays blank instead of showing zero.

Right click each field in the Rows well and each field in the Columns well of the matrix and turn on "Show items with no data". After that, a measure like Amount = COALESCE(SUM('SalesTable'[salesamount]), 0) will show 0 for the previously blank cells.

If some cells still stay blank after enabling that option, it usually means there is no active relationship path from the row/column table to the fact table, so the fact table cannot expand the combinations. Check that your dimension tables filter the fact table through active relationships.

If this helped, a thumbs up and marking it as the accepted solution would be appreciated.

Best,
Shai Karmani

Hello KalaiselvanP 

Go to Format pane - Values -Blank cell text (or "Show 'blank' as") and type 0.
This forces the display to show 0 for cells with no data, even when your measure genuinely returns blank (which happens when there's no matching fact row at that row/column intersection your +0/COALESCE measures never even get evaluated in that case, since the cell isn't blank value, it's blank because it's not queried). This setting bypasses that problem entirely at the display layer.

Hi KalaiselvanP 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

Hi,

If your measure is already returning 0 but the Matrix still displays a blank, the issue is often not the measure itself but the fact that there is no row in the result set for that intersection. This is common with DirectQuery models.

Here are a few things to check:

1. Enable "Show items with no data"

If your rows and columns come from dimension tables, enable Show items with no data for the row/column fields in the Matrix. This allows Power BI to display combinations that don't have matching fact records, so your measure can return 0 instead of a blank.

2. Use a measure with COALESCE

Instead of using a calculated column, create a measure like:

Amount =

COALESCE(

    SUM('Sales'[SalesAmount]),

    0

)

or

Amount =

IF(

    ISBLANK(SUM('Sales'[SalesAmount])),

    0,

    SUM('Sales'[SalesAmount])

)

3. Verify whether the cell actually exists

If there is no matching row for a particular Row × Column combination, Power BI cannot evaluate the measure for that intersection, so it remains blank even if you add +0 or use COALESCE().

4. Check your relationships

Ensure that:

• Your Date table is related correctly to the fact table.
• The matrix Rows and Columns are coming from dimension tables with active relationships.
• There are no filters removing those combinations.

5. DirectQuery limitation

With DirectQuery, missing combinations are determined by the SQL query generated by Power BI. If the source query doesn't return a record for a particular intersection, Power BI cannot replace it with 0 unless the combination exists (or "Show items with no data" is enabled).

If none of the above resolves the issue, could you please share:

• Which field is used in the Rows?
• Which field is used in the Columns (looks like Month)?
• Is SalesAmount a column or a measure?
• Are the Row and Column fields coming from dimension tables or the same fact table?

That will help identify whether this is a missing row combination or a measure evaluation issue.

Hope this helps.

Thanks