Deckler DAX for Humans

The No CALCULATE Guide that Makes DAX Easy
1. Auflage 2025
ISBN: 978-1-80669-994-0
Verlag: De Gruyter
Format: EPUB
Kopierschutz: 0 - No protection

E-Book, Englisch, 594 Seiten

Although DAX has been around for over a decade, many struggle to master the language primarily because DAX is often taught through the CALCULATE function, which is the most complex and unintuitive function in all of DAX. But what if DAX could be taught without CALCULATE? The result would be an incredibly intuitive and easy way to learn DAX.
DAX for Humans stands the traditional approach to learning DAX on its head, foregoing the traditional, legacy methods of learning DAX for a more modern approach that focuses on core DAX concepts and not any specific function. Even if you know nothing about DAX, from the very first chapter you will learn the essentials of the DAX language, as well as a single pattern to solve the majority of DAX problems. From that point forward, you'll explore how to work with the basic building blocks of the DAX language and apply what you learn to real-world business scenarios across customers, human resources, projects, finance, operations, and more.
By the end of this book, you'll be able to apply your DAX skills to simple, complex, and advanced scenarios; understand how to optimize and debug your DAX code; and even know how to efficiently apply artificial intelligence to help you write and debug your DAX code.

Autoren/Hrsg.

Deckler, Greg

Fachgebiete

• Mathematik | Informatik EDV | Informatik EDV & Informatik Allgemein

Leseproben

1

Introducing DAX

Data Analysis eXpressions (DAX) is a formula language that many might recognize as being similar to Excel’s formula language. While this similarity is perhaps rather superficial, it is sufficient for our purposes that similar to how users can create formulas in Excel using Excel’s formula language, so too can Power BI Desktop users create calculations in Power BI using DAX. While DAX can also be used in Excel with the Power Pivot add-in as well as in Analysis Services, this book focuses on the use of DAX within Power BI Desktop, which is Microsoft’s premier business intelligence and reporting software and service.

Historically, DAX has a reputation of being difficult to learn and master. One might find this surprising considering that there are almost 200 functions in DAX that share the same name and functionality as Excel’s formula language. Considering that DAX has just over 250 functions in total, these nearly 200 functions represent the majority of the DAX language and yet Excel’s formula language has never been classified as “difficult to learn”.

This seeming contradiction has its roots in the fundamental differences between how Excel’s formula language “thinks” and how DAX “thinks”. However, a larger issue is the traditional method by which DAX is taught, which centers on DAX’s CALCULATE function. DAX’s CALCULATE function is one of, if not the most, complex and difficult to understand functions in the DAX language. Attempting to learn DAX by first learning CALCULATE is like trying to learn physics by starting with quantum mechanics.

Therefore, this book takes a fundamentally different approach to learning DAX. In fact, by the end of this chapter, you will have already learned many of the functions required to create most common DAX calculations as well as learned a single, fundamental DAX formula pattern that will help you create just about any DAX calculation you can conceive of.

First, however, you will need to download and install the software where you will be writing your DAX formulas, so let’s start by getting Power BI Desktop installed on our computer.

Getting Power BI Desktop

As mentioned, Power BI Desktop is Microsoft’s premier business intelligence and reporting application. Power BI Desktop is designed to connect to data, mash up that data into a model and then analyze and visualize that data interactively in the form of bar charts, line charts, waterfall charts, etc.

To install Power BI Desktop, you must have a Windows PC running Windows 8, 8.1, 10 or 11. It is also recommended that you have at least 10 GB of disk storage space available.

The easiest way to install Power BI Desktop is to enter the following link into any browser window, https://aka.ms/pbidesktop. When prompted to open the Microsoft Store, click the Open button as shown in :

Figure 1.1: Microsoft Store open prompt

Within the Microsoft Store application, click the Install button to install Power BI Desktop as shown in :

Figure 1.2: Installing Power BI Desktop from the Microsoft Store

Alternatively, you can type the word “store” into the Windows search bar and open the Microsoft Store directly as shown in :

