At the end of 2025, we launched DAX Lib, a public repository of DAX user-defined functions. So far, importing a DAX library has required a few manual steps, like clicking Add to Power BI on daxlib.org and then copying and pasting the DAX code into the TMDL View of your model, or using the dedicated tool in Tabular Editor 3. Both work well, but if you already use an AI assistant next to Power BI Desktop, we can now delegate this task to it. The DAX Lib MCP server version 0.1.0 is available now.

MCP server

The DAX Lib MCP server connects your assistant to the DAX Lib catalog and to the Power BI Desktop models on your computer. It should work with any MCP client that supports local (stdio) servers, including Claude, Codex, GitHub Copilot in VS Code, Grok Code, Cursor, and many others. If it does not work for you, please let us know.

What you can do with it

You can make requests like these to your AI assistant:

• search DAX Lib for packages ("Search DAX Lib for packages that generate SVG images");
• look at the details, the versions, and the README of a package ("Show the README of DaxLib.SVG");
• list the Power BI Desktop models open on your machine;
• install the functions of a package into a local model ("Install DaxLib.SVG into the Sales Report model").

The MCP server downloads the package and writes the functions into the model directly, so you get the original code (including format and comments) and not a version retyped by a language model.

The installation adds the user-defined functions of the package to the semantic model. If a function with the same name already exists, it is replaced, without changing anything else in the model. The MCP server does not save your file after you import a package; save it in Power BI Desktop to keep the changes.

You stay in control

You choose the package, the version, and the target model. The assistant follows your instructions and doesn’t choose a package for you. The install tool is marked as a tool that changes data, so most MCP clients ask for your approval before they run it. Read the request before you approve it.

About privacy: the MCP server has no telemetry and does not collect usage data. It only invokes the DAX Lib API to search the catalog and to download package details, READMEs, and packages. The server is open source under the MIT license, so you can verify all these statements in the code on GitHub: daxlib/daxlib-mcp.

Limits you should know

The DAX Lib MCP server has a few limits:

• It runs on Windows x64 only. No macOS plans at this time; if you need it, open a GitHub issue.
• It installs only into models open in Power BI Desktop on the same computer.
• The model needs compatibility level 1702 or higher, required for user-defined functions. The DAX Lib MCP server doesn't change it for you, but the Power BI Authoring MCP server (if installed) can raise it.
• It does not remove or update existing functions. To update a package, just install the new version.
• If you do not specify a version of a package, you get the latest published one, which can be a prerelease.

How to start

You need Windows x64, Node.js 18 or later, and an MCP client. Follow the installation instructions and start looking for function libraries that can help your models and your reports.

We suggest using the Power BI Authoring MCP server to move from "is there a library for this?" to "write the measure" in the same conversation by using both MCP servers.

DAX Lib is a community project, and SQLBI supports it by developing and maintaining its infrastructure and tools. The MCP server is one more of those tools.

There are no more excuses to include user-defined functions in your semantic models: try DAX Lib with your assistant and write your feedback if you have suggestions or find bugs.

How to get a continuous X-axis at the month level in Power BI with any Date table, like the one we get using the auto date/time feature.

Power BI enables the continuous X-axis on the auto date/time hierarchy even at the month level. We can get the same behavior on any Date table by setting the right metadata in TMDL.

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.

How auto date/time navigation works

The table created by auto date/time exposes a single hierarchy with four levels: Year, Quarter, Month, and Day. You cannot customize these levels, and the user does not see any other column in that table. Internally, Quarter and Month are text columns sorted by a hidden numeric column.

If we place the Date hierarchy from the auto date/time column on the X-axis of a line chart and drill down to the Month level, the X-axis type is still Continuous, whereas when we use the Year Month column of the classic Date table, the X-axis is Categorical.

The Type property of the line chart is usually available only when you can switch between Ordinal and Categorical, as in the auto date/time example. However, it is hidden when Power BI forces the X-axis to Categorical, as in the previous line chart for the classic Date table.

At first glance, this looks like an exception that Power BI makes for its own tables. But in reality, the line chart offers the continuous axis because the hidden table carries specific metadata, and that metadata tells the visual how to treat each level of the hierarchy. If we apply the same metadata to our Date table, we get the same behavior.

This is the TMDL definition of the hidden table generated by auto date/time, stripped of annotations, formats, lineage tags, and summarize attributes:

table LocalDateTable_eee2517b-a62f-4b3d-a512-4aa237fa2329
    isHidden
    showAsVariationsOnly
    column Date
        dataType: dateTime
        isHidden
        dataCategory: PaddedDateTableDates
        sourceColumn: [Date]

    column Year = YEAR([Date])
        dataType: int64
        isHidden
        dataCategory: Years

    column MonthNo = MONTH([Date])
        dataType: int64
        isHidden
        dataCategory: MonthOfYear

    column Month = FORMAT([Date], "MMMM")
        dataType: string
        isHidden
        dataCategory: Months
        sortByColumn: MonthNo

    column QuarterNo = INT(([MonthNo] + 2) / 3)
        dataType: int64
        isHidden
        dataCategory: QuarterOfYear

    column Quarter = "Qtr " & [QuarterNo]
        dataType: string
        isHidden
        dataCategory: Quarters
        sortByColumn: QuarterNo

    column Day = DAY([Date])
        dataType: int64
        isHidden
        dataCategory: DayOfMonth
        summarizeBy: none

    hierarchy 'Date Hierarchy'
        level Year
            column: Year
        level Quarter
            column: Quarter
        level Month
            column: Month
        level Day
            column: Day

Note the dataCategory property on the column: only those on Date and Year matter. Indeed, the dataCategory for Quarter is Quarters when it should be QuarterOfYear, but these intermediate levels between Year and Date are ignored by Power BI.

The metadata needed

The line chart provides the continuous axis on a hierarchy when all these conditions are met:

1. The columns come from a user hierarchy. Placing the individual columns from a table on the X-axis is not enough; you must use hierarchy levels.
2. The lowest level of the hierarchy is a date column, with dataCategory set to PaddedDateTableDates.
3. The top level of the hierarchy has dataCategory set to Years.
4. The visual includes only levels of the hierarchy starting from the top level marked as Years.

That is it. You do not need anything else, but a few practical examples can help!

The date-related values accepted by dataCategory on a column are: Years, Quarters, QuarterOfYear, Months, MonthOfYear, Weeks, WeekOfYear, WeekOfMonth, Days, DayOfYear, DayOfMonth, DayOfWeek, Hours, Minutes, Seconds, Date, PaddedDateTableDates. Power BI Desktop does not show any of them in the Data category dropdown, which only lists geography, URL, image, and barcode categories. You need the TMDL view in Power BI Desktop, Tabular Editor, or the Power BI Modeling MCP server described later.

Configuring the hierarchies

In the Date table we created in our sample model, we already have columns like Year, Year Month sorted by Year Month Number, and so on. This is a partial definition to highlight the properties to set in the TMDL view of Power BI Desktop, or in Tabular Editor.

table Date
    dataCategory: Time

    column Date
        dataType: dateTime
        isKey
        formatString: mm/dd/yyyy
        dataCategory: PaddedDateTableDates
        sourceColumn: Date

    column Year
        dataType: int64
        dataCategory: Years
        sourceColumn: Year

    column 'Year Quarter'
        dataType: string
        isHidden
        sourceColumn: Year Quarter
        sortByColumn: 'Year Quarter Number'
[...]

    hierarchy Year-Quarter-Month
        level Year
            column: Year
        level Quarter
            column: 'Year Quarter'
        level Month
            column: 'Year Month Short'
        level Date
            column: Date

    hierarchy Year-Month
        level Year
            column: Year
        level 'Year Month'
            column: 'Year Month'
        level Date
            column: Date

Three things to highlight:

• The table is marked as a date table. This sets dataCategory: Time on the table and isKey on the Date Only the latter (isKey) is required to activate the Continuous axis; the dataCategory property of the table is ignored.
• The Date[Date] column has dataCategory: PaddedDateTableDates. This is an undocumented but required property to activate the Continuous axis.
• The hierarchies start at Year and end at Date. The intermediate levels can be any column with a date-related category.

In the following screenshot, you can see the continuous chart used at the quarter and month level of two different hierarchies.

Considerations about visualizations

The line chart tooltip shows the Year and Month level of the hierarchy, even when the month-level column is Year Month, which already includes the year. For example, in the Year-Month hierarchy, the tooltip displays 2024 twice: once at the year level and again in the month description.

This is more visible when we use the Year-Quarter-Month hierarchy: the drill-down to the Month level shows the name in three levels in the tooltip: Year, Quarter, and Month.

This is mainly a cosmetic issue, but it is worth noting. To improve the tooltip user experience, you can use the Quarter and Month columns in the Date table to create a hierarchy that shows non-redundant names in the tooltip. For example, this is the tooltip of the Chart-Friendly hierarchy included in the sample file.

The metadata does not change the behavior of visuals that do not have a continuous axis type, such as the clustered column chart. The hierarchy works as usual there and in any other visual, including slicers and filters.

Automating the change with the Power BI Modeling MCP server

If you use an LLM with the Power BI Authoring MCP server connected to Power BI Desktop, you can describe the configuration instead of writing the TMDL by hand. The server exposes operations on columns and user hierarchies, including the dataCategory property. You can find an updated description of the AI clients available for this task here: AI tools for Power BI Desktop.

Here is a prompt that produces the configuration of the first example:

Connect to the semantic model open in Power BI Desktop.
In the table 'Date', set the dataCategory property of these columns without changing any other property:
- Date: PaddedDateTableDates
- Year: Years
Make sure the Date column is key.
Create a user hierarchy named 'Calendar' in 'Date' with these levels in this order:
Year (column Year), Quarter (column 'Quarter'), Month (column 'Month'), Date (column Date).
Do not create, delete, or rename any other object. When done, export the TMDL of the 'Date' table so I can review the result.

The LLM completes the action requested smoothly.

In Power BI Desktop, we have the new Calendar hierarchy that displays the expected tooltip by selecting a point on the Month level on the Continuous X-axis.

Conclusions

The continuous X-axis for a date hierarchy on a line chart is not a privilege of the auto date/time table. It depends on a few dataCategory values (the most important one being PaddedDateTableDates on the date column) and a user hierarchy that starts with the year and ends with the date. With these properties set through TMDL, Tabular Editor, or an MCP-driven LLM, your Date table offers the same navigation without the downsides of auto date/time.

We covered another approach in a previous article that does not require metadata which is not visible in the Power BI Desktop UI. However, in some cases you may want more control over the name of each level, or other clients you use may not fully support date columns with a custom format for hierarchy levels. The solution in this article may feel more “natural” to users with past experience navigating auto date/time tables.

Defining the right contract is a key activity for a DAX developer using AI. Here we introduce the concept of a contract through simple examples.

Defining the right contract is a key activity for a DAX developer using AI. In this article, we introduce the concept of a contract through simple examples.

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.

To describe what a contract is, let me indulge for a bit in the evolution of what happened in the world of Business Intelligence over the years. We promise it will be just a few paragraphs, but we feel it is important to properly introduce the contract and put it in the correct perspective.

How business logic is hidden in code

Once upon a time, there were users and developers. Users wanted reports, but they did not know how to build the models and calculations. Developers could build models and calculations, but they did not know the business details or the specific requirements for calculating over user data. Developers needed precise instructions to write the calculations, and this led to an insane number of endless meetings between users and developers, with the (mostly vain) goal of clearly defining how to compute measures. Developers kept bringing up edge cases where the calculation defined so far would fail, and users got bored by all the details developers were asking for. Eventually it worked, but the friction was real.

Sometimes, young developers (including ourselves!) believed that writing proper documentation upfront would help solve the problem. Therefore, developers wrote insanely complex documentation, and users read it, mostly five minutes before the meeting, because it was just complex and considered unnecessary. Eventually, models were in place, and the real documentation on how the model computed values ended up hidden in DAX (or MDX, at the time) and SQL code. Any update, as you may well imagine, turned into a nightmare.

With the advent of self-service BI, many users became developers. They no longer needed any documentation because they know the business; instead, they now write Power Query transformations, calculated columns, and complex DAX code to make the semantic model work. Again, the business logic remains hidden in many measures and transformations that often contain serious levels of complexity. The model works for a while but, after a couple of months, nobody knows exactly how it computes the numbers.

The outcome is the same: the business logic remains hidden; the only difference is that the number of models grew immensely.

This scenario is not unique to Business Intelligence: most IT businesses work the same way. Documentation is boring to write, boring to read, mostly non-existent, and, when it exists, it is never up to date. We are pretty sure you know a few Excel files that nobody can understand or modify, but that are crucial to your (or your customer's) business.

AI changes the role of documentation

Now that AI assistants are a thing, the scenario is likely to change again, and not for the better. Users will remain users; they no longer need to become expert developers. The AI agent covers the role of developers by writing DAX code, Power Query, or SQL transformations based on the users’ prompts. The number of reports will grow again, and knowledge of how they work will be hidden even further.

However, there is some good news. Developers hate writing documentation. Users hate reading documentation, not to mention writing it. But… You know who loves both writing and reading documentation? Your AI agent. An agent is not bored by documentation details, and it is also great at taking partial information and correcting the documentation on the fly, keeping information about how a model computes values up to date. An agent is also very good at finding small inconsistencies in your specifications and providing clear examples of when and how they manifest. Finally, agents are extremely good at guiding users through writing concise documentation and verifying that all the code in your semantic model follows the rules defined in the documentation.

The documentation the agent creates is the contract. A contract does not contain anything obvious (like “The customer table contains customers”); it speaks the language of the business, and it defines clear rules to compute values over your data. Those rules need not be defined up-front through meetings. They can be derived by trial and error, and they can be adjusted at any time: every time you discover a behavior that you did not consider in the beginning, you change the measure by changing the contract and asking AI to review the DAX code.

When you write DAX with an AI assistant, the measure is not the most important part of the work. The contract is.

Defining a customer ranking contract

Let me show you a very practical example of how to change your mindset when working with AI. Imagine you want to create a measure to compute the ranking of a customer. The most obvious prompt is something like: “Create a measure to compute the ranking of a customer”, right? Wrong. This is not the right way to approach creating a measure.

Before anything else, you start with the contract, and you use AI to define it through a simple dialogue:

I want to compute a customer ranking based on Sales Amount. What questions do we need to answer before we can proceed?

The agent answers by asking the most relevant questions you need to answer before even approaching the measure. For example, ChatGPT Astra produces these questions:

Answering all these questions defines the contract. That is why your next prompt should define the contract, not the measure. We are not going to elaborate on the type of ranking that we want to create. The definition is in the contract, despite our personal way of ranking being quite exotic:

Save this contract for the ranking of customers:
- A customer competes with all the customers that purchased at the time of the analysis. For example, if the report shows March 2026 and the first sale is on January 1st 2026, the customer cohort includes all customers who purchased between Jan 1^st and March 31^st.
- Customers with no sales are automatically excluded from the ranking because of the previous rule
- All filters need to be considered for the ranking, except for any filter on customers.
- The customer with the highest sales is ranked 1. Ties need to be removed by adding the customer key to the ranking.
- Customers are identified by Customer[CustomerKey]. However, on the matrix, users will use the customer name. In case of ambiguity (multiple customers visible), show a blank.

You may think the contract is complete. It is not; unanswered questions remain because we asked the agent to consider the competitor cohort as the list of all customers who purchased anything up to now. This leads to further questions, and the agent highlights them:

Therefore, the next prompt solves the three open issues:

Modify the contract for the ranking of customers, and add these rules:
- use the cumulative sales to produce the ranking
- customer key is added in ascending order to the rank order
- historical buyers with no sales in the selected period participate in the ranking

Please note: so far, we asked it to save the contract, not just use it for the measure. The agent will save its own version of the contract, whose wording is likely to differ from mine and be organized differently. It is always a good idea to show the contract once it has been digested by the agent:

Show me the contract used to rank customers

In our experience, AI-written contracts are clearer and less ambiguous than what we provided, and you can see that in the answer.

Now that the contract is well defined, we have obtained several benefits:

• We have clear, concise documentation of the measure’s behavior. This behavior is easy to understand without even looking at the DAX code, and it can be shared with both AI and humans to discuss the algorithm;
• AI has a precise set of instructions to write the DAX code for the measure;
• If we must modify this measure in the future, we do not have to review the code; we can speed up development by reading and modifying the contract, then ask AI to modify the measure to adhere to the new contract.

The last prompt is what we thought should have been the first:

Create a measure to compute the ranking of a customer

To this last prompt, we obtain the desired measure, which is not trivial:

Customer Rank = 
VAR HasOneCustomer = HASONEVALUE ( Customer[CustomerKey] )
VAR CurrentCustomerKey = SELECTEDVALUE ( Customer[CustomerKey] )
VAR LastVisibleDate = MAX ( 'Date'[Date] )
VAR CustomersWithSales =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Sales, Customer[CustomerKey] ),
            "@CumulativeSales", [Sales Amount]
        ),
        REMOVEFILTERS ( Customer ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] <= LastVisibleDate
        )
    )
VAR CurrentCustomer =
    FILTER (
        CustomersWithSales,
        Customer[CustomerKey] = CurrentCustomerKey
    )
VAR CurrentSales = MAXX ( CurrentCustomer, [@CumulativeSales] )
VAR Result =
    IF (
        HasOneCustomer && COUNTROWS ( CurrentCustomer ) = 1,
        1
            + COUNTROWS (
                FILTER (
                    CustomersWithSales,
                    [@CumulativeSales] > CurrentSales
                        || (
                            [@CumulativeSales] = CurrentSales
                                && Customer[CustomerKey] < CurrentCustomerKey
                        )
                )
            )
    )
RETURN
    Result

The measure is far from perfect; it can probably be written more efficiently using RANK, and, most importantly, it should be carefully validated before going into production. However, these tasks are outside the scope of this article. Here we only wanted to show the process of defining the contract.

You can see the measure at work in the following matrix: Karen Westbrook, for example, ranked first in 2023 but moved to third in 2024 because new customers appeared who purchased more than she did.

You can easily see how much easier it is to read the contract than the measure.

Conclusions

When developing semantic models, contracts are key to creating calculations successfully. Although it is tempting to do so, you should refrain from creating any measure, no matter how simple, without first creating a contract.

Contracts can be very simple or very complex, but they offer a compact way to store and share details about your semantic model, how values are computed, and any peculiarities in your calculations.

Start getting used to it. Contracts are the new first-class citizens in the BI world; they bring many benefits and, to our knowledge so far, no drawbacks.

The time has come: in 2026, using AI to write DAX has started to be effective.
Latest news and the new courses are at https://www.sqlbi.com/ai/

We also created three new video courses that should not become obsolete in two weeks!
𝗔𝗜 𝗳𝗼𝗿 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜: 𝗜𝗻𝘁𝗿𝗼 (𝗳𝗿𝗲𝗲!)
𝗗𝗔𝗫 𝘄𝗶𝘁𝗵 𝗔𝗜: 𝗘𝘀𝘀𝗲𝗻𝘁𝗶𝗮𝗹𝘀
𝗗𝗔𝗫 𝘄𝗶𝘁𝗵 𝗔𝗜: 𝗦𝗰𝗲𝗻𝗮𝗿𝗶𝗼𝘀

Marco wrote a long post to recap what has changed between 2023 and 2026 - see the link below!

A simple daily average can hide complexities and return different values. We explore several edge-case scenarios to build the desired calculation using AI to accelerate the development.

The time has come: in 2026, using AI to write DAX has started to be effective. Let me recap what has changed between 2023 and 2026, and what we are doing with our three new courses to best assist you through these changes.

I started my career as a developer: Basic, Assembler, Pascal, C, C++, Delphi, C#, and many other specific domain languages along the way. Writing code is magical and time-consuming. Over time, I shifted my focus to data analytics, but I never stopped coding. Much less, and with much less time, with many more compromises. In the last two years, the support of AI to improve developer productivity has been absolutely amazing, from completing one line of code to writing complete files and now managing entire repos. This should be the introduction to another article, because this one is about DAX: why does my second life as a developer matter?

The reason is that, while I adopted AI relatively early to write software, I was not enthusiastic about using AI to write DAX code. Fourteen months ago, in July 2025, I wrote a post for the 300th edition of our Newsletter to clarify the position of SQLBI on AI. The message was that AI speeds up specific tasks, that the end-to-end time savings were smaller than what you would have expected from faster prototype creation, and that we would write about AI only when we saw clear, measurable benefits.

You might think that it was a conflict-of-interest type of situation: after all, I have been teaching DAX for a living for many years. But no, that was not the reason, because in teaching, my goal has always been to help people achieve their goals faster and more efficiently. Other topics simply become important as technology evolves. I waited, simply because AI was not ready for DAX. Hey, have you heard people say DAX is hard? Well, I don't think it is hard; it's just different, and AI learns by example, adapting and generalizing concepts across languages. But with DAX, that wasn't entirely possible, because it’s unique (and beautiful!) in its own way.

Well, I have big news. The wait is over; the time has come. Writing DAX with AI started to make sense this year. The acceleration we've seen in DAX proficiency with the new frontier models released in the last six months has been incredible. We are not at the “perfect” level; performance could still be a weakness, but we are well past the point of considering it helpful. Today, we use AI every day to transform data, create and modify semantic models, and draft long, complex DAX code! We published two articles in August about using AI to write DAX.

So, I have news about our upcoming training and about clarifying our position at SQLBI on using AI to write DAX: long-time readers of this blog know that we have been cautious about using AI to write DAX. Clearly, something changed, and if you are interested, I want to recap the journey since the first experiments with AI in DAX and explain our current position. Sit down, relax, and enjoy the read.

The new courses

We recently created three courses for different steps in using AI to write DAX:

• AI for Power BI: Intro to understand the basics of AI in Power BI. This is not about Copilot; it is about using agents, skills, plugins, and MCP servers to manipulate Power BI models and reports. If you already use your Claude Code connected to Power BI, you probably already have this knowledge. If you are still using ChatGPT in a web browser by copying and pasting DAX code, you definitely need to learn the foundational concepts that apply to this new way of working, regardless of the specific tool. The tool choice doesn’t really matter, as long as it connects to MCP servers.
• DAX with AI: Essentials to learn how to read the DAX code generated by AI tools. If you already know DAX, you will see how to write correct prompts and how to actually use the agents to write measures. If you already know the AI tools but you are new to DAX, you will learn how to read DAX even if you are not able to write it.
• DAX with AI: Scenarios to deal with more complex scenarios where your work is mainly to control the direction of the development, which includes writing DAX code and modifying the model so that it generates the correct result for the business problem described. This course can be seen as a library of business scenarios you can use to create a specific solution or to learn generic techniques applied to different use cases. The course also includes foundational concepts (specific data modeling and DAX techniques) which you can apply to several scenarios. You can pick only the foundational concepts you need for the use case you are interested in, or you can just go through the whole course from A to Z. You have options.

The courses are built with two distinct profiles in mind. The first is someone who doesn't know DAX, uses an LLM (or would like to), and wants better results; this person needs to learn enough DAX to understand what the model produces. The second is someone who already knows DAX and mainly uses AI to copy-and-paste DAX code from a chat window. Our new courses are more complete for the first profile, while they provide tremendous guidance on the right combination of tools and prompts to be more efficient and effective for the second profile. That profile will also use this as an opportunity to validate their DAX knowledge in a new context.

In these courses, we don't teach AI tools because they change so often that a course can become obsolete before it is even published. This also means it doesn’t matter whether you use Visual Studio Code, Claude, Codex, other tools, or one day a chat integrated into Power BI (we all would like that, right?). We provide help installing it on separate pages that we'll keep up to date, but the courses focus on using AI effectively for DAX development.

We focus on how you write the request, we offer reasoning about the problem, we give instructions, and we check the result. Many AI videos for Power BI assume you already know the tools or show what's possible without explaining what matters when you use it for work. We focused on teaching without worrying about the demo effect.

But what about all the other courses? Are they still relevant? Yes, you will just use those skills in a different way. But let’s ask different AI models; I wrote the following prompt:

Is it worth attending a DAX training now that AI can write good DAX code?

Here are the answers from three frontier models – no cherry picking!

                    2020                         2026
             ─────────────────             ─────────────────
                 DAX syntax                    DAX semantics
               function recall                 filter context
              pattern knowledge               model reasoning
                   ↓                               ↓
             write the code               describe the intent
                                                ↓
                                           AI writes code
                                                ↓
                                      YOU verify correctness
                                                ↓
                                      YOU diagnose edge cases

The classroom courses on DAX do not change. If you attend one, you will learn to write and read DAX, and you need to read it to check the code the AI writes. Of course, we are also integrating into the in-person courses how to use the AI tools in the context of DAX development.

And now, the full story behind this change.

What we published about writing DAX with AI, 2023 to 2026, and our assessment by period. The details and the links are in the table at the end of the post.

What we said, and why we said it

In March 2023, we published an unplugged video where we wrote DAX measures with ChatGPT-4. A reader asked how ChatGPT could produce good DAX if most DAX on the internet is inaccurate, and my answer was that it wouldn't. In May 2024, we repeated the test with ChatGPT-4o. The result was interesting, but not ready for prime time.

The reason was never opposition to AI. I would not go back two years and write .NET code without Copilot. However, in 2023 and 2024, and for most of 2025, you couldn't think of writing DAX with AI without being able to read it well, which implies you could also write it. We did not want to suggest AI to write DAX: telling someone who cannot understand DAX (as many people were at the time) to blindly trust DAX measures generated by AI would have been a risk we did not want to promote.

In August 2025, we ran an experiment we did not publish: We tried to use AI to generate exercises for our courses. It gave ideas, it wrote good descriptive text, and the exercises were correct in their English. But they were also wrong. They repeated the example just shown instead of testing whether the student had understood the concept in a different scenario – and in several cases, they told the student to do the wrong thing.

That experiment fairly summarizes 2025. Productivity was low, given the time and skill required to get something usable. My logic is simple: if a tool saves me time, it works; if it doesn't, it doesn't make me more productive, it isn't ready, and I can wait for another version.

What changed in 2026

In February 2026, I saw the first sign. I sent Alberto a chat where a model asked for an example to explain a DAX concept and got it badly wrong. When I pointed out the error, it corrected it, corrected a second problem I hadn't noticed, and explained why. The correction showed that the AI had started to reason over the model rather than over the syntax. I like to say that AI failed the exam, but it was a very promising student.

Since Opus 4.8, we have seen big improvements, and the current generation of models (Opus 5, Fable, Sol, Astra, Grok 4.6, and others) reaches a level of DAX-writing quality we can no longer ignore. In my experience, two capabilities have changed. The first is generalization: the ability to abstract a concept and apply it to a different problem, which wasn't there two years ago and wasn't this refined six months ago. The second is the ability to work for one or two hours on a task without going off track. The sentence, "AI only repeats what it has read," could have been true in 2024, but it is definitely not true anymore. The latest models write DAX at a level above most of the students who leave our classroom courses. Not always, not for every problem, but the productivity improvement is definitely here.

Recently, we used AI to produce the exercises for the video courses, the task that had failed in August 2025. This time, we designed every exercise and let the model execute it; we reviewed everything before publishing. The result: more exercises, clearer descriptions, in less time. When I updated our generative AI guidelines on July 8, 2026, I wrote that we used AI for DAX analysis and "rarely in DAX coding". Two months later, that sentence was already outdated, which shows how fast things change.

Why we did not say this earlier

I expect the objection: "A year ago you said AI was not ready for DAX, and now you sell courses about it." All true, but there are no contradictions between this and our other statements. To make life easier for those looking for these statements, I included a table at the end of the article.

When there was a lot of hype and the tools did not work, we said it was too early. Now the tools are not perfect, but they are at a level where, if you check what they are doing, they save a lot of time. We kept applying the same rules for evaluating technologies, and the tools evolved. In July 2025, we said we would discuss AI to write DAX only when we saw clear, measurable benefits: we see them now.

Another reason is specific to training. A generic LLM course in 2025 would have filled a classroom, and it would have been obsolete within six months. It was too early for us to produce content about it. I don't know whether September 2026 is early, late, or on time, but it is the moment when we have something to teach that we expect to still be valid in a year or more.

I know, it looks like we made a U-turn instead of simply applying our judgment about the maturity of the tools. However, I stand by that, which was preferable than telling people to write DAX with AI too early. Today, it's not always 100% correct, and performance isn't always ideal. But the productivity gains are significant enough to justify its use. You should still be able to read DAX and validate the measures, but you probably don't have to be an advanced DAX expert as if you had to write all the code. That makes a big difference.

What works today

In August 2026, we published two articles that illustrate the capabilities of AI in DAX. The first is creating DAX functions with AI to remove duplicated code: when two measures share most of their code, in any language you move the common part into a function. With user-defined functions, you can do that in DAX too, and AI can write the function and replace the corresponding code in existing measures. Without AI, you can do this with some automated tools, but they stop at the first small difference between the two functions, whereas AI can handle those differences by moving parameters and adjusting the existing code to fit the new function.

When I write a measure, I have to look for the edge cases where the business rule could fail. This takes a lot of time, which is why almost nobody creates such tests. The AI connected to the semantic model finds the cases and writes the DAX queries in about ten minutes while I do something else. I have to check the expected values, because the tests must be validated at some point. In this case, the time saved doesn't shorten DAX writing, but it makes it possible to do validation work that was usually ignored.

