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.

This article describes the new Expression Context property of calculated columns in Power BI, explaining how user-aware calculated columns work, why they are not materialized, and how to use them as virtual calculated columns for localization and custom security scenarios.

A calculated column is computed when the table is refreshed and stored in the model (in Import mode), just like any other column, so its value does not depend on the user who is connected. The introduction of user-aware calculated columns in Power BI changes this picture because we can define a calculated column that is evaluated at query time and depends on the user running the query. This behavior can be obtained by setting the Expression Context property of a calculated column to User Context.

NOTE: You may find the term “user-context-aware” in articles and documentation from other sources. At SQLBI, we felt that “user-aware” is simpler and less ambiguous as to the scope of this feature. The focus is really on user awareness.

This feature might seem to be a small addition intended to support localization scenarios. However, the implications go beyond localization: any calculated column with a simple expression can become a virtual calculated column: a column that exists in the model but is not stored in memory. Indeed, a consequence of user-aware calculated columns is that they do not materialize the columns, even in Import mode. The ability to manage unmaterialized calculated columns is a feature required to support calculated columns in Direct Lake over OneLake; this topic is not discussed in this article.

We start by introducing the Expression Context property, which enables user-aware calculated columns. We then present three main use cases for user-aware calculated columns: localization based on user culture, row-level calculations stored as virtual columns, and securing sensitive columns without relying on object-level security (OLS). In the second part of the article, we provide more information about the internals of this feature if you are interested in knowing more about the implications of materialization, the DAX functions that make a column user-aware, and the limitations of user-aware calculated columns.

The Expression Context property

When we create a calculated column in Power BI, we can now choose the Expression Context for the column. The property has two values: Standard and User Context. Standard is the default and represents the historical behavior: the column is computed at process time, the result is stored in the model, and the expression cannot use user-aware DAX functions like USERCULTURE.

User Context is the new option. A column with Expression Context set to User Context is called a user-aware calculated column. The expression is evaluated at query time, in an empty filter context, with access to the model restricted by active security roles. Within this evaluation, the engine recognizes the user-aware DAX functions and returns values that depend on the current user.

The semantics of a user-aware calculated column are otherwise identical to those of a standard calculated column. The expression is evaluated for each row of the table, with a row context active on the table itself. Relationships behave as expected, RELATED and RELATEDTABLE work, and calculations on the row are performed as usual. The result of the expression does not depend on the report or on any visual: the value of a user-aware column is the same in every visual that displays it, given that the user is the same.

In other words, user-aware calculated columns have the same row-by-row semantics as a standard calculated column, with three differences:

1. The calculated column is executed within the security context of the current user.
2. The result of the calculated column can depend on the user identity if its DAX expression includes user-aware functions.
3. The column is not materialized.

When a User Context column does not use any user-aware function and does not access rows from other tables, it returns the same value for every user. The only difference from a Standard calculated column is that it is not materialized. We call these columns virtual calculated columns: columns that exist in the model and are available to filters, slicers, and visuals, but are not stored in memory.

Use cases for user-aware calculated columns

We identified three main use cases for user-aware calculated columns; we expect more patterns to emerge in the future.

Localization with user-aware calculated columns

Localization is the main use case that motivated the design of user-aware calculated columns. The scenario is straightforward: we want columns whose values depend on the language of the user running the report. For example, consider a Date table that localizes month and day-of-week names. A natural choice is the same formula we would use as a part of a Date calculated table, just with the addition of the USERCULTURE parameter:

Month = 
FORMAT ( 
    'Date'[Date], 
    "mmmm", 
    USERCULTURE() 
)

However, when a calculated column must be computed at query time, we want to reduce its dependence on cardinality to control the overall execution cost. For example, if a column represents a month, it is better to depend only on a column that has the same cardinality (Month Number) instead of depending on a column with more unique values that would return the same result (Date):

Month = 
FORMAT ( 
    DATE ( 2020, 'Date'[Month Number], 1 ), 
    "mmmm", 
    USERCULTURE() 
)

Month Short = 
FORMAT ( 
    DATE ( 2020, 'Date'[Month Number], 1 ), 
    "mmm", 
    USERCULTURE() 
)

For these expressions to work, we set Expression Context to User Context in both calculated columns, Month and Month Short. With the columns configured as user-aware, USERCULTURE returns the culture of the current user, and the FORMAT function returns the month name in the appropriate language. A German user sees Januar, an Italian user sees Gennaio, and a French user sees Janvier.

Similarly, we create two columns to display the day of the week that depend on Date[Day of Week Number]:

Day of Week = 
FORMAT ( 
    DATE ( 2020, 1, 4 + 'Date'[Day of Week Number] ), 
    "dddd", 
    USERCULTURE() 
)

Day of Week Short = 
FORMAT ( 
    DATE ( 2020, 1, 4 + 'Date'[Day of Week Number] ), 
    "ddd", 
    USERCULTURE() 
)

These columns change the names displayed in a report depending on the user. In the following example, the same report is shown side by side, for two users with different languages; column and measure names are displayed without translation (e.g., Year, Day of Week, and Sales Amount).

In this article, we discuss a new feature for translating the content of the model, not its metadata – such as column and measure names. Those are covered in the existing documentation. If you are new to localization in Power BI semantic models and you want to learn more about metadata and report translations, please take a look at Plan Translation for Multiple-Language Reports in Power BI in the Microsoft documentation.

