DAX – Curated SQL

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.

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.

DAX Lib: Shared DAX User-Defined Functions

Published 2025-12-15 by Kevin Feasel

Marco Russo shares some code:

Three months ago, Microsoft introduced the User-Defined Functions (UDFs) in the DAX language. From the first day, https://daxlib.org has been available to share libraries of functions with the Power BI community. We published DAX Lib with a low profile because we did not have many libraries available at the beginning, but now it is time to spread the word!

Using DAX Lib is fast and simple: copy the function code from a TMDL script in DAX Lib, then paste it into the TMDL view of your Power BI model and apply it. Watch the video to see a complete walkthrough.

Check out that video, as well as the functions available in the “DAX app store.”

1 Comment

Choosing RANK() over RANKX() in DAX

Published 2025-12-03 by Kevin Feasel

Marco Russo and Alberto Ferrari make a decision:

In this article, we are not going to discuss the syntax of the RANK and RANKX functions. If you need more information, we suggest you consult DAX Guide for syntax, as well as the following articles, which introduce both functions: Introducing the RANK window function in DAX and Introducing RANKX in DAX.

RANKX is the classic method of ranking in DAX; RANK is a newer window function that works faster, better, and in a more flexible way. RANK is used in both visual calculations and measures. Which function should you use in which scenario? The answer depends on your requirements: each solution has pros and cons.

Read on for the comparison criteria and when you should choose each.

Comments closed

DAX Functions and Model Dependency

Published 2025-11-20 by Kevin Feasel

Marco Russo and Alberto Ferrari describe model dependency:

In a previous article, Introducing user-defined functions in DAX, we introduced the syntax and the capabilities of this feature that was introduced in preview in September 2025. While there could be many ways to categorize functions, every function can be one of the following:

Model-independent function – Has no references to specific tables, columns, calendars, or measures within a semantic model. A model-independent function can have dependencies on other model-independent functions, but it cannot depend on a model-dependent function.

Model-dependent function – Has one or more direct or indirect references to specific tables, columns, calendars, measures, or other model-dependent functions within a semantic model.

Read on to learn more about each of these categories.

Comments closed

Tying DAX Studio Queries to Workspace Monitoring

Published 2025-11-18 by Kevin Feasel

Chris Webb correlates some data:

A few weeks ago I wrote a blog post about how you can now link event data from Workspace Monitoring to data in the Fabric Capacity Metrics App using OperationId values. In the latest (3.4.0) release of DAX Studio there’s a new feature that you might have missed that link queries run from there to Workspace Monitoring and the Capacity Metrics App in the same way.

Read on to see how it works.

Comments closed

Primary Keys and DAX Query Performance

Published 2025-11-14 by Kevin Feasel

Phil Seamark explains why including primary keys in summarize statements can be a bad thing:

When writing DAX queries, performance tuning often comes down to small design decisions that have big consequences. One such decision is whether to include Primary Key columns from Dimension tables in your SUMMARIZECOLUMNS statements. This is particularly important when those Dimension tables use DUAL or IMPORT storage modes.

This article explains why doing so can lead to inefficient query plans. It describes what happens under the hood. It also shows how to avoid this common pitfall.

Read on to learn more.

Comments closed

Finding the Top 10 Products each Year with DAX

Published 2025-11-04 by Kevin Feasel

Marco Russo and Alberto Ferrari build a top ten list:

We have written and updated a few pieces in the past about how to find the top products, such as Filtering the top products alongside the other products in Power BI and Filtering the Top 3 products for each category in Power BI.

Generally speaking, finding the top products requires using GENERATE and TOPN. However, there is an interesting variation of this scenario that solves a specific business problem. Once we have determined the top 10 products by year, we want to filter only those that appear in the top 10 in most years. Obtaining that list of products helps identify evergreen products, that is, the products that remain in the best-seller list consistently.

Click through for the demonstration.

Comments closed

Generating a DAXX File for Performance Tuning

Published 2025-10-31 by Kevin Feasel

Phil Seamark does some troubleshooting:

When troubleshooting slow DAX queries, sharing the right diagnostic information with an expert can make all the difference. That’s where a DAXX file comes in. This special file format is created using DAX Studio. It bundles essential metadata and performance details without exposing query results. It’s perfect for collaborative optimisation.

Read on to learn more about what a DAXX file is and how it can be useful in the performance tuning process.

Comments closed

DAX: VALUES in SUMMARIZE

Published 2025-10-23 by Kevin Feasel

Marco Russo and Alberto Ferrari talk about values:

We discussed VALUES in previous articles: Choosing between DISTINCT and VALUES in DAX and Using VALUES in iterators. However, there is a third case where VALUES could be used with a table reference, which is when you use SUMMARIZE to group by columns you want to iterate. In this article, we describe this particular scenario to understand when VALUES is needed to retrieve the blank for an invalid relationship using SUMMARIZE and SUMMARIZECOLUMNS.

When you use SUMMARIZE, you may want to use VALUES over the aggregated table in case it could have an additional blank row for an invalid relationship, and you must ensure that this blank row is included. This condition is uncommon because SUMMARIZE often includes blank rows for invalid relationships that are implicitly included. For example, consider the following measure that uses SUMMARIZE over the Sales table, grouping by Customer[State] and Customer[City] to apply an adjustment to Columbus, Ohio (note that there are other cities with that name in other states):

Curated SQL tip number 17: if you want to show up here, use the best city in Ohio as a (positive) example.

Comments closed

Category: DAX