nach oben

2022 | Buch

Kapitel lesen Erstes Kapitel lesen

Up and Running with DAX for Power BI

A Concise Guide for Non-Technical Users

verfasst von: Alison Box

Verlag: Apress

Enthalten in: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Einloggen, um Zugang zu erhalten

Über dieses Buch

Take a concise approach to learning how DAX, the function language of Power BI and PowerPivot, works. This book focuses on explaining the core concepts of DAX so that ordinary folks can gain the skills required to tackle complex data analysis problems. But make no mistake, this is in no way an introductory book on DAX. A number of the topics you will learn, such as the concepts of context transition and table expansion, are considered advanced and challenging areas of DAX.

While there are numerous resources on DAX, most are written with developers in mind, making learning DAX appear an overwhelming challenge, especially for those who are coming from an Excel background or with limited coding experience. The reality is, to hit the ground running with DAX, it’s not necessary to wade through copious pages on rarified DAX functions and the technical aspects of the language. There are just a few mandatory concepts that must be fully understood before DAX can be mastered. Knowledge of everything else in DAX is built on top of these mandatory aspects.

Author Alison Box has been teaching and working with DAX for over eight years, starting with DAX for PowerPivot, the Excel add-in, before moving into the Power BI platform. The guide you hold in your hands is an outcome of these years of experience explaining difficult concepts in a way that people can understand. Over the years she has refined her approach, distilling down the truth of DAX which is “you can take people through as many functions as you like, but it’s to no avail if they don’t truly understand how it all works.”

You will learn to use DAX to gain powerful insights into your data by generating complex and challenging business intelligence calculations including, but not limited to:

Calculations to control the filtering of information to gain better insight into the data that matters to youCalculations across dates such as comparing data for the same period last year or the previous periodFinding rolling averages and rolling totalsComparing data against targets and KPIs or against average and maximum valuesUsing basket analysis, such as “of customers who bought product X who also bought product Y”Using “what if” analysis and scenariosFinding “like for like” salesDynamically showing TopN/BottomN percent of customers or products by salesFinding new and returning customers or sales regions in each month or each year

Who This Book Is For

Excel users and non-technical users of varying levels of ability or anyone who wants to learn DAX for Power BI but lacks the confidence to do so

Inhaltsverzeichnis

Frontmatter

Chapter 1. Show Me the Data

Abstract

The key to understanding DAX is getting to grips with the challenging concepts that underpin the expressions. Most DAX expressions you’ll write will amount to only a few lines of code, but it’s what goes on under the hood that is the secret to understanding their evaluation. For example, take this DAX expression:

Alison Box

Chapter 2. DAX Objects, Syntax, and Formatting

Abstract

Now that you’re up to date with the data we’ll be using throughout this book, the next step is learning how to construct DAX expressions. In this chapter, you will compare and contrast DAX expressions to Excel formulas as this will provide context for your knowledge. You will learn to reference objects, the syntax of DAX expression, and how you can format your DAX code, making it easier to read and debug.

Alison Box

Chapter 3. Calculated Columns and Measures

Abstract

In the previous chapter, you learned the syntax used by the DAX language, and now you’re ready to write your first DAX expressions. In DAX, there are three types of expression: calculated columns, measures, and calculated tables. However, in this chapter, we will only be addressing the first two types (we look briefly at calculated tables in Chapter 15).

Alison Box

Chapter 4. Evaluation Context

Abstract

You have learned to author simple calculated columns and measures, but one of the most fundamental questions for DAX users is how these two types of expression differ. At this stage, you understand that calculated columns are row-level calculations and that measures are calculations that are performed at the report level. However, we need to be more specific regarding this differentiation, and you need to understand that the definitive difference lies in the context in which the expressions are evaluated. In calculated columns, expressions are evaluated in the row context; in measures, they are evaluated in the filter context. It is the latter of these that will be the main focus in this chapter. Once you understand the implications of the filter context, the implications of the row context are more readily understood.

Alison Box

Chapter 5. Iterators

Abstract

There is a group of functions in DAX that are referred to as iterators, and from their name, we can infer that these functions iterate tables in the evaluation of a DAX expression. Any DAX function that ends in an “X” is an iterator, such as the “X” aggregators: SUMX, AVERAGEX, MAXX, MINX, COUNTAX. There are also “X” iterating functions that aren’t aggregators such as CONCATENATEX and RANKX. Just to make life even more confusing, there are iterating functions that don’t end in “X” such as FILTER and ADDCOLUMNS.

Alison Box

Chapter 6. The CALCULATE Function

Abstract

CALCULATE is the most important function in DAX. Quite a sweeping statement you might think but as soon as you get to grips with CALCULATE, you’ll quickly realize that there won’t be many expressions you author in DAX where this function won’t be required, even though you might think we’ve done pretty well up to now. In this chapter, you will learn how to construct expressions using CALCULATE which you will find relatively straightforward. It’s understanding when and why you must use CALCULATE, and its purpose inside the measure, that will be more challenging to grasp, and so this will be the true focus of this chapter.

Alison Box

Chapter 7. DAX Table Functions

Abstract

A skill that will serve you well when working with DAX is a good imagination. You’ve already learned to construct a picture in your mind of the current filters that are propagating through the data model. The scanning of tables by iterators can only be envisaged, and designing the correct CALCULATE expression is done through inferring what filters must be changed. There is yet another aspect of DAX that is hidden from us, and that therefore must be imagined. That is the generation of virtual tables. Much of your DAX code will involve building in-memory tables that are used in the evaluation of the measure. In this chapter, we are going to explore this concept, how we create table expressions through the use of table functions, and their purpose in manipulating the data model. In doing so, we will be focusing on the most ubiquitous of the table functions, and that is the FILTER function.