However, the previous report shows another important design challenge for the semantic model: if we want to make sure that the selection applied to a report shown in a certain language will be preserved when the same report is shown in another language, we cannot apply a filter or a selection directly on a user-aware column. To prevent Power BI from doing that, we should use the Group By Columns property, instructing our Month and Day of Week columns to use Month Number and Day of Week Number, respectively, not only for the sort order but also to identify the unique values of the columns. This way, the slicer will store the filter as a selection of numeric values from Date[Day of Week Number] rather than a list of translated strings that would not exist in other languages. We can edit the Group By Columns property in TMDL view or in Tabular Editor, and we provide more information about this property in the Understanding Group By Columns in Power BI article on SQLBI.

For example, the following screenshot shows the TMDL view definition of Day of Week, but we can generalize the rule for any user-aware column we want to use for translations:

• Use the USERCULTURE function in the DAX expression of the calculated column,
• Set Expression Context to User Context,
• Apply the proper Sort by Column property if required,
• Assign the proper Group by Columns setting to identify the column(s) to use to identify the selection without relying on a translated, user-aware column.

Virtual calculated columns for row-level calculations

As we mentioned earlier, virtual calculated columns enable redundant columns without storage and processing costs being incurred.

For example, instead of importing Sales[Line Amount] we often compute it by using Sales[Quantity] * Sales[Net Price] to keep the model consistent and efficient, as in this measure:

Sales Amount = 
SUMX ( 
    Sales, 
    Sales[Quantity] * Sales[Net Price] 
)

This works well for aggregations, but the measure becomes the only way to access the calculation, and there is no Sales[Line Amount] field to drag into a slicer or use in the filter pane. User-defined functions solve the centralization problem, but they must still be invoked from other DAX expressions, and a user cannot apply a filter on Sales[Line Amount] through them.

User-aware calculated columns offer a new option. The classic Line Amount expression in a Sales table can be written as a virtual calculated column:

Line Amount = Sales[Quantity] * Sales[Net Price]

In a Standard calculated column, this expression produces a high-cardinality column. The values are computed at process time, stored in memory, and compressed by VertiPaq with limited efficiency precisely because of the high cardinality. However, the calculation itself is trivial and could be performed at query time at a negligible cost.

When Expression Context is set to User Context, the same column becomes virtual. The expression is evaluated at query time when a measure or visual references the column. There is no memory cost, no processing cost, and the logic remains centralized in the model where it belongs. We can still write filters and measures that reference Sales[Line Amount] without the cost of a redundant high-cardinality column.

The potential higher cost at query time is limited when we have simple expressions like this one. The reason is that the formula engine can push the calculation to the storage engine when the expression involves only basic operators on columns of the same table. In this case, the storage engine performs the multiplication during the column scan, with no need for the formula engine to iterate row by row. For tables with fewer than 100 million rows, the cost difference compared to reading a materialized column is typically not relevant; we will revisit this with concrete benchmarks once the feature reaches general availability.

The story is different when the expression cannot be pushed to the storage engine, as is the case with complex DAX measures with iterators. Whenever the expression triggers a callback to the formula engine for each row, the cost can grow significantly. As a rule, simple arithmetic on columns of the same table is pushed down efficiently, whereas expressions involving table functions, complex IF branches, or user-aware functions usually require formula engine intervention.

In short, virtual calculated columns work best for row-level expressions that the storage engine can compute. This way, we obtain the centralization advantages of a column in the model (the logic lives in one place, and the column is available to filters, slicers, and visuals) without paying the cost of redundant high-cardinality columns. We will revisit the full guidance in the Conclusions.

Important: Virtual calculated columns can have a significant impact on how we design optimized semantic models. Be mindful, however, that this feature is in preview. As such, we will publish more guidelines once it is consolidated.

Securing sensitive columns with user-aware calculated columns

The presence of sensitive columns that must be hidden from certain users is typically addressed by object-level security (OLS), which removes the column from the model entirely for those users. The problem with OLS is that any Power BI report referencing the hidden column becomes invalid for restricted users: the visual fails with an error, because the column does not exist for them. Therefore, report designers have to maintain separate report pages, or even separate reports, for each role, which quickly becomes impractical.

The user-aware calculated columns offer a workaround for this limitation by using row-level security (RLS). The goal is to hide sensitive information from some users while keeping the rest of the table fully accessible. The technique shown here trades column-level invisibility for content-level invisibility: the column is still present in the model and in the field list, the report continues to render correctly, but the values are blank for users without permission. The same report works for both admin and restricted users without any role-specific layout. The trade-off is that restricted users can see that restricted columns exist, but they cannot see their values.

The canonical example is a Salary column in an Employee table, but the Contoso sample we use does not include one, so we demonstrate the same pattern with an Income Bracket column in the Customer table. The technique applies equally to any other case in which a single column contains information that only certain users should be privy to.

We focus on three tables in the model: Sales, Customer, and CustomerIncome. CustomerIncome is hidden from the report and stores Bracket Number and Income Bracket for every customer.

RLS on CustomerIncome is defined for the "No Income Bracket" role, with a filter that returns FALSE for every row in the table. That makes the entire CustomerIncome table invisible to members of that role; an admin role with no filter sees all rows.

As the diagram view shows, there is no relationship between Customer and CustomerIncome. Quite surprisingly, if we created a relationship from CustomerIncome to Customer, the RLS filter that returns FALSE on CustomerIncome would propagate through the relationship to Customer and then to Sales. Restricted users would then see no customers and no sales at all: the report would become empty rather than partially redacted. The design relies on the filter remaining confined to the LOOKUPVALUE expression inside the calculated column. Keeping the two tables disconnected is what makes that possible, and for the same reason, we must avoid any relationship that could let the CustomerIncome filter reach the rest of the model.

