Forum Discussion
Matrix - Show Blank instead Zero
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
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:
- 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:
- 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.
7 Replies
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:
- 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:
- 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.
v-robertq-msft You're a legend! Thank you very much 👍
If I do it this way I get errors in the query editor because the columns are configured as decimals. =(
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:
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
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?
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