Alison Box

Chapter 8. The ALL Function and All Its Variations

Abstract

In previous chapters, we have explored the filter context and how the construct of the visual, slicers, and filters all come together to filter the data model on the evaluation of a measure. You have learned that with the CALCULATE function, you can modify these filters programmatically. What you don’t yet know is how to remove filters so you can calculate your own totals and subtotals. But better still, knowing how to remove filters means you can programmatically reapply totally different filters than those that are currently defining the filter context. Let me introduce you to the ALL function that allows you to take control of this aspect of the evaluation of your measures.

Alison Box

Chapter 9. Calculations on Dates: Using DAX Time Intelligence

Abstract

Have you ever wanted to compare sales for the current month against sales for last month? Or perhaps something a little more ambitious, such as cumulative totals or even a rolling monthly average? If the answer is yes, and why wouldn’t it be, calculations using date data such as these require the use of a group of DAX functions called “time intelligence” functions. Exploring these functions will be the focus of this chapter, and you will learn how to design expressions to enable you to evaluate data across different granularities of time such as financial years, quarters, months, and even down to the day grain. In doing so, you will be able to compare and contrast calculations over those periods to build insights into the data that’s important to you, such as trends and patterns over time.

Alison Box

Chapter 10. Empty Values vs. Zero

Abstract

In this chapter, we will look at a very specific DAX behavior, and that is how DAX treats empty, missing, and null values.

Alison Box

Chapter 11. Using Variables: Making Our Code More Readable

Abstract

We’ve managed very well so far without the use of variables in our DAX code. Indeed, variables haven’t always been around in the DAX language. They came on board in 2015, five years after DAX was first developed. In this chapter, we will elaborate on why variables are so useful when writing DAX expressions, and once you’ve learned how to utilize them, we will be including them henceforth in our expression, where applicable.

Alison Box

Chapter 12. Returning Values in the Current Filter

Abstract

There is often a requirement when designing reports to display the value or values selected in slicers or in the Filters pane. This might be to show these values in the title of a visual using conditional formatting or to show them in Card visuals, as shown in Figure 12-1.

Alison Box

Chapter 13. Controlling the Direction of Filter Propagation

Abstract

Up to now, you have understood that filters only flow from the one side of the relationship to the many, from dimensions into the fact table, as indicated by the arrows in the linking lines in Model view; see Figure 13-1.

Alison Box

Chapter 14. Working with Multiple Relationships Between Tables

Abstract

In our data model, all our tables have single relationships between other tables. Indeed, it’s only possible to have one active relationship between any two tables, but you can have as many inactive relationships as you want. In this chapter, you will learn how to use multiple relationships between tables and activate inactive relationships. This may be because you require multiple links from a dimension table into the fact table. However, there is another less obvious use of inactive relationships that we will discover in this chapter, and that is using comparison dimension tables. Here, we can use measures to force filter propagation through the comparison dimension table, therefore being able to compare a column from a default dimension with its counterpart in a comparison dimension.

Alison Box

Chapter 15. Understanding Context Transition

Abstract

You could also say that nothing in DAX that’s worth anything is easy. Certainly, the concept of context transition is one of the more challenging theories to get to grips with in DAX. It can’t be explained in a few short paragraphs, and therefore, we dedicate this entire chapter to teaching you the details of what context transition is and how it is used within DAX expressions. It’s only then can you move forward in the following chapter to explore some practical applications of this concept. Once you understand the purpose of context transition in your code, a whole range of challenging calculations becomes possible. In fact, most DAX expressions you meet will probably be using context transition, and indeed, there will come a time when most DAX expressions you write will use it.

Alison Box

Chapter 16. Leveraging Context Transition

Abstract

In the last chapter, you learned how context transition enables you to programmatically aggregate data into dimensions and virtual tables. You could then author expressions that grouped and aggregated data at this higher granularity. Once you have learned the skill of using DAX in this way, the world of DAX opens up to you considerably. You will now be able to author more complex calculations that enable you to gain deeper data insights. In this chapter, you will be applying your knowledge of context transition to solving the following data analysis questions:

Alison Box

Chapter 17. Virtual Relationships: The LOOKUPVALUE and TREATAS Functions

Abstract

Our data model comprises well-defined physical relationships between the tables, generating a star schema. However, there is another type of relationship we can create, and that’s a “virtual” relationship. A virtual relationship is a DAX expression that simulates the behavior of a physical relationship defined in the data model. In this chapter, you will learn to create virtual relationships that can resolve problems created by anomalies in the data model. Such anomalies can exist for the following reasons:

Alison Box

Chapter 18. Table Expansion

Abstract

Alison Box

Chapter 19. The CALCULATETABLE Function

Abstract

Now that you are officially a DAX expert, you are ready to confront DAX expressions that will truly test your knowledge and understanding of DAX. One of the DAX functions that can only be understood with a clear grasp of how DAX works is CALCULATETABLE, and this rather obscure function is the last function we will investigate in this book.

Alison Box

Backmatter

Titel: Up and Running with DAX for Power BI
verfasst von: Alison Box
Verlag: Apress
Electronic ISBN: 978-1-4842-8188-8
Print ISBN: 978-1-4842-8187-1
DOI: https://doi.org/10.1007/978-1-4842-8188-8