We then copy the sensitive columns into Customer using two calculated columns, where we set the Expression Context to User Context:

Income Bracket = 
LOOKUPVALUE ( 
    CustomerIncome[Income Bracket], 
    CustomerIncome[CustomerKey], Customer[CustomerKey] 
)

Income Bracket Number = 
LOOKUPVALUE ( 
    CustomerIncome[Bracket Number], 
    CustomerIncome[CustomerKey], Customer[CustomerKey] 
)

The key behavior is that LOOKUPVALUE returns BLANK whenever the matching row in CustomerIncome is filtered out by the active security role. Users in "No Income Bracket" therefore see a blank value in Customer[Income Bracket] for every customer, while admins see the real bracket. This is the matrix with Sales Amount by Income Bracket and Continent visible to admin users, who see every bracket and every region populated correctly.

Users in the "No Income Bracket" role do not see any names in the Income Bracket column. Note below the Now viewing as: No Income Bracket banner at the top: the column still exists, the report still runs, but Income Bracket collapses to a single blank row. All the other Customer columns (Address, Age, City, Country, and so on) remain fully accessible because no filter is propagated onto Customer.

It is important to note that the cost of LOOKUPVALUE is paid for each row of the Customer table where the column is evaluated, at every query. For a small Customer table, this is negligible; for a large one, it can become noticeable. Be mindful of this when applying the pattern to high-cardinality tables.

Materialization of calculated columns

A Standard calculated column in Import mode is materialized: the engine evaluates the expression for each row during model processing and stores the result in the column, exactly like any other imported column. From the storage engine point of view, there is no difference between a materialized calculated column and an imported column. Both are queried at the storage engine level with the same speed and behavior – except for a potentially lower compression rate for the calculated column.

A user-aware calculated column is not materialized. The column does not occupy memory and does not exist in the storage engine. When a query references the column, the storage engine evaluates the expression at query time.

It is important to note that materialization is not a property that we can control directly. Materialization is the consequence of the combination of two factors: the Expression Context property and the storage mode of the table. In Import mode, a Standard column is materialized, and a User Context column is not. In DirectQuery mode, calculated columns have always been unmaterialized: the engine translates the expression into a SQL query and computes the values at query time. With DirectQuery, the User Context property does not change the materialization, since the column was already unmaterialized.

The following table shows the combinations of table storage mode and supported Expression Context settings.

Reading the table is straightforward once we accept that materialization is derived rather than chosen: we pick Standard or User Context, we pick the storage mode of the table, and the engine determines whether the column is materialized in memory. Direct Lake on OneLake is the storage mode where the calculated column is always unmaterialized, regardless of the Expression Context property. Import is the only storage mode in which a calculated column is materialized; the User Context option also makes the column unmaterialized in Import mode.

User-aware expressions and calculated columns

A user-aware DAX expression is one that depends on the user running the query, affecting which DAX functions can be used and the security perimeter for accessing data.

The set of user-aware DAX functions includes USERCULTURE, USERPRINCIPALNAME, USEROBJECTID, USERNAME, and CUSTOMDATA. An expression is user-aware when it calls one of these functions directly, or when it references another expression that does so indirectly, like a measure or a calculated column that internally uses USERPRINCIPALNAME.

These functions return values that are known only when a user runs a query. Therefore, they cannot be used in a Standard calculated column because there is no user at process time. The engine raises an error when a Standard calculated column attempts to use a user-aware function. Be mindful that User Context is what allows the column to use these functions. We explicitly choose User Context as the Expression Context, and only then can the expression invoke the functions listed above.

A user-aware calculated column has access only to the rows visible to the user through the corresponding security roles. If a DAX expression aggregates rows from a table or attempts to access other rows or tables in the model, the access is limited to the security perimeter defined by the active security roles for the current user. This is an important difference in the semantics of a calculated column: for example, certain classification techniques (e.g. best products) may require the use of Standard calculated columns that are not user-aware; implementing Non Visual Totals in a semantic model requires calculated tables that access the entire model regardless of the security roles – although, at the time of writing, we do not have an Expression Context property for calculated tables.

It is important to note that user-aware columns can also contain expressions that do not use any user-aware functions and do not access any other rows of the model, whether in the same table or in other tables. In that case, the column is just a virtual calculated column.

Limitations of user-aware calculated columns

User-aware calculated columns have four important limitations:

• They cannot be used in relationships. A relationship in Import mode creates a model-level structure that cannot depend on the user.
• They cannot be referenced (directly or indirectly) in standard calculated columns. Because a Standard calculated column must not depend on the user context, any direct or indirect dependency is not allowed. The model prevents us from creating such conditions, and will raise an error if we try to save a model that violates this.
• They cannot be referenced (directly or indirectly) in calculated tables. A calculated table cannot be user-aware. Therefore, like standard calculated columns, calculated tables must not depend on the user context, directly or indirectly. The model raises an error if we try to save a model that violates that.
• They cannot be referenced (directly or indirectly) in row-level security (RLS). The row-level security expressions must be evaluated to determine which rows are visible in the user-aware space. Therefore, they cannot depend on user-aware columns. In this case as well, the model raises an error if we try to save a model that violates this condition.

The relationship limitation warrants a more detailed explanation because it affects certain modeling techniques. A relationship is a structural property of the model in Import mode that is defined during processing. The engine relies on relationships to optimize queries and to propagate filters between tables. To do so efficiently, the engine builds internal data structures that require for the column to be materialized. A user-aware column does not exist in storage, so there is no column for the relationship to use.

