A better way to get results without data

Author:Murphy | View: 28156 | Time: 2025-03-23 19:37:15

Introduction

A few weeks ago, I already wrote an article about this topic:

How to show a result when there is no data in Power BI

In that article, I used Power Query to generate additional data rows to fill the data table and get the needed result.

Now I found a much easier way to solve this challenge.

First, I will explain the challenge. If you know my article linked above, the following section describes how I found the new approach. Then I will show you the new solution.

The challenge

Imagine a report with a table showing multiple tasks with the associated costs and the status of each task:

Figure 1 – Table as starting point (Figure by the Author)

Now, imagine a similar table. But now there are no tasks in the status "Failed":

Figure 2 – Table with missing status (Figure by the Author)

Now, my client wants to see zeroes instead of empty cells, and he wants to see all possible statutes, even when no task has a specific status, as shown in the Figure above.

The problem is that no calculation is executed if no values exist in the data table, which means it is impossible to show a zero instead of empty cells.

Inspiration for the new approach

A few days ago, one of my clients asked me about an issue with his Power Bi Report.

Please understand that I can only show a few details here.

In a nutshell: He calculated a percentage and detracted the resulting amount from 1.

The Measure looks like this:

Measure Name = 1 - ( CALCULATE(SUM(column1)
                               ,[Column2] = "Filter Text"
                               )
                           / 12345 )

As a result, he always got 100 % when expecting an empty result.

After some testing, I realized the "1 –" part was the culprit.

The result was calculated correctly when the CALCULATE() function returned a value.

Without any result from CALCULATE(), the engine calculates 1–0 = 1 à 100 %

Power BI regarded the value 1 as a constant and didn't apply any filter.

As a result, Power BI returned 100 % when no data existed in the table.

I had to add an IF() to solve his issue to check if the CALCULATE() function returned a value:

Measure Name = 
 VAR Result = CALCULATE(SUM(column1)
                        ,[Column2] = "Text"
                        )
RETURN
IF (NOT ISBLANK(Result)
     , 1 - ( Result / 12345 )
     )

Now the Measure returned a value only when CALCULATE() returned a value.

Reversing the idea

From this, the idea emerged to reverse this "issue" and transform it into a solution.

What if I could add a Measure to my solution and calculate 0 + SUM(‘table'[column])?

In this case, I would get 0 if SUM didn't return any result. And when SUM() returns a value, it will not change by adding 0.

But wait. There's a problem: How can I display any result when no rows with the Status "Failed" exists?

To solve this problem, I must slightly change my data model.

Initially, my data model consisted only of the table with the values.

To force a calculation, I need a table with all the Statuses and use it as a Dimension to enforce a Filter Context for each row in this table:

Figure 3 – The Status table (Figure by the Author)

Then, I must add a relationship to my Demo Data table:

Figure 4 – Data model with two tables (Figure by the Author)

Now I changed my report to get the Status column from the Status table and added a new Measure:

Costs = 0 + SUM('Demo Data 2'[Cost])

Below, you can see the data from the original table (With data for all statuses), and below, you can see the data from the table with no rows for the Status failed using the new solution:

Figure 5 – Both results compared (Figure by the Author)

This is the result needed by my client.

Conclusion

The dataset is fictional and created from scratch in Excel, like in the previous article.

While my first solution was built on the assumption that the data table was all I had, now I had to create a new Status table and ensure that all possible statuses were contained in this table.

You might be able to extract this information from your data source.

If not, you might use my old solution, based on Power Query, or create a table in Power BI with the "Enter Data" feature.

Regardless of how you add the additional table, you need it to be able to generate a Filter context, which can be used by the Measure to always return a value.