Press "Enter" to skip to content

Category: DAX

User-Defined Functions vs Calculation Groups in DAX

Published 2026-04-02 by Kevin Feasel

Marco Russo and Alberto Ferrari take a look back at calculation groups:

The introduction of user-defined functions (UDFs) in DAX changes the way we think about code reuse. Before UDFs existed, calculation groups were the only mechanism for sharing common logic across multiple calculations. Many developers adopted calculation groups not because they were the ideal tool for code reuse, but because there was no alternative.

Now that user-defined functions are available, it is time to revisit this practice. User-defined functions and calculation groups serve fundamentally different purposes. Understanding the distinction between the two is essential for building well-organized, efficient semantic models.

Click through for a dive into these two concepts and when to use each.

Leave a Comment

Aggregation on a Filtered Range with SUMIFS()

Published 2026-03-24 by Kevin Feasel

Ben Richardson uses a DAX function:

Sometimes the columns have shifted, the totals row isn’t showing up, or the colour coding they used last month is gone.

This is not a pivot table problem, pivot tables are excellent tools! The issue is using them for the wrong job.

If you need to explore data – rotating it, slicing it, asking “what does this look like by region?” – pivot tables are unbeatable.

But if you just need a report that always looks the same, month after month, we really recommend SUMIFS.

Click through to see an example of the function in Excel, though it also works the same way in Power BI.

Leave a Comment

Prevent Future Date Spillage in Power BI Visuals

Published 2026-03-20 by Kevin Feasel

Kenneth Omorodion lives in the now:

For Power BI developers, one very common (and frustrating) issue is when measures spill into future dates on charts especially when working with some time intelligence DAX calculations (e.g. MTD, YTD, etc.), date dimensions that extend beyond current date, and forecast-enabled tables.

In Power BI charts (e.g. line or bar charts), apart from dates with data, measures are also evaluated for every date on the axis, regardless if there is data or not. For example, if my dates table runs to 2026 December, but my data table only have data up to today, when I create a measure that leverages MTD or YTD for example, Power BI will tend to evaluate the measure for all dates that exist in my Dates table, unless I explicitly apply a logic to prevent this behaviour. This behaviour might result in flat lines on charts, misleading trends, and confusion to intended users.

In this article, I will demonstrate some examples of approaches to prevent or manage future dates spillage in Power BI.

Click through for some tips.

Leave a Comment

UDFs to Support the Like-for-Like Pattern in DAX

Published 2026-03-11 by Kevin Feasel

Marco Russo and Alberto Ferrari support a pattern:

DAX user-defined functions (UDFs) are a powerful tool for improving the quality of your semantic models. DAX authors with an IT background are accustomed to creating generic code using functions. However, many DAX creators came from different backgrounds of expertise, such as statistics, business, and marketing. They may not recognize the immense power that functions have brought to the Power BI community.

In this article, we want to practically show, through an example, how to wisely use functions to improve the generalization of code and to reduce the complexity of your semantic models, with the goal of raising curiosity towards user-defined functions and – in general – the world of code development.

Read on for an example, as well as a link to the like-for-like pattern and what it means.

Comments closed

Pain Points around Direct Lake

Published 2026-02-27 by Kevin Feasel

Teo Lachev describes a pair of problems:

I’m helping an enterprise client modernize their data analytics estate. As a part of this exercise, a SSAS Multidimensional financial cube must be converted to a Power BI semantic model. The challenge is that business users ask for almost real-time BI during the forecasting period, where a change in the source forecasting system must be quickly propagated to the reporting the layer, so the users don’t sit around waiting to analyze the impact. An important part of this architecture is the Fabric Direct Lake storage to eliminate the refresh latency, but it came up with a couple of gotchas.

Click through for those two problems.

Comments closed

Debugging DAX Variables via TOJSON() and TOCSV()

Published 2026-02-25 by Kevin Feasel

Marco Russo and Alberto Ferrari write out some intermediate results:

In a previous article, Debugging DAX measures in Power BI, we described several techniques to find errors in a DAX formula. The most basic approach, one that requires no external tools, is to temporarily change the RETURN statement of a measure so that it returns the value of an intermediate variable instead of the final result. When the variable contains a scalar value such as a number or a string, this is straightforward: you change the RETURN, observe the result in the report, and compare it with your expectations.

Read on to see how these functions work.

Comments closed

DAX DATEADD Parameters and Calendar-Based Time Intelligence

Published 2026-02-10 by Kevin Feasel

Marco Russo and Alberto Ferrari check how two sets of functionality overlap:

The primary reason to adopt the new calendar-based time intelligence in Power BI is its flexibility. Classic time intelligence functions work out of the box and deliver meaningful results in most scenarios. However, to do so, they make assumptions about the calendar structure and the desired outcomes. Sometimes, the choices are not aligned with the user requirements, and developers need to author their own time intelligence calculations.

The new calendar-based time intelligence functions provide greater flexibility by allowing developers to configure parameters that drive the internal algorithms to meet diverse requirements. Using these parameters requires a precise understanding of the scenario for which they were built, which requires some attention to detail.

Click through to learn more.

Comments closed

Performance of Regular vs Limited Relationships in DAX

Published 2026-01-02 by Kevin Feasel

Marco Russo and Alberto Ferrari do a performance comparison:

Relationships between different data islands are the most common case of limited relationships. In that scenario, performance depends on multiple factors, most of which are not under the control of a DAX developer. Indeed, when mixing data from different data islands, the DAX formula engine must act as a bridge between them, resulting in complex execution plans. Besides, when two tables reside in different data islands, only limited relationships can connect them. Therefore, a performance comparison would not make sense, as there are no alternative options to link the tables.

However, a model can have limited relationships in the very special case of two tables stored in the same data island and connected by a many-to-many cardinality relationship. By nature, many-to-many cardinality relationships are limited. While they seem like a convenient way to link two tables when the key used to link them together is not unique in both tables, many-to-many cardinality relationships are extremely expensive, and a wise data modeler should limit their use strictly to cases where they are absolutely necessary. In this article, we analyze the differences between regular and limited relationships, focusing solely on performance.

Read on to learn more.

Comments closed