This limitation has a significant practical consequence. User-aware columns cannot be used to build calculated relationships: relationships built on columns that are themselves the result of a calculation. Common examples include a column that combines two existing columns to form a composite key (see COMBINEVALUES) or a column that retrieves a price range based on a value in the table (as in Static segmentation, shown in the following picture). These columns must remain Standard calculated columns and pay the cost of materialization, because they exist for the sole purpose of feeding a relationship.

Conclusions

Recapping, user-aware calculated columns introduce a new dimension to a feature (calculated columns) that has existed since the very first version of DAX. The Expression Context property determines whether a column is evaluated in the model context or the user context; only in the latter case can the expression use user-aware DAX functions.

The most evident use case is localization, but the implications are broader. Virtual calculated columns occupy a useful middle ground between columns imported from the source and measures defined in the model: they expose a column to the user interface without paying the cost of additional storage, while keeping the calculation centralized in a single place. Custom security and personalization scenarios can also be implemented with this new feature.

There are limitations to be aware of. User-aware columns trade structural participation in the model (relationships, calculated tables, RLS, and dependencies from standard calculated columns) for the flexibility of being evaluated at query time; the cost can become noticeable for high-cardinality tables.

The rule is simple: use user-aware columns when a column depends on the user, or when the expression is simple enough that the storage engine can evaluate it at query time at minimal cost. Use standard calculated columns when materialization is needed for relationships, when the expression is complex, or when query performance is critical. As is often the case, understanding when each option applies is what makes the difference between a model that scales well and one that does not.

A slicer cannot filter a measure: let's analyze this common request by explaining how to use a slicer to filter a measure, after discussing the real meaning of using a measure with a slicer.

A slicer cannot filter a measure. In this article, we analyze this common request by explaining how to use a slicer to filter a measure, after discussing the real meaning of using a measure with a slicer.

A very common request by Power BI newbies is, “How can I use a slicer to filter a measure rather than a regular model column?” The most common answer to this question is, “You cannot filter a measure through a slicer”. The answer is entirely correct because there is no such thing as “filtering a measure”. However, elaborating on the why gives us a good way to explain not only what is wrong with the question, but also how to further reason about the requirements needed to obtain a working solution.

Interpreting the question

Let us pretend the question is “I want to filter the sales amount greater than 100,000 USD.” The question is incomplete. One may want to filter products with sales greater than 100,000, or customers, or stores, or any other table/column. Indeed, a filter is always applied to a column, not to a measure. A measure cannot be used as a filter unless we define the granularity of the filter, that is, the column we will use to evaluate the measure in a given context.

Let us add some background information, as the misunderstanding stems from the fact that you can filter a visual with a measure in the Power BI user interface. Indeed, you can use a measure as a filter in a matrix, as in the following example, where the matrix on the right is filtered by the Sales Amount measure in the filter pane.

However, the filter is not on the measure itself. The matrix filters brands with Sales Amount greater than 100,000. The granularity of the filter is provided automatically by the matrix, which groups data by brand. Indeed, changing the column we slice by in the matrix, changes the result.

The total of the unfiltered matrix is the same as above, whereas the total of the filtered matrix is different, because the filter has a different granularity.

In other words, a measure can be a filter if you define its granularity. Filtering Sales Amount greater than 100,000 USD is nonsense. On the other hand, filtering customers (or products) with sales greater than 100,000 USD makes a lot of sense.

Implementing a slicer

If one wants to use a slicer to filter a measure, they can do so as long as they define the granularity of the filter. The slicer can be conveniently used to adjust the filter parameters; in our example, the Sales Amount value that should be used as the minimum.

As an example, one may want to produce a report like the following.

The slicer filters the Sales Amount measure, but it clearly states the granularity at which the filter is applied: products. Therefore, selecting an item in the slicer restricts the calculation to products with sales exceeding the specified limit.

There are multiple ways to produce such a report; a very convenient one is to rely on a function to embed the filtering logic and on a calculation group to serve as the user interface. Each calculation item in the calculation group invokes the function passing the correct parameters.

First, the function:

Local.FilterProductsBasedOnMeasure = 
(
    resultExpression : EXPR,
    filterMeasure: MEASUREREF,
    filterLimit: SCALAR
) =>
    CALCULATE (
        resultExpression,
        KEEPFILTERS (
            FILTER ( Product, filterMeasure > filterLimit )
        )
    )

The function accepts three parameters: the result to produce, the measure to use as a filter, and the filter limit. The granularity of the filter is specified inside the function body, as the function filters Product, providing the product as the filter granularity.

Once the function is defined, one calculation group can contain all the items that produce the filtering, by invoking the function passing SELECTEDMEASURE as the value to compute, Sales Amount as the measure to use as a filter, and the limit as the third argument:

Products selling more than 100 USD = 
Local.FilterProductsBasedOnMeasure ( SELECTEDMEASURE ( ), [Sales Amount], 100 )

Products selling more than 1,000 USD = 
Local.FilterProductsBasedOnMeasure ( SELECTEDMEASURE ( ), [Sales Amount], 1000 )

Products selling more than 10,000 USD = 
Local.FilterProductsBasedOnMeasure ( SELECTEDMEASURE ( ), [Sales Amount], 10000 )

Products selling more than 100,000 USD = 
Local.FilterProductsBasedOnMeasure ( SELECTEDMEASURE ( ), [Sales Amount], 100000 )