Figure 1.3: Opening the Microsoft Store from the Windows search bar.

In the Microsoft Store search bar, type “power bi desktop” and click on the suggested app:

Figure 1.4: Power BI Desktop app in the Microsoft Store.

You can now click on the Install button as shown in .

Watch the Install button to keep track of installation status. The app download is approximately 730 MB. Once downloaded and installed, the Install button will now have the text “Open”. Click the Open button to open Power BI Desktop.

It is recommended that you right-click the Power BI Desktop icon in the Windows taskbar and select “Pin to taskbar”. This pins Power BI Desktop to the taskbar for easy access. Next, close any splash screen that might appear for Power BI Desktop. Finally, on the Power BI Home page, select Blank report:

Figure 1.5: Power BI Desktop Home page.

We are now ready to briefly tour Power BI Desktop.

Touring Power BI Desktop

As shown in , the Power BI Desktop user interface is generally analogous to other Microsoft Office applications, such as Microsoft Excel, with a Header, Ribbon, and central area, in this case known as the Canvas.

Figure 1.6: Power BI Desktop.

Header

As with other Microsoft Office applications, the Header area provides the ability to quickly save the file using the far-left icon as well as the name of the file, in this case “Untitled”, a Search bar and standard minimize, maximize and close icons on the far right.

Ribbon

Below the Header is the Ribbon which provides access to various actions that can be performed across a number of tabs such as File, Home, Insert, Modeling, View, Optimize, and Help. Some of these tabs are conditional, such as the External tools tab shown in . The External tools tab only appears if external (third-party) tools are installed such as Tabular Editor or DAX Studio.

Views

Below the ribbon on the far left is the Views pane. The Views pane contains four different views listed from the top to the bottom:

• Report view – Used for building interactive reports consisting of one or more visualizations.

• Table view – Used for viewing the underlying table data.

• Model view – Used for viewing and editing the semantic model (relationships between tables).

• DAX Query view – Used for writing and debugging DAX queries.

You will become familiar with these views as you progress in this book.

Canvas

To the right of the Views pane is the Canvas. The Canvas is actually different depending upon the view selected. In Report view, the Canvas is the central area where you place visuals as shown in .

However, in Table view, the Canvas presents the report data in a manner similar to an Excel file as columns and rows as shown in :

Figure 1.7: Table view.

Additionally, as shown in , only certain ribbon tabs are available while in Table view.

In the Model view, the Canvas is again different and displays the various tables of the model as well as any relationships between those tables.

Figure 1.8: Model view.

Finally, in DAX Query view, the Canvas serves as an area for entering DAX queries and viewing results.

Figure 1.9: DAX Query view.

We will explore the purpose and function of the Canvas in each of these views throughout the rest of the book but let’s now move on to Fly-out panes.

Fly-out panes

Similar to Ribbon tabs, the Fly-out panes are conditional depending upon which View you are in. These fly-out panes provide access to various functionalities including:

• Data – Enables you to select data as well as edit the properties of the data and edit the semantic model.

• Visualizations – Provides the ability to build and modify visuals

• Filter – Allows you to filter the entire report, individual pages or individual visuals.

These are not the only fly-out panes available but are the three primary fly-out panes used within this book. Other fly-out panes will be introduced as necessary throughout the rest of the book.

The fly-out panes can be expanded and collapsed by clicking on the double chevron icons ( >> to collapse, << to expand ) in the upper-right corner of each fly-out pane.

Pages

Pages in Power BI Desktop are similar to tabs in Microsoft Excel. Just as an Excel workbook can have multiple tabs of data, so too can a Power BI Desktop file have multiple report pages.

The Pages area also has different functions depending upon the particular View you are in. For example, in the Model view, the Pages area is used to present multiple views of the semantic model as shown in , while in DAX Query view, the Pages area is used to create multiple DAX queries as shown in .

Footer

The Footer area also varies depending upon your current View. However, in general, the Footer area provides additional controls and information such as a zoom slicer, the current...