Press "Enter" to skip to content

Category: DAX

Context Transition in DAX

Published 2024-09-11 by Kevin Feasel

Marco Russo and Alberto Ferrari draw on a cocktail napkin:

In previous articles, we introduced a visual approach to describing two important DAX concepts: the filter context and the row context. This article completes this short series by describing the context transition using a graphical visualization.

This article provides a different perspective on the context transition already covered in other articles: you should read them to get more insights on this important concept for DAX.

Read on to see how it all fits together.

Comments closed

Displaying Dates without Data in Power BI

Published 2024-08-29 by Kevin Feasel

Alon Ohayon looks for the missing month:

When you’re using Power BI, you probably create line charts that show data by month pretty often. It usually works great, but what happens if some months don’t have any data?

By default, Power BI just skips those months in the chart. That might seem okay, but it can actually be misleading—especially if you’re looking for trends over time, including the months with no activity.

Alon shows us an example of how to do this in DAX and that works. But if you can solve this at the data layer, such as when querying from a SQL Server, that’s even better. This happens to be one of the good uses of a calendar table: giving you a complete set of months (or whatever time period you want) that you can then use to left join to your data, returning either a data point with a value, or a NULL that you can coalesce with 0 to ensure that you have a result for each month.

Comments closed

The Importance of Filtering Columns instead of Tables in DAX

Published 2024-08-29 by Kevin Feasel

Marco Russo and Alberto Ferrari explain it to us:

You have probably heard multiple times the same answer to many questions about DAX and, to be honest, about nearly anything IT-related: “it depends”. Is it fine to create a calculated column, or is it better to avoid it? Yes, no… it depends. Is it better to create one, two, or three fact tables? It depends. Is a composite model the right choice? It depends. However, a few questions have a strong, clear answer, and in this article, we focus on one of those. Is it better to filter a table or a column with CALCULATE? Here, the answer is simple and definite: filter columns, not tables. The same principle is applied to CALCULATETABLE, even though the example in this article only shows CALCULATE.

With that answer in mind, read on for the wherefore.

Comments closed

A Visual Explanation of Row Context in DAX

Published 2024-07-30 by Kevin Feasel

Marco Russo and Alberto Ferrari get visual:

Row context is the second fundamental concept in writing DAX code. In a previous article, we introduced the first concept – the filter context – using a visual approach. In this article, we rely on graphical visualization to describe a row context.

This article provides a different perspective on a topic already discussed in other row context articles: read them to get more insights about this important concept for DAX.

Click through for a great primer on the topic.

Comments closed

Variable Evaluation Time in DAX

Published 2024-07-16 by Kevin Feasel

Marco Russo and Alberto Ferrari evaluate some variables:

Variables are an important element of DAX to improve readability and performance. Variables are created through the VAR keyword, which can be repeated multiple times for multiple variables, followed by RETURN, the keyword that defines the result of the expression.

Read on for several examples of how you can use variables, and even if it’s possible for the engine to ignore variable declarations if the variable in question never sees use.

Comments closed

Window and Information Functions in DAX

Published 2024-07-09 by Kevin Feasel

Nikola Ilic continues a series on getting ready for the DP-600 exam:

If you’re coming from the SQL world, you might have already heard about window functions. However, window functions are relatively new enhancement in the DAX language. Similar to SQL, they aim to provide the possibility to calculate specific expressions over a sorted and partitioned set of rows.

Read on to learn about three window functions in DAX, followed by a discussion of information functions and what they do.

Comments closed

Antipattern: DAX Measures Never Returning Blank

Published 2024-07-08 by Kevin Feasel

Chris Webb explains the value of BLANK:

Following on from my earlier post on the Query Memory Limit in Power BI, and as companion to last week’s post on how a DAX antipattern using Calculate() and Filter() can lead to excessive memory consumption by queries (and therefore lead to you hitting the Query Memory Limit), in this post I want to look at the effects of another DAX antipattern on performance and memory usage: measures that can never return a blank value.

Read on to see how much of a difference using DAX to fill a grid with 0’s can make.

Comments closed

DAX Variables and Iterators

Published 2024-07-05 by Kevin Feasel

Nikola Ilic takes us through two major concepts in DAX:

“DAX is simple, but not easy!” – famously said Alberto Ferrari, when asked which best describes Data Analysis Expression language. And, that’s probably the most precise definition of the DAX. It may look very easy at first glance, but understanding nuances and how DAX really works, requires a lot of time and “try and fail” cases.

Obviously, this article is not a deep-dive into DAX internals and will not go into these nuances, but it will (hopefully) help you to get a better understanding of the few very important concepts that will make your DAX journey more pleasant and assist you in preparing the DP-600 exam.

Click through for Nikola’s explanation of each.

Comments closed

DAX Memory Usage from Filtering on a Table

Published 2024-07-01 by Kevin Feasel

Chris Webb talks Power BI anti-patterns:

Following on from my last post on the Query Memory Limit in Power BI, in this post I want to look at one of the most common DAX antipatterns and its effect on query memory usage: filtering on a whole table, rather than an individual column, in the filter parameters of the Calculate() function. A lot has already been written on this particular antipattern from the point of view of query performance – for example see here and here – but it’s only with the recent addition of the Execution Metrics Profiler/Log Analytics event that you can see how bad it is for memory usage too.

Read on for an example. In Chris’s case, there’s a 3x memory difference between doing the right thing and the wrong thing, so this can easily add up.

Comments closed

Explaining Filter Context in DAX

Published 2024-06-18 by Kevin Feasel

Marco Russo and Alberto Ferrari bust out the drawing board:

The filter context is a fundamental concept to understand in order to write good DAX code. In this article, we describe the filter context using a visual approach – we rely on a graphical visualization that represents the different interactions that exist in the filter context when you use the visual elements, filters, and slicers of a report. This is the first step to plan the changes required to obtain the desired result: these changes can be obtained by using the CALCULATE function, which removes, adds, and replaces existing filters in the filter context.

This article provides a different perspective on a topic already discussed in other filter context articles: read them to get more insights about this importance concept for DAX.

Read on to learn more about how it works.

Comments closed