Creating a more flexible solution

Depending on the user’s needs, the granularity of the filter can be passed as an additional parameter. This way, the calculation items can change not only the limit but also the granularity (or the measure) to use as a filter. Despite its simplicity, this pattern can easily be extended to accommodate rather complex user needs.

For example, we can create two slicers: one to define the granularity, and one to define the measure boundaries, like in the following example, where we first create a new disconnected table to let users select the filter granularity:

TableToFilter = 
    SELECTCOLUMNS ( 
       { "Product", "Customer", "Store" },
       "Table to filter", [Value]
    )

A new function reads the content of the selected item in the slicer to direct the filter to the proper granularity:

Local.FilterTableBasedOnMeasure = (
        resultExpression : EXPR,
        filterMeasure : MEASUREREF,
        filterLimit : SCALAR
    ) =>
    VAR TableToFilter =
        SELECTEDVALUE ( TableToFilter[Table to filter] )
    VAR Result =
        SWITCH (
            TableToFilter,
            "Product",
                CALCULATE (
                    resultExpression,
                    FILTER (
                        Product,
                        filterMeasure > filterLimit
                    )
                ),
            "Customer",
                CALCULATE (
                    resultExpression,
                    FILTER (
                        Customer,
                        filterMeasure > filterLimit
                    )
                ),
            "Store",
                CALCULATE (
                    resultExpression,
                    FILTER (
                        Store,
                        filterMeasure > filterLimit
                    )
                )
        )
    RETURN
        Result

Finally, a new calculation group lets users choose the amount only, without defining the granularity – which is selected through the disconnected table:

    Sales amount more than 100 USD = 
    Local.FilterTableBasedOnMeasure ( SELECTEDMEASURE (), [Sales Amount], 100 )

    Sales amount more than 1000 USD = 
    Local.FilterTableBasedOnMeasure ( SELECTEDMEASURE (), [Sales Amount], 1000 )

    Sales amount more than 10,000 USD = 
    Local.FilterTableBasedOnMeasure ( SELECTEDMEASURE (), [Sales Amount], 10000 )

    Sales amount more than 100,000 USD = 
    Local.FilterTableBasedOnMeasure ( SELECTEDMEASURE (), [Sales Amount], 100000 )

    Sales amount more than 1,000,000 USD = 
    Local.FilterTableBasedOnMeasure ( SELECTEDMEASURE (), [Sales Amount], 1000000 )

The result is a report where users can choose both the limit value and the granularity.

Conclusions

Sometimes, answering a newbie’s question with the most concise answer is the best option; sometimes it is not. A simple (wrong) requirement, like the one analyzed in this article, may require further investigation to explain why it is wrong and, ultimately, yield interesting solutions.

The key takeaway is that a slicer does not (and cannot) filter a measure directly; instead, it lets users tune the parameters of a filter that is applied to a business entity at a specific granularity (such as Product, Customer, or Store). Once you make that granularity explicit, you can deliver the “filter a measure” experience in a correct, flexible way: You can encapsulate the logic in a reusable function and expose choices through calculation groups.

Learn how to specify the parameter types in DAX user-defined functions using MEASUREREF, COLUMNREF, TABLEREF, and CALENDARREF.

This article describes the parameter types available in DAX user-defined functions, focusing on the specialized reference types MEASUREREF, COLUMNREF, TABLEREF, and CALENDARREF.

In a previous article, Introducing user-defined functions in DAX, we described the syntax for creating user-defined functions, including the two passing modes (VAL and EXPR) and the fundamental parameter types SCALAR and TABLE. In this article, we build on that foundation and focus on the complete type system, with particular attention to the reference types introduced in March 2026 that provide better documentation, stronger validation, and improved IntelliSense support.

Before diving into the new types, let us briefly recap the full picture of parameter types and passing modes available in DAX user-defined functions.

Parameter types and passing modes

Each parameter of a user-defined function has two properties: a type, which describes what kind of value the parameter accepts, and a passing mode, which describes how the value is transferred from the caller to the body of the function. The following table summarizes all the valid combinations.

SCALAR and TABLE are the two types that work with both VAL and EXPR. When no passing mode is specified, the default is VAL for both. ANYVAL is an abstract type for SCALAR and TABLE. Despite the name, it does not exactly restrict the passing mode. You could use ANYVAL as a shortcut for VAL for any data type; we discourage using ANYVAL with EXPR because of the confusion it could generate. All the remaining types (ending with “REF”) force the EXPR passing mode. The passing mode keyword can be omitted for these types because only EXPR is valid.

The types in the lower part of the Type/Passing Mode table (MEASUREREF, COLUMNREF, TABLEREF, and CALENDARREF) are specializations of ANYREF. They share the same passing mode, but they restrict the kind of expression the caller can provide. These are the types we focus on in the rest of this article.

ANYREF and its limitations

ANYREF declares a parameter that accepts any expression and is always passed as an expression. It is the most permissive reference type: the function accepts whatever expression the caller provides: a measure reference, a column reference, a table reference, or an arbitrary DAX calculation. The expression provided is substituted into the body of the function wherever the parameter appears. It is important to highlight that a DAX formula is accepted by ANYREF as a valid argument: ANYREF should not be interpreted as “a reference to any existing object” but rather “a reference to any expression”. Writing ANYREF implies EXPR: writing ANYREF with or without EXPR has the same meaning and produces the same effects.

