Building calculated columns

In this section, you will learn how to create calculated columns in Power BI using DAX. The building of calculated columns is a great way of extending the analytical capability of Power BI and by the end of this chapter, you will feel very comfortable with creating new columns through DAX. The writing of calculated columns logically occurs after the data model has been developed, therefore, in order to follow along with this section, you will need to open the pbix file Chapter 5 Leveraging DAX.pbix from the Microsoft-Power-BI-Start-Guide-Second-Edition-main\Starting Examples directory.

Calculated columns are stored in the table in which they are assigned, and the values are static until the data is refreshed. You will learn more about refreshing data in a later chapter.

There are many use cases for calculated columns, but the two most common are as follows:

• Descriptive attributes
• Concatenated key columns

Now you...

Calculated measures – the basics

Calculated measures are very different than calculated columns. Calculated measures are not static, and operate within the current filter context of a report; therefore, calculated measures are dynamic and ever-changing as the filter context changes. You were introduced to filter context in the previous chapter. The concept of the filter context will be slightly expanded on later in this chapter. Calculated measures are powerful analytical tools, and because of the automatic way that measures work with filter context, they are surprisingly simple to author.

Before you start learning about creating measures, let's first discuss the difference between implicit and explicit measures.

Implicit aggregations occur automatically on columns with numeric data types. You saw this in the previous chapter when the month number column was incorrectly aggregated after being added to a report. There are some advantages to this default behavior&...

Filter context

The automatic filtering that occurs in Power BI is a really awesome feature and is one of the reasons that so many companies are gravitating to this tool. The active relationships that are defined in the data model, and that you learned how to create in the previous chapter, are automatically used by DAX to perform the automatic filtering of calculated measures. This is directly tied to the concept of the filter context. You were introduced to the filter context in the previous chapter. I want to briefly expand on the previous chapter here before discussing the CALCULATE function.

A simple definition of the filter context would be that it is simply anything in your report that is filtering a measure. There are quite a few items that make up the filter context. Let's take a look at a few of them:

• Any attributes on the rows; this includes the different axes in charts.
• Any attributes on the columns.
• Any filters applied by slicers (visual filters); slicers...

Time intelligence

Another advantage of Power BI is how easily time intelligence can be added to your data model. DAX has a comprehensive list of built-in time intelligence functions that can be easily leveraged and add significant analytical value to your data model. In this section, you are going to learn how to use these built-in functions to create the following measures:

• Year to Date Sales
• Year to Date Sales (Fiscal Calendar)
• Prior Year Sales

Take a look at the alternative methods for calculating time intelligence in the DAX cheat sheet at https://tinyurl.com/pbiqs-daxcheatsheet.

In order to leverage time intelligence functions in DAX, you must have a date table in your data model and that date table must have all available dates with no gaps. These are very important conditions that must be met. Oftentimes, developers try to use the date column from their transaction table (fact table). This can result in calculations that do not work and return incorrect results...

Role-playing tables with DAX

In Chapter 4, Building the Data Model, you learned how to develop your data model to deal with role-playing tables, by importing a table multiple times. We mentioned then that there was an alternative method using DAX. In this section, we will explore this alternative method and the pros and cons of using DAX versus the method you have previously learned.

Since leveraging DAX does not require importing a table multiple times, you will immediately gain savings on storage and, unlike the other method, with DAX you will not need to manage multiple tables in Power BI Desktop.

The DAX method requires that inactive relationships be created in order to work correctly. Inactive relationships are not often used in DAX because they are not used automatically like active relationships. Unlike active relationships, you can have more than one inactive relationship between two tables.

Let's create a new relationship between the Internet Sales...

Summary

In this chapter, you learned that DAX allows you to significantly enhance your data model by improving the analytical capabilities with a relatively small amount of code. You also learned how to create calculated columns and measures and how to use DAX to perform useful time series analysis on your data.

This chapter merely scratched the surface of what is possible with DAX. As you further explore the DAX language on your own, you will quickly become a proficient author of DAX formulas. As with everyone who learns DAX, you will inevitably learn that there is a layer of complexity to DAX that will require further education to really master. When you get to this point, it would be advantageous to look for classes or books that will help you to take your skills to the next level and truly master DAX!

Visualizing trend data

The term trend data refers to displaying and comparing the change in value over time. Power BI provides many options in this category, each with its own focus. The idea for each of the visuals is to draw attention to the total value across a length of time. Create a new report page called Trend Data, and dive right in to see what the differences are between the following options:

• Line and area charts
• Combo charts
• Ribbon charts
• Waterfall charts
• Funnel charts

To begin, let’s explore the line and area charts. These are the most commonly used charts for visualizing trend data, and the ones that the visualizations report consumers are likely most familiar with already.

Line and area charts

The Line chart is the most basic of the options when it comes to analyzing data over time. The Area chart and Stacked area chart are based on the Line chart; the difference is that the area between the axes and the line...

Visualizing KPI data

KPIs, or Key Performance Indicators, are measurable values that demonstrate how well a company is achieving a certain objective. Power BI has several options to measure the progress being made toward a goal for operational processes. The strength of a KPI visual lies in its simplicity. It displays a single value and its progress toward a specific goal.

Create a new report page called KPI Data and take a closer look at the gauge and KPI visuals.

Gauge

The Gauge visual displays a single value within a circular arc and its progress toward a specified goal or target value. The Target value is represented by a line within the arc. With the current dataset, there is no measure that can be used to illustrate an accurate business goal, so one will have to be created. Before setting up this visual, a new calculated measure will need to be created.

The gauge will be using the Total Sales field as the Value field. The target will be 10% more than the previous...

Visualizing data using cards

The ways for Power BI to get detailed data into the hands of a user are vast. Tables, matrices, bar charts, and combo charts all provide large quantities of data to users in a single visual. Sometimes, like with a KPI, users just need to see a number. When the trend or target components of a KPI are not required, turn to the Card visualization. The Card is the most basic of visuals displaying only a single value. If slightly more detail is necessary but required at a group level, look to the Multi-row card.

Before moving on, create a new report page called Card Data.

Card

The Card is useful for highlighting a series of related metrics in a dashboard, displaying the most recent or oldest date in a dataset, and calling out important numbers for a detailed report. Some formatting options are available to change the font size or color, but at its core, the card visual just displays a single value.

Let’s look at setting up a Card:

...

Visualizing geographical data

One of the most exciting ways to visualize data in Power BI is through the various maps. All the maps serve the same purpose, to illustrate data in relation to locations around the world, but there are some small differences between each of them. All of the maps, except the Shape map, have the option to visualize latitude and longitude coordinates, which will be the best way to ensure the appropriate location is displayed. The reason for this is that the information provided to the visual will be sent to Azure Maps or Bing Maps to verify the positioning on the map. If you do not provide enough detail, then Azure may not return the desired results.

For example, if you were to provide the map visual with a field that contains only the city name, that could result in some confusion because there may be multiple cities with that name in multiple states, provinces, or even countries. In these scenarios, you will either want to supply some sort of geo...

Advanced visualizations

Power BI contains a robust set of visuals for analyzing data, from tables and charts to immersive mapping. Sometimes, you want to go beyond a standard set of visuals. Thankfully, Power BI also provides a set of visualizations that go far beyond just displaying data points in interesting ways. Let’s explore some unique and interesting ways Power BI allows you to interact with data and build unique reporting solutions.

Natural language with Q&A

Not all data is as straightforward as showing the sales amount by month. Often, when a report is being developed, you may not know all the different visualizations a user would like to see. While Power BI has great flexibility thanks to built-in cross-filtering, drilldown, and the ability to see data behind a visual, it will never be able to cover all possible reporting scenarios. One of the most powerful ways to enable self-service functionality in Power BI is using the Q&A visual. The Q&A...

Data visualization tips and tricks

You have created eight different report pages filled with different visuals and investigated different configuration options for each of them. That being said, you have barely scratched the surface of all the features that are available to you, and with the very quick update cycle that Power BI has, that list of features will keep growing. This final section will explore a couple of features that are not exclusive to just one visual but can really help out when designing a report. It is highly recommended to watch the monthly videos that the Power BI team embeds in the product update announcements each month, which are published at https://powerbi.microsoft.com/blog/. This way, you can know exactly what is new and how to use it.

Changing visuals

Throughout this chapter, the workflow has been the same: add a blank visual then add fields. Often, this will work in a real-world development environment as well. However, there are times when you...

Summary

In this chapter, the focus was on how to configure visuals and what data they best illustrate. You also saw a couple of the most common formatting options that are used with these visuals. In the next chapter, you will look into the concept of digital storytelling. Power BI has a strong set of options that can be leveraged to allow users to experience and navigate through the data in an adventurous and exploratory manner.

Join our community on Discord

Join our community’s Discord space for discussions with the authors and other readers:

https://packt.link/ips2H