DAX – Curated SQL

Using a Continuous Axis for Date Tables in Power BI

Published 2026-10-22 by Kevin Feasel

Marco Russo and Alberto Ferrari gussy up a visual:

In a previous article, Creating a simpler and chart-friendly Date table in Power BI, we showed you how to create a Date table you can use in a line chart with a continuous X-axis instead of a categorical one, even when the granularity is the month rather than the day. That technique stores every level as a DateTime column and relies on custom format strings to display the month name.

The auto date/time feature in Power BI provides the same user experience using a different technique. Its hidden date table uses text columns for quarters and months, sorted by a numeric column. However, the line chart still offers a continuous X-axis when you drill down to the month level. We keep saying that auto date/time is not a best practice, but this behavior is useful. Can we obtain it with our own Date table? Yes, we can.

If you have a Date table in your model (as you should!), this article explains how to enable the continuous X-axis in your report when the user groups dates by year, quarter, month, or week.

Click through to see how it looks, as well as how it works.

When ApproximateDistinctCount is Slower than DistinctCount in DAX

Published 2026-10-16 by Kevin Feasel

Chris Webb explains the paradox:

As a follow-on from last week’s post about the new ApproximateDistinctCount function in DAX, I want to share something I found out from my recent testing for this function: ApproximateDistinctCount is sometimes slower than DistinctCount. This sounds counter-intuitive, right? After all ApproximateDistinctCount is a performance optimisation for the sometimes slow, expensive DistinctCount function. But it turns out that there are certain engine optimisations for DistinctCount that can’t be replicated for ApproximateDistinctCount which mean that for low-cardinality columns using DistinctCount can be faster.

Click through for an example.

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.

Comments closed

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.

Comments closed

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

Category: DAX