This flexibility comes at a cost. Because ANYREF accepts anything, the function author cannot make assumptions about the nature of the expression. Is it a measure that triggers a context transition? Is it a simple column reference? Is it an arbitrary calculation? With ANYREF, the answer could be any of these. The function code must therefore be defensive: whether the expression may or may not trigger a context transition, the function author should use an explicit CALCULATE to ensure consistent behavior if a context transition is needed – something that would not be necessary if the parameter passed were a measure reference.

The lack of specificity also affects the caller’s experience. IntelliSense and other development tools cannot provide meaningful guidance when the parameter accepts just any expression. The developer who calls the function must rely on documentation, or on reading the function body, to understand what is expected.

When a parameter is declared as ANYREF, we recommend using the suffix Expr in the parameter name: for example, amountExpr or targetExpr.

For example, here is a model-dependent function that filters customers whose purchase amount (provided as ANYREF) is greater than a minimum value (lowerAmount):

Local.TopCustomersAnyRefA = ( 
    amountExpr : ANYREF, 
    lowerAmount : DOUBLE 
) =>
    FILTER (
        Customer, 
        amountExpr > lowerAmount
    )

The Local.TopCustomersAnyRefA function can be used in three different versions of the AnyRef A measure: as a measure reference, as an expression, and as an expression embedded in CALCULATE, respectively. The expression used is the same as that defined in the Total Quantity measure:

Total Quantity = 
SUM ( Sales[Quantity] )

AnyRef-A 1 = 
CALCULATE ( 
    [Sales Amount],
    Local.TopCustomersAnyRefA ( [Total Quantity], 20 )
)

AnyRef-A 2 = 
CALCULATE ( 
    [Sales Amount],
    Local.TopCustomersAnyRefA ( SUM ( Sales[Quantity] ), 20 )
)

AnyRef-A 3 = 
CALCULATE ( 
    [Sales Amount],
    Local.TopCustomersAnyRefA ( CALCULATE ( SUM ( Sales[Quantity] ) ), 20 )
)

The second version of AnyRef A, which has an expression not embedded in CALCULATE, returns the same values as Sales Amount because the Local.TopCustomerAnyRefA function returns all customers: the result of the amountExpr argument is evaluated without filtering the iterated customer, since the context transition is missing.

We can fix the function by embedding the amountExpr parameter in a CALCULATE, which is redundant but harmless when the argument is a measure reference. However, this would prevent using a column reference if the developer wanted to provide a Customer column as the argument of amountExpr. Not that it would have been a good idea, but ANYREF does not impose any restrictions on the argument to use:

Local.TopCustomersAnyRefB = ( 
    amountExpr : ANYREF, 
    lowerAmount : DOUBLE 
) =>
    FILTER (
        Customer, 
        CALCULATE ( amountExpr ) > lowerAmount
    )

AnyRef-B 2 = 
CALCULATE ( 
    [Sales Amount],
    Local.TopCustomersAnyRefB ( SUM ( Sales[Quantity] ), 20 )
)

This way, all the versions of the measure AnyRef-B return the same value.

MEASUREREF

A MEASUREREF parameter accepts only a reference to a measure defined in the semantic model. The caller must provide the name of an existing measure; arbitrary DAX expressions are not accepted.

This restriction has an important semantic implication. A measure reference always triggers a context transition when evaluated in a row context. When we declare a parameter as MEASUREREF, we inform the reader of the function code that a context transition will occur wherever this parameter is used within an iterator. This makes the code easier to think about because the parameter’s behavior is predictable.

With ANYREF, the function author should wrap the parameter in an explicit CALCULATE to guarantee context transition, because the caller might provide an expression that does not trigger it on its own, as we illustrated with the previous examples for ANYREF. With MEASUREREF, CALCULATE is redundant for this purpose, though it causes no harm. The constraint imposed by the MEASUREREF type guarantees the behavior.

When a parameter is declared as MEASUREREF, we recommend using the suffix Measure in the parameter name: for example, salesMeasure or targetMeasure.

For this example, we created a version of the function we used in the previous ANYREF example, this time specifying MEASUREREF as the type of the amountMeasure parameter:

Local.TopCustomersMeasureRef = ( 
    amountMeasure : MEASUREREF, 
    lowerAmount : DOUBLE 
) =>
    FILTER (
        Customer, 
        amountMeasure > lowerAmount
    )

MeasureRef 1 = 
CALCULATE ( 
    [Sales Amount],
    Local.TopCustomersMeasureRef ( [Total Quantity], 20 )
)

There is only one version of the measure we can use: the one that provides Total Quantity as an argument.

Indeed, trying to provide an expression as the argument for amountMeasure generates a syntax error:

MeasureRef 2 = 
CALCULATE ( 
    [Sales Amount],
    Local.TopCustomersMeasureRef ( SUM ( Sales[Quantity] ), 20 )
)

The declaration of MeasureRef 2 would return the following error: An invalid argument type was passed into parameter ‘amountMeasure’ of the user-defined function. Expected ‘MEASUREREF’ but got ‘SCALAR’.

The error message mentions SCALAR because the expression SUM ( Sales[Quantity] ) could be evaluated before executing the Local.TopCustomersMeasureRef function, and its result would be a scalar in that case. However, the important part is that the expected argument should have been MEASUREREF, and it is not.

COLUMNREF

A COLUMNREF parameter accepts only a reference to a column defined in a table in the semantic model. The caller must provide a qualified column reference, such as Sales[Unit Price] or Product[Unit Price]; arbitrary expressions are not accepted.

COLUMNREF is particularly useful when writing model-independent functions. Instead of hardcoding column names in the function body (which would create a dependency on the model structure), we declare the columns as COLUMNREF parameters and let the caller specify which columns to use. This design makes the function portable across models with different table and column names.

COLUMNREF parameters work well in combination with two DAX functions designed for inspecting reference parameters, TABLEOF and NAMEOF:

• TABLEOF retrieves the table where a given column is defined: if the caller passes Sales[Unit Price] as the priceColumn parameter, then TABLEOF ( priceColumn ) returns the Sales This combination allows us to reduce the number of parameters in the function signature. Instead of asking the caller for both a table and a column from that table, we can ask for only the column and from there, derive the table by using TABLEOF.
• NAMEOF returns the name of a column reference as a string, which can be useful for dynamic operations that require the column name in text form.

When a parameter is declared as COLUMNREF, we recommend using the suffix Column in the parameter name: for example, priceColumn or dateColumn.

We start with an educational example, SumProduct, which just multiplies two columns row-by-row and sums the result:

SumProduct = ( 
    quantityColumn: COLUMNREF, 
    priceColumn: COLUMNREF 
) =>
    SUMX (
        TABLEOF ( quantityColumn ),
        quantityColumn * priceColumn
    )

Total Cost = 
SumProduct ( Sales[Quantity], Sales[Unit Cost] )

The result of the Total Cost measure computed this way is identical to SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] ). However, this first example is meant to be merely educational to show that by using TABLEOF, it is possible to obtain the table from a column reference parameter without an additional parameter for the table reference.

However, this simple example already shows an important limitation: the formula inside the function assumes that the two columns belong to the same table. If this condition is not true, the error could be misleading. For example, the following measure generates a syntax error and is not valid:

Total Cost Mismatch = 
SumProduct ( Sales[Quantity], 'Product'[Unit Cost] )

The syntax error is: A single value for column ‘Unit Cost’ in table ‘Product’ cannot be determined. This error is not very clear because it is generated by the SUMX function used in SumProduct when referencing columns from two different tables. Unfortunately, the current version of UDFs in preview comes with limitations in what we are going to describe now, but validating the parameters is something we want to introduce in this article. Ideally, we would like to customize the error message by validating that the two arguments belong to the same table. We achieve this by using the following code:

SumProductSafe = ( 
    quantityColumn: COLUMNREF, 
    priceColumn: COLUMNREF 
) =>
    IF (
        NAMEOF ( TABLEOF ( quantityColumn ) ) == NAMEOF ( TABLEOF ( priceColumn ) ),
        SUMX (
            TABLEOF ( quantityColumn ),
            quantityColumn * priceColumn
        ),
        ERROR ( "All the column references must belong to the same table" )
    )

Total Cost Safe Mismatch = 
SumProduct ( Sales[Quantity], 'Product'[Unit Cost] )

In this case, the error message should be: All the column references must belong to the same table. Unfortunately, the current implementation does not support this kind of validation before execution. We hope that Microsoft will support such validation before the user-defined functions are generally available, by using the syntax in this example, or equivalent.

The result of the Total Cost measure computed by SumProduct is identical to SUMX ( Sales, Sales[Quantity] * Sales[Unit Cost] ). However, this first example is meant to be merely educational.

For a more meaningful example, consider a scenario in which a PriceRange-disconnected table in the model defines price ranges (a more complete coverage of this scenario is available in the DAX Pattern, Static segmentation).

We can create a model-independent function that retrieves the segment corresponding to a specified value. In order to be model-independent, the function exposes all the model dependencies as parameters, which in this case are all column references:

RangeLookupUnchecked = (
    search        : SCALAR VAL,
    minColumn     : COLUMNREF,
    maxColumn     : COLUMNREF,
    targetColumn  : COLUMNREF
) =>
    SELECTCOLUMNS (
        FILTER ( 
            TABLEOF ( minColumn ),
            minColumn <= search && maxColumn > search 
        ),
        "@Result", targetColumn
    )

The RangeLookupUnchecked function does not validate that the three columns belong to the same table. An error in the arguments provided to the function might be difficult to interpret. Therefore, we would like to create a safer version of the function that verifies that all the column references do belong to the same table, and returns a specific error otherwise:

RangeLookup = (
    search        : SCALAR VAL,
    minColumn     : COLUMNREF,
    maxColumn     : COLUMNREF,
    targetColumn  : COLUMNREF
) =>
    IF (
        NAMEOF ( TABLEOF ( minColumn ) ) == NAMEOF ( TABLEOF ( maxColumn ) )
            && NAMEOF ( TABLEOF ( minColumn ) ) == NAMEOF ( TABLEOF ( targetColumn ) ),
        SELECTCOLUMNS (
            FILTER ( 
                TABLEOF ( minColumn ),
                minColumn <= search && maxColumn > search 
            ),
            "@Result", targetColumn
        ),
        ERROR ( "All the column references must belong to the same table" )
    )

Currently, the syntax error from an invalid column reference occurs before the code that generates the customized error, but we hope to make this check possible in the future. We could also define a version of the function for the dynamic segmentation pattern, which returns a table and can be used as a CALCULATE filter in a measure (with the same disclaimer for the validation code that might not be executed as we would like in the current preview of UDFs):

