Matrix - Show Blank instead Zero

[lordtopcat]

Hi,

Just wondering if there is a way, like in Excel to show a blank in a cell (Matrix visual) when the result is 0?

Cheers,

Jamie

[v-robertq-msft · Accepted solution]

Hey, @lordtopcat

You don't need to create 12 measures for each column whose value is zero, you can try replacing zero with null in Power Query Editor, you can follow this:

1. Open Power Query Editor, select the columns you want to display in visual, and the columns used in the measures you created to display in visual.

(You can hold down ctrl and left click on all columns that you want to achieve multiple selections), like this:

2. Click 'Replace Values', from '0' to null, like this:

And you can see that all zero values will be blank in the visual.

Best regards

Qin Community Support _Robert Team

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[zahlenschubser]

If I do it this way I get errors in the query editor because the columns are configured as decimals. =(

[lordtopcat]

@v-robertq-msft  You're a legend! Thank you very much 👍

[mahoneypat]

Sure.  Here is one way to do it.  First store your expression in a variable, then check if it is zero and return blank if so.

MyMeasure = var __result = SUM(Table[Column])

return IF(__result = 0, BLANK(), __result)

Regards,

Pat

[lordtopcat]

Oh, sorry should have clarified - I have 12 values that I need this to apply to, I don't want to have to create 12 more measures just to suppress the zeros.

This is such a basic function, surely there's a toggle somewhere I'm missing?

[mahoneypat]

Another option is to use conditional formatting on the font color to match the background when the result is 0.  However, you will need to set that for each column too.

Regards,

Pat

[NatachaPN]

I suggest you to create a measure like this:

MyMeasure =
VAR x = SUM(MyTable[MyField])
RETURN if(x=0, BLANK(), x)

Use that measure instead the field. For instance, you have this table "Tabla":

You add a measure: 

MyMeasure =

VAR x = SUM(Tabla[Columna2])

RETURN if(x=0, BLANK(), x)

having this behavior on the matrix: