Dynamically Changing Visualizations

Visualizations in a Power BI report use data from the Power BI model in two ways. First, values from columns are used to populate visual elements like the axis in a column chart, row labels in a table visual, or selection items in a slicer. We use the term label to generically refer to these elements. Second, aggregated data from the model, typically in the form of DAX measures, provides the results that a visual represents. While it is not visually clear, the buckets or wells that are used to bind a visual to data fields distinguish between these two types of data usage. For instance, the buckets Axis and Legend demand label data and the Values bucket needs aggregated data:

Figure 2.2.1: Field buckets for a Power BI visual

While Power BI provides many ways to create compelling reports out of the box, sometimes you want to go beyond these. This chapter presents approaches to dynamically change both ways of data binding through DAX....

The business case

A bicycle company, QuantoBikes, uses a Power BI model in order to keep track of their sales. Based on the data in the fSales table, the board has defined three key performance indicators (KPIs): the sales per month, the year-to-date sales, and the 12-month rolling sales. These KPIs can be analyzed by order date, invoice date, or delivery date, which are also available in the fSales table. Additionally, the board is interested in the sales by product, country, and retail type. The data for this information is stored in three different tables: Products, Customers, and Cities.

You can download the model file for this chapter, 2.2 Dynamically changing visualizations.pbix, from https://github.com/PacktPublishing/Extreme-DAX/tree/main/Chapter2.2.

The relationships between the different tables are depicted below:

Figure 2.2.2: The model diagram

The Date to fSales relationships consist of one active relationship from Date[Date] to fSales...

Dynamic measures

A visual is bound to a measure by adding the measure to an appropriate bucket, like the Value bucket in a column chart. What we want to achieve is to let the user select a KPI using a slicer and adapt the measure to that selection. As measure binding is static (we cannot dynamically replace the measure with another measure), we need to create a DAX measure that responds to the slicer selection.

For this dynamic measure to work, a couple of things are needed:

1. We need to create basic measures for each KPI.
2. In order to use a slicer, we need to create a helper table with the KPI description.
3. We need to create a new measure that, based on the selection, selects the corresponding basic KPI measure.

Let us start with the basics.

The basic KPI measures

First, we create the three basic DAX functions for our KPIs:

1. The sales per month will be calculated by the DAX table function SUMX:

   Sales = SUMX(
              fSales...

Dynamic labels

Consider the following challenge. Your Power BI report contains a column chart with sales by city. The users of your report want to have the option to select other labels for this chart, allowing them to view sales by, say, retail type or product group. Since you have already provided them with some slicers to select the measure used in the visual, you want to use a slicer to select the chart label as well.

As this book is about DAX, we solve this problem with DAX. There are other options in Power BI: you could use bookmarks to show different charts, or allow the report user to select other fields from the model with personalized visuals. All options have pros and cons, like ease of use, the need to change DAX code, and the possibility of using a slicer or other report element to make the selection. We do not elaborate on the other options here.

In this section, a helper table and custom DAX measures are used to solve this challenge.

Solution...

Combining dynamic labels and dynamic calculations

If you want to combine the dynamic labels and the dynamic calculations in one visual, you need to use a logical combination of the DAX logic we have seen so far. We can still use SWITCH to determine selections made by the user, but keep in mind that the number of options grows fast. In our case, we have three helper tables, each with three options, meaning 3 x 3 x 3 = 27 options inside the SWITCH function. That is a lot of DAX code to write! Here are examples of the results:

Figure 2.2.11: Sales by countries and order date

Figure 2.2.12: 12-month rolling sales by retail type and delivery date

It would be too much to include all this code on these pages. Instead, you can find the code in the model for this chapter. For now, let us take a look at some parts of the calculation. Below is the start of the DAX formula with three variable declarations to capture the choices of the user:

DynHelperSales3 = 
    VAR...

Summary

In this chapter, you have learned how to use helper tables to capture user input. Depending on your intended use, a helper table can be as simple as a few rows with options, or a larger list based on other data in the Power BI model. In most cases, helper tables have no relationships with other tables in the model. User input in the form of a selection in a slicer can be captured in DAX measures. The SWITCH function is used to select the appropriate calculation based on the user input.

Keep in mind that when using multiple helper tables for dynamic selections, it is better to use an extended SWITCH statement than to work with nested SWITCH functions. Be aware that nesting can also occur through calling another measure that does its own SWITCH. Piling up dynamic selectors like this can eventually lead to performance issues.

In the next chapter, we dive deeper into calendar-based analysis. While we have used several time-intelligence DAX functions in this chapter, many...