ValuesInSegment = (
    filterColumn  : COLUMNREF,
    minColumn     : COLUMNREF,
    maxColumn     : COLUMNREF,
    targetColumn  : COLUMNREF
) =>
    GENERATE (
        TABLEOF ( targetColumn ),
        FILTER ( 
            VALUES ( filterColumn ),
            IF (
                NAMEOF ( TABLEOF ( minColumn ) ) == NAMEOF ( TABLEOF ( maxColumn ) )
                    && NAMEOF ( TABLEOF ( minColumn ) ) == NAMEOF ( TABLEOF ( targetColumn ) 
),
                minColumn <= filterColumn && maxColumn > filterColumn,
                ERROR ( "minColumn, maxColumn, and targetColumn arguments must belong to the same table" )
            )
        )
    )

Segmented Sales = 
CALCULATE ( 
    [Sales Amount],
    ValuesInSegment (
        Sales[Net Price],
        PriceRange[Min Price], PriceRange[Max Price], PriceRange[Segment]
    )
)

The result of Segmented Sales filters Sales Amount only for the segment grouped in the visual, whereas the original Sales Amount measure ignores that filter because it comes from a disconnected table.

TABLEREF

A TABLEREF parameter accepts only a reference to a table defined in the semantic model. The caller must provide the name of an existing table; table expressions such as FILTER or SELECTCOLUMNS are not accepted.

This type is useful when the function needs to operate on a model table and must guarantee that the provided argument is an actual table from the model, not a derived or filtered table expression. By us constraining the parameter to a table reference, the function can rely on the table having the full set of columns and relationships defined in the model.

When a parameter is declared as TABLEREF, we recommend using the suffix Table in the parameter name: for example, salesTable or customerTable.

Using TABLEREF is probably uncommon because TABLE EXPR is more flexible and does not impose a restriction on the table that should be evaluated inside the function. However, we may want to ensure that the table is a model table so we can use functions like ISFILTERED and ISCROSSFILTERED using a valid table argument. For example, the HasRelationships function returns TRUE if the sourceTable filters targetTable in the current filter context, meaning that there are one or more relationships connecting the two tables and propagating the filter context:

HasRelationships = (
    targetTable : TABLEREF,
    sourceTable : TABLEREF
) => 
    CALCULATE (
        ISCROSSFILTERED ( targetTable ),
        sourceTable,
        REMOVEFILTERS ()
    )

Product filter Sales = 
HasRelationships ( 
    Sales,
    'Product'
)

PriceRange filter Sales = 
HasRelationships ( 
    Sales,
    PriceRange
)

The Product filter Sales and PriceRange filter Sales measures show how to use the HasRelationships function.

The example is merely educational. We suggest using TABLEOF whenever possible to reduce the number of parameters, and considering TABLE EXPR instead of TABLEREF to give more flexibility to the developers using a function.

CALENDARREF

A CALENDARREF parameter accepts only a reference to a calendar defined in the semantic model. CALENDARREF is designed for calendar-based time intelligence functions.

When a parameter is declared as CALENDARREF, we recommend using the suffix Calendar in the parameter name — for example, dateCalendar.

As an example, we create a DatesPYTD function that applies a previous year-to-date transformation by combining DATESYTD and SAMEPERIODLASTYEAR:

DatesPYTD = ( targetCalendar : CALENDARREF ) =>
    CALCULATETABLE (
        DATESYTD ( targetCalendar ),
        SAMEPERIODLASTYEAR ( targetCalendar )
    )

YTD Sales = 
CALCULATE ( 
    [Sales Amount],
    DATESYTD ( 'Gregorian' )
)

PYTD Sales = 
CALCULATE (
    [Sales Amount],
    DatesPYTD ( 'Gregorian' )
)

The result of PYTD Sales is like YTD Sales, shifted by one year.

Why use specific reference types instead of ANYREF

The new reference types are specializations of ANYREF: they share the same passing mode (EXPR), but they restrict the accepted expressions. A natural question is, “why should we bother with the restriction when ANYREF already works?”. There are two primary reasons.

The first reason is validation. When we use a specific reference type, the engine and IntelliSense can enforce constraints at the point of the function call. If a developer mistakenly passes a column reference to a MEASUREREF parameter, the error is reported immediately with a clear message. If a developer passes a FILTER expression to a TABLEREF parameter, the engine rejects it before the function body executes. With ANYREF, these mistakes would produce confusing errors deep inside the function body or, worse, incorrect results without any error at all.

The second reason is documentation. A function signature is the first thing a developer reads when deciding whether and how to use a function. A parameter declared as MEASUREREF immediately communicates that the function expects a measure, that context transition will occur, and that arbitrary expressions are not accepted. A parameter declared as COLUMNREF communicates that the caller must provide a column from a model table. A parameter declared as ANYREF communicates none of these things; the developer must read the function body to understand what is expected, even though adopting a consistent naming convention for the parameters helps clarify that.

These two reasons reinforce each other. Better documentation reduces the likelihood of mistakes, and stronger validation catches the mistakes that still occur. Together, they make functions easier to use, easier to maintain, and safer to share across models and libraries.

Conclusions

The parameter type system in DAX user-defined functions (UDFs) provides a spectrum from the most permissive type (ANYREF) to the most restrictive (MEASUREREF, COLUMNREF, TABLEREF, and CALENDARREF), which are specializations of ANYREF that restrict the accepted expressions to specific categories.

The rule is simple: use the most specific parameter type that satisfies your function’s requirements. If the function expects a measure, use MEASUREREF. If it expects a column, use COLUMNREF. If it expects a model table reference, use TABLEREF. If it expects a calendar, use CALENDARREF. Reserve ANYREF for those cases where the function genuinely needs to be able to accept any kind of expression. The more specific the type, the clearer the intent of the function, the stronger the validation, and the more helpful the development tools become for the developers who use your functions.