Power BI mainly uses SUMMARIZECOLUMNS to run queries. SUMMARIZECOLUMNS does not return a row when all the columns computed by the query return BLANK. This behavior is intentional and desirable, as it reduces the number of rows in reports. Despite being useful and intuitive, sometimes users want to see also rows that produced blanks because a value of blank (or zero) is meaningful.
Indeed, there is quite a difference between showing that a product did not produce any sale, and not showing the product at all. The former provides an indication of bad performance, whereas the latter hides the information as if it were not relevant. This is not to say that always showing zeroes is a suggestion. In some reports, zeroes have a meaning, in others they do not. The choice is made by the report author after a careful analysis of the requirements.
An example helps in clarifying the concept. Look at the following report:
The report is showing the sales amount in different European countries in 2008. Fine, but are there other countries that did not produce any sales? The answer is yes, but the report is not showing them. By removing the filter on the slicer and adding the year on the column of the report, the picture becomes more comprehensive:
This last report shows that Ireland, Italy and several other countries did not produce any sales in 2008, even though there were sales in 2007 and 2009. As we anticipated, this information might be precious or not; this is not relevant for this article. The topic of the article is the technique to show zeroes instead of blanks in a controlled way, in case you need that. Before we move further, let us elaborate on what “controlled way” means.
Imagine we build a report similar to the previous ones, this time at the product level, focusing on Silver cameras and camcorders. There are many products that did not produce sales in 2008; one is more interesting than the others: Contoso Telephoto Conversion Lens X400 Silver:
What makes this specific product interesting is that the product had sales in 2007, no sales in 2008 and it started selling again in 2009. Its behavior is different than the other products. Indeed, for most of these products one can argue that they start to produce sales when they were introduced in the market. Their behavior is quite intuitive: no sales up to a given point in time, then they start selling. We want to highlight this specific product because it shows a gap in sales when it was already present on the market. For other products, we are happy to blank them until their first sale. By doing this, we show gaps when they are real, and we avoid showing non-relevant information, that is products that could not produce sales because they were not even available to sell.
Therefore, the requirement is to be able to control when to start showing blanks. In our scenario, we control the presence of zeroes based on the product. In your specific scenario, requirements might be different; therefore, you should adapt the technique layed out here to your needs.
The final report needs to be the following, where you can see the zero for 2008, in the product we highlighted before:
As it always happens, there are multiple solutions to the same scenario. Forcing a value to be zero instead of BLANK is as easy as adding zero to the value. Indeed, BLANK plus zero equals zero. Despite being simple, this solution would show zero for any combination of columns that would result in a blank. For example, it would show zeros for all the years before 2007, or after 2009, just because these years are present in the Date table, despite Sales not containing any row for those years. The solution needs to be more sophisticated than a simple sum.
A pure DAX solution first determines the dates of the first and last transaction in the Sales table. Then, the measure checks whether the current date is in the detected range, in order to decide whether to add the zero or not. Here is a first solution:
SalesZero = VAR FirstSaleEver = CALCULATE ( MIN ( 'Sales'[Order Date] ), ALLEXCEPT ( Sales, 'Product' ) ) VAR LastSaleEver = CALCULATE ( MAX ( 'Sales'[Order Date] ), REMOVEFILTERS () ) VAR CurrentDate = MAX ( 'Date'[Date] ) VAR ForceZero = FirstSaleEver <= CurrentDate && CurrentDate <= LastSaleEver VAR Amt = [Sales Amount] + IF ( ForceZero, 0 ) RETURN Amt
You can change the formula to accommodate different needs. For example, an alternative way of detecting whether to force zero checks whether the currently selected range intersects the dates between FirstSaleEver and LastSaleEver:
… VAR ForceZero = FirstSaleEver <= MAX ( 'Date'[Date] ) && MIN ( 'Date'[Date] ) <= LastSaleEver …
You can choose the formulation that better fits your requirements.
This code runs fast and it works just fine. Still, there is a problem with it. The logic that defines whether a product should show zero or not is based on the Sales table. We determine whether to show a zero or not based on the presence of sales. This detail is basically a negation of our initial requirement: we wanted to be able to control when to show zero independently from Sales Amount. Indeed, if a product were introduced in the market on – say – the first of December, we want to start showing zeroes from the first of December, even though the first sale of the product were on the 15th of December. With the current implementation, this is not possible.
A better solution would be to store the information about the starting date in a separate table. If the logic – as it is in our scenario – is based on the product only, then a column in the Product table would be just fine. In that case, to determine the starting date to produce zeroes one could use a MIN of the column in Product. In a more complex scenario, you might have more sophisticated rules. For example, the starting date for zeroes might depend on the store, on the country, or – in general – on combinations of different dimensions.
In the more generic scenario, the best option is to avoid adding the logic to the DAX code and rely on a physical table instead. As an example, we created the following table using DAX, even though it is better to create it with Power Query to avoid the danger of circular dependencies:
ZeroGrain = VAR MaxSale = MAX ( Sales[Order Date] ) VAR ProdsAndDates = GENERATE ( 'Product', VAR FirstSaleOfProduct = CALCULATE ( MIN ( 'Sales'[Order Date] ) ) VAR Dates = DATESBETWEEN ( 'Date'[Date], FirstSaleOfProduct, MaxSale ) RETURN Dates ) VAR Result = SELECTCOLUMNS ( ProdsAndDates, "ProductKey", 'Product'[ProductKey], "Date", 'Date'[Date] ) RETURN Result
The table contains one row for each combination of date and product for which we want to show zero instead of BLANK. The table is quite large, because it is close to the CROSSJOIN of Product and Date tables. Later in the article we show how to reduce its size, if needed. We need to create relationship with the relevant dimensions; in our case, they are Product and Date:
Once the model is completed, the measure no longer needs to perform calculations over dates, because the entire logic about when to show zero instead of BLANK is now in the ZeroGrain table. Therefore, the code becomes much simpler:
SalesZeroWithTable = VAR ForceZero = COUNTROWS ( ZeroGrain ) > 0 VAR Amt = [Sales Amount] + IF ( ForceZero, 0 ) RETURN Amt
The result is the very same as the previous measure. The main advantage of using the ZeroGrain table is that the logic to decide whether to show BLANK or zero can now be much more complex. The price, in terms of performance, is due at process time, no longer at query time.
If the size of the ZeroGrain table is too large, you can change its granularity. For example, a good option would be to create the ZeroGrain table at the month granularity instead of the day granularity. By following this path, you reduce the size of the ZeroGrain table but, at the same time, you lose the option of using a regular strong relationship between Date and ZeroGrain. This can be easily solved by relying on a many-to-many cardinality relationship based on the YearMonth column. The increase in complexity due to the many-to-many cardinality relationship is mitigated by the reduced size of the ZeroGrain table and the two options work at comparable speed.
Once implemented for a single measure, this very same technique can be used to create a calculation group that applies the same logic to any measure, making the choice of using BLANK or zero a feature, by using the correct value for the calculation group.
The code of the calculation items is the same as the measure; therefore, we do now show it in the article. If you are interested in the calculation group solution, you can find it in the downloadable content of this article. The result is visible in the next figure, where we used the calculation group on the columns of the report, which is filtering only 2008. You can easily appreciate that when the Show blank calculation item is selected, the result is blank, whereas when Show zero is selected, the countries with no sales show zero instead.
The choice of which technique to use depends on your specific requirements. For simple scenarios, using the DAX-only based solution works just fine. But, if the requirements are complex or you need more control over when and how to show a zero instead of a blank, then using the ZeroGrain table proves to be an interesting technique to reduce the DAX code complexity, hence the query speed.
Create a summary table for the requested totals over set of groups.
SUMMARIZECOLUMNS ( [<GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, <GroupBy_ColumnName> [, [<FilterTable>] [, [<Name>] [, [<Expression>] [, … ] ] ] ] ] ] ] ] ] )
Returns a blank.
BLANK ( )
Returns the smallest value in a column, or the smaller value between two scalar expressions. Ignores logical values. Strings are compared according to alphabetical order.
MIN ( <ColumnNameOrScalar1> [, <Scalar2>] )
Returns a table that is a crossjoin of the specified tables.
CROSSJOIN ( <Table> [, <Table> [, … ] ] )