Category: DAX

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.

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.

Marco Russo and Alberto Ferrari explain how window functions work in DAX:

Window functions like OFFSET and WINDOW return rows from a table based on the current row. For example, OFFSET (-1) returns the previous row. The main question is: how does OFFSET determine the current row? Intuitively, it searches in the row context and in the filter context for values of columns, and it determines the current row. Unfortunately, that intuitive understanding is missing many details. Although it is easy to intuit the current row in simple queries, things are sometimes more complex. To make sense of the current row in a non-trivial scenario, you need to understand apply semantics.

Read on to learn more, especially when there are partition groups (think the PARTITION BY section in a T-SQL window clause).

Marco Russo provides an option:

DAX is not like M when it comes to data manipulation, and it is not supposed to do that. However, if you need something in DAX similar to Table.FromList in M, this blog post is for you.

If you have a list of values in a string in DAX and you want to obtain a table with one row for each item in the list, you can do the following:

Definitely an example of “There’s a better way to do this than to use DAX” but sometimes you’re stuck, I suppose.

Marco Russo and Alberto Ferrari share some advice:

In the article Using KEEPFILTERS in DAX, we described how to use KEEPFILTERS, which is a function that preserves the existing filter on columns affected by a new filter applied by CALCULATE or CALCULATETABLE. We suggest reading that article if you are not familiar with KEEPFILTERS. However, we wanted to clarify a rule of thumb you can apply to quickly decide when to use KEEPFILTERS or not in most cases. This will allow you to only invest more time when you are dealing with specific requirements.

Read on for common use cases of KEEPFILTERS, as well as recommendations on when and how you should use the function.