Press "Enter" to skip to content

Category: DAX

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.

Leave a Comment

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

The Year in DAX 2025

Published 2025-12-31 by Kevin Feasel

Marco Russo lays out a list:

This was a big year for DAX, with the public preview that started in September for two new features: user-defined functions (UDFs) and calendar-based time intelligence.

The user-defined functions are the most significant update to the DAX language since 2015, when Microsoft introduced variables. Can you believe that for more than five years we had to write DAX code without variables? Well, the user-defined functions we just mentioned will have an even bigger impact in the long term.

Read on for more information on key additions, new DAX functions, and all of the neat stuff that the SQLBI team put together this year.

Comments closed