These are two examples where we see clear and measurable benefits. When writing DAX code for an existing model, you have to be careful. When AI generates the model, the outcome is usually better. We are at the point where it can save time and money, depending on your skills. I can give many examples where writing a DAX formula takes less time than writing the prompt for it, at least for someone who knows DAX. At the same time, if I write complex calculations or transformations in DAX, it can save me time typing the formula if I review it carefully later. For someone who doesn't know DAX well, the quality of the DAX it produces usually addresses the question (if the requirements are clear), even if the code isn't ideal or bulletproof from a performance standpoint.

What still requires your attention

Nothing is perfect, neither are humans. We still have to pay attention to the prompt, the context, and, in particular, the model used in this work. There is a huge difference between the latest frontier models and what was released a year ago or more. Evaluating specific models is a topic for another article, and it will probably only be valid for a few weeks. So I prefer to focus on what you need to watch out for.

Set the goal, define the requirements clearly, identify edge cases, create tests, and make your model and measures more robust than what you would have done in the past: not because it was unnecessary, but because it was too expensive. We now have tools at our disposal that save us enough time to then focus on improving the quality of the solution we create. We will write more about this in future articles, and we have started incorporating these best practices into our new courses.

Conclusions

We have used the same rule for many years: we use a tool when it saves time in our work, we say what does not work as long as it does not work, and we say it works when it starts to work. When it comes to writing DAX with AI, that moment came in 2026: using AI today for DAX development is productive, and we can recommend it.

I also have another recommendation: Use AI to improve your productivity, but do not stop thinking. Your added value is in the corrections you apply to the output, and in DAX, they are probably more than the 5 to 10% that apply to other languages.

There has never been a better time to Enjoy DAX!

Timeline: what we published, and when

A simple daily average masks a level of complexity that may not be immediately apparent. In this article, we use AI to create a daily average, and then to analyze several edge-case scenarios to build a sound calculation.

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.

The goal is not to test the assistant or demonstrate complex DAX code. The goal is to show that AI makes it easier to write code. In doing so, it leaves more time for humans to focus on inspecting border cases, refining the formula, and producing better calculations. The very same scenario would be challenging for a human, too.

We start with the following prompt:

Create a measure Average Daily Sales (Selling Days) that computes the average daily sales amount by iterating over Date and computing [Sales Amount]

A plausible answer is the following, although your execution result may differ:

Average Daily Sales (Selling Days) =
AVERAGEX ( 'Date', [Sales Amount] )

The Date table contains one row per day. DAX evaluates Sales Amount for each date visible in the filter context, as it iterates the Date table reference. We can ask the assistant to iterate over the Date[Date] column:

Rewrite the measure to iterate over the distinct values of Date[Date], instead of the entire Date table. Keep the existing blank-handling behavior and explain whether this change affects the denominator in this model.

Average Daily Sales (Selling Days) =
AVERAGEX ( VALUES ( 'Date'[Date] ), [Sales Amount] )

The measure now works on a single-column table, and it seems a bit more efficient. However, both versions have the same issue: AVERAGEX excludes blank results from the average but considers zero as a relevant value. The two different table expressions produce the same behavior and result.

The following figure shows a simple average over five rows, with the two possible calculations. AVERAGEX performs the calculation on the left, and it ignores blanks.

To make the issue more evident, look at the following matrix, where we filtered one color (Brown) and expanded January 2024.

The value shown for January 2024 is the same as 01/19/2024, the only day with sales.

Product filters Sales; therefore, it filters its results, but it does not filter Date through the single-direction relationships. January shows 31 days, but Sales Amount is blank for 30 of them. We can ask AI for a query that makes the denominator visible:

Show me a DAX query for January 2024 that compares all product colors with the color Brown. Return the color, sales amount, the number of dates with a nonblank Sales Amount, and the average over those dates. Use the Sales Amount measure and query-scoped definitions for any additional measures. Do not change the model. Explain why a date without sales is excluded from this average.

The query generated should return a result very similar to the following one.

In January 2024, total sales are 188,419.28 across 27 days. The first measure returns 6,978.49. January has 31 days, but four have a blank Sales Amount and are excluded from the denominator. For Brown products, things are even worse: only one day has sales. In both cases, the number of considered days is less than 31.

There is no arithmetic error here. The measure computes average sales over days with a non-blank Sales Amount. For this sample, these are the days with sales. If this is the intended business definition, the measure is correct. However, if we want average sales per calendar day, the Brown result should be 8,454.80 divided by 31, or 272.74: the denominator should include days without sales.

To obtain the latter definition, we include an additional requirement in the prompt:

Generate a revised measure named Average Daily Sales (Calendar Days). Divide the total Sales Amount by the number of visible rows in Date, which has one row per day. A product filter must change the amount without removing days from the denominator. Use safe division, preserve a blank amount for now, and do not yet adjust for the beginning or end of the available history.

The generated measure does not have an iterator anymore:

Average Daily Sales (Calendar Days) =
DIVIDE ( [Sales Amount], COUNTROWS ( 'Date' ) )

Because Date contains one row per day, COUNTROWS returns the number of visible days. A filter on Product changes the numerator and not the denominator, because Date does not receive filters from other tables. The result now corresponds to the more refined requirements.

Another option would have been to replace blanks with zero inside AVERAGEX. For a period with sales, that would solve the denominator problem, even though the measure generated would be slower.

However, neither approach tells us whether all visible dates should participate in the calculation, in case the available calendar goes beyond the date range for which we have transactions.

To evaluate that condition, we ask AI to inspect the dates with the following prompt:

Generate a DAX query returning one row with the number of Date rows, the minimum and maximum Date[Date], the number of Sales rows, the minimum and maximum Sales[Order Date], the distinct count of order dates, and Sales Amount. Also count blank order dates and distinct order dates missing from Date. Query the model without modifying it. Report the date ranges without assuming that the first and last transaction prove data completeness.

The query shows that the calendar runs from January 1, 2022 through December 31, 2026. The first transaction is on May 21, 2022, and the last is on March 21, 2026.

The calendar in the Date table has months without transactions. Even if we reduce the calendar from May 2022 to March 2026, May 2022 (the first month with sales) contains 11 observed days, and March 2026 (the last month with sales) contains 21. Dividing sales by 31 days in either month understates the average.

The problem occurs at both ends of the available history. It also affects totals that include dates before or after that history, in case we did not reduce the calendar.

Notice the assumption we just introduced. The first transaction does not prove that data collection started on that date, and the last transaction does not prove that later dates are unavailable. It is possible that there were no sales on other days.

For the following example, we use the first and last transaction dates as the boundaries of a continuous observation period. We consider every date within that period available, including dates without transactions. In a production model, it is better to use exact data-availability dates when transaction dates cannot be used reliably.

Before improving the formula, we can ask AI to challenge the definition:

Review this average daily sales calculation before proposing another formula. Find small examples that distinguish days without sales from days outside the available data. Include product filters, incomplete first and last months, an empty period, nonconsecutive date selections, and totals. State the expected denominator and identify any business rule that the data alone cannot establish.

This is a useful role for AI. Instead of asking only for implementation, we also ask it to find cases that might disprove it. A proposed test becomes useful when we can state its expected result independently and then execute it.

The AI result is a table containing several edge case scenarios that must be carefully inspected and covered by our new rule.

After analyzing the scenarios, we end up with the following requirements for our average:

• Count the selected dates that fall within the observation period.
• Include days without sales within that period.
• Use the same observation period for every product, customer, and store selection.
• Return zero when there are days observed but no sales for the current selection.
• Return blank when there are no observed days selected.

The difference between zero and blank matters: zero means we observed a time period and found no sales; blank means we want to ignore that period for the average.

A possible mistake would be to compute the first and last sale after removing only the Date filter, because it is incompatible with our requirements. With that approach, a product selection can move the boundaries. In our sample file, the last sale of a Green product is February 12, 2026, whereas other products have sales through March 21, 2026. Selecting Green should not make March unavailable. However, for our requirements, the March average is zero over 21 days observed.

For the same reason, we must not use each month’s first and last transaction as that month’s boundaries. March 2026 has its first transaction on March 3, but March 1 and March 2 are inside the overall observation period, so they must be counted.

We now have enough information to create the request for the complete measure:

Generate a measure named Average Daily Sales using these rules:
- Define one inclusive observation period from the first to the last Sales[Order Date], ignoring all ordinary report filters when finding those boundaries.
- Count only currently visible, nonblank Date[Date] values inside that period.
- Preserve nonconsecutive selections.
- Compute Sales Amount over exactly those dates while preserving the other report filters.
- Include dates without sales in the denominator.
- Return zero for an observed selection without sales, and blank if there are no observed dates or no boundary dates.
- Use variables to make the steps readable.

The resulting code is considerably longer than the first expression we obtained at the beginning of the article:

Average Daily Sales =
VAR FirstObservedDate =
    CALCULATE ( MIN ( Sales[Order Date] ), REMOVEFILTERS () )
VAR LastObservedDate =
    CALCULATE ( MAX ( Sales[Order Date] ), REMOVEFILTERS () )
VAR ObservedDates =
    FILTER (
        VALUES ( 'Date'[Date] ),
        NOT ISBLANK ( 'Date'[Date] )
            && 'Date'[Date] >= FirstObservedDate
            && 'Date'[Date] <= LastObservedDate
    )
VAR NumberOfDays = COUNTROWS ( ObservedDates )
VAR Amount =
    CALCULATE ( [Sales Amount], KEEPFILTERS ( ObservedDates ) )
RETURN
    IF (
        NOT ISBLANK ( FirstObservedDate )
            && NOT ISBLANK ( LastObservedDate )
            && NumberOfDays > 0,
        DIVIDE ( COALESCE ( Amount, 0 ), NumberOfDays )
    )

FirstObservedDate and LastObservedDate are evaluated after removing any report filters. Therefore, a filter on products or months in a matrix row does not move the boundaries. REMOVEFILTERS does not bypass row-level security: the interval is shared within the data visible to the current security context.

ObservedDates starts from VALUES ( 'Date'[Date] ), which contains the dates visible in the current filter context. FILTER keeps only those dates within the observation period and excludes a possible blank member.

The code considers only the selected dates. For example, if the user selects January and March, the denominator must not include February. If it counted the elapsed days between the first and last selected date, it would include February, which the user could have excluded. The same principle applies to a selection of individual days or weekdays.

The Average Daily Sales measure then counts ObservedDates and evaluates Sales Amount over the same set. KEEPFILTERS intersects this set with the current context. Filters applied to Product, Customer, and Store continue to affect the amount.

Finally, the measure returns a result only when the boundaries exist and at least one observed day is selected. COALESCE explicitly returns zero for an observed period without sales.

At this point, the AI has generated considerably more DAX than the original request had suggested. If we need the same rule in several measures, we can ask AI to place the logic in a DAX user-defined function (UDF). The following prompt must be executed in the same LLM session we previously used, so it implicitly references the measure Average Daily Sales which the AI created before:

Generate a DAX user-defined function named Contoso.AveragePerObservedDay that encapsulates the observed-date selection, day counting, amount evaluation, and zero-versus-blank policy of the revised measure. Accept three parameters: an amount expression, the first observed date, and the last observed date. The caller must supply the boundaries.

The result is a reusable function that encapsulates the complex logic and can be reused in different measures:

Contoso.AveragePerObservedDay =
    (
        AmountExpression : SCALAR EXPR,
        FirstObservedDate : SCALAR VAL,
        LastObservedDate : SCALAR VAL
    ) =>
        VAR ObservedDates =
            FILTER (
                VALUES ( 'Date'[Date] ),
                NOT ISBLANK ( 'Date'[Date] )
                    && 'Date'[Date] >= FirstObservedDate
                    && 'Date'[Date] <= LastObservedDate
            )
        VAR NumberOfDays = COUNTROWS ( ObservedDates )
        VAR Amount =
            CALCULATE ( AmountExpression, KEEPFILTERS ( ObservedDates ) )
        RETURN
            IF (
                NOT ISBLANK ( FirstObservedDate )
                    && NOT ISBLANK ( LastObservedDate )
                    && NumberOfDays > 0,
                DIVIDE ( COALESCE ( Amount, 0 ), NumberOfDays )
            )

The function is intentionally tied to the Date table that must exist in the model. It accepts AmountExpression as the expression to compute within the two observation boundaries specified in the following arguments. The caller decides where the boundaries come from, while the function implements the selection, counting, and empty-result rules.

Once the function is in place, we ask the AI to generate the measure that invokes the function:

Generate a measure named Average Daily Sales (Function) that calls Contoso.AveragePerObservedDay with Sales Amount. Preserve the business behavior of Average Daily Sales.

The measure generated should be very close to the following one:

Average Daily Sales (Function) =
Contoso.AveragePerObservedDay (
    [Sales Amount],
    CALCULATE ( MIN ( Sales[Order Date] ), REMOVEFILTERS () ),
    CALCULATE ( MAX ( Sales[Order Date] ), REMOVEFILTERS () )
)

Other additive amounts using the same date relationship, observation period, and zero policy can reuse the function. A balance or a distinct customer count would need a separate analysis: dividing an aggregate by a day count is not generally equivalent to averaging its daily values.

Conclusions

Although it looks like a simple task, computing an average hides a lot of complexity. This is true for simple calculations, but it is also true (and harder) for more complex calculations. When you define a calculation, you always need to ask yourself how the calculation will behave in border-case scenarios, typically at the beginning or at the end of a time period, at the subtotal and total levels, with different selections, or with blank values in some of the columns or partial calculations.

This is where AI shines, because we can delegate several tasks: perform deeper analysis, try different versions of the code, and find examples that may prove the measure correct or disprove it, showing that some fixes are still needed.

Before the advent of AI, BI professionals spent much of their time writing and debugging DAX code. Today, AI can reduce that time, helping us improve the quality and soundness of measures and calculations.

A DAX calculation can produce convincing values at the detail level and fail at the total. The problem is often that the business rule was evaluated at the wrong granularity. This video introduces four grains, or levels of granularity, to identify before writing your DAX formula.

A DAX calculation can produce convincing values at the detail level and still fail at the total. The problem is often not the total itself: the business rule was evaluated at the wrong granularity. This article introduces four grains, or levels of granularity, that should be identified before writing your DAX formula.

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.

The principle to remember is the following:

Evaluate the business rule at the lowest grain where all its inputs have one unambiguous meaning; aggregate only afterward.

There are four grains involved in applying this principle:

• The source-table grain.
• The business-rule grain.
• The iteration or evaluation grain.
• The requested output grain.

These grains can be the same; in that case, the code is simple to write. The interesting problems arise when they differ. To showcase the scenario, we use a very simple model about producing lemonade.

Introducing the lemonade model

The sample model describes lemonade production. It contains two tables joined by a regular one-to-many relationship.

Recipe contains one row for each recipe. Ingredient rates are properties of a recipe; they are not additive values, and as such, they cannot be aggregated.

Batch contains one row for each production batch, and each batch, expressed in liters produced, follows a recipe.

Water is additive. Therefore, the Total Water is straightforward, and it is already visible in the report:

Total Water = SUM ( Batch[Water Liters] )

