Category: DAX

Marco Russo and Alberto Ferrari answer a question:

In a previous article, Choosing between DISTINCT and VALUES in DAX, we explained how to choose the proper function to iterate the unique values visible in a column in the current filter context. We suggest reading that article before starting this one, because it describes the same underlying problem. Here, we discuss whether to use VALUES in an iterator. This choice depends on the answer to this question: Do you want to include or exclude the blank row generated by an invalid relationship when iterating over the rows of a table reference?

Read on to see how the answer affects your decision.

Leave a Comment

Marco Russo and Alberto Ferrari’s example goes sideways:

DAX calculation items do not provide full recursion. However, a limited form of recursion is available, known as sideways recursion. We describe this complex topic through examples. Let us start by understanding what recursion is and why it is essential to discuss it. Recursion may occur when a calculation item refers to itself, resulting in an infinite loop within the application of calculation items (read the linked article in case you are not familiar with the concept of “application”, which is different from “execution”). Let us elaborate on this.

Read on for a demonstration of the principle. I haven’t dug into the topic, but I was curious because I’d never heard of “sideways recursion” before. It turns out that there’s some discussion of it in the DAX community and there was something known as Simpson’s sideways recursions from the 1980s, but I’m not sure if that’s the same thing.

Leave a Comment

Marco Russo and Alberto Ferrari take us through a complicated function:

ALLSELECTED is among the most complex functions in the whole DAX language. ALLSELECTED is the only DAX function that leverages shadow filter contexts. Moreover, ALLSELECTED has a slightly different behavior when used in SUMMARIZECOLUMNS or inside an iterator. Using ALLSELECTED with SUMMARIZECOLUMNS mostly produces the expected result, whereas using ALLSELECTED inside an iterator can produce weird results. Mixing the two techniques is the perfect recipe for a problematic report!

In this article, we briefly describe ALLSELECTED features in both scenarios (SUMMARIZECOLUMNS and iterators), and then we provide the best practices about the function, showing an example where mixing the two behaviors produces an unexpected result.

Read on to learn more.

Comments closed

Marco Russo and Alberto Ferrari talk in hypotheticals:

DAX is a functional language. This means that – no matter how complicated it is – a measure is just ONE function call. Then, functions call other functions, creating the intricacies of a sophisticated DAX expression. However, there is always just one function at the top level. This is, at the same time, beautiful and painful, elegant and complex to understand. It is fair to say that being functional is what makes DAX so fascinating.

However, when a DAX formula is executed, it loses its functional nature. Indeed, in the end it needs to be transformed into a set of simpler queries executed by one of the engines of DAX: either the storage engine or the formula engine. During this step, the function execution is transformed, and it becomes much simpler.

Click through to see how the IF() function works in such a world.

Comments closed