Power BI is a business intelligence tool developed by Microsoft that allows users to visualize data through interactive reports and dashboards. It connects to various data sources, transforms and models the data, and creates rich visualizations that provide actionable insights.
One common requirement when building reports in Power BI is to display a blank value instead of zero for measures that have no data. By default, Power BI displays zero for null or blank values. However, displaying blanks instead of zeros can improve the readability of reports.
For example, in a sales report, you may want to show blanks in the sales amount column for dates when no sales occurred, rather than displaying zeros. Blanks clearly indicate missing values, while zeros could be misinterpreted as having actual zero sales values.
In this article, we will go through different methods you can use to display blank instead of zero in Power BI reports and dashboards.
Using the Blank Value DAX Function
The simplest way to return a blank instead of zero in Power BI is to use the BLANK() DAX function.
DAX stands for Data Analysis Expressions. It is the formula language used in Power BI for data modeling, data analysis and calculations.
The BLANK() function returns a blank value. You can wrap a measure that returns zero for nulls with the BLANK() function to convert those zeros into blanks.
Here is an example DAX measure definition that uses BLANK():
Sales Amount = IF(ISBLANK(SUM([Sales])), BLANK(), SUM([Sales]))
This measure checks if the sum of the Sales column is blank using ISBLANK(). If it is, it returns a blank value using BLANK(). Otherwise, it returns the Sales Amount.
You can then use this measure in your report visuals, and Power BI will display blanks instead of zeros.
The BLANK() function provides a simple way to replace zeros with blanks for measures. However, the disadvantage is that you need to edit the DAX formula for each measure you want to fix.
Using the Format Pane
Another option is to use the Format pane in Power BI Desktop to format a measure to display blanks instead of zeros.
To do this:
1. Select the value field that you want to format in the Fields pane.
2. Open the Format pane using the Modeling tab.
3. Expand the Value section.
4. Change the Default Summarization to Don’t summarize.
5. Enable the Replace empty values with setting.
6. Leave the replace value blank.
This will configure that field to always replace null or zero values with a blank.
The benefit of this approach is you can apply it directly on the field without modifying the DAX measure definition. However, the limitation is that it only works for fields displayed individually, not when grouped into a matrix visual.
Using the ISBLANK Function
You can also use the ISBLANK() DAX function to conditionally return a blank string based on whether a measure evaluates to blank/null.
Here is an example:
Sales Text =
IF(
ISBLANK(SUM([Sales])),
“”,
CONCATENATE(“Sales: “, SUM([Sales]))
)
This measure checks if the Sales sum is blank using ISBLANK(). If so, it returns an empty string “”. Otherwise, it returns a text string concatenated with the Sales amount.
The advantage of this approach is it allows you to handle null values differently in the same measure, instead of creating a separate blank measure.
You can extend this pattern to build more complex logic into your measures to elegantly handle blank values.
Custom Formatting in Visuals
A simple way to display blanks instead of zeros in visuals is to apply custom number formatting.
Most visuals in Power BI have a Format pane that allows configuring the text box formatting, including custom number formats.
To display blanks for zeros, open the Format pane for a text box in a visual and set the Category to Number. Then enter this custom format:
#;-#;
This will display any zero values as blanks.
The limitation of this approach is that it only affects the formatted text box. The underlying data values remain unchanged. So it may lead to inconsistencies in other visuals using the same field but without the custom format applied.
Using conditional formatting
Instead of changing the number format, you can also apply conditional formatting rules to hide the zero values.
For example, select the text box you want to format, open the Format pane, go to Conditional formatting, and create a new rule like:
Field Value is 0, Text color is White
This will set the text color to white for zero values, effectively hiding them in visuals with white backgrounds.
You can create multiple conditional formatting rules to handle nulls, zeros, and negative values differently.
The main drawback to this approach is that white text on white background does not fully mimic a blank value. Advanced users may be able to tell the difference.
Using the ALLSELECTED DAX Filter
Here is an advanced technique using DAX filters and variables to replace zeros with blanks in Power BI.
First, create a measure using ALLSELECTED() to generate a table containing all filter contexts applied to a report.
For example:
Filter Table =
VAR CurrentFilters = ALLSELECTED(‘TableName’)
RETURN
CurrentFilters
Next, add a logical filter to the measure you want to fix:
Sales Blank =
VAR FilterTable = [Filter Table]
VAR BlankValue =
CALCULATE(
[Sales],
FILTER(FilterTable, [Sales] = 0 || ISBLANK([Sales]))
)
RETURN
IF(ISBLANK(BlankValue), BLANK(), [Sales])
This filters the Sales measure to calculate only for filter contexts where Sales is zero or blank. It stores this in a BlankValue variable, replacing actual zeros with blanks using BLANK().
Finally, it checks if BlankValue is blank, returning BLANK() if so, otherwise returning the original Sales value.
This allows replacing zeros with blanks while retaining all filtering context.
The downside is the complexity of DAX variables and filters. So this is best reserved for advanced Power BI users.
Using the ISINSCOPE Function
An alternative approach that is simpler than the ALLSELECTED method is using ISINSCOPE().
ISINSCOPE() tells you if a row is in the current filter context.
You can combine it with ISBLANK() to conditionally return blanks:
Sales Blank =
IF(
ISBLANK([Sales]) || NOT(ISINSCOPE([Sales])),
BLANK(),
[Sales]
)
This checks if Sales is blank, or if the current row is not in the filter context using ISINSCOPE(). If so, it returns BLANK().
This allows replacing zeros with blanks in visuals without modifying the model or adding complex DAX.
Setting Default Summarization for Columns
If you have an entire column where you want to replace nulls and zeros with blanks by default, you can configure this at the table level in Power BI Desktop.
For any column, right click and select Default Summarization. Change it from Sum to Don’t summarize.
This will prevent Power BI from summing the column, and instead display the raw blank and zero values.
You can then use the formatting techniques discussed earlier to selectively convert zeros to blanks in visuals.
The limitation is that Don’t Summarize prevents you from using aggregate measures like SUM() for that column. So it only works if you don’t need to summarize the data.
Using the ALLOWEDVALUES Function
The ALLOWEDVALUES() function in DAX provides another technique to replace blank cells with a text value like “NaN”.
For example:
Sales Allowed =
VAR AllowedList =
ALLOWEDVALUES(‘Sales Table'[Sales])
RETURN
IF(ISBLANK(AllowedList), “NaN”, [Sales])
ALLOWEDVALUES() gives you the unique list of values in a column. Check if this is blank using ISBLANK(), and if so, replace with text.
This preserves aggregation while substituting a readable text value for blanks.
A limitation is that it requires hard-coding the substitution logic into each measure. So it may be better suited for replacing blanks in dimension columns rather than measures.
Using BLANKASZERO in Report-level Formatting
Report-level custom formatting in Power BI provides an option called BLANKASZERO that can help convert blank values to zeros.
This can be useful if you want to retain blanks in your model, but display zeros in a specific report visual for clarity.
To use it, in Power BI Desktop, go to Report settings, open the Custom formatting pane, and set BLANKASZERO to True.
This will convert NULL and blank values to zero only within that report, without affecting the model.
Limitations are that it only works at the report level, and cannot differentiate between true zeros vs. converted blanks. But it provides an easy option for report-specific formatting.
Falling Back to Default Formatting
An easy way to convert zeros to blanks is to fall back to Power BI’s default formatting behavior for visuals.
By default, bar charts and other visuals will omit zero values and display gaps instead to improve readability.
You can convert measures showing zeros back to this behavior by:
– Setting Y axis to Start at Zero = False
– Disabling totals for axes
– Removing custom number formatting
This allows blanks again without modifying data values.
The catch is it only works for certain visual types like bar charts. And it hinders comparing relative values between series. But it can be a quick fix in simple use cases.
Applying Filtering to Measures
Measure filtering allows applying filters to measures to manipulate the results dynamically.
For example, you can create a measure filtered to exclude zeros:
Sales Ex Zero =
CALCULATE([Sales], FILTER(ALL(‘Sales’), [Sales] <> 0))
This uses CALCULATE() and FILTER() to limit the Sales measure to only non-zero values.
You can then selectively use this in visuals when you want to omit zeros entirely rather than just hiding them.
Limitations are that filter context needs to be considered carefully to avoid misrepresenting results. So measure filtering is best for simple cases.
Using the SWITCH Function
The SWITCH() function in DAX provides logic similar to a nested IF statement which can help implement dynamic formatting.
For example:
Sales Format =
SWITCH(
TRUE(),
ISBLANK([Sales]), BLANK(),
[Sales] = 0, “”,
[Sales] < 0, "Negative",
[Sales]
)
This checks Sales for blanks, zeros, negatives, and returns the appropriate text format.
An advantage over nested IFs is easier readability and maintenance.
The main limitation is that SWITCH() relies on an explicit value match, rather than ranges like IF statements. So it may require more cases.
Formatting with Variables
For complex formatting logic, you can store sub-expressions in variables to simplify measures.
For example:
VAR Blank = ISBLANK([Sales])
VAR Zero = [Sales] = 0
RETURN
SWITCH(
TRUE(),
Blank, BLANK(),
Zero, “”,
[Sales]
)
By storing repeated sub-expressions in variables, you can reduce duplication and improve readability.
Variables also allow reusing logic across multiple measures consistently.
The main limitations are variable scoping rules and performance overheads if not used judiciously.
UsingDisconnectedTables to Persist Formatting
Formatting options like custom number formats lose their settings when refreshing datasets.
To persist custom blanks formatting, you can utilize Disconnected Tables.
For example:
1. Create a Disconnected Table copying the Sales Table.
2. Apply formatting like custom number formats to this table.
3. Use this Disconnected Table in visuals instead of the original Sales Table.
Disconnected Tables maintain custom formatting on refresh.
The limitation is managing an extra duplicate table. So this is best for persistent formatting that needs to apply across multiple reports.
Conditionally Merging Columns
To consolidate blanks across multiple columns, you can conditionally merge columns using DAX expressions.
For example:
Sales Combined =
IF(
NOT(ISBLANK([OnlineSales])),
[OnlineSales],
IF(
NOT(ISBLANK([StoreSales])),
[StoreSales],
BLANK()
)
)
This will display Online Sales if not blank, else Store Sales, else a blank value.
By merging columns, you avoid showing zeros when both columns are blank.
Limitations are complexity growing exponentially for multiple columns, and loss of granular data. So use judiciously when cross-column blanks are required.
Conclusion
To summarize, here are some best practices for displaying blank values instead of zeros in Power BI:
– Use BLANK() function for measures if possible
– Leverage default formatting behavior for visuals
– Apply custom formatting judiciously at visual level
– Minimize overriding model formatting to avoid inconsistencies
– Use report-level settings carefully due to limited scope
– Test across different visual types and filter states
– Balance simplicity vs. flexibility based on exact requirements
Converting zeros to blanks requires planning to avoid affecting user interpretation. Evaluate trade-offs between formatting methods and visualize your data thoughtfully with appropriate blank values.