Press "Enter" to skip to content

Category: DAX

Tips and Tricks with ALLSELECTED in DAX

Published 2025-07-16 by Kevin Feasel

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

Month-over-Month and Year-over-Year Calculations in Power BI

Published 2025-07-03 by Kevin Feasel

Boniface Muchendu does a bit of comparison:

Power BI variance measures are essential for delivering actionable insights through dynamic comparisons like month-over-month (MoM) and year-over-year (YoY) performance. This guide shows how to build flexible, filter-aware DAX measures and apply them within clean, user-friendly visuals to enhance your reports.

Click through to see them in action.

Comments closed

DISTINCT vs VALUES in DAX

Published 2025-07-02 by Kevin Feasel

Marco Russo and Alberto Ferrari compare two keywords:

When you begin modelling in DAX, DISTINCT and VALUES often appear interchangeable: both return the list of unique values for a column in the current filter context. In a clean development model, they behave the same, so it is easy to pick one at random – or worse, swap between them without thinking.

However, they are not identical. The subtle difference is crucial in production models that may one day contain invalid relationships or bad data. 

Read on to see how each works and how they differ in practice.

Comments closed

IF Statements and DAX

Published 2025-06-17 by Kevin Feasel

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

Power BI Model Analysis via INFO Functions in DAX

Published 2025-06-12 by Kevin Feasel

Reza Rad is leading this interrogation:

There are many DAX functions for covering day-to-day business-related calculations using measures and calculated columns. However, there is also a set of functions that can be helpful to the BI team and developers in gaining insights from the Power BI model itself. The insights can include things such as the number of both-directional relationships, the dependency of the calculations, the list of columns in tables, etc. These functions are in the category of INFO functions in DAX. Let’s see what they are and how they work.

Click through for a list, as well as how you can make use of them.

Comments closed

Controlling Selections in Calculation Groups

Published 2025-06-03 by Kevin Feasel

Marco Russo and Alberto Ferrari looks at calculation groups:

Calculation groups are often used to display options in a report to change the calculation of existing measures by selecting items on a slicer. However, only a single calculation item can be executed for a measure reference, which could make the semantic model harder to use when the user selects two or more items in a calculation group.

Two new calculation group properties, multipleOrEmptySelectionExpression and noSelectionExpression, provide a way to control the calculation in these conditions that, so far, ignored the presence of the calculation group, thus executing the measures without applying any transformation. This article shows how to use these features and provides guidance on using the feature in preview: despite not having a user interface to manage these new properties, the TMDL view in Power BI Desktop and external tools like Tabular Editor already allow you to create and publish a semantic model that uses these new properties.

Read on to see how these properties work.

Comments closed

Top DAX Functions to Know

Published 2025-05-29 by Kevin Feasel

Annamarie Van Wyk builds a top 5 list:

If you’ve dipped your toes into Power BI, chances are you’ve come across DAX and thought, “Wait, what now?” You’re not alone. DAX (short for Data Analysis Expressions) is the formula language that makes Power BI so powerful—but it can be a bit intimidating when you’re just starting out.

The good news? You don’t need to master all of it to start seeing results. In fact, there are a few Power BI DAX functions that will do most of the heavy lifting in your reports. In this post, I’ll walk you through five essential DAX functions that every Power BI analyst should know. These aren’t just helpful—they’re game-changers once you get the hang of them.

Click through for that list, including simple examples of each.

Comments closed

Dynamic Pareto Analysis in Power BI

Published 2025-04-22 by Kevin Feasel

Marco Russo and Alberto Ferrari get us to 80%:

The Pareto analysis is an analytical technique used to identify the most impactful elements within a dataset, based on the principle that a small proportion of causes often leads to a large proportion of effects. For example, the ABC Classification in DAX Patterns is also based on the Pareto principle. However, typical implementations often face limitations. The analysis based on the Pareto principle commonly uses categorical axes, such as customer names or identifiers, making it impossible to leverage a continuous axis on a Power BI line chart. A categorical axis creates a scrollbar on the line chart when there are too many data points, limiting the ability to compare the distribution of data points in different categories within the same line chart.

This article shows how to overcome this limitation by introducing a numeric axis reflecting each item’s position based on the selected measure.

Read on to see how it all works.

Comments closed