Working with AutoExist

When working with DAX, you should not forget that the whole point of what you are doing is to create useful output from your model. It is therefore useful to have some understanding of how Power BI visualizes results from a model. Without going into everything possible in Power BI visual reports, there are some fundamentals of how visualizations and a Power BI model work together that need to be discussed here. One of these is a lesser-known concept called AutoExist, which is the subject of this chapter.

AutoExist is a Power BI feature that aims to speed up reports by only evaluating DAX measures for relevant data points. The challenge here is that Power BI determines which data points are relevant by guessing, and while these guesses are good in many cases, sometimes they are not. Through a practical example, you will learn how AutoExist works, how to leverage it to solve specific problems and optimize the performance of your reports, and how to solve the...

The Power BI model

Most of this chapter is based on a small sample model:

Figure 2.4.1: Diagram of the Power BI model

We have a fact table, fSales, with sales transactions for customers in the Customer table. A Customer has a responsible, who is an employee; the model therefore relates the Responsible column in Customer to the EmpNr column in Employee. So, with this model, we can report sales by customer, but also by employee, or any of either's attributes.

This model file, 2.4 AutoExist.pbix, can be found at https://github.com/PacktPublishing/Extreme-DAX/tree/main/Chapter2.4.

We will mainly work with the results of a single DAX measure for sales:

Sales = SUM(fSales[SalesPrice])

In addition, we use a measure that only returns sales for some product categories:

Sales (core products) =
CALCULATE(
    [Sales],
    KEEPFILTERS(
        Product[Category] 
        IN {"Bikes", "Clothing", "Accessories"}
  ...

How Power BI visualizes the output of a model

As you will know, a Power BI report's main components are visualization objects. These objects take some fields from a model (either included in the same file, or a remote model) and render a visualization of the information provided by the model.

As with many elements in Power BI, these visualizations work like a beginner user would expect in many situations. When things become more complex, however, you may easily run into unexpected results. In these cases, it is useful to understand a bit more about the technicalities of visualizations. Why do they show what they show, and how do they do it?

Visual filters and context

A core concept in Power BI is that of filters. This is specifically true for DAX, but filters do play an important role in visualizations as well. Let's take this simple report as an example:

Figure 2.4.2: A simple Power BI report

You can see what the chart represents: sales numbers...

What AutoExist is, and what it does

In the previous section, we discussed how visuals are populated with data and calculated results. Typically, when using measures in a visual, more calculations are performed than what you get to see in the visual. Calculations are also needed to determine which data points should be presented.

This raises the question: how many calculations are being done? And is it possible to control that number? AutoExist is a Power BI feature that optimizes the number of calculations needed to populate a visual. In this section, you will learn how this is done.

Using multiple filters in a visual

To explain AutoExist, we create a table visual using two columns from the Customer table:

Figure 2.4.8: Table with Country and RetailType

As you can see from the table, there are some customers in Canada with the Limited retail type. In Australia, however, there are none. We know that when we add a measure to this table, we will not necessarily...

Example: The case of the missing workdays

Having something like AutoExist sounds like a wonderful idea. Indeed, minimizing the number of calculations to be done is always helpful for performance. But there are situations where AutoExist gets in the way of what you want your Power BI model to achieve. In this section and the next, we will discuss a scenario where you have to circumvent AutoExist for things to work correctly. As with most of the cases in this part of the book, this one is based on a real customer's analytical challenges.

The business case

Let's assume that the company QuantoBikes, which sells bikes, parts, and accessories, has a business-to-business (B2B) sales channel where sales representatives actively pursue prospects, close deals, and book orders. As a B2B business, sales take place mostly on workdays. Here is a chart of typical sales in July 2020:

Figure 2.4.13: Daily sales chart for QuantoBikes

As a month progresses, we are interested...

How to solve the missing workdays problem

The case of the missing workdays is a not-so-exotic situation that appears to bring up rather deep issues. It can happen in any situation where you change the context to calculate something other than what's available in the original context. In other words, whenever you use CALCULATE to change context, you may run into this problem without even noticing it. As filtering and context transformation is core to working with DAX, it is a fundamental issue. And as you may have guessed, it has everything to do with AutoExist.

The root of the problem

Let's take a very close look at the context in the last datapoint in May 2020's order intake chart. We haven't included all report elements in the image, but you can guess that there are a number of filters in this context:

• 'Calendar'[Year]: 2020
• 'Calendar'[Month]: May
• 'Calendar'[Workday in Month]: 21

What we would...

Optimizing report performance with AutoExist

When you know how AutoExist works, you can use this knowledge to solve common performance issues in Power BI reports. In this section, we discuss a number of things for you to consider.

Granularity in fact tables

As we discussed in Chapter 1.2, Model Design, the typical structure of a Power BI model consists of fact tables and filter (or dimension) tables. In most real-life cases, multiple fact tables exist in a model. By connecting these to the same filter tables, you can compute aggregate results over these fact tables simultaneously.

Things get a bit more complicated when your fact tables have different granularity. With this, we mean when some facts are gathered on a more detailed level than other facts. Take, for instance, a sales model for a company that sells blocks. Blocks come in different colors and shapes, as well as different sizes, types, and so on.

This model can be found as 2.4 AutoExist - Product Hierarchy...

Summary

While most of the DAX challenges you will face, and most of this book's chapters, are fundamentally about context, or what is being calculated, this chapter focused on which calculations are done to populate a visual from a Power BI model.

Power BI applies AutoExist to guess the combinations of label values to evaluate. The simple rule is:

• When columns from the same table are used in a visual, only combinations of column values that are found in rows of the table are evaluated.
• When columns from different tables are used, all combinations of values are evaluated.

Understanding how AutoExist works will help you to find out why you sometimes do not see results in a visual when, logically, you would expect them. It also helps to avoid performance problems in reports that are the result of using too many columns from too many tables in one visual. An important lesson is this: be careful with table visuals!

The next chapter returns mainly...