However, the business question we want to answer is: “How many sugar spoons and how many liters of lemon juice should we allocate to prepare the three batches?”

The ingredient requirements are business rules:

• Sugar spoons are water liters multiplied by the sugar rate of the recipe.
• Lemon juice liters are water liters multiplied by the lemon percentage of the recipe.

The calculations are easy for an individual batch. For example, B01 uses 10 liters of water and the Sweet recipe, so it requires 30 spoons of sugar and 2.5 liters of lemon juice. Difficulty appears when multiple recipes are aggregated.

The following measures look reasonable at first sight. Total Water computes the total water, whereas the two percentages need to be aggregated, and AVERAGE may seem like a good idea:

Wrong Sugar = [Total Water] * AVERAGE ( Recipe[Sugar Spoons per Liter] )

Wrong Lemon = [Total Water] * AVERAGE ( Recipe[Lemon Juice % of Water] )

Both measures produce the expected value when the report shows one recipe in each row, but the total is clearly wrong. The value is correct at both the batch and recipe levels. Above the recipe level, it is not correct.

At the total level, Wrong Sugar computes 14 liters multiplied by the unweighted average of 1 and 3 spoons per liter. The result is 28. Wrong Lemon computes 14 multiplied by the average of 10% and 25%, returning 2.45 liters.

Neither calculation represents the production plan. The batches contain 12 liters of Sweet lemonade and only 2 liters of Classic lemonade. A simple average gives both recipes the same weight, producing an incorrect result.

By default, Power BI does not sum the visible rows. A measure is evaluated independently in every cell. In the total cell, the requested output contains both recipes, and the formula explicitly asks DAX to average their rates. DAX answers that question correctly; it is the question that is wrong, because it does not consider the different granularities at play.

The four grains identify where the mistake occurs.

The source grain is a property of the data, not of the DAX code. In this model, Batch and Recipe have different grains. The model works because every batch identifies its recipe through a relationship.

The business-rule grain is the level at which the rule can be evaluated without inventing an aggregation for any of its inputs. In the current model, you can first sum water by recipe because the ingredient rate is constant within each recipe.

This produces the following intermediate result.

Only after applying the rule to each row of this table is it safe to aggregate. The correct totals are 38 sugar spoons and 3.20 liters of lemon juice.

Evaluating at a finer grain is also safe, as we show with the next measure. In our example, computing the rule once for every batch produces the same result because the rate is constant for all batches of the same recipe.
However, evaluating below the necessary grain can perform more work than required. A useful operational interpretation of the central rule is therefore:

Never evaluate above the business-rule grain. A finer grain can be correct; the coarsest grain that still preserves an unambiguous meaning is usually more efficient.

The DAX expression defines the evaluation and the granularity: the table passed to the SUMX iterator declares the grain of the calculation. For example, the following measure evaluates the sugar rule at the Batch granularity. It multiplies the water liters in each Batch row by the number of sugar spoons per liter defined in the corresponding recipe:

Sugar Spoons at batch grain =
SUMX (
    Batch,
    Batch[Water Liters] *
        RELATED ( Recipe[Sugar Spoons per Liter] )
)

This formula is correct. However, you can perform the same calculation at the business-rule grain.

Sugar Spoons =
SUMX (
    SUMMARIZE (
        Batch,
        Recipe[Recipe],
        Recipe[Sugar Spoons per Liter]
    ),
    [Total Water] * Recipe[Sugar Spoons per Liter]
)

SUMMARIZE builds one row for each recipe visible in the current filter context. SUMX iterates those rows. Total Water invokes the context transition because it is a measure, so it returns the water for the current recipe. The rate comes from the recipe itself, which is the same iterated row. Finally, SUMX aggregates the recipe results, only after the rule has been applied.

The lemon calculation follows the same structure:

Total Lemon =
SUMX (
    SUMMARIZE (
        Batch,
        Recipe[Recipe],
        Recipe[Lemon Juice % of Water]
    ),
    [Total Water] * Recipe[Lemon Juice % of Water]
)

In this specific calculation, grouping only by Recipe[Lemon Juice % of Water] would also be correct and more efficient. Recipes with the same percentage can be combined because the percentage is the only non-additive input the rule uses. However, grouping by recipe as well can be simpler for an initial implementation, and it is easier to change if the rule later needs other recipe properties. Similarly, Sugar Spoons could aggregate by Recipe[Sugar Spoons per Liter], using a single multiplication for all recipes with the same number of sugar spoons per liter.

The source-grain and business-grain versions return the same values at the recipe rows and at the total. Both formulas evaluate the multiplication before combining rows with different rates.

The query generated for the report determines the requested output grain. A matrix can request a value for each batch, each recipe, or the entire production plan. It can also change after a user drills down or adds a field to the visual.

A robust measure does not rely on the output grain to produce correct results. In order to make the rule unambiguous, the measure enforces its own evaluation grain internally:

• On a recipe row, the iterator sees one recipe.
• At the grand total, the iterator sees both recipes and sums two results.
• If the visual does not display Recipe at all, the measure still evaluates the rule by recipe.

This is why you should not fix a total by blindly iterating the rows currently visible in a visual. The visual grain is a presentation choice. The business-rule grain belongs in the measure and should remain valid when the visual changes.

Recognizing the same problem in budgeting

Budgeting scenarios are a larger version of the lemonade example. Actual sales might exist by transaction, product, and day, while a budget is defined by product category and month. The source tables have different grains, the requested report can use yet another grain, and an allocation rule introduces its own grain.

There are only two correct choices when a report requests detail below the budget grain:

• Do not display the budget at unsupported detail.
• Define an explicit allocation rule that creates that detail.

Repeating a category budget for every product is not allocation. It duplicates the value. Similarly, computing one allocation factor after categories or periods have already been combined evaluates the rule at a grain above its valid level.

The Budget pattern makes the business choices explicit and computes prior-year sales at the forecast grain before allocating the forecast. The article, Budget and Other Data at Different Granularities in PowerPivot illustrates the related modeling problem with daily sales and monthly budgets. In both cases, the complexity comes from creating meaning across grains: the DAX formula in the model measure is the final representation of those choices.

The four-grain framework scales to these scenarios:

The developer must decide whether the requested output is supported directly, needs an allocation, or should return blank. You cannot delegate that decision to AVERAGE or the total row of a matrix.

A checklist for grain-aware DAX

Before writing a measure, answer these questions:

1. What does one row mean in every source table used by the calculation?
2. Which keys make every rate, threshold, percentage, status, or other rule input unambiguous?
3. Which inputs are additive before the rule is applied, and which are not?
4. What virtual table should an iterator enumerate to express the business-rule grain?
5. Is the chosen evaluation grain independent of the fields currently displayed in the visual?
6. Has any upstream transformation aggregated away a key required by the rule?
7. What should happen when the report requests detail below the grain supported by the data?
8. Can the result be verified by manually computing a few business-grain rows and aggregating them?

The last test can be very effective. Do not start by checking whether the grand total equals the visible rows. First, build the small intermediate table at the business-rule grain, validate each row, and only then validate its aggregation.

Conclusion

The hardest part of many DAX calculations is deciding where to evaluate the business rule. The source grain describes the available data. The business-rule grain describes where the rule has meaning. The iteration grain describes what the DAX code actually does. The requested output grain describes what the report asks for. Correct results require these four grains to be compatible, not necessarily identical.

The lemonade model is a simple example to show what could go wrong, because it has only two recipes and three batches. In a real model, the same mistake can be hidden behind thousands of products, changing rates, allocations, and several dimensions.

The rule of thumb is to evaluate the business rule at the lowest grain where all its inputs have one unambiguous meaning, and aggregate only afterward.

Let's talk about matrix totals in this episode of The Whiteboard!
Special guest: the SQLBI Whiteboard!

Learn abstract DAX concepts in a more interactive way with "The Whiteboard" series.
Read more: https://www.sqlbi.com/blog/marco/2022/07/14/the-whiteboard-video-series-on-sqlbi-youtube-channel/

#thewhiteboard

SQLBI Whiteboard is a free, open-source whiteboard for Windows 11 designed for teaching DAX, SQL, and data modeling, with pen ink, live application capture, and syntax-highlighted code on the same canvas.

• Pen-first ink — Low-latency, pressure-aware strokes with palm rejection, rear-eraser support, and a barrel-button laser pointer, alongside full touch and mouse support on an unbounded canvas.
• Live application capture — Embed a live view of any running window or display on the board — a Power BI report, SSMS, anything — then freeze it and draw over it.
• DAX and SQL snippets — Paste code into containers with live syntax highlighting, and format DAX or T-SQL with one key.
• Boards are files — Documents are local .wboard files with no account and no cloud service, and Markdown-based .wimport recipes build a prepared board from a text file.

SQLBI Whiteboard is a standalone application rather than a collaboration service: there is no shared canvas, no tenant, and no sign-in. It is designed for the room, the projector, the recorded lesson, and the pen — the way we teach.

Download it from the Microsoft Store, or as an installer or portable ZIP from GitHub, where the source code is available under the MIT license. Documentation, guide, and FAQ are at whiteboard.sqlbi.com.

How to use AI to identify relevant test cases in a semantic model and create repeatable DAX tests for a measure.

This article describes how to use AI to identify relevant test cases in a semantic model and create repeatable DAX tests for a measure.

A DAX measure is correct only when it produces the expected result in every situation of filter context and available data. A value being correct in a report for a particular product or customer does not prove that the measure works for other selections. This matters when the measure implements business logic that depends on the active filters.

We usually validate this logic by creating report pages that isolate a few customers and transactions. This is useful during development because we can see the data and we can reason about the result. However, manual validation is difficult to repeat after every change. It is also easy to forget a filter combination that previously exposed a problem.

AI can help with the repetitive part of this work. Once connected to the semantic model, the AI assistant can explore the data, find representative cases, and generate DAX queries that reproduce the required filter contexts. The expected results still require human validation. The role of AI is to find and automate the cases, not to define the business requirement.

Understanding the measure to test

We start from the New and returning customers pattern and, in particular, from the Dynamic relative implementation of the # New Customers measure. The pattern takes into account all the report filters. For example, if the report filters the Audio category, a customer is considered new on the first date when they purchase an Audio product. An earlier purchase in another category does not make the customer exist for the Audio category.

The same rule applies to brands. If the filter contains Contoso and Northwind Traders, the two brands form one selection. The customer is new on their first purchase date across the union of those brands. The measure must not compute a separate first purchase date for each brand.

This behavior makes the measure a good candidate for regression testing. A modification can preserve the result of a simple case while changing how the measure handles a category filter or a multiple-brand selection. Therefore, testing only a straightforward positive result is not enough.

Identifying four types of tests

We want tests that can detect both incorrect inclusions and incorrect exclusions. For this reason, we manually identify four types of cases before asking AI to automate them:

• A positive testchecks that a qualifying customer is counted. This is the simplest valid case and it confirms that the main path of the calculation works.
• A negative testchecks that a customer without a qualifying purchase in the selected context is not counted. This detects filters that are ignored or incorrectly removed.
• A prevent false positive testuses a case that looks valid under a narrower filter but is invalid under the filter being tested. This detects a measure that counts too many customers.
• A prevent false negative testuses a valid case with an earlier purchase outside the current selection. This detects a measure that counts too few customers because it evaluates the first purchase over the wrong set of products.

We manually created the following report pages, which isolate one case for each purpose. Read the details of each example to understand the type of tests implemented, which are intended to evaluate possible regressions in case of changes to the measure. You can skip the details of each case if you just want to see how to automate this process.

Testing a positive case

Customer 362, Cindy Ramos, purchased Audio products from both Contoso and Northwind Traders on April 9, 2007. This is the first purchase date in the selected two-brand Audio context. Therefore, # New Customers must return 1.

The report also shows later purchases when all brands and categories are visible. Those later transactions do not affect the expected result on April 9, 2007.

This test confirms that a customer with a qualifying first purchase is included. However, it does not prove that the measure excludes transactions outside the selection.

Testing a negative case

Customer 11317, Deanna Sara, made a purchase on July 9, 2007. That transaction was for Fabrikam in Cameras and camcorders, so it is outside the selected Contoso and Northwind Traders Audio context. The expected value of # New Customers for that date and selection is 0.

The same customer first purchased Audio from Northwind Traders on July 23, 2007. The report returns 1 on that later date under the two-brand Audio filter, but it must not return 1 on July 9.

This test detects a measure that removes or ignores the category and brand filters while finding the first purchase.

Preventing a false positive

The same customer provides a more subtle case. Deanna Sara bought Audio from Northwind Traders on July 23, 2007, and from Contoso on August 14, 2007. If the report selects only Contoso, the customer is new on August 14 because that is her first Contoso Audio purchase.

The expected result changes when the report selects both Contoso and Northwind Traders. The first purchase in that union occurred on July 23. Therefore, the customer is not new on August 14, and # New Customers must return 0.

This case detects an implementation that evaluates the first purchase separately for every selected brand. This implementation could pass the positive and negative tests. However, it still counts the same customer as new more than once within a multiple-brand selection.

Preventing a false negative

Customer 175, Nicholas Robinson, made a purchase on January 18, 2007, outside the selected Audio context. The first purchase in the Contoso and Northwind Traders Audio selection occurred on October 27, 2007. Consequently, # New Customers must return 1 on October 27.

This case detects a measure that uses the first purchase across all products. That behavior would be correct for a dynamic absolute calculation, but it is incorrect for the dynamic relative measure we are testing.

Asking AI to create the tests

Manually finding these cases requires repeated filtering and inspection of transaction histories. We can automate the discovery and generation process by connecting an AI assistant to Power BI Desktop through the Power BI Modeling MCP server. The local server lets the assistant inspect the semantic model and execute or validate DAX queries against it.

We used the following prompt:

I want to create tests for the # New Customers measure. It is dynamic, which takes into account all the filters in the report for the calculation. Therefore, if the report filters one category (Audio, for example), a customer is reported as new the first time they buy a product of the Audio category. Find cases for one and two brands selected that show positive, negative, and prevent false positive and false negative. Create the DAX queries to validate the test. The query should return one or more row with the test name, the status, and a description of the test result (with an explanation of the issue found). We'll assign each query to a function and run one or more tests together by using UNION of their results.

The prompt provides the semantic rule, the dimensions that must vary, the four test purposes, and the required output contract. This information is important. A generic request to test the measure would leave the assistant free to choose cases that do not exercise the relevant filter behavior.

The assistant inspected the model and generated eight tests: four for a single selected brand and four for a two-brand selection. We manually verified the selected transactions before keeping their expected values. This step prevents a circular test in which the measure under test also determines the expected result.

Organizing tests with DAX functions

The generated query uses DAX user-defined functions to keep every test independent and give every result the same shape. DAX user-defined functions (UDF) can be defined and evaluated in DAX query view by using the FUNCTION keyword in a DEFINE block. They require compatibility level 1702 or higher and are generally available in Power BI Desktop and the Power BI service starting from the June 2026 release. A common TestResult function compares the actual and expected values and returns one row with the test name, status, and description.

