DAX – Curated SQL

Developing DAX Contracts

Published 2026-10-07 by Kevin Feasel

Marco Russo and Alberto Ferrari redline a few things:

Contract. As a DAX developer, you will use this term frequently; indeed, when developing code with an AI assistant, defining the contract is the most important task you need to complete to obtain a useful semantic model.

First things first: what is a contract? In simple terms, it is a text that identifies the rules at work in a specific semantic model. It is not just an algorithm definition: a contract can define multiple measures and multiple algorithms at the same time. However, it is not even a vague description of the model in terms of entities and relationships. It sits somewhere between the two: documentation that defines how to perform calculations in a specific model.

Read on to understand what they mean by a contract in this sense, and what has changed with the proliferation of language models generating DAX code.

Approximate Distinct counts in DAX

Published 2026-10-05 by Kevin Feasel

Chris Webb deals with management:

As you’ve probably seen in the blog post for the September 2026 release of Power BI, Import mode and Direct Lake mode semantic models now support the ApproximateDistinctCount() DAX function. I tested its performance on a Direct Lake semantic model with a 1.4 billion row fact table based on the NYC Taxi sample data and as you would expect, it was a lot faster than doing a regular distinct count in most cases.

Chris notes that the primary challenge is human: getting people to understand that, for their purposes and at the scale in which approximate distinct counts makes sense, non-biased approximations are just as good as actuals. That’s because 119,374,399 and 118,998,706 generally aren’t distinguishable in practice for things like monthly unique user counts. In both cases, the actual business user would still write “120 million” (or maybe “119 million” to be more precise).

Now, in cases in which the precise number does matter? Absolutely use the real distinct count. But those scenarios are rarer than you’d think.

Building Daily Averages in DAX

Published 2026-09-24 by Kevin Feasel

Marco Russo and Alberto Ferrari take generative AI tooling for a spin:

Computing an average daily sales amount is straightforward: you should iterate over the dates, compute sales for each day, and average the results. An AI assistant can generate DAX code that works in just a few seconds; whether it computes the number you need is a different question.

In this article, we ask AI to generate every measure, function, and validation query. We start with a simple average and test it against more demanding scenarios. Our job is to explain the business requirements, challenge the proposed code, and verify the results. Each test reveals a missing detail from the original request.

The prompts below show a conversation with an AI agent. They describe how to get the DAX code from the AI. They are not a verbatim transcript: a different agent might produce different expressions.

This shows the duality of generative AI outcomes: you can end up with a good solution, but unless you know the ins and outs of what you’re doing, you can end up in a sub-optimal situation.

DAX at the Correct Granluarity

Published 2026-09-11 by Kevin Feasel

Marco Russo and Alberto Ferrari provide some advice:

DAX code is often short. Understanding the question that the code must answer may be much harder.

Consider a calculation that multiplies a quantity by a rate. The formula appears simple, yet it is correct only if the quantity and the rate are evaluated while the rate has a clear meaning. If you perform the calculation after combining different rates, DAX must aggregate the rates somehow: AVERAGE, MIN, or MAX can return a number. However, returning a number does not make that number meaningful.

Read the whole thing.

Comments closed

Choosing COUNTROWS() over DISTINCTCOUNT() in DAX

Published 2026-08-10 by Kevin Feasel

Phil Seamark provides some guidance:

Counting transactions is one of the most common things a retail model is asked to do. How many transactions did we do in March. How many in New Zealand. How many involved a T-shirt.

The obvious measure is the one everybody writes:
Transactions = DISTINCTCOUNT ( FactTransaction[TransactionID] )
It is correct, and on a large fact table it is one of the most expensive things you can ask the storage engine to do.

Read on for a better alternative, and more importantly, when it’s a better alternative.

Comments closed

The Performance Impact of Visual Calculations

Published 2026-07-29 by Kevin Feasel

Marco Russo and Alberto Ferrari dig into the nuance:

The goal of visual calculations is to simplify some reports and calculations, rather than to optimize performance. However, it is common sense that – in some scenarios – visual calculations can bring some benefit from the performance point of view.

The main idea is that a report may precompute some values and then, to further elaborate on them, it may use the content of the virtual table rather than recompute the values multiple times.

Read on to learn how you can adjudge what that visual calculation is doing for you in terms of performance relative to alternatives. But hey, if you’re stuck with Power BI Report Server, you don’t have to worry about visual calculations potentially slowing anything down. Mostly because you don’t get visual calculations.

Comments closed

Applying Different Formatting Rules at Levels of a Hierarchy

Published 2026-07-14 by Kevin Feasel

Marco Russo and Alberto Ferrari format things differently:

A challenging requirement in Power BI reports is that of applying different formatting rules based on the level of aggregation. At the year level, the background shade may reflect each year’s share of the grand total. At the quarter level, a status color may indicate whether the quarter is above or below the average. At the month level, the color may flag exceptional values, like months that contribute more than a defined threshold to their year. Each level has its own logic; what the conditional expression of the measure needs to know is which level the current cell belongs to.

Read on to see how you can pull this off.

Comments closed

Tabular Editor CLI 0.6.0 Release

Published 2026-07-07 by Kevin Feasel

Ruben Van de Voorde announces a new update:

Since announcing the Tabular Editor CLI, we’ve been hard at work polishing the CLI and bashing the bugs we found, thanks to your help. We deeply appreciate all the input we received so far through GitHub, talking to you at events, comments on these blogs, and all other channels you engage with us (leave yours at the bottom of this page). Keep it coming!

We’re now at a point where we feel ready to share the updated version with you: version 0.6.0.

This is still in a limited public preview, so it’s free until the end of September. After that point, it becomes a paid product.

Comments closed

Using REMOVEFILTERS in DAX UDFs

Published 2026-06-30 by Kevin Feasel

Marco Russo and Alberto Ferrari make use of REMOVEFILTERS:

A DAX user-defined function, also known as a UDF, is expected to return a scalar or a table. However, because functions are fundamentally macro-expansion of DAX code, it is possible to return CALCULATE modifiers if the function is to be called only as a filter argument of CALCULATE.

To show a practical example of when the feature proves to be useful, we debug a measure that fails because some calendar filters are not being removed correctly. Fixing the measure elegantly requires creating a function that removes filters rather than returning a value.

Click through for that example.

Comments closed

Optional Parameters in DAX UDFs

Published 2026-06-19 by Kevin Feasel

Marco Russo and Alberto Ferrari give us an option:

When Microsoft announced that DAX User-defined functions (UDFs) are generally available (GA), another new feature was also announced: it is now possible to define optional parameters in a function and assign them default values.

A parameter is optional when the caller can leave it out. In that case, the function still needs a value to work with, so it falls back to a default. DAX provides that default through an expression written directly in the function signature, next to the parameter it belongs to. This is the mechanism we describe in this article.

Read on to see how it works.

Comments closed

Category: DAX