Each test function applies filters for one customer, category, brand selection, and date. TREATAS reproduces the filter context of the report, whereas COALESCE converts a blank result to zero when the customer must not be counted.

The following extract shows the common result function and the two-brand false-positive test:

// Creates the standard one-row result returned by every test.
TestResult = (
    testName : STRING,
    actualValue : INT64,
    expectedValue : INT64,
    passDescription : STRING,
    failureExplanation : STRING
) =>
    ROW (
        "Test name", testName,
        "Status", IF ( actualValue = expectedValue, "PASS", "FAIL" ),
        "Description",
            IF (
                actualValue = expectedValue,
                passDescription,
                failureExplanation
                    & " Expected " & FORMAT ( expectedValue, "0" )
                    & ", got " & FORMAT ( actualValue, "0" ) & "."
            )
    )

// Guards against calculating the first purchase separately for each brand.
Test_NewCustomers_2Brands_NoFalsePositive = () =>
    VAR Actual =
        COALESCE (
            CALCULATE (
                [# New Customers],
                TREATAS ( { 11317 }, Customer[CustomerKey] ),
                TREATAS ( { "Audio" }, Product[Category] ),
                TREATAS (
                    { "Contoso", "Northwind Traders" },
                    Product[Brand]
                ),
                TREATAS ( { DATE ( 2007, 8, 14 ) }, 'Date'[Date] )
            ),
            0
        )
    RETURN
        TestResult (
            "2 brands - prevent false positive",
            Actual,
            0,
            "Customer 11317 is not counted because the first purchase "
                & "in the selected union was on 2007-07-23.",
            "The first date was evaluated per brand instead of across "
                & "the selected brand union."
        )

The final EVALUATE statement combines the selected functions with UNION. We can add or remove function calls to run the complete suite or only the tests related to a specific change:

EVALUATE
UNION (
    Test_NewCustomers_1Brand_Positive (),
    Test_NewCustomers_1Brand_Negative (),
    Test_NewCustomers_1Brand_NoFalsePositive (),
    Test_NewCustomers_1Brand_NoFalseNegative (),
    Test_NewCustomers_2Brands_Positive (),
    Test_NewCustomers_2Brands_Negative (),
    Test_NewCustomers_2Brands_NoFalsePositive (),
    Test_NewCustomers_2Brands_NoFalseNegative ()
)

The sample Power BI file you can download below, contains the helper function and all eight test functions.

Reading the test results

Running the query against the original measure produces one row for each test. All eight tests return PASS.

A failing row reports the expected and actual values and includes an explanation of the likely issue. Therefore, the query provides the first piece of information required to investigate a regression when it detects one.

Keeping the tests useful

The test query is deterministic after we select the customers, dates, filters, and expected values. The AI discovery process does not need to be deterministic because it is only used to create the initial suite. Once the tests are reviewed, they become ordinary DAX code that we can store with the project and execute again.

These tests depend on the sample data. If transactions or dimension values change, we must review the cases or run them against a stable test model. To implement tests, we always need controlled input data, regardless of whether we use AI to create these tests.

Moreover, the measure passing all eight tests does not prove that the measure is correct for every possible context. The goal of this article was to provide a more efficient way to create tests, but the completeness of the test suite depends on the calculation implemented and the model complexity that could affect the DAX expression.

Conclusions

Testing a DAX measure requires more than comparing a few totals. We must reproduce the filter contexts that define the business rules and include cases that detect both false positives and false negatives.

AI connected to the semantic model can reduce the effort required to find those cases and write the corresponding DAX queries. However, we must verify the expected results independently before accepting the tests. Use AI to explore the model and generate the repetitive code; keep the definition of what is correct behavior under human control.

How to leverage AI to refactor existing DAX code, remove duplicated parts, and centralize the common code in user-defined functions.

Code written by AI can contain many duplicate parts. User-defined functions are a great tool to simplify complex measures; this article shows how to leverage AI to build better DAX code.

AI is getting better and better at generating DAX code, and as of today, it is pretty common to see rather complex DAX code that is entirely AI-generated. Sometimes AI-generated code requires refactoring or adjustment to make it more performant or reusable.

In this article, we show the process of first obtaining DAX code that actually works with AI, and then refactoring it to produce a better version. How do we refactor it? With AI, of course. AI can be a great tool if you ask it the right questions.

As an example, we are going to use the New Customer pattern. The prompt is deliberately simple and somewhat ambiguous, as it does not contain any clarification about what we mean by “new” customer, or how we want to generate the code. However, we are not interested in the algorithm but, rather, in the type of DAX code generated:

Create two measures: I want to compute the new customers and then the sales of new customers

AI quickly answers with two measures:

# New Customers = 
VAR FirstVisibleDate =
    MIN ( 'Date'[Date] )
VAR LastVisibleDate =
    MAX ( 'Date'[Date] )
VAR CustomersInCurrentPeriod =
    VALUES ( Sales[CustomerKey] )
VAR NewCustomers =
    FILTER (
        CustomersInCurrentPeriod,
        VAR CurrentCustomerKey = Sales[CustomerKey]
        VAR FirstPurchaseDate =
            CALCULATE (
                MIN ( Sales[Order Date] ),
                REMOVEFILTERS (),
                TREATAS ( { CurrentCustomerKey }, Customer[CustomerKey] )
            )
        RETURN
            FirstPurchaseDate >= FirstVisibleDate
                && FirstPurchaseDate <= LastVisibleDate
    )
RETURN
    COUNTROWS ( NewCustomers )

Sales New Customers = 
VAR FirstVisibleDate =
    MIN ( 'Date'[Date] )
VAR LastVisibleDate =
    MAX ( 'Date'[Date] )
VAR CustomersInCurrentPeriod =
    VALUES ( Sales[CustomerKey] )
VAR NewCustomers =
    FILTER (
        CustomersInCurrentPeriod,
        VAR CurrentCustomerKey = Sales[CustomerKey]
        VAR FirstPurchaseDate =
            CALCULATE (
                MIN ( Sales[Order Date] ),
                REMOVEFILTERS (),
                TREATAS ( { CurrentCustomerKey }, Customer[CustomerKey] )
            )
        RETURN
            FirstPurchaseDate >= FirstVisibleDate
                && FirstPurchaseDate <= LastVisibleDate
    )
RETURN
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS (
            TREATAS ( NewCustomers, Customer[CustomerKey] )
        )
    )

AI being AI, the result is not always the same. However, most of the time it produces a similar algorithm. Despite not being super-duper optimized, this algorithm works fine, and it is not a bad solution for a small/medium data model.

As humans, we quickly notice that most of the code is duplicated. Both measures compute the NewCustomers variable; the first one just counts its rows, whereas the second uses the variable as a table filter in CALCULATE. It is the perfect scenario where designing a user-defined function (UDF) would have been a great solution.

However, because of the final step, creating the function is not entirely obvious. A good DAX developer would solve the scenario immediately. What about AI? Let us test it:

Both measures have a very similar logic, apart from the last step. Can you create a function that embeds most of the logic of new customers, so to produce simpler measures and put the DAX code at a common factor in the function?

AI decides to create a function for the NewCustomers variable only and, AI being AI, it decides to make the function as model-independent as possible, by passing the required columns as arguments. This is definitely a very good idea, but it also increases the number of parameters to call the function, unless we create a proxy:

NewCustomersSet = (
    customerKey : COLUMNREF,
    orderDate : COLUMNREF,
    dateColumn : COLUMNREF
) =>
    VAR FirstVisibleDate = MIN ( dateColumn )
    VAR LastVisibleDate = MAX ( dateColumn )
    VAR CustomersInCurrentPeriod = VALUES ( customerKey )
    VAR NewCustomers =
        FILTER (
            CustomersInCurrentPeriod,
            VAR CurrentCustomerKey = customerKey
            VAR FirstPurchaseDate =
                CALCULATE (
                    MIN ( orderDate ),
                    REMOVEFILTERS (),
                    TREATAS ( { CurrentCustomerKey }, customerKey )
                )
            RETURN
                FirstPurchaseDate >= FirstVisibleDate
                    && FirstPurchaseDate <= LastVisibleDate
        )
    RETURN
        NewCustomers

Indeed, despite the function being very generic, the measures are still quite complicated:

# New Customers =
COUNTROWS (
    NewCustomersSet (
        Sales[CustomerKey],
        Sales[Order Date],
        'Date'[Date]
    )
)

Sales New Customers =
VAR NewCustomers =
    NewCustomersSet (
        Sales[CustomerKey],
        Sales[Order Date],
        'Date'[Date]
    )
RETURN
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS (
            TREATAS ( NewCustomers, Customer[CustomerKey] )
        )
    )

We want to go one step further by simplifying the measures at the cost of generating a model-dependent UDF:

I don't want the function to use so many arguments. Create one function that accepts as its only parameter the formula to compute, and then returns the formula computed for only the new customers.

Now that our intentions are clearer, AI produces the desired code:

NewCustomers = (
    expression : SCALAR EXPR
) =>
    VAR FirstVisibleDate = MIN ( 'Date'[Date] )
    VAR LastVisibleDate = MAX ( 'Date'[Date] )
    VAR CustomersInCurrentPeriod = VALUES ( Sales[CustomerKey] )
    VAR NewCustomersSet =
        FILTER (
            CustomersInCurrentPeriod,
            VAR CurrentCustomerKey = Sales[CustomerKey]
            VAR FirstPurchaseDate =
                CALCULATE (
                    MIN ( Sales[Order Date] ),
                    REMOVEFILTERS (),
                    TREATAS ( { CurrentCustomerKey }, Customer[CustomerKey] )
                )
            RETURN
                FirstPurchaseDate >= FirstVisibleDate
                    && FirstPurchaseDate <= LastVisibleDate
        )
    RETURN
        CALCULATE (
            expression,
            KEEPFILTERS (
                TREATAS ( NewCustomersSet, Customer[CustomerKey] )
            )
        )

And the two measures are straightforward, as desired:

# New Customers = 
NewCustomers ( [# Customers] )

Sales New Customers = 
NewCustomers ( [Sales Amount] )

There are several non-trivial aspects to note here. First, when asked to refactor the function, AI actually changed the algorithm for NewCustomers, moving from COUNTROWS to CALCULATE with a filter. This is not an easy step at all. Moreover, when we requested simpler code, AI decided to reuse the # Customers measure rather than keeping the old COUNTROWS.

Conclusions

AI is a great tool to author DAX code. Clearly, the code needs to be validated thoroughly before putting it in production. When using AI, a good practice is to ask exactly for what you want, so the code can be generated in a single pass and be good, straight out of the box.

User-defined functions are a great tool in Power BI to centralize code. When authoring code with AI, never forget to search for opportunities to use UDFs, as AI does not always use them. However, a human who can read and understand DAX can produce good results with minimal effort by using functions.

Microsoft recently introduced a new default visual design style for Power BI named Fluent 2. I personally disagree with a few choices in the new defaults, including the removal of alternating row colors from Table and Matrix visuals.

However, the feature is still available. You can select a Matrix and change its Style property from the Format pane, under Visual, Style presets. Here is the difference between the two styles: None is the setting you get when you create a new matrix with Fluent 2, which is on the right in the following screenshot. On the left is how the matrix looks once we set the Style to Default.

Therefore, fixing one Matrix is straightforward: change its style from None to Default.

Because it took me almost half an hour to fix this properly using modern LLMs, I hope this post will help others (humans and agents) facing the same issue in the future!

Visual calculations can either improve or degrade the performance of a report. Let's see how we ensure visual calculations have a positive impact on performance.

Visual calculations can either improve or degrade the performance of a report. In this article, we outline how we ensure visual calculations have a positive impact on performance.

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.

Imagine we want to compute a report containing the distinct count of customers by year, as well as the growth in percentage between the current year and the previous years.

The measure required is straightforward:

YOY % =
VAR CY = [# Customers]
VAR PY =
    CALCULATE ( [# Customers], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR Result =
    DIVIDE ( CY - PY, PY )
RETURN
    Result

We chose DISTINCTCOUNT on purpose: we wanted a measure that is non-aggregatable and heavy in terms of computational requirements. When executed on the version of Contoso with 23M rows in Sales, the report requires around 13 seconds to execute, and this is not surprising at all. Before looking at the details in the server timings, let us reason as to why the measure is expected to be slow.

First of all, not only is DISTINCTCOUNT a heavy operation to perform, but it is also non-additive – as such, it requires multiple scans of the fact table to compute. Secondly, time intelligence calculations require complex reasoning that is difficult to optimize. The DAX engine first computes the distinct count of customers by year. Then for each year, it determines the previous year; and to compute the previous year's distinct count, it then runs another query against the database. In other words, each cell requires its own query to retrieve the value of the previous year.

Indeed, when looking at the details, we see confirmation of our reasoning: there are many storage engine queries, each computing the distinct count for one year.

As humans, the first consideration that comes to mind is quite simple: why compute the value of the measure on a single year when the engine has already computed the value of the measure grouped by year? The value is already there; just use it without needing to recalculate it, right? True, but this is a very human behavior: we see the yearly value because the matrix is sliced by year; therefore, we cut corners and use the previously-computed value. DAX is more generic; it needs to work no matter what we slice by, which is why it uses a slower, yet more generic algorithm.

However, at the end of the day, we still feel that something can be improved. If the value is already there, there should be a way to use it with no further calculation. Indeed, a visual calculation does exactly this. When using visual calculations, the engine first computes the source query (that is, a query that retrieves all the values computed by model measures), and then the next step of calculation happens on the virtual table, with no further need to query the data model.

The same algorithm, with a visual calculation, is the following:

YOY % =
VAR CY = [# Customers]
VAR PY =
    PREVIOUS ( [# Customers], COLUMNS )
VAR Result =
    DIVIDE ( CY - PY, PY )
RETURN
    Result

By using PREVIOUS, we are asking DAX to retrieve the value of # Customers in the previous column, with no need to invoke another query on the database.

The version using the visual calculation is significantly faster.

6 seconds rather than the previous 13 seconds. Not only is it faster, but by analyzing the storage engine queries, we observe a much more reasonable behavior. The engine retrieves the base measure by the different aggregation levels – it requires subtotals, which is why there are multiple storage engine queries – and then the remaining part of the calculation is all in the formula engine. Because the virtual table is rather small, the cost to the formula engine is quite low.

If we had used Sales Amount, an additive and lighter measure, rather than a distinct count, the result would be similar, but the timings would be so quick – with the query running in around 100 milliseconds – that we would have to use a much larger database to get a feeling for the behavior. Distinct count is used only to make the scenario clearer and to avoid optimizations specific to additive measures.

So far, we have seen one side of the coin: visual calculations improve performance when there is a small number of basic heavy calculations on top of which we need to perform further elaboration. The good news is that this happens pretty frequently, so giving visual calculation a try is a good step in building reports. However, there is another side of the coin to investigate: is it possible that visual calculations slow down performance? Unfortunately, the answer is yes.

When a visual calculation is added to a visual, the engine adds the VISUAL SHAPE structure to the source table so that it can navigate in the hierarchies defined by the visual (ROWS and COLUMNS). Adding VISUAL SHAPE forces the table to undergo a densification process.

Densification adds to the source table all the combinations of values that may be present, but that were skipped as part of the optimizations of SUMMARIZECOLUMNS. We describe the densification process in more detail in the SQLBI+ whitepaper Understanding Visual Calculations in DAX, and we demonstrate it further in the corresponding SQLBI+ video course.

For the sake of this article, it suffices to think of densification as a step that adds rows to the virtual table to represent all combinations of values across rows and columns. In our example, there are 11 brands and 10 years; therefore, the source table can contain a maximum of 110 rows (not including subtotals, which we ignore to simplify the scenario). All 110 cells contain a value; therefore, SUMMARIZECOLUMNS returns all of them. However, if there were no purchases for one of the brands within one of the years, the corresponding cell in the matrix would be blank, and the original virtual table returned by SUMMARIZECOLUMNS would not even include that row, because it would have a useless BLANK value. It is worth remembering that SUMMARIZECOLUMNS does not return rows where all the measures evaluate to BLANK. During the densification process, DAX adds that row back to accommodate any visual calculation results that may be non-blank.

In other words, SUMMARIZECOLUMNS has a specific optimization to reduce the number of rows returned to avoid processing empty rows. However, in visual calculations, the engine needs to recreate those rows, albeit temporarily, to guarantee a correct evaluation of the visual calculations themselves. If the virtual table is very large, the densification process can take a long time, thereby degrading the report’s performance significantly.

To demonstrate this, we use Sales Amount and the YOY % of the sales amount (if we were to use DISTINCTCOUNT, the timings would just be too large to make the tests viable), and we slice by Store[Name], Product[Name], and Date[Year].

There are 67 stores, 2,517 products, and 10 years. The full source table is now much larger: 1,686,390 rows in total. Because there are so many cells to compute, the report with the measure is much slower than the one we used earlier, and the storage engine queries also take much longer to execute.

The report using the measure is running in 15.6 seconds, with a significant amount of time spent in the formula engine processing SAMEPERIODLASTYEAR across many combinations of values.

In the server timings, it is worth noting line 2, where 1,318,524 rows were processed. That is the original SUMMARIZECOLUMNS. Line 8 computes 16,458,828 rows and aggregates sales by date, so to compute the SAMEPERIODLASTYEAR in the formula engine.

Despite looking like a bad result, it is not. Not only is it not bad, but it is also the result of a combination of optimizations that aim to reduce the number of rows processed by both Power BI and SUMMARIZECOLUMNS.

The effect of these optimizations vanishes when using a visual calculation. When using visual calculations, the DAX engine must materialize the full result of SUMMARIZECOLUMNS and then extend it during the densification step to handle missing values. On large virtual tables, this takes a lot of time.

These are the server timings of the visual calculation version of the same report.

The version with visual calculations is now four times slower than the version based on the measure. There are no more multiple queries to the storage engine (the engine only needs to retrieve the most granular data for the sales amount; everything else is computed from this raw dataset), but the densification step takes a long time, resulting in poor performance.

With different database sizes, the numbers are different. With the small database that you can download with this article, the difference looks even larger: it goes from around 40 milliseconds for the measure-based report to more than 400 milliseconds for the visual calculation. The reason is that the storage engine becomes irrelevant, which somewhat distorts the results.

Conclusions

Visual calculations are a powerful tool. When it comes to making some calculations easier, they are just great. However, do not assume they are always faster than measure-based reports, as they compute values only once. We performed several other tests, and our conclusion is that what matters most is the size of the virtual table. With small virtual tables, visual calculations are great. As soon as the virtual table grows, performance can be at serious risk. Be mindful that measure-based reports are also optimized to compute only what is visible in the matrix. Therefore, if some levels are not expanded, they are not computed. With visual calculations, it does not matter whether the matrix is fully expanded or not; the engine must always produce the full densified virtual table at the leaf level.

As always, when it comes to performance, you should perform extensive testing. Your model, your measures, and your report may all have different characteristics from our demo model. Hence, your mileage may vary widely.

How to apply a different formatting rule at each level of a hierarchy using ISINSCOPE in a measure or ISATLEVEL in a visual calculation.

This article describes how to apply different formatting rules at each level of a hierarchy (one rule at the year level, another at the quarter level, another at the month level) using ISINSCOPE in a measure or ISATLEVEL in a visual calculation.

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.

Two DAX functions address this challenge: ISINSCOPE and ISATLEVEL. They look similar, but they live in different places. ISINSCOPE inspects the group-by columns of the query and is used within a measure that becomes part of the semantic model. ISATLEVEL inspects the visual layout and is used within a visual calculation that lives at the report layer. The choice between the two is about where the per-level logic should live, and not about which one detects the level correctly (both do).

In this article, we describe how to use both approaches to drive per-level formatting rules. We start with a matrix visual that applies a different background color rule at each level of a Year-Quarter-Month hierarchy. We then move to a report with a Synoptic Panel visual, where the principle is the same, but the hierarchy is different.

If you are new to ISINSCOPE and want to understand how it differs from HASONEVALUE, please look at the article, Distinguishing HASONEVALUE from ISINSCOPE.

The scenario: a different rule at each level

We start with a matrix that displays Sales Amount across the Year, Quarter, and Month levels of the Calendar hierarchy. Our goal is to drive the cell background color with three independent rules. At the year level, the shade reflects each year's share of the grand total: darker shades for years that contribute more, lighter shades for years that contribute less. At the quarter level, the cell turns green when the quarter's value is at or above the average quarter, and pink when it is below. At the month level, the cell is highlighted in gold only when that month contributes more than 15% towards that year's total, marking it as an exception.

This treatment is not achievable with a single static rule applied to every cell, nor with a fixed color per level. Each level requires a calculation of its own. The expression must first detect the current level, then run the rule associated with that level. The expression can be either a measure in the semantic model or a visual calculation in the visual itself.

Using ISINSCOPE in a measure

ISINSCOPE returns TRUE when the specified column is currently used as a group-by column in the query. In a matrix, this corresponds to the column being either the row of the current cell or a column above it in the hierarchy. We use ISINSCOPE to dispatch each cell to the rule that applies to its level.

We define a measure that returns a color name, with one branch per level:

Level Color = 
SWITCH (
    TRUE,

    -- Month level: highlight months exceeding 15% of their year
    ISINSCOPE ( 'Date'[Year Month] ) || ISINSCOPE ( 'Date'[Year Month Short] ),
        VAR MonthValue = [Sales Amount]
        VAR YearTotal =
            CALCULATE (
                [Sales Amount],
                REMOVEFILTERS ( 'Date' ),
                VALUES ( 'Date'[Year] )
            )
        VAR Share = DIVIDE ( MonthValue, YearTotal )
        RETURN
            IF ( Share > 0.15, "Gold", BLANK () ),

    -- Quarter level: green if at or above the average quarter, pink if below
    ISINSCOPE ( 'Date'[Year Quarter] ),
        VAR QuarterValue = [Sales Amount]
        VAR AverageQuarter =
            CALCULATE ( 
                AVERAGEX (
                    VALUES ( 'Date'[Year Quarter] ),
                    [Sales Amount]
                ),
                REMOVEFILTERS ( 'Date' )
            )
        RETURN
            IF ( QuarterValue >= AverageQuarter, "LightGreen", "LightPink" ),

    -- Year level: shade by share of grand total
    ISINSCOPE ( 'Date'[Year] ),
        VAR YearValue = [Sales Amount]
        VAR GrandTotal =
            CALCULATE ( [Sales Amount], REMOVEFILTERS ( 'Date' ) )
        VAR Share = DIVIDE ( YearValue, GrandTotal )
        RETURN
            SWITCH (
                TRUE,
                Share > 0.40, "SteelBlue", 
                Share > 0.25, "CornflowerBlue", 
                Share > 0.15, "SkyBlue", 
                "LightBlue" 
            )
)

The order of the conditions inside the outer SWITCH is important. When the current cell in the report is at the month level, ISINSCOPE returns TRUE for Year Month Short, Year Quarter, and Year, because all three columns are in scope. We test from the most specific level to the most general, so the first match identifies the actual current level and runs the rule that belongs to it. The code checks two columns for the month level (Year Month and Year Month Short) because the model has two versions: the full month name and the short 3-letter name, which is the one used in the previous screenshot.

Each branch is self-contained. The month branch uses the REMOVEFILTER / VALUES pattern to obtain the year total (read Using ALLEXCEPT versus ALL and VALUES for more details about the pattern); the quarter branch averages Sales Amount across all quarters in the model; the year branch divides the year value by the grand total. The functions used within each branch are standard DAX and available in any measure of the semantic model.

The logic implemented at the quarter and year levels compares values against the model totals, ignoring slicers that could limit the date range shown in the visual. If the comparison should consider only the time period displayed in the visual, we could use ALLSELECTED instead of REMOVEFILTERS in the expressions at the quarter and year levels.

The measure is now part of the semantic model and is available to any report that uses it. The advantage is reusability across reports; the cost is that only someone with semantic model authoring rights can create it.

Using ISATLEVEL in a visual calculation

ISATLEVEL serves the same dispatching purpose, but it uses a different mechanism. ISATLEVEL indicates whether a column is visible at the current level of the visual. While ISINSCOPE inspects the group-by columns of the query, ISATLEVEL inspects the visual layout (technically, it is the VISUAL SHAPE of the query). ISATLEVEL is designed for visual calculations, which are defined in the report layer of a specific visual and do not require any change to the semantic model.

The structure of the expression is the same SWITCH dispatch on the current level, this time using ISATLEVEL:

Visual Level Color = 
SWITCH (
    TRUE,

    -- Month level: highlight months exceeding 15% of their year
    ISATLEVEL ( [Year-Quarter-Month Month] ),
        VAR MonthValue = [Sales Amount]
        VAR YearTotal = COLLAPSE ( [Sales Amount], [Year-Quarter-Month Quarter] )
        VAR Share = DIVIDE ( MonthValue, YearTotal )
        RETURN
            IF ( Share > 0.15, "Gold", BLANK () ),

    -- Quarter level: green if at or above the average quarter, pink if below
    ISATLEVEL ( [Year-Quarter-Month Quarter] ),
        VAR QuarterValue = [Sales Amount]
        VAR AverageQuarter =
            CALCULATE ( 
                AVERAGEX (
                    ROWS,
                    [Sales Amount]
                )
            )
        RETURN
            IF ( QuarterValue >= AverageQuarter, "LightGreen", "LightPink" ),

    -- Year level: shade by share of grand total
    ISATLEVEL ( [Year-Quarter-Month Year] ),
        VAR YearValue = [Sales Amount]
        VAR GrandTotal =
            COLLAPSEALL ( [Sales Amount], ROWS )
        VAR Share = DIVIDE ( YearValue, GrandTotal )
        RETURN
            SWITCH (
                TRUE,
                Share > 0.40, "SteelBlue", 
                Share > 0.25, "CornflowerBlue", 
                Share > 0.15, "SkyBlue", 
                "LightBlue" 
            )
)

The reference syntax [Year-Quarter-Month Month] corresponds to the column as it appears in the matrix visual. The exact name depends on how the hierarchy is configured in the visual. Keep in mind that ISATLEVEL takes a visual reference, not a model column path.

Inside each branch, the per-level arithmetic is the same as in the measure version, but the building blocks are visual-aware. In a visual calculation, we obtain the year total of a month row by collapsing the visual up to the year level with COLLAPSE; we obtain the grand total by collapsing all the way up with COLLAPSEALL. The arithmetic is identical, but it is expressed in terms of the visual matrix rather than the model.

The result matches the version driven by the ISINSCOPE measure. This is not a coincidence: in a standard hierarchical matrix, the visual shape mirrors the group-by columns of the query, so the dispatch reaches the same branch, and the per-level arithmetic produces the same value. The semantic model, however, is unchanged. The entire logic lives in the report and can be modified by a report developer who does not have authoring rights over the semantic model.

Another difference worth mentioning is that the results are identical because we did not filter out any dates outside the matrix visual. If we did that, for example, by filtering only a few years, the results would differ because the visual calculation only considers the visible periods, whereas the measure we implemented always compares the displayed values against the full model. While for the visual calculation we have no choice, for the measure we could have implemented a calculation that is local to the visual by using ALLSELECTED instead of REMOVEFILTERS in the quarter and year levels, as we mentioned in the previous section.

A Synoptic Panel demo

The matrix is the simplest scenario because the hierarchy is a familiar calendar. To show that the same principle applies in a completely different context, we move to the Synoptic Panel custom visual, which has been created by OKVIZ, a sister company of SQLBI. Synoptic Panel displays measures over an image, associating values with regions drawn on the image. The hierarchy involved is not a calendar hierarchy, but the different levels that group tickets sold in a large venue (we simulated The Sphere in Las Vegas for this example). The individual seats are grouped by sector, and the sectors are grouped by category.

The business logic required is the following:

• Category: gradient according to occupation % for selected events
• Sector: gradient according to occupation % for selected events
• Seat: full color if there is at least one ticket sold for the selected events

The % Occupation measure is assigned to the Custom Color property for the areas. However, the behavior of that measure depends on the level displayed in the visual, which is detected by using ISINSCOPE:

% Occupation = 
VAR AverageTicketEvent = DIVIDE ( [# Tickets], [# Events] )
RETURN SWITCH (
    TRUE,
    ISINSCOPE ( Seats[Seat] ), 
        (AverageTicketEvent > 0) * 1,
    ISINSCOPE ( Seats[Sector] ) || ISINSCOPE ( Seats[Category] ),
        DIVIDE ( AverageTicketEvent, [Tot seats] ),
    BLANK()
) 

The expression follows the same pattern as the one we used in the matrix earlier: a SWITCH dispatch with one branch per level, and a different calculation inside each branch. In this case, we used the same algorithm for two levels, Sector and Category.

The same algorithm can be implemented as a visual calculation; in this case, the # Tickets and # Events measures must be included as hidden measures in the visual to be available as columns in the visual calculation:

Occupation Visual Calc = 
VAR AverageTicketEvent = DIVIDE ( [# Tickets], [# Events] )
RETURN SWITCH (
    TRUE,
    ISATLEVEL ( [Seat] ), 
        (AverageTicketEvent > 0) * 1,
    ISATLEVEL ( [Sector] ) || ISATLEVEL ( [Category] ),
        DIVIDE ( AverageTicketEvent, [Tot seats] ),
    BLANK()
) 

The difference is in where the logic lives and not in the result. The measure-based approach adds an artifact to the semantic model. The visual calculation approach keeps the logic confined to the visual, even though we must include in the visual the measures that provide the information to implement the algorithm (# Tickets, # Events, and Tot seats).

Choosing between the two approaches

The choice between ISINSCOPE in a measure and ISATLEVEL in a visual calculation is about where the per-level logic should live, not a question of correctness.

A measure with ISINSCOPE is the natural choice when we own the semantic model, and we want the level-detection logic to be reusable across multiple reports and visuals. The measure becomes part of the shared model and can be referenced anywhere. Moreover, ISINSCOPE is the mandatory choice if the format logic needs data not represented in the visual, because a visual calculation cannot access other data in the model.

A visual calculation with ISATLEVEL is the natural choice when we cannot or do not want to modify the semantic model. This is a common situation for a report developer who is consuming a shared dataset or who is building a report on top of a Power BI semantic model published by another team. In these cases, the developer does not have the rights to add measures to the model. An alternative could be to create a composite model solely to define additional measures to support formatting; however, this approach also requires the rights to create and publish a new semantic model, which could be another limiting factor. A visual calculation keeps the logic local to the report and does not require additional permissions.

Surprisingly, even when the developer owns the semantic model and has all necessary rights, a visual calculation may still be preferable for purely presentation-related logic. A per-level coloring expression is closer to the visual than to the data. Keeping it in the visual calculation avoids cluttering the semantic model with measures that have no analytical meaning beyond a specific visual.

Finally, from a performance standpoint, a visual calculation is usually preferable because it operates on the smaller set of rows used to populate the visual, whereas a measure could require additional internal queries, thus resulting in slower reports.

Conclusions

Dynamic formatting by hierarchy level becomes useful when each level has its own rule: share-of-total shading at one level, status comparison at another, exception highlight at a third. The pattern is always the same: a SWITCH detects the current level and runs the appropriate rule; however, detection can occur at two different architectural layers of the report.

ISINSCOPE is used in model measures and inspects the group-by columns of the query. ISATLEVEL inspects the visual layout and is used in visual calculations that live at the report layer. Both detect the level correctly in a matrix and in a Synoptic Panel; both can produce the same visible result.

For a model developer building a reusable artifact, ISINSCOPE is the natural option in a measure. For a report developer working on a shared dataset without the ability to modify the model, ISATLEVEL in a visual calculation is the practical choice. The choice between the two is about where the per-level logic should live.

3 years ago, we wrote our “Generative AI guidelines at SQLBI”.

We think it’s time to update them. Over the past 3 years, the world has changed: it is simply no longer possible to avoid using generative AI services for a multitude of purposes. Therefore, we want to update our guidelines to clarify how we use these tools.

First of all, our two simple concepts did not change:

• We look forward to using AI to improve productivity: our productivity and the productivity of our readers.
• Whenever we publish content generated by AI engines, we will always make that clear to our readers.

While these principles are still valid, I want to update the considerations I wrote three years ago.

I want to start with our current adoption in production. We produce video courses about DAX and Data Modeling for the international market. Thanks to AI, we improved the quality of subtitles in the original language (English) and their derived translations, which now benefit from higher quality. There is still a human review at the end of the loop, but we know that the quality of our video courses has definitely improved.

We can use AI for DAX analysis and rarely in DAX coding, and we perform a human review before publishing. While this is not the case for the articles and books we publish, we have seen improvements in many models for simple, recurring tasks that generate DAX code, and we have started using these tools for testing, evaluation, use case preparation, and demos. We see, almost daily, that knowing DAX allows users to write more structured prompts that prevent LLMs from going down the wrong path. For now, knowing DAX is still an advantage even if you do not write it directly.

We use AI in other stages of the development of content and semantic models. We do not create articles with AI. However, we use AI across different parts of the production process, primarily to improve the quality of the final result rather than just to increase our productivity. Providing tools to help AI agents be more accurate and efficient is an area we are exploring.

We will improve the consumption experience on our websites for users and AI agents. We did not integrate AI services into our websites as we intended to three years ago. It seems more productive to look at how to collaborate with AI agents. In a world where AI agents do not pay for training, this access might not remain free. However, we are far from understanding what constitutes a sustainable economic model for advanced content.

We will always be transparent when using AI-generated technical content. Compared to three years ago, I added “technical” to the previous sentence. We use AI to generate the comics in the SQLBI newsletter, and a full disclaimer seems excessive for a comic named “AI BI Blunders”. We want to keep our freedom to have fun, and the risk that someone takes things too seriously is a price we are willing to pay to put a smile on your face (and definitely ours).

Rereading my post, I’ve managed to keep it shorter than the one I wrote three years ago. So, let me add one short pro tip: use AI to improve your productivity, but don’t become a victim of AI. If you stop thinking, you become “disposable”. Your added value is in applying the 5 to 10% of corrections needed on the AI output. In the case of DAX, probably more than 5 to 10%, especially if you also need efficient code.

Thank you for your time reading it!

How to implement a DAX function that removes filter-keep column filters from a calendar, using REMOVEFILTERS as the return value of the function.

In this article, we implement a function that removes filter-keep column filters from a calendar, using REMOVEFILTERS as the return value of the function.

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.

Introducing the scenario

We wrote a measure that computes the running total for the last three months, using basic time intelligence functions and calendar-based time intelligence:

Measure 3 Months = 
VAR RefDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Sales Amount],
        DATESINPERIOD ( 'Gregorian', RefDate, -3, MONTH, ENDALIGNED )
    )

Please note that we used ENDALIGNED in DATESINPERIOD to ensure the calculation aligns the time period with its end. If you are not familiar with the behavior of ENDALIGNED, you should read Understanding DATEADD parameters with calendar-based time intelligence. A thorough understanding of the particular behavior of ENDALIGNED is key in order to fully appreciate the problem to fix, so we strongly recommend checking out that article before or after reading this one.

To verify that the measure computes the correct value, we also authored a visual calculation that computes the same value, with the visual calculation technique:

Visual 3 Months = 
    CALCULATE ( 
        SUM ( [Sales Amount] ), 
        RANGE ( -2, TRUE, ROWS ) 
    )

It is worth noting that we had to use -2 in RANGE rather than -3, because the current row is included in the range.

The two measures produce different results at the quarter and year levels because of the different techniques they use.

However, we are mainly interested in the measure, and we use the visual calculation only for debugging purposes. If you want to better understand how RANGE and visual calculations work, make sure to check out this mini-course in SQLBI+: Understanding visual calculations in DAX. From now on, we will focus only on the month level.

Spotting the glitch in the measure

Right now, all the numbers look correct. However, because there may be many rows to check, a simple visual calculation helps in focusing on the presence of errors:

Test = IF ( [Measure 3 Months] - [Visual 3 Months] <> 0, "Error" )

The calendar table includes, among the many columns, one column indicating the weekday. One of the requirements is that the measure should work if users decide to focus on specific weekdays. In technical terms, we call such columns filter-keep columns, that is, columns whose filter needs to be maintained when the filter on the Date table is changed. You can find more information about filter-keep columns here: Introducing calendar-based time intelligence in DAX. Luckily, the calendar-based time-intelligence functions treat filter-keep columns in a sweet and safe way. Unfortunately, as we will discover, our measure does not. To demonstrate this, we add a slicer for the weekday and the test column to the report, focusing on Wednesday only.

You can see that several months show an error: the visual calculation does not compute the same value as the measure. We will spare you the math: the visual calculation works smoothly, whereas the measure fails to compute the correct result.

In the video, we outline the full debugging process to explain how to find the issue. Here, we go straight to the solution.

When the measure is computing the reference date, it uses this expression:

VAR RefDate = MAX ( 'Date'[Date] )

MAX is being computed in the current filter context, which includes the weekday. Therefore, it finds the last Wednesday in the month. For some months, this will be the end of the month. For some others, it will be very close to the end of the month, while for several other months it will be a few days before the end of the month. Because of this, it will happen pretty frequently that the value of RefDate is not close enough to the end of the month to trigger the behavior of ENDALIGNED. As a consequence, the dates returned by DATESINPERIOD will include periods from subsequent months, thus producing an incorrect result.

Therefore, we need to ensure that the reference date ignores the weekday filter.

Fixing the bug

To fix the problem, we could add REMOVEFILTERS on the Date[Weekday] column (as well as weekday number) because the sort-by-column feature is being used. While focusing on the columns we want to remove the filter from, we may also notice that the table includes not only the weekday, but also its short version (Mon, Tue, and so on). We need to remove the filters from these columns as well to provide more flexibility for our users.

In general, we need to remove filters from any column that is not already included in the calendar (either as a level or as a time-related column) and that we want to consider as a filter-keep column. The list is known today, but it may grow later, when the semantic model is further developed.

Therefore, we decided to consolidate the list of columns into a function that removes the filter from any filter-keep column in the specific calendar.

The thing is: we need to remove filters, not return a table. When we think about a function, we think about a DAX expression that has a return value. In our case, the function needs to perform an action (removing the filter) rather than returning a table. However, because functions will be expanded inside the code, we can make a function “return” REMOVEFILTERS, thereby leveraging the fact that its code will be replaced when the function is being invoked:

Gregorian.RemoveFilterKeepColumns = () => 
REMOVEFILTERS ( 
    'Date'[Day of Week], 
    'Date'[Day of Week Number], 
    'Date'[Day of Week Short] 
)

The function can work only when it is being used as a CALCULATE argument, as we do in the measure:

Measure 3 Months = 
VAR RefDate = 
    CALCULATE ( 
        MAX ( 'Date'[Date] ),
        Gregorian.RemoveFilterKeepColumns()
    )
RETURN
    CALCULATE (
        [Sales Amount],
        DATESINPERIOD ( 'Gregorian', RefDate, -3, MONTH, ENDALIGNED )
    )

Because the function body will be expanded in the code, at execution time, this is the actual measure being executed:

Measure 3 Months = 
VAR RefDate = 
    CALCULATE ( 
        MAX ( 'Date'[Date] ),
        REMOVEFILTERS ( 
            'Date'[Day of Week], 
            'Date'[Day of Week Number], 
            'Date'[Day of Week Short] 
        )
    )
RETURN
    CALCULATE (
        [Sales Amount],
        DATESINPERIOD ( 'Gregorian', RefDate, -3, MONTH, ENDALIGNED )
    )

The function will not work if called differently, because its result is not a table; it is a CALCULATE modifier. However, as long as developers call the function from inside CALCULATE, it works just fine.

Now that the measure has been verified and debugged, we can remove the visual calculation and proceed with further development of the model.

An alternative, a very valid alternative, is to use an EXPR parameter and embed the CALCULATE call inside the function:

Gregorian.ComputeRemovingFilterKeepColumns = ( formulaExpr : EXPR ) => 
CALCULATE ( 
    formulaExpr,
    REMOVEFILTERS ( 
        'Date'[Day of Week], 
        'Date'[Day of Week Number], 
        'Date'[Day of Week Short] 
    )
)

Conclusions

Functions use macro-expansion in DAX. This opens up the possibility of using code in functions that would not work as standalone code, but will work when executed in the proper environment. Specifically, in this article, we outlined how you can make a function “return” REMOVEFILTERS if the function is being used in CALCULATE only.

As a bonus takeaway from the article, we outlined a specific behavior of filter-keep columns in calendar-based time intelligence by debugging a measure that is incorrect when filters are applied through slicers.

How to define optional parameters in DAX user-defined functions and set default values for parameters not specified by the caller.

This article describes how to define optional parameters in DAX user-defined functions and set default values for parameters not specified by the caller.

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.

If you are new to DAX user-defined functions and you want to learn more, please take a look at our other articles about DAX user-defined functions before reading any further. Here, we assume you already know how to define a function and call it from a measure or a calculated column. We deliberately use simple functions in the first part of the article, so that the syntax remains the focus; a more practical scenario follows later.

The syntax of optional parameters

When we declare a parameter, we can add a default expression after the parameter name, followed by its optional type hints, using an equal sign. The general form of a function is the following:

<FunctionName> =
    ( <ParameterName> [ : <Type> <Subtype> <PassingMode> ] [ = <DefaultExpression> ], ... )
    => <FunctionBody>

The only addition compared to a function with mandatory parameters is the default expression, = <DefaultExpression>. A parameter with a default expression is optional; one without is mandatory.

Consider the following function, which increments a number. The first parameter, x, is mandatory. The second parameter, y, is the increment amount and is optional. If it is not specified, the function adds 1:

DEFINE
    FUNCTION Increment = ( x : NUMERIC, y : NUMERIC = 1 ) => x + y
EVALUATE
{
    Increment ( 3 ),        -- Returns 4, the default for y is 1
    Increment ( 10, 20 )    -- Returns 30, y is specified to be 20
}

The first call provides only x. Because y is omitted, DAX evaluates its default expression, 1, and uses the result as the value of y; the function returns 3 + 1. The second call provides both arguments, so the function returns 10 + 20.

When the caller omits an argument, DAX evaluates the corresponding default expression and uses its result as the value of that parameter. The default expression respects the type hints of the parameter and can call other functions, both built-in functions and user-defined functions, but it cannot reference other parameters of the same function.

Omitting the trailing parameters

A function can have more than one optional parameter. The next function extends Increment with a third parameter, limit, which caps the result. Both y and limit are optional. By default, the function increments by 1 and caps the result at 10:

DEFINE
    FUNCTION IncrementLimit =
        (
            x : NUMERIC,
            y : NUMERIC = 1,
            limit : NUMERIC = 10
        ) =>
            MIN ( x + y, limit )

EVALUATE
{
    IncrementLimit ( 1 ),        -- Returns 2, the default for y is 1 and for limit it is 10
    IncrementLimit ( 5, 4 ),     -- Returns 9, y is 4 and the default for limit is 10
    IncrementLimit ( 5, 25, 20 ) -- Returns 20, y is 25 and limit is 20
}

The first call provides only x: y defaults to 1 and limit defaults to 10, so the result is MIN ( 1 + 1, 10 ), which is 2. The second call provides x and y but omits limit, so the result is MIN ( 5 + 4, 10 ), which is 9. The third call provides all three arguments, so the result is MIN ( 5 + 25, 20 ), which is 20.

When the parameters you skip are the last ones, you do not need to write anything in their place. You stop the list of arguments earlier, and DAX uses the default expression for every parameter you did not provide. In other words, you only need the separating commas up to the last argument you actually provide; everything after that can be left out.

Skipping a parameter in the function call

The previous calls always omit parameters from the end of the list. You can also skip a parameter in the middle, while still passing an argument in a later position. To do this, you leave the position empty. You write the comma, but no value before it:

DEFINE
    FUNCTION IncrementLimit =
        (
            x : NUMERIC,
            y : NUMERIC = 1,
            limit : NUMERIC = 10
        ) =>
            MIN ( x + y, limit )

EVALUATE
{
    IncrementLimit ( 5, 25, 20 ), -- Returns 20, y is 25 and limit is 20
    IncrementLimit ( 5, , 20 )    -- Returns 6, the default for y is 1 and limit is 20
}

In the second call, the empty position before the comma tells DAX to use the default expression of y. The value 20 is assigned to limit. The result is therefore MIN ( 5 + 1, 20 ), which is 6.

This syntax is useful when a function has several optional parameters, and you want to set only one of the later ones. The empty position is not pleasant to read, but it is valid; here, leaving the gap is a choice of the caller, not something the function definition imposes.

Optional parameters should come last

DAX does not require optional parameters to be the last ones in the signature. You can declare a mandatory parameter after an optional one, as in the following function, where limit is mandatory but follows the optional y:

DEFINE
    FUNCTION IncrementBadPractice =
        (
            x : NUMERIC,
            y : NUMERIC = 1,
            limit : NUMERIC
        ) =>
            MIN ( x + y, limit )

EVALUATE
{
    IncrementBadPractice ( 5, , 20 ) -- Returns 6, the default for y is 1 and limit is 20
}

The result is the same as before, 6. The problem is the IncrementBadPractice function call, not the result. Because limit is mandatory, the caller must always provide it. However, limit comes after the optional y, so the only way to provide limit while keeping the default of y is to write the empty position: IncrementBadPractice ( 5, , 20 ). Here the gap is not a choice; the function forces the caller to write it.

For this reason, we suggest the best practice: When you make a parameter optional, make all the following parameters optional as well. The IncrementLimit function follows this rule: once y is optional, limit is optional too. The IncrementBadPractice function breaks it, and the cost is awkward code at every call site.

Detecting missing parameters

So far, every default has been a fixed value. Sometimes there is no natural fixed default, and you want the function to behave differently depending on whether the caller provided the argument at all. You can obtain this behavior by using BLANK as the default expression and then testing the parameter with ISBLANK inside the function body.

For example, the following function divides two numbers. The third parameter, roundingDigits, controls the number of decimal places. When the caller omits it, the function returns the full-precision result. When the caller provides it, the function rounds the result to the requested number of digits:

DEFINE
    FUNCTION RoundDivision =
        (
            x : NUMERIC,
            y : NUMERIC,
            roundingDigits = BLANK ()
        ) =>
            VAR Result = DIVIDE ( x, y )
            RETURN
                IF (
                    ISBLANK ( roundingDigits ),
                    Result,
                    ROUND ( Result, roundingDigits )
                )

EVALUATE
{
    FORMAT ( RoundDivision ( 2, 3 ), "0.#########" ),    -- Returns 0.666666667, no rounding
    FORMAT ( RoundDivision ( 2, 3, 0 ), "0.#########" ), -- Returns 1, round to 0 digits
    FORMAT ( RoundDivision ( 2, 3, 1 ), "0.#########" ), -- Returns 0.7, round to 1 digit
    FORMAT ( RoundDivision ( 2, 3, 2 ), "0.#########" ), -- Returns 0.67, round to 2 digits
    FORMAT ( RoundDivision ( 2, 3, 3 ), "0.#########" )  -- Returns 0.667, round to 3 digits
}

The first call omits roundingDigits. Its default expression, BLANK, becomes the value of the parameter, so ISBLANK returns TRUE and the function returns the unrounded result. The other calls provide the number of digits, so ISBLANK returns FALSE and the function rounds the result accordingly: zero digits give 1, one digit gives 0.7, two digits give 0.67, and three digits give 0.667.

This pattern is useful when the choice is between doing something and doing nothing. By using BLANK as the default value, we represent the absence of a value, which the function then interprets.

Be mindful of one limitation: the function cannot distinguish an omitted argument from an argument that is explicitly blank. If the caller writes RoundDivision ( 2, 3, BLANK () ), ISBLANK returns TRUE exactly as it does for an omitted argument. This is rarely a problem in practice, but it is worth keeping in mind when blank is a legitimate value for the parameter.

Conclusions

Optional parameters make a user-defined function easier to call in the common case, while still allowing full control when needed. You define an optional parameter by giving it a default expression in the signature. You skip it at call time by omitting the trailing arguments, or by leaving an empty position when you want to keep the default of one parameter while setting a later one.

The default expression is evaluated only when the caller omits the argument; it respects the type hints of the parameter, and it can call other functions. The default values are visible next to the parameters they belong to, which keeps the signature self-documenting.

Be mindful of the order of the parameters. DAX lets you place a mandatory parameter after an optional one, but this should be avoided: every parameter after the first optional one should be optional as well. Otherwise, the caller is forced to write empty positions just to reach a mandatory argument. Follow the rule, and your functions will be both correct and pleasant to call.

Learn the differences between similar but different DAX functions that remove filters from the filter context.

DAX offers many functions to remove filters from the filter context. In this article, we analyze the differences among the most commonly-used functions.

Computing values in DAX is all about understanding how to manipulate the filter context to obtain the desired output. DAX offers a wide variety of functions to manipulate the filter context, including a rich set designed to remove filters. Among the many, four are used the most: ALL, ALLSELECTED, ALLEXCEPT, and REMOVEFILTERS. Choosing the right one can be tough.

In this article, we do not want to dive into too many details; the goal is to let our readers understand when to use which function. Whenever needed, we provide links to deepen your knowledge about specific topics. Make sure to read the additional content if you want to know more about some specific behaviors.

Table functions or CALCULATE modifiers?

You probably have noticed that we introduced the goal of the functions as functions to remove filters. However, ALL, ALLSELECTED, and ALLEXCEPT are generally described as table functions; they return a table rather than remove a filter. Unfortunately, this adds confusion to the narrative. These functions are, at the same time, filter removal functions and table functions. Deciding when to use them, and in which of their dual form, is not a simple task.

If you want to deepen your knowledge about the difference between ALL-prefixed functions being used as CALCULATE modifiers or as table functions, you should read the following article: Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT.

As concerns this article, the important fact is that any function whose name starts with ALL can be used either as a CALCULATE modifier or as a table function. When used as CALCULATE modifiers, these functions do not return a table; instead, they simply remove filters from the filter context. Over time, this dual behavior of ALL* functions created some confusion. This is why, in August 2019, Microsoft introduced the new REMOVEFILTERS function. REMOVEFILTERS is just an alias for ALL, and it can only be used as a CALCULATE modifier. There is no difference between REMOVEFILTERS and ALL when used in CALCULATE. Therefore, these two CALCULATE expressions produce the very same result:

CALCULATE ( [Sales Amount], ALL ( Product[Category] ) )

CALCULATE ( [Sales Amount], REMOVEFILTERS ( Product[Category] ) )

REMOVEFILTERS is the only alias that exists for the set of ALL* functions. As you may easily imagine, an alias for ALLSELECTED would be REMOVEFILTERSSELECTED, not a cute alias at all! It would only add confusion to an already complex topic.

The good news is that this simple consideration reduces the number of differences to learn, from four to three: there is no need to distinguish between REMOVEFILTERS and ALL. We use ALL when we need the function to return a table; we can choose between REMOVEFILTERS and ALL when we want a CALCULATE modifier. In our opinion, REMOVEFILTERS provides a better idea of the modifier goal.

To remove or to ignore filters, that is the question

ALL, ALLEXCEPT, and ALLSELECTED perform a slightly different operation depending on how we use them. When used as table functions, they ignore certain filters in the filter context and return a table computed without them. When used as CALCULATE modifiers, these functions instruct CALCULATE to modify the filter context by removing some filters. The difference is subtle.

In the following code snippet, we use ALL to ignore the filter context and return all the products, despite the filter context filtering only red products:

CALCULATE (
    COUNTROWS ( 
        ALL ( Product )
    ),
    Product[Color] = "Red"
)

ALL is used as a table function; it returns all products, regardless of the filter. An alternative, more verbose and less understandable way of expressing the same code is the following:

CALCULATE (
    CALCULATE ( 
        COUNTROWS ( Product ),
        ALL ( Product )
    ),
    Product[Color] = "Red"
)

In this example, ALL (we could have used REMOVEFILTERS) instructs CALCULATE to remove any filter from the Product table. Therefore, when Product is evaluated in COUNTROWS, the filter context is different: any filter on Product is removed.

The difference is not very relevant in most scenarios. However, when learning DAX, it is important to understand the subtle difference between removing and ignoring, as it greatly helps clarify the filter context in specific areas of your formula.

Choosing when to use what: the short answer

Before diving into more detailed descriptions, here is how you choose when to use what:

• Use REMOVEFILTERS when the intent is simply to clear filters in CALCULATE
• Use ALL when a table expression is needed, or when using legacy patterns that rely on the dual nature of ALL.
• Use ALLSELECTED when computing visual totals that keep outer selections.
• Use ALLEXCEPT when the goal is to preserve one grouping grain and remove the rest; however, you should prefer the REMOVEFILTERS/VALUES combination, instead of ALLEXCEPT.

You can use this short set of rules as a reference. In the remainder of the article, we provide a short explanation of these rules, with pointers to additional content for a more complete description.

When to use ALL and REMOVEFILTERS

ALL is useful whenever one needs to ignore filters present in the filter context. For example, the following measure computes the sales amount of all the brands, regardless of any filter existing on the Product[Brand] column:

All Brands Sales = 
CALCULATE ( 
    [Sales Amount], 
    REMOVEFILTERS ( 'Product'[Brand] )     -- You can use ALL, with no differences
)

When used in a matrix that slices by Product[Brand], this measure always produces the total.

If the matrix is not sliced by Product[Brand], then REMOVEFILTERS has no effect, because there is no filter on Product[Brand] to remove.

REMOVEFILTERS can be used with a column, as in the example, or with a table as an argument. When used with a table, it removes (or ignores!) filters on any table column. Indeed, the All Products Sales measure that uses REMOVEFILTERS on Product, produces the grand total even when slicing by Product[Category]:

All Products Sales = 
CALCULATE ( 
    [Sales Amount], 
    REMOVEFILTERS ( 'Product' )     -- You can use ALL, with no differences
)

Here is the result, with the two measures side-by-side.

A third, less frequently-used version of REMOVEFILTERS takes no argument. One can use REMOVEFILTERS() or ALL () to remove any filter from any table in the entire model.

In short, we use REMOVEFILTERS (or ALL) when we want to explicitly remove (or ignore) filters from columns in the model. The goal is almost always to obtain the grand total of a matrix (or any visual).

One scenario where we must use ALL rather than REMOVEFILTERS is when we need a table and not a filter modifier in CALCULATE: in that case, REMOVEFILTERS is not allowed. For example, the following code does not work, because REMOVEFILTERS is not a table function:

Sum All Products = SUMX ( REMOVEFILTERS ( Product ), [Sales Amount] )

Indeed, SUMX requires a table to iterate, and REMOVEFILTERS is not a table function. The code runs correctly when we use ALL:

Sum All Products = SUMX ( ALL ( Product ), [Sales Amount] )

The other functions described in this article do not have this distinction, so we can use them as both modifiers and table functions.

When to use ALLSELECTED

Sometimes REMOVEFILTERS (or ALL) is overkill. REMOVEFILTERS ignores all filters in the filter context, including not only the filters from the current visual but also those from other visuals. Look what happens with the matrix if we add a slicer that filters certain brands.

The slicer is filtering five brands. However, the measure still reports the grand total of 4,373,105.53 because REMOVEFILTERS is removing any filters on the Product[Brand] column, regardless of which visual created the filter. In the example, there are two filters. One is created by the slicer and one is created by the current visual; both filters operate on the Product[Brand] column. REMOVEFILTERS is ignoring both.

The requirement to remove the filter from the current visual while keeping filters on other visuals is very common, and it is often referred to as “visual totals”. You can see that the total shown in the matrix right now has no visual explanation. We know it is the grand total of all products, but a user looking at the report may be confused. On the other hand, a visual total produces a total that is strongly connected to the numbers already present in the visual.

The DAX function used to achieve visual totals is ALLSELECTED. ALLSELECTED ignores filters from the current visual, but it maintains filters from other visuals:

Allselected Brands Sales = 
CALCULATE ( 
    [Sales Amount], 
    ALLSELECTED ( 'Product'[Brand] )
)

Looking at the result, you can appreciate that the total considers the filter on the five brands selected with the slicer, but it ignores the filter on the brand on the current row of the matrix.

ALLSELECTED is a very commonly-used function in DAX. That said, it is also very dangerous if you do not follow best practices. You can read about the best practices of ALLSELECTED in the article, ALLSELECTED best practices. For the bravest among our readers, a complete explanation of how ALLSELECTED works with shadow filter contexts can be found here: The definitive guide to ALLSELECTED. This latter article is not for the faint of heart; we strongly recommend following the best practices, as this makes it unnecessary to read the most complex topics, while still living a happy life as a DAX developer.

When to use ALLEXCEPT

ALLEXCEPT is a variation of REMOVEFILTERS (or ALL). It produces the same effect as REMOVEFILTERS, except for some columns. It is a very tempting function, because it is short and sweet, but it hides some level of complexity. ALLEXCEPT removes (or it ignores) filters from any column of a table, except the ones specifically provided as further arguments:

AllExcept Brand Sales = 
CALCULATE (
    [Sales Amount],
    ALLEXCEPT ( 'Product', 'Product'[Brand] )
)

In this example, the measure ignores all filters on the Product table, except those on the Product[Brand] column. It is very useful when we need to compute subtotals in a matrix, like in the following report.

As you can see, AllExcept Brand Sales produces the subtotal at the Product[Brand] level. It produces the subtotal by removing all filters from the Product table, except for the Product[Brand] column. By using ALLEXCEPT, one can use any column from Product as the second level of the matrix, while still producing the subtotal as the measure result. It is mostly used when there is a need to compute the ratio of the current selection against the brand total.

Despite ALLEXCEPT being tempting, we suggest that our readers use the REMOVEFILTERS/VALUES combination to obtain the same result:

AllValues Brand Sales = 
CALCULATE (
    [Sales Amount],
    REMOVEFILTERS ( 'Product' ),
    VALUES ( 'Product'[Brand] )
)

This last measure produces the same result as the one using ALLEXCEPT, but it works smoothly even when no filter is explicitly applied to the Product[Brand] column. If you want to understand more about why this is relevant, you can read the following article: Using ALLEXCEPT versus ALL and VALUES.

Conclusions

Choosing which function to use to remove filters from the filter context is a simple topic for DAX experts. However, if you are unsure about which function to use, then you are in very good hands. Many DAX developers are still unsure about when to use what.

Let us conclude with the set of rules again:

• Use REMOVEFILTERS when the intent is simply to clear filters in CALCULATE.
• Use ALL when a table expression is needed, or when using legacy patterns that rely on its dual nature.
• Use ALLSELECTED when computing visual totals that keep outer selections.
• Use ALLEXCEPT when the goal is to preserve one grouping grain and remove the rest; however, you should prefer the REMOVEFILTERS/VALUES combination over ALLEXCEPT.

After you have read the article, and properly digested the rationale behind each rule, this set should guide you in choosing the right function for your task.

User-aware calculated columns are not materialized: we can use them as virtual calculated columns for localization and for custom security scenarios. This feature is available through the new Expression Context property of calculated columns in Power BI.