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.

How to highlight a row based solely on the maximum value in the last column using visual calculations, a tool that can be used efficiently to format visuals.

This video explains how to navigate the lattice of visual calculations in DAX. The visual context goes beyond the row context and filter context by adding the concept of levels within the lattice. The video shows how to use COLLAPSEALL and EXPAND to move between levels, using COLLAPSEALL as an absolute reference point from which you can reach any desired level.

A key topic is the difference between ROWS/COLUMNS and VALUES of ROWS/COLUMNS. ROWS and COLUMNS always return all values at the current level, ignoring filters, while VALUES of ROWS/COLUMNS respects the active filter context. The video also shows how to create reusable visual calculation functions that encapsulate lattice navigation logic, simplifying otherwise complex code.

These techniques build on the article Using visual calculations to highlight an entire row: We recommend reading the article and watching its companion video before this SQLBI+ video.

Visual calculations can be used efficiently to format visuals. This article presents an interesting technique to highlight a row based solely on the maximum value in the last column.

When it comes to visuals, users may want to specific cells highlighted in order to spot important information quickly. While browsing the forums, we came across an interesting requirement that can easily be solved with a DAX measure: highlight an entire row based on the value in the last column of the visual only. In our example, we highlight Wide World Importers because it has the maximum value (71,904.98) in the last year (2026).

Introducing the measure solution

The scenario can be easily solved with a regular measure that computes the last visible year, then the values of different brands in that year, and finally searches for the brands with the maximum value (there may be more than just one) and produces “Yellow” for those brands only:

Color =
VAR LastYear =
    CALCULATE ( MAX ( 'Date'[Year] ), ALLSELECTED () )
VAR BrandsInLastYear =
    CALCULATETABLE (
        ADDCOLUMNS ( VALUES ( 'Product'[Brand] ), "@Sales", [Sales Amount] ),
        ALLSELECTED ( 'Product'[Brand] ),
        'Date'[Year] = LastYear
    )
VAR MaxValueInLastYear =
    MAXX ( BrandsInLastYear, [@Sales] )
VAR BrandsWithMaxValueInLastYear =
    SELECTCOLUMNS (
        FILTER ( BrandsInLastYear, [@Sales] = MaxValueInLastYear ),
        'Product'[Brand]
    )
VAR CurrentBrand =
    SELECTEDVALUE ( 'Product'[Brand] )
VAR Result =
    IF ( CurrentBrand IN BrandsWithMaxValueInLastYear, "Yellow" )
RETURN
    Result

A good DAX developer writes this code and obtains the desired result. However, the code has a couple of drawbacks worth pointing out:

• Using a model measure to change the behavior of a visual is a bit overkill. Every visual has specific requirements that necessitate creating multiple measures whose sole purpose is to alter the aesthetics of the report.
• The measure works fine if the visual includes the year on the columns and the brand on the rows. Changing the structure of the visual, for example, using the Product[Category] on the columns, requires also changing the measure code.

Implementing a visual calculation

It would be much more convenient to embed the aesthetic changes in a visual calculation, so that the model does not become messy. Using a visual calculation requires less intuitive code because it requires navigating the visual lattice using EXPAND and COLLAPSE. On the other hand, the ability to reference ROWS and COLUMNS, and to use nice functions like LAST, simplifies some of the calculations:

Color = 
VAR LastYear = 
    CALCULATE ( 
        CALCULATE ( 
            LAST ( [Year], COLUMNS ), 
            EXPAND ( COLUMNS ) 
        ),
        COLLAPSEALL( ROWS COLUMNS ) 
    )
VAR RowsInLastYear = 
    CALCULATETABLE ( 
        CALCULATETABLE ( 
            FILTER ( 
                ROWS COLUMNS,
                [Year] = LastYear
            ),
            EXPAND ( ROWS COLUMNS )
        ),
        COLLAPSEALL ( ROWS COLUMNS )
    )
VAR MaxValueInLastYear = MAXX ( RowsInLastYear, [Sales Amount] )
VAR BrandsWithMaxValueInLastYear = 
    SELECTCOLUMNS ( 
        FILTER ( RowsInLastYear, [Sales Amount] = MaxValueInLastYear ), 
        "Brand", [Brand] 
    )
VAR CurrentBrand = [Brand]
RETURN
    IF ( CurrentBrand IN BrandsWithMaxValueInLastYear, "Yellow" )

The main advantage of using a visual calculation is that the code is in the visual rather than in the model. Therefore, if the visual is copied to a different page and reorganized, the visual calculation code can be modified to work in the new visual.

However, the solution is not optimal yet. The visual calculation needs to reference the column names in multiple places. For example, if one uses a different measure, they need to modify the code. If Category is in the rows rather than Brand, the code needs to be adapted again. The chances of making mistakes are quite high.

In this scenario, functions are king. A good way to think about the scenario is to write a function that takes a table representing the matrix content and returns the name of the brand (or whatever column is required) with the maximum value for the year (or, again, whatever column is required). The visual calculation will only need to build the matrix, pass it down to the function along with the names of the columns to use, and then decide the color to use.

A possible implementation of the function is the following:

RowHeaderOfLastCol = 
    ( 
        matrix : TABLE, 
        colHeader : COLUMNREF, 
        rowHeader : COLUMNREF, 
        valueExpr : EXPR 
    ) => 
    VAR LastColHeader = MAXX ( matrix, colHeader )
    VAR RowsInLastCol = 
        FILTER (
            matrix,
            colHeader = LastColHeader
        )
    VAR MaxValueInLastCol = MAXX ( RowsInLastCol, valueExpr )
    VAR RowWithMaxValueInLastCol = 
        FILTER (
            RowsInLastCol, 
            valueExpr = MaxValueInLastCol
        )
    VAR Result = SELECTCOLUMNS ( RowWithMaxValueInLastCol, "rowHeader", rowHeader )
    RETURN Result

As you may notice, the function no longer references Brand, Sales Amount, and Year. The function is, by its nature, generic. It receives a table representing the matrix content and searches for the row headers that contain the maximum value in the last column.

The visual calculation is much simpler, as it only needs to build the table containing the matrix and then invoke the function:

Color = 
VAR BestBrands = 
    CALCULATETABLE ( 
        CALCULATETABLE ( 
            RowHeaderOfLastCol ( ROWS COLUMNS, [Year], [Brand], [Sales Amount] ),
            EXPAND ( ROWS COLUMNS )
        ),
        COLLAPSEALL ( ROWS COLUMNS )
    )
VAR CurrentBrand = [Brand]
RETURN 
    IF ( CurrentBrand IN BestBrands, "Yellow" )

The main advantage is that the reference to the column names appears in just one line: the function call. The remaining part of the visual calculation does not need to be adapted for a different visual, thus making it easier to produce different implementations of the same business logic.

As an example, the following is a visual calculation that highlights rows in a matrix that contains the category as the top level, and the brand as the second level only:

Color = 
VAR BestBrands = 
    CALCULATETABLE ( 
        CALCULATETABLE ( 
            RowHeaderOfLastCol ( VALUES (ROWS COLUMNS ), [Year], [Brand], [Sales Amount] ),
            EXPAND ( [Year] ),
            EXPAND ( [Brand] )
        ),
        COLLAPSE ( ROWS COLUMNS )
    )
VAR CurrentBrand = [Brand]
RETURN 
    IF ( CurrentBrand IN BestBrands, "Yellow" )

The result is that brands are highlighted locally in the current category.

Conclusions

In its simplicity, this example shows several useful techniques in the management of your code, and also several DAX features that are not trivial, like using ROWS COLUMNS to create a table with the content of the matrix, the navigation in the lattice, and the capability of passing columns of the virtual table down to functions.

Going into the many details would take much longer, and that type of content would not be suitable for a short article and its related video. If you are interested in learning more about these topics, we have published a longer video, How to navigate the lattice of visual calculations, available to all our SQLBI+ subscribers.

We released a new session in SQLBI+:

• How to navigate the lattice of visual calculations: This video explains how to navigate the lattice of visual calculations in DAX, using COLLAPSEALL and EXPAND to move between levels. A key topic is the difference between ROWS/COLUMNS and VALUES of ROWS/COLUMNS. The video also shows how to create reusable visual calculation functions that encapsulate lattice navigation logic, simplifying otherwise complex code.

Stay tuned for new SQLBI+ content coming in 2026!

Alberto and I recorded an unplugged session to talk about the new edition of the Mastering DAX Video Course. You can watch it above, but if you prefer a quick read, here are some thoughts.

The question we heard the most over the years was some version of “where do I start?” People kept telling us that our material was great but too advanced, that you needed another book before ours. That was fair, and it pushed us to rethink the whole approach. This edition starts from zero. Well, almost. We still assume you know Power BI Desktop, but when it comes to DAX, we build everything from the ground up, at a calmer pace, with new ways to explain things like the filter context.

The training ended up being more than 30 hours, which is a lot. But we designed it so that you do not have to finish it. Wherever you stop, you already know something useful. If you reach halfway (chapter 10), you have a solid level for most real-world scenarios. The rest is there to further improve your skills and for when you need it.

We are also quite excited about the new modules on user-defined functions and calendar-based time intelligence. User-defined functions in particular will change the way people write and maintain DAX in the coming years, including how AI tools interact with DAX code. That is a topic we got into during the video, and it is worth hearing Alberto’s take on it.

A few things we did not include in this edition: optimization and VertiPaq internals are gone. They have their own book now. Honestly, if you write good DAX and follow the basics (like filter columns, not tables), you rarely need to optimize anything. DAX is simple, but not easy. It takes practice, and the exercises (coming soon after the training ships) are there to help with that.

The unplugged video has a few other moments that did not make it into this recap: some about what it is like to record 30 hours of technical content in a foreign language, and some that are just fun to watch. What are you waiting for? Watch the video, and enjoy DAX!

Alberto and I discuss the third edition of the Mastering DAX Video Course: what changed, why we redesigned it to be more accessible, and what new content we added.

Should you use DAX user-defined functions (UDF) or calculation groups? Learn when to use either, and how they complement each other in the design of a semantic model in Power BI.

This article describes the different roles of user-defined functions and calculation groups, explaining when to use either, and how they complement each other in the design of a semantic model.

The introduction of user-defined functions (UDFs) in DAX changes the way we think about code reuse. Before UDFs existed, calculation groups were the only mechanism for sharing common logic across multiple calculations. Many developers adopted calculation groups not because they were the ideal tool for code reuse, but because there was no alternative.

Now that user-defined functions are available, it is time to revisit this practice. User-defined functions and calculation groups serve fundamentally different purposes. Understanding the distinction between the two is essential for building well-organized, efficient semantic models.

Three tools, three purposes

A semantic model offers three different tools to a DAX developer, and each serves a distinct role:

• Measures expose specific calculations to the end user in the semantic model.
• Calculation groups expose common filters or transformations that can be applied to any measure.
• User-defined functions allow a developer to write code once and reuse it everywhere, in measures, calculated columns, security roles, and also calculation groups!

The key distinction is the target audience. Measures and calculation groups are visible to the report user; they appear in reports and visuals. On the other hand, user-defined functions are invisible to report users. A function is simply a tool for the developer to arrange code in the best possible way. The report user never directly sees, selects, or interacts with a function.

This separation leads to a clear design principle: the decision to expose a feature as a measure or as a calculation group is a user-facing decision. By contrast, the sharing of business logic is an internal implementation detail of the semantic model.

When to use calculation groups

Calculation groups remain the best tool when the goal is to provide the end user with a choice that applies to all measures in a visual. Two scenarios illustrate this well.

The first scenario is a common filter. When users need to select a filter that applies to every measure in a report, a calculation group lets them do so through a single slicer. For example, a calculation group with items like “Current Month” and “Last Quarter” allows the user to choose which period to analyze. The selected calculation item is then applied to all the measures in the visual without requiring the developer to create separate measure variants for each combination.

The second scenario is a common transformation. When all the numbers in a visual should be divided by 1,000 or by 1,000,000 for readability, a calculation group applies that transformation to every measure at once. The user simply selects the desired scale factor, and the transformation is applied to all the measures.

In both cases, the defining characteristic is the same: the calculation group applies a single transformation to all measures without distinction. This is precisely what makes calculation groups valuable to the report user; the same characteristic limits their flexibility when the goal is something different.

A practical example: new and returning customers

Consider a scenario in which the user must choose between analyzing new and returning customers. A “new customer” is one whose first purchase falls within the current filter context; a “returning customer” is one whose first purchase occurred before the current filter context. This is a good candidate for a calculation group because the same segmentation logic applies to every measure in the visual. Whether the user is viewing Sales Amount, # Orders, Margin, or Margin %, the filter restricts the data to the same subset of customers.

The user selects “New customers” or “Returning customers” from a slicer, and every measure in the visual is filtered accordingly. This is a user-facing decision: the user chooses which customer segment to analyze. A calculation group is the correct tool for this purpose:

New customers = 
VAR CustomersWithNewDate =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            Sales[CustomerKey],
            "@NewCustomerDate", 
                CALCULATE ( MIN ( Sales[Order Date] ) )
        ),
        ALLEXCEPT ( Sales, Customer )
    )
VAR NewCustomers =
    FILTER (                              
        CustomersWithNewDate,
        [@NewCustomerDate] IN VALUES ( 'Date'[Date] )
    )
VAR Result =
    CALCULATE(
        SELECTEDMEASURE(),
        NewCustomers
    )
RETURN Result

Returning customers = 
VAR MinDate = MIN ( 'Date'[Date] )
VAR CustomersWithNewDate =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            Sales[CustomerKey],
            "@NewCustomerDate", 
                CALCULATE ( MIN ( Sales[Order Date] ) )
        ),
        ALLEXCEPT ( Sales, Customer )
    )
VAR ExistingCustomers =
    FILTER (
        CustomersWithNewDate,
        [@NewCustomerDate] < MinDate
    )
VAR ReturningCustomers =
    SELECTCOLUMNS (
        ExistingCustomers,
        "CustomerKey", Sales[CustomerKey]
    )
VAR Result =
    CALCULATE(
        SELECTEDMEASURE(),
        KEEPFILTERS ( ReturningCustomers )
    )
RETURN Result

This example works well because the calculation item applies the same segmentation logic to every measure without distinction. The user makes a single choice, and the entire visual responds. Please note that a full description of the new and returning customers pattern is available in the New and returning customers article at www.daxpatterns.com.

Why calculation groups are not ideal for code reuse

Despite their versatility, calculation groups are not a good tool for sharing code across multiple calculations. The reason is structural: calculation groups, like measures, do not have parameters.

In DAX, the typical way to pass information to shared code in a measure or calculation group is through the filter context. A developer modifies the filter context before calling a measure or applying a calculation item, and the shared code reads the information from that modified context. This approach works, but it is expensive. Transferring information through the filter context may require additional work at query time, thereby degrading performance.

A user-defined function accepts parameters directly. The function is expanded in the query plan, much like macros in other languages like C/C++. Because the parameters are resolved before the code is executed, the result is a more efficient query plan that does not generate additional work at execution time.

The performance difference may be negligible on a small model with a simple calculation. However, as the complexity of the shared logic grows, and especially when the same logic is invoked multiple times within a single query, the overhead of passing parameters through the filter context adds up. Using a function with explicit parameters can help us avoid this overhead.

User-defined functions for business logic

User-defined functions are the primary tool for sharing and reusing code within and across models (more details here: Model-dependent and model-independent user-defined functions in DAX). Whenever a piece of business logic is complex enough to warrant a single definition, especially logic containing parameters that might vary, or rules that should only be defined in one place, a UDF is the appropriate choice.

We have two examples to clarify this.

The first scenario is encapsulating business logic. Consider the customer segmentation from the previous section. The logic that identifies new customers is business logic: it defines what “new” means. If we place that logic in a function, we can reuse it both in the calculation item and in a standalone measure. The business rule is defined once; every consumer of that rule, whether a calculation item or a measure, calls the same function. The business logic is defined in three model-independent functions embedded in two model-dependent functions that are referenced later in calculation items and measures:

DaxPatterns.NewReturning.Absolutes.CustomersWithNewDate = (
    TxCustomerKeyColumn: COLUMNREF, 
    CustomerTable: TABLEREF, 
    TxDateColumn: COLUMNREF 
) =>
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            TxCustomerKeyColumn,
            "@NewCustomerDate", 
                CALCULATE ( MIN ( TxDateColumn ) )
        ),
        ALLEXCEPT ( TABLEOF ( TxCustomerKeyColumn ), CustomerTable )
    )

DaxPatterns.NewReturning.Absolutes.NewCustomers = ( 
    TxCustomerKeyColumn: COLUMNREF, 
    CustomerTable: TABLEREF, 
    TxDateColumn: COLUMNREF, 
    DateColumn: COLUMNREF 
) => 
    FILTER (                              
        DaxPatterns.NewReturning.Absolutes.CustomersWithNewDate ( 
            TxCustomerKeyColumn, 
            CustomerTable, 
            TxDateColumn 
        ),
        [@NewCustomerDate] IN VALUES ( DateColumn )
    )

DaxPatterns.NewReturning.Absolutes.ReturningCustomers = (
    TxCustomerKeyColumn: COLUMNREF, 
    CustomerTable: TABLEREF, 
    TxDateColumn: COLUMNREF, 
    DateColumn: COLUMNREF
) =>
    VAR MinDate = MIN ( DateColumn )
    VAR ExistingCustomers =
        FILTER (
            DaxPatterns.NewReturning.Absolutes.CustomersWithNewDate ( 
                TxCustomerKeyColumn, 
                CustomerTable, 
                TxDateColumn 
            ),
            [@NewCustomerDate] < MinDate
        )
    VAR ReturningCustomers =
        SELECTCOLUMNS (
            ExistingCustomers,
            "CustomerKey", TxCustomerKeyColumn
        )
    RETURN ReturningCustomers

Local.NewCustomers = () => 
DaxPatterns.NewReturning.Absolutes.NewCustomers ( 
    Sales[CustomerKey], 
    Customer, 
    Sales[Order Date], 
    'Date'[Date] 
)

Local.ReturningCustomers = () => 
DaxPatterns.NewReturning.Absolutes.ReturningCustomers ( 
    Sales[CustomerKey], 
    Customer, 
    Sales[Order Date], 
    'Date'[Date] 
)

The calculation items can be defined using much shorter expressions that reference the model-dependent functions:

New customers = 
CALCULATE (
    SELECTEDMEASURE (),
    NewCustomers ()
)

Returning customers = 
CALCULATE (
    SELECTEDMEASURE (),
    ReturningCustomers ()
)

The same user-defined functions can be used in measures:

Sales New Customers = 
CALCULATE ( 
    [Sales Amount],
    Local.NewCustomers ()
)

Sales Returning Customers = 
CALCULATE ( 
    [Sales Amount],
    Local.ReturningCustomers ()
)

The business logic lives in exactly one place. If the definition of “new customer” changes, we update the function, and every consumer reflects the change.

The measures we created help us illustrate the second scenario: side-by-side visuals. Suppose we want a report that shows both the standard Sales Amount and the Sales Amount for new customers in the same visual, as two separate columns, together with two other columns with Margin and Margin %. If we rely solely on a calculation group, the selected calculation item is applied to every measure in the visual. We cannot have one measure with the calculation item active (Sales Amount) and the other two measures without the “new customer” (Margin and Margin %) in the same visual. By defining a standalone measure that calls the function, we can place both the Sales Amount and the Sales New Customers measures in the same visual without interference.

If you are interested in another practical, step-by-step example of this approach that describes how we transform an existing calculation into a more generic function, read the article Creating functions for the like-for-like DAX pattern.

When not to use a function

One could argue that every single calculation should be written as a UDF, with measures and calculation items serving merely as function calls with the appropriate parameters. We do not suggest going to this extreme. If a measure contains a simple SUM or a straightforward subtraction, there is no benefit in wrapping it inside a function. Moving a simple operation into a function only hides the calculation and makes the code harder to read.

The guideline is pragmatic: use a function when the logic is complex enough to benefit from a single definition, when it contains parameters that might vary, or when the same business rule must be applied in multiple places. For simple calculations, write the code directly in the measure.

For example, the calculation items in the Period calculation group may be simple enough not to require a separate function definition. Consider the fact that the actual implementation in the sample file is longer because of the need to simulate a specific “current” date:

Current Year = 
CALCULATE ( 
    SELECTEDMEASURE(),
    PARALLELPERIOD ( 'Date'[Date], 0, YEAR )
)

Last Year = 
CALCULATE ( 
    SELECTEDMEASURE(),
    PARALLELPERIOD ( 'Date'[Date], -1, YEAR )
)

Conclusions

User-defined functions and calculation groups are complementary tools that serve different audiences. A calculation group is a feature exposed to report users; it lets them apply a common filter or transformation to all measures in a visual. A user-defined function is invisible to the user; it is a tool for the developer to organize business logic in one place and reuse it wherever needed.

The design of a semantic model benefits from keeping this distinction clear. Decide what the user needs to see and interact with; that determines whether to use a measure or a calculation group. Then decide how to implement the underlying logic efficiently, and consider user-defined functions as a tool available for that implementation.

When business logic is complex or shared across multiple measures, place it in a function. Let measures and calculation items call that function with the appropriate parameters. The result is a semantic model that is easier to maintain, consistent in its business rules, and more efficient in its query plans.

The third edition of the Mastering DAX video course is available!

The third edition of the Mastering DAX video course is now available!

The world has changed a lot since the release of the second edition. While the foundational concepts of DAX have not changed, DAX has evolved as a language, incorporating user-defined functions, calendar-based time intelligence, and visual calculations over time.

In the meantime, Power BI is now part of the larger Microsoft Fabric ecosystem, and the adoption of Power BI and DAX keeps increasingly steadily. LLMs have arrived – whether they are ready to write DAX or not is a big “it depends”, but it is inevitable that more and more DAX code will be written by LLMs in the years to come.

Therefore, why bother to learn DAX? Several reasons.

First, because DAX could be way shorter than the prompt required to generate it. Second, to validate the code generated by the machine. Third, because it is fun. Well, we are biased here!

Why a third edition of the video course, then? Well, in this case too, several reasons, the same that guided the writing of the third edition of The Definitive Guide to DAX book which we released in November 2025.

First, new features: window functions, visual calculations, calendar-based time intelligence, and user-defined functions simply did not exist when we produced the second edition.

Second, to provide a smoother learning path. We completely rewrote the book first, then used it as the screenplay for the video course: same structure, same examples, different delivery. The book is precise, definitive, and detailed. The video course is more conversational, where we take the time needed to explain what is going on, often by writing the code step by step. Some will prefer the book, some will choose the video course, and others will use both! However, we start with the basics and introduce the theoretical concepts of the DAX language more gradually, in turn enabling newbies to write simple DAX formulas after the first two modules, rather than overwhelming students with all the theory before writing real-world measures. We know you want to jump into writing code ASAP!

We added many original illustrations to the book that we also use in the video course, using a digital whiteboard to illustrate abstract concepts more visually. We experimented with this teaching technique in our classrooms courses and in certain YouTube videos; the feedback we received confirmed it was a good idea, and we applied it to the video course, too.

Thus, this is a completely new video course. If you were an existing student with an active license for the second edition at the time we released the third edition, you will clearly see the differences because you now have access to both video courses: the second and the third edition. If you are a new student, you will just enjoy the new approach!

One note about the exercises: they are not ready yet, we will add them in April 2026, starting from the first modules. We already included all the sample files that you can use to follow the demos in the lectures, and you can play with them, too. Expect proper exercises to assess your knowledge after each module in the coming weeks.

Now, a few key metrics about the third edition:

• >30 hours of recorded video lectures. This is double the duration of the content available in the second edition.
• Full coverage of any and all DAX features released all the way until December 2025.
• Professionally reviewed English subtitles. Automatic translations to Arabic, Chinese (Standard), Chinese (Traditional), French, German, Italian, Japanese, Korean, Polish, Portuguese (Brazil), Russian, Spanish, Ukrainian.

As mentioned above, all students with an active license for the second edition of the Mastering DAX video course automatically received access to the third edition, with the same expiration date.

If you do not have access to the Mastering DAX video course yet, or if you want to extend your current expiration date, take full advantage of our launch offer with a 20% discount valid until April 3, 2026. Moreover, to celebrate the 10- year anniversary of Power BI and of our video course, we are extending the 20% discount to all video courses offered by SQLBI! Hurry and make sure to take advantage of this offer now, or else you’ll have to wait for Black Friday!

If this is your first time enrolling in the Mastering DAX video course, we recommend you just follow the lectures in the proposed order and make sure you gain some experience by practicing your newfound skills on the exercises provided along the way.

As we often say, DAX is simple, but it is not easy. We worked hard to make sure this third edition will be enjoyed by both newbies starting from zero knowledge of DAX and DAX professionals who were early adopters of Power BI and have already written thousands of DAX measures. Everyone will be able to level up with this new video course.

Enjoy DAX!

Create a DAX model-independent user-defined function (UDF) to easily apply the like-for-like pattern to your data.

This article offers a comprehensive guide to changing the like-for-like pattern into model-independent functions to enhance flexibility and simplify DAX code.

DAX user-defined functions (UDFs) are a powerful tool for improving the quality of your semantic models. DAX authors with an IT background are accustomed to creating generic code using functions. However, many DAX creators came from different backgrounds of expertise, such as statistics, business, and marketing. They may not recognize the immense power that functions have brought to the Power BI community.

In this article, we want to practically show, through an example, how to wisely use functions to improve the generalization of code and to reduce the complexity of your semantic models, with the goal of raising curiosity towards user-defined functions and – in general – the world of code development.

We use the like-for-like comparison pattern as an example: https://www.daxpatterns.com/like-for-like-comparison/. We are neither going to describe the pattern code, nor going to evaluate its quality. The goal is to show how to transform a pattern that requires manual intervention into a set of generic functions that greatly simplify the creation of new semantic models.

Quick analysis of the like-for-like pattern

The goal of the pattern is to show the sales amount for only the stores that were open across all the years analyzed. In the next report, you will see that several stores (Connecticut, Hawaii, and Idaho) were not open in 2022; therefore, following the pattern, they should not participate in the comparison.

Using the code in the pattern, the report removes the stores that were not open during the entire selected period, from the calculation.

The pattern is based on two items: a table (StoreStatus) containing information on whether a store is open or closed each year, and the Same Store Sales measure, which uses the information in StoreStatus to limit the calculation to stores open for the entire period. Here is the code of the pattern:

StoreStatus = 
VAR AllStores =
    CROSSJOIN (
        SUMMARIZE ( Sales, 'Date'[Year] ),
        ALLNOBLANKROW ( Store[StoreKey] )
    )
VAR OpenStores =
    SUMMARIZE (
        Sales,
        'Date'[Year],
        Sales[StoreKey]
    )
RETURN
    UNION (
        ADDCOLUMNS ( OpenStores, "Status", "Open" ),
        ADDCOLUMNS ( EXCEPT ( AllStores, OpenStores ), "Status", "Closed" )
    )

  
Same Store Sales = 
VAR OpenStores =
    CALCULATETABLE (
        FILTER (
            ALLSELECTED ( StoreStatus[StoreKey] ),     -- Filter the stores
            CALCULATE (                                -- where the Status is
                SELECTEDVALUE ( StoreStatus[Status] )  -- always OPEN
            ) = "Open"                                 --
        ),                                             --
        ALLSELECTED ( 'Date' )                         -- Over all selected years
    )
VAR FilterOpenStores =
    TREATAS (                 -- Use OpenStores to filter
        OpenStores,           -- Store[StoreKey]   
        Store[StoreKey]       -- by changing its data lineage
    )
VAR Result =
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS ( FilterOpenStores )
    )
RETURN
    Result 

If you want to implement the pattern in one of your models, you need to manually adapt the code to make it work with your specific table and column names. For example, it is likely that you want to perform a like-for-like comparison on other entities, like Product, Customer, or any other entity that is relevant to your business. Needless to say, adapting the code requires you to spend some time understanding how it works to avoid any mistakes.

The question is simple: Is there a better way to implement the pattern and reduce the implementation to simpler steps? Thanks to UDFs, the answer is yes.

The goals of using UDFs

By using UDFs, we strive to obtain several benefits:

• Creating functions that can be used in different semantic models with minimal effort.
• Centralizing the code, so that subsequent optimizations of the functions provide benefits to all the models using them.
• Sharing the functions with the community, to benefit from other people’s ideas, if any.

All these benefits can be achieved by simply converting the calculated table code and the measure code into functions, while paying close attention to the key distinction between model-dependent and model-independent functions. If you are not familiar with these terms, you can find more information in this article: Model-dependent and model-independent user-defined functions in DAX.

Basically, a model-dependent function knows about the structure of tables and columns in your model, whereas a model-independent function is completely agnostic about the structure of the model. A model-independent function needs to receive as parameters all the columns and tables required to perform its calculation.

At first glance, it may seem as though creating model-independent functions is a waste of time, a geeky thing with no real value. However, we are about to show you the opposite: thinking in terms of model-independent functions is the key to widening your view about functions and producing elegant and reusable code.

We could show the resulting code straight here. However, for educational purposes, it is more beneficial to show the process of moving from the original pattern to the generic UDF step by step.

Moving measures and calculated tables into functions

The first step is to replace the code in both the measure and the calculated table with functions. As you can see from the following code, we just moved the entire code from both the measure and the calculated table into two functions, with no parameters:

Local.StoreStatus = 
    ( ) =>
    VAR AllStores =
        CROSSJOIN (
            SUMMARIZE ( Sales, 'Date'[Year] ),
            ALLNOBLANKROW ( Store[StoreKey] )
        )
    VAR OpenStores =
        SUMMARIZE (
            Sales,
            'Date'[Year],
            Sales[StoreKey]
        )
    RETURN
        UNION (
            ADDCOLUMNS ( OpenStores, "Status", "Open" ),
            ADDCOLUMNS ( EXCEPT ( AllStores, OpenStores ), "Status", "Closed" )
        )

Local.SameStoreSales = 
    () =>
    VAR OpenStores =
        CALCULATETABLE (
            FILTER (
                ALLSELECTED ( StoreStatus[StoreKey] ),     -- Filter the stores
                CALCULATE (                                -- where the Status is
                    SELECTEDVALUE ( StoreStatus[Status] )  -- always OPEN
                ) = "Open"                                 --
            ),                                             --
            ALLSELECTED ( 'Date' )                         -- Over all selected years
        )
    VAR FilterOpenStores =
        TREATAS (                 -- Use OpenStores to filter
            OpenStores,           -- Store[StoreKey]   
            Store[StoreKey]       -- by changing its data lineage
        )
    VAR Result =
        CALCULATE (
            [Sales Amount],
            KEEPFILTERS ( FilterOpenStores )
        )
    RETURN
        Result

The function names start with Local to identify them as model-dependent functions. They are model-dependent because throughout the DAX code, we use column and table names that are present in the model. If we did not do that, then moving this code to another model, where table and column names are likely to differ, would invalidate the DAX code.

Creating model-independent functions

The next step is to split each of the functions into two: a model-independent function that does not reference any object in the semantic model, and a model-dependent function that calls the model-independent function by passing the required parameters.

This step is highly relevant because it separates model details from business logic. We execute it by replacing each and every column and table name in the UDF with a parameter. The model-independent functions are prefixed with DaxPatterns.LikeForLike, because this is the name we want to use for the library.

Let us start with the OpenStores function, which computes the table with the stores open each year:

DaxPatterns.LikeForLike.StoreStatus = 
    (
        dateYearNumberColumn : ANYREF,
        storeColumn : ANYREF,
        salesTable : ANYREF
    ) =>
    VAR AllStores =
        CROSSJOIN (
            SUMMARIZE ( salesTable, dateYearNumberColumn ),
            ALLNOBLANKROW ( storeColumn )
        )
    VAR OpenStores =
        SUMMARIZE (
            salesTable,
            dateYearNumberColumn,
            storeColumn
        )
    
    RETURN
        UNION (
            ADDCOLUMNS ( OpenStores, "Status", "Open" ),
            ADDCOLUMNS ( EXCEPT ( AllStores, OpenStores ), "Status", "Closed" )
        )

Local.StoreStatus = ( ) => 
    DaxPatterns.LikeForLike.EntityStatus ( 'Date'[Year], Sales[StoreKey], Sales )

The business logic is entirely included in the DaxPatterns.LikeForLike.StoreStatus function, which receives as parameters the columns and tables required to compute the table. This UDF would work in any model, because it is model-independent. However, for the function to be useful, it must be called with the right set of parameters. This step is accomplished by the Local.StoreStatus function, which just executes the mapping between the model and the model-independent function, without adding any business logic.

The calculated table definition just calls the model-dependent function:

StoreStatus = Local.StoreStatus ()

In a very similar way, we split the second function into two. The only additional detail is that the function accepts the measure to compute as an argument. Indeed, measure names such as Sales Amount are model-dependent details and cannot be part of a model-independent function.

DaxPatterns.LikeForLike.ComputeForSameStore = 
    (
        storeStatusKeyColumn : ANYREF,
        storeStatusStatusColumn : ANYREF,
        storeKeyColumn : ANYREF,
        dateTable : ANYREF,
        formulaExpr : EXPR
    ) => 
    VAR OpenStores =
        CALCULATETABLE (
            FILTER (
                ALLSELECTED ( storeStatusKeyColumn ),          -- Filter the entities
                CALCULATE (                                    -- where the Status is
                    SELECTEDVALUE ( storeStatusStatusColumn )  -- always OPEN
                ) = "Open"                                     --
            ),                                                 -- 
            ALLSELECTED ( dateTable )                          -- Over all selected years
        )
    VAR FilterOpenStores =
        TREATAS (                  -- Use OpenEntities to filter
            OpenStores,            -- the dimension store key    
            storeKeyColumn         -- by changing its data lineage
        )
    VAR Result =
        CALCULATE (
            formulaExpr,
            KEEPFILTERS ( FilterOpenStores )
        )
    RETURN
        Result

Local.ComputeForSameStore = 
    (
        formulaExpr : ANYREF
    ) =>
    DaxPatterns.LikeForLike.ComputeForSameStore ( 
        StoreStatus[StoreKey],
        StoreStatus[Status],
        Store[StoreKey],
        'Date',
        formulaExpr
    )

The Same Store Sales measure becomes much simpler, because it just needs to call Local.ComputeForSameStore with the right parameter:

  
Same Store Sales = Local.ComputeForSameStore( [Sales Amount] )

The advantage of creating model-independent functions is that their business logic can now be copied to any model. In each model, you can create the Local function to map the corresponding columns and table names, but most of the work needs no refactoring.

Creating more generic model-independent functions

Now that we have the two model-independent functions, we can observe that the business logic of the like-for-like pattern could work not only for stores, but for any entity – for example, it could work for products. If you carefully think about it, the only differences in terms of business logic between products and stores are the column and table names. However, because these details are now parameters of the function, we can create a more generic function that accepts any entity rather than just stores.

While producing this new version of the functions, we also observe that products are neither open nor closed. Stores can be open or closed, but products can be active or inactive. Therefore, we change the terminology in the code, shifting from the semantically meaningful Open and Closed to the more generic terms Active and Inactive.

Despite this looking like a small detail, it is not. Choosing the correct names reflects the clear intention of moving from the particular to the generic. The more generic our functions are, the more reusable they will be.

DaxPatterns.LikeForLike.ComputeForSameEntity = 
    (
        entityStatusKeyColumn : ANYREF,
        entityStatusStatusColumn : ANYREF,
        entityKeyColumn : ANYREF,
        dateTable : ANYREF,
        formulaExpr : EXPR
    ) => 
    VAR OpenEntities =
        CALCULATETABLE (
            FILTER (
                ALLSELECTED ( entityStatusKeyColumn ),          -- Filter the entities
                CALCULATE (                                     -- where the Status is
                    SELECTEDVALUE ( entityStatusStatusColumn )  -- always OPEN
                ) = "Active"                                    --
            ),                                                  -- 
            ALLSELECTED ( dateTable )                           -- Over all selected years
        )
    VAR FilterOpenEntities =
        TREATAS (                  -- Use OpenEntities to filter
            OpenEntities,          -- the dimension entity key    
            entityKeyColumn        -- by changing its data lineage
        )
    VAR Result =
        CALCULATE (
            formulaExpr,
            KEEPFILTERS ( FilterOpenEntities )
        )
    RETURN
        Result

DaxPatterns.LikeForLike.EntityStatus = 
    (
        dateYearNumberColumn : ANYREF,
        entityColumn : ANYREF,
        transactionTable : ANYREF
    ) =>
    VAR AllEntities =
        CROSSJOIN (
            SUMMARIZE ( transactionTable, dateYearNumberColumn ),
            ALLNOBLANKROW ( entityColumn )
        )
    VAR OpenEntities =
        SUMMARIZE (
            transactionTable,
            dateYearNumberColumn,
            entityColumn
        )
    
    RETURN
        UNION (
            ADDCOLUMNS ( OpenEntities, "Status", "Active" ),
            ADDCOLUMNS ( EXCEPT ( AllEntities, OpenEntities ), "Status", "Inactive" )
        )

Computing like-for-like at the Product level

Now that the model-independent functions are entirely agnostic about both the model details and the entity details, we can implement the like-for-like pattern at the Product level by just creating two local functions that instantiate the parameters of the model-independent functions appropriately:

ProductStatus = Local.ProductStatus ()

Local.ProductStatus = 
    () => 
    DaxPatterns.LikeForLike.EntityStatus ( 'Date'[Year], Product[ProductKey], Sales )

Local.ComputeForSameProduct = 
    (
        formulaExpr : ANYREF
    ) =>
    DaxPatterns.LikeForLike.ComputeForSameEntity ( 
        ProductStatus[ProductKey],
        ProductStatus[Status],
        Product[ProductKey],
        'Date',
        formulaExpr
    )

Same Product Sales = Local.ComputeForSameProduct ( [Sales Amount] )

Using the Same Product Sales measure, users can easily perform like-for-like comparison at the Product level rather than at the Store level.

The most relevant point is that there is no need to learn or understand the implementation details of the pattern. For a user or developer to implement the pattern, it is sufficient to know how to pass the correct parameters to model-independent functions, thereby minimizing friction in subsequent implementations.

Finally, if new optimizations or feature functions are introduced in DAX and a code review is needed, it suffices to update the model-independent functions, while ensuring that all measures that use the model-dependent functions benefit from the new features. The code is centralized in functions that are agnostic to model details, clearly separating the business logic from local details.

Conclusions

User-defined functions are a great feature in DAX. However, they must be used correctly to maximize benefits for developers. Whenever you develop code, you always start with a measure, because it is easy to modify and debug. However, once the code runs fine, you should always ask yourself whether it can be moved to a more generic measure so you can use the same logic elsewhere. During this process, try to think in terms of model-dependent and model-independent functions.

Not every measure or piece of DAX code will benefit from this method. Nonetheless, improving your ability to abstract from your context and to reason at a higher level will make your DAX code more elegant and easier to maintain.

Let’s see how companies manage 𝗳𝗹𝗲𝘅𝗶𝗯𝗹𝗲 𝘄𝗼𝗿𝗸𝘀𝗽𝗮𝗰𝗲 𝗮𝗹𝗹𝗼𝗰𝗮𝘁𝗶𝗼𝗻 with 𝗦𝘆𝗻𝗼𝗽𝘁𝗶𝗰 𝗣𝗮𝗻𝗲𝗹 in Power BI, mapping office seats, verifying availability, moving across floors, and connecting the layout to live reservation data using custom SVG floor plans derived from AutoCAD files.

𝗦𝘁𝗲𝗽𝘀 𝗶𝗻 𝘁𝗵𝗲 𝗰𝗮𝘀𝗲 𝘀𝘁𝘂𝗱𝘆:

• ConvertDWG office plans into SVG
• Map seat/workstation IDs to data in the model
• Manage multi-floor layouts with dynamic map selection
• Enable tooltips, interactions, and linked visuals

𝗥𝗲𝗮𝗱 𝘁𝗵𝗲 𝗳𝘂𝗹𝗹 𝗰𝗮𝘀𝗲 𝘀𝘁𝘂𝗱𝘆 𝗵𝗲𝗿𝗲:
https://okviz.com/usecase/workspace-synoptic-panel/

𝗧𝗿𝘆 𝗦𝘆𝗻𝗼𝗽𝘁𝗶𝗰 𝗣𝗮𝗻𝗲𝗹
https://okviz.com/synoptic-panel/

We released a new course for SQLBI+ subscribers: Understanding Visual Calculations in DAX. This is not an introduction to visual calculations for users. The goal is to explain the details of implementing visual calculations for experienced DAX developers, including new concepts such as visual shape and visual context.

The training is reference material for model and report developers who want to understand how visual calculations work internally, for troubleshooting, and to build better semantic models that delegate to visual calculations operations that do not belong in a centralized semantic model, such as most formatting-related operations specific to a report.

The course includes over 2.5 hours of videos and a white paper in PDF format that should be used as companion content. The first draft of the whitepaper was released in 2024; we have now finalized and updated the document to align with the latest updates to visual calculation functions. The video course has been produced very recently and includes more practical examples of the user interface, whereas the white paper has the same structure and content but does not provide user interface instructions.  Combining both resources is usually the best idea!

Visual Calculations is a Power BI feature that is not available in other client tools, such as Excel. Other content we produced has an introductory description of visual calculations: the latest book (The Definitive Guide to DAX, 3rd edition) and the video course (Mastering DAX: the third edition will be available by the end of March 2026). This SQLBI+ content goes deeper and complements the DAX book and video course.

The course is organized into the following modules:

• Introducing visual calculations
  • Visual calculations and window functions
• Understanding the visual shape
  • Visual calculations are new columns in the virtual table
  • Understanding densification
• Understanding the visual context
  • Understanding EXPAND, COLLAPSE, EXPANDALL, and COLLAPSEALL
  • Navigating the lattice of the virtual table
  • Accessing the virtual table through ROWS and COLUMNS
  • Understanding the unique behaviors of the visual context
  • Understanding ROWS and COLUMNS used together
  • Understanding reset and direction
  • Using CALCULATE in visual calculations
• Understanding blank handling
• Understanding visual calculation functions
  • Understanding PREVIOUS, NEXT, FIRST, LAST
  • Understanding LOOKUP, LOOKUPWITHTOTALS, and auto-expand
  • Understanding RUNNINGSUM
  • Understanding ISATLEVEL
  • Understanding MOVINGAVERAGE
  • Understanding RANGE
• Visual calculations and calculation groups
• Visual calculation examples
  • Computing the moving average over the last six months
  • Computing growth over the same period last year
  • Comparing sales over the average of the siblings
  • Computing year-to-date
  • Computing the Pareto / ABC Class
• Conclusions

Stay tuned for new SQLBI+ content coming later in 2026, and thank you to all SQLBI+ subscribers for their support!

How to use the TOJSON and TOCSV functions to inspect the content of intermediate table variables when debugging a DAX measure.

This article describes how to use the TOJSON and TOCSV functions to inspect the content of intermediate table variables when debugging a DAX measure.

In a previous article, Debugging DAX measures in Power BI, we described several techniques to find errors in a DAX formula. The most basic approach, one that requires no external tools, is to temporarily change the RETURN statement of a measure so that it returns the value of an intermediate variable instead of the final result. When the variable contains a scalar value such as a number or a string, this is straightforward: you change the RETURN, observe the result in the report, and compare it with your expectations. We see this in the following example:

Delta Avg 2 =
VAR CurrentValue = [Avg Transaction]
VAR ReferenceValue = CALCULATE ( [Avg Transaction], ALLSELECTED ( ) )
VAR CurrentDelta = CurrentValue - ReferenceValue
VAR Result = DIVIDE ( CurrentDelta, ReferenceValue )
RETURN ReferenceValue

That technique remains fully valid: read the previous article if you are not familiar with it yet.

The situation becomes more complex when the variable you want to inspect contains a table. A measure can only return a scalar value, so you cannot simply return a table variable. In the previous article, we used CONCATENATEX to convert a table into a string by manually specifying which columns to include and how to format them. However, this approach requires writing a specific CONCATENATEX expression for each table you want to inspect, choosing the columns, defining the separator, and adjusting the format every time. This is time consuming, especially during an active debugging session where you may need to inspect several variables in quick succession.

A full-featured DAX debugger is available in a commercial tool, Tabular Editor 3, which provides step-by-step execution and variable inspection. Another tool you may find useful is DAX Studio, which is free. However, not all developers have access to these tools, and even those who do sometimes need a quick, lightweight technique that works directly in Power BI without opening another tool.

TOJSON and TOCSV offer exactly that. These two functions convert a table into a string (JSON or CSV format, respectively) without requiring you to specify the columns or the format. You pass the table variable, and the function produces a complete textual representation of its content. The result is a scalar string that a measure can return and that a visual can display in a report.

It is important to highlight that debugging a measure often requires inspecting its value in a specific filter context. For example, you might notice that a matrix shows an incorrect value for a specific cell, such as a particular combination of year and product category, or for one of the subtotals. In that case, displaying the debugging output in a card visual would not be sufficient, because a card only shows the value in the filter context of the visual, so you should use external slicers and the filter pane to reproduce the filters combination to investigate. A more effective approach is to use the debugging measure directly in a matrix, so that you can inspect the content of the table variable within the filter context where the incorrect result appears. This is a typical scenario: the total does not correspond to the expected value, and you need to see what the intermediate table contains for that specific cell.

This article describes the syntax and practical use of TOJSON and TOCSV for this purpose. We illustrate the technique with several examples and discuss the limitations you should be aware of.

Inspecting table variables in a measure

Consider the following scenario. A measure builds an intermediate table in a variable (for example, using ADDCOLUMNS, FILTER, or SUMMARIZE) and then aggregates it to produce a final result, but the numbers are not what you expect. Here is the measure we use in this example, with two errors that are highlighted in the comments:

Date MAX Start = 
VAR TargetAmount = [Max Daily Amount]
RETURN IF (
    NOT ISBLANK ( TargetAmount ),
    VAR DailySales = 
        ADDCOLUMNS ( 
            VALUES ( 'Date'[Date] ),
            "@DayAmount", [Sales Amount] 
        )
    VAR TargetDatesAmount =
        FILTER ( 
            DailySales, 
            -- Error 1: it should be TargetAmount instead of [Max Daily Amount]
            [@DayAmount] == [Max Daily Amount]
                && NOT ISBLANK ( [@DayAmount] ) 
        )
    VAR TargetDates =
        SELECTCOLUMNS ( 
            -- Error 2: it should be TargetDatesAmount instead of DailySales
            DailySales,
            'Date'[Date]
        )
    VAR Result =
        IF ( 
            COUNTROWS ( TargetDates ) = 1,
            FORMAT ( TargetDates, "mm/dd/yyyy" ),
            "Too many dates"
        )
    RETURN Result
)

The Date MAX Start measure should return the date when the Max Daily Amount was achieved, but it is not working.

We suspect that the intermediate table might contain unexpected rows or incorrect values, but we cannot see it directly – in the wild, you will not have the comments saying where the error is! We need a way to peek inside that variable, in the specific filter context where the result is wrong. The following measure inspects the TargetDatesAmount variable by using CONCATENATEX:

Date MAX ConcatenateX = 
VAR TargetAmount = [Max Daily Amount]
RETURN IF (
    NOT ISBLANK ( TargetAmount ),
    VAR DailySales = 
-- ...
-- skipping implementation that is identical to Date MAX Start
-- ...
    RETURN 
        CONCATENATEX ( 
            TOPN ( 10, TargetDatesAmount ), 
            FORMAT ( 'Date'[Date], "mm/dd/yyyy" )
                & ": " & [@DayAmount] & ", "
        )
)

The result of Date MAX ConcatenateX shows that the values in TargetDatesAmount are not being filtered.

We would see the same result if we iterated DailySales instead of TargetDatesAmount in CONCATENATEX, which indicates that the filter is ineffective. As described in the measure comments, we should replace the Max Daily Amount comparison with TargetAmount in the FILTER iteration. Similarly, by changing the CONCATENATEX iteration, we can see that TargetDates does not have the filtered rows once we fix the first bug, because we iterate over DailySales rather than TargetDatesAmount, as highlighted in the comments. Inspecting the variables helps us identify and fix these errors.

As we described in the introduction, the CONCATENATEX approach works, but it requires writing a custom expression for each table you want to inspect. With TOJSON and TOCSV, you can achieve the same result with a single function call, and there is no need to specify the columns.

Converting a table to a string with TOCSV

TOCSV converts a table into a string, formatted as comma-separated values. Because the result is a string, it can be returned by a measure and displayed in a report visual.

The syntax of TOCSV is:

TOCSV ( <Table>, [<MaxRows>], [<Delimiter>], [<IncludeHeaders>] )

The first argument is the table to convert. The optional MaxRows parameter controls how many rows are included in the output; its default value is 10. The Delimiter parameter specifies the column separator (the default is a comma), and IncludeHeaders determines whether the first line contains column names (the default is TRUE). To inspect a table variable, we temporarily change the RETURN expression of the measure so that it returns the TOCSV output instead of the original result, thus reducing the code from 5 lines using CONCATENATEX to just one line:

Date MAX TOCSV = 
VAR TargetAmount = [Max Daily Amount]
RETURN IF (
    NOT ISBLANK ( TargetAmount ),
    VAR DailySales = 
-- ...
-- skipping implementation that is identical to Date MAX Start
-- ...
    RETURN 
        TOCSV ( TargetDatesAmount, 3 )
)

The output is a plain text representation of the table content. Each row appears on a separate line, and columns are separated by the chosen delimiter. This is typically enough to verify whether the table contains the expected rows and values. We reduced the output to three rows to limit the vertical space used in the following screenshot.

Converting a table to a string with TOJSON

TOJSON works similarly to TOCSV, but it produces a JSON-formatted string instead of a string formatted as comma-separated values. The syntax is simpler:

TOJSON ( <Table>, [<MaxRows>] )

The only optional parameter is MaxRows, which defaults to 10, the same as TOCSV. TOJSON does not support parameters for delimiters or header control because the JSON format has a fixed structure:

Date MAX TOJSON = 
VAR TargetAmount = [Max Daily Amount]
RETURN IF (
    NOT ISBLANK ( TargetAmount ),
    VAR DailySales = 
-- ...
-- skipping implementation that is identical to Date MAX Start
-- ...
    RETURN 
        TOJSON ( TargetDatesAmount, 3 )
)

The JSON output contains three elements: a “header” array with column names, a “rowCount” field indicating the total number of rows in the original table (regardless of MaxRows), and a “data” array with the actual row values.

The “rowCount” field in the JSON output is particularly useful: it tells you how many rows the table actually contains, even when only the first few rows are displayed, as is the case in the screenshot above. This information is not available in the TOCSV output.

Choosing between TOCSV and TOJSON

Both functions serve the same purpose in a debugging context. The choice between them largely comes down to personal preference and readability.

TOCSV produces a more compact output that is easier to read at a glance, especially for small tables with a few columns. TOJSON produces a more structured output that includes the row count and is easier to parse programmatically. In our experience, TOCSV is more practical for quick visual inspection during debugging. TOJSON is more useful when you need to know the total row count of the original table, or when you plan to copy the output into another tool for further analysis.

Another difference between the two functions is when the table is empty: TOCSV always return a result, showing the column names in that case, whereas TOJSON does not return any result, which makes it hard to investigate the content of the table when it is empty. (Thank you Jan S for reporting it!)

Understanding the MaxRows limitation

Both TOCSV and TOJSON default to returning only 10 rows. This is an intentional design choice: converting a large table to a string can produce a very long text, which is both hard to read and potentially expensive to compute. The default limit of 10 rows keeps the output manageable.

For debugging purposes, 10 rows are often sufficient. When we are verifying the structure of an intermediate table (like checking which columns are present, whether the values look correct, and whether unexpected rows appear), the first few rows usually provide enough evidence to identify the problem. We used only 3 rows in the example to maximize the visibility of the screenshots in the article, but we usually keep the default of 10.

However, there are scenarios where 10 rows are not enough. If the issue you are investigating only manifests further down in the table, or if you need to verify the complete content, you can increase the MaxRows parameter.

Be mindful that increasing MaxRows significantly can produce a very long string. A visual may truncate the output, and the measure evaluation can become slower. For most debugging sessions, a value between 10 and 20 is a reasonable range. If you need to inspect a table with hundreds or thousands of rows, consider using DAX Studio or Tabular Editor 3 instead, which are better suited for exploring large datasets.

It is also important to note that the sort order of the rows returned by TOCSV and TOJSON cannot be controlled directly. The functions return rows in whatever order the engine provides, which may not be deterministic. Indeed, the previous example comparing TOCSV and TOJSON outputs shows three days in January (January 3^rd, 4^th, and 11^th, respectively) that are not the first three days available in the month. If row order is important for your investigation, you should sort the table explicitly before passing it to TOCSV or TOJSON. For example, you might wrap it in a TOPN expression with the desired sort order:

Date MAX TOCSV Sorted = 
VAR TargetAmount = [Max Daily Amount]
RETURN IF (
    NOT ISBLANK ( TargetAmount ),
    VAR DailySales = 
-- ...
-- skipping implementation that is identical to Date MAX Start
-- ...
    RETURN 
        TOCSV ( 
            TOPN ( 3, TargetDatesAmount, 'Date'[Date], DESC )
        )
)

Date MAX TOJSON Sorted = 
VAR TargetAmount = [Max Daily Amount]
RETURN IF (
    NOT ISBLANK ( TargetAmount ),
    VAR DailySales = 
-- ...
-- skipping implementation that is identical to Date MAX Start
-- ...
    RETURN 
        TOJSON ( 
            TOPN ( 3, TargetDatesAmount, 'Date'[Date], DESC )
        )
)

This way, the result includes the first three days with sales for each month, even though they are not sorted within the output produced by TOCSV and TOJSON (you can control the order in CONCATENATEX, which requires additional parameters).

A practical debugging workflow

Let us describe a typical debugging workflow that uses TOCSV to inspect intermediate variables.

Suppose we are developing a measure that computes a result through several steps, each stored in a variable. The result is not what we expect. Rather than guessing which step is wrong, we can systematically inspect each table variable by temporarily changing the RETURN statement. In the previous examples, we have seen several approaches to investigating the TargetDatesAmount variable by using CONCATENATEX, TOCSV, and TOJSON. Looking at the content produced, we locate and fix the first error, now testing the following measure:

Date MAX Step 2 = 
VAR TargetAmount = [Max Daily Amount]
RETURN IF (
    NOT ISBLANK ( TargetAmount ),
    VAR DailySales = 
        ADDCOLUMNS ( 
            VALUES ( 'Date'[Date] ),
            "@DayAmount", [Sales Amount] 
        )
    VAR TargetDatesAmount =
        FILTER ( 
            DailySales, 
            -- Error 1 fixed
            [@DayAmount] == TargetAmount
                && NOT ISBLANK ( [@DayAmount] ) 
        )
    VAR TargetDates =
        SELECTCOLUMNS ( 
            -- Error 2: it should be TargetDatesAmount instead of DailySales
            DailySales,
            'Date'[Date]
        )
    VAR Result =
        IF ( 
            COUNTROWS ( TargetDates ) = 1,
            FORMAT ( TargetDates, "mm/dd/yyyy" ),
            "Too many dates"
        )
    RETURN 
        TOCSV ( TargetDatesAmount, 3 )
)

With this version of the measure, the TargetDatesAmount variable now has only one row per month and year, which indicates that the filter is working correctly.

At this point, if we look at the result, we still see the same incorrect “Too many dates” sentence we had in the beginning. We must investigate more, so we return TOCSV applied to the next variable, TargetDates:

Date MAX Step 3 = 
VAR TargetAmount = [Max Daily Amount]
RETURN IF (
    NOT ISBLANK ( TargetAmount ),
    VAR DailySales = 
-- ...
-- skipping implementation that is identical to Date MAX Step 2
-- ...
    RETURN 
        TOCSV ( TargetDates, 3 )
)

The result no longer includes Sales Amount computed for each date. However, we see three dates instead of one in each cell. If we used TOJSON, we would see a larger “rowCount” in each cell. The filter we fixed in TargetDatesAmount does not apply to the next step. Why? By reviewing the code more closely, we notice that we referenced DailySales again instead of TargetDatesAmount when iterating over the table in SELECTCOLUMNS. We fix this reference and we test the code again:

Date MAX Step 4 = 
VAR TargetAmount = [Max Daily Amount]
RETURN IF (
    NOT ISBLANK ( TargetAmount ),
    VAR DailySales = 
        ADDCOLUMNS ( 
            VALUES ( 'Date'[Date] ),
            "@DayAmount", [Sales Amount] 
        )
    VAR TargetDatesAmount =
        FILTER ( 
            DailySales, 
            -- Error 1 fixed
            [@DayAmount] == TargetAmount
                && NOT ISBLANK ( [@DayAmount] ) 
        )
    VAR TargetDates =
        SELECTCOLUMNS ( 
            -- Error 2 fixed
            TargetDatesAmount,
            'Date'[Date]
        )
    VAR Result =
        IF ( 
            COUNTROWS ( TargetDates ) = 1,
            FORMAT ( TargetDates, "mm/dd/yyyy" ),
            "Too many dates"
        )
    RETURN 
        TOCSV ( TargetDates, 3 )
)

At this point, the TargetDates variable has only one row per cell.

We can revert to Result after the RETURN statement and see the correct report:

Date MAX Fixed = 
VAR TargetAmount = [Max Daily Amount]
RETURN IF (
    NOT ISBLANK ( TargetAmount ),
    VAR DailySales = 
-- ...
-- skipping implementation that is identical to Date MAX Step 4
-- ...
    RETURN Result
)

To recap, the process is the following: we replace the RETURN expression with TOCSV (or TOJSON) of the variable to inspect. We check the output, confirm whether that variable contains the expected content, and then move on to the next variable. Once we identify the step that produces the incorrect result, we can focus our analysis on that specific part of the measure.

After debugging is complete, we restore the original RETURN expression. The TOCSV or TOJSON call was never part of the measure logic: it was only a temporary lens used to inspect the calculation.

Conclusions

TOJSON and TOCSV are simple functions with a specific and very practical use in everyday DAX development: they let us convert a table into a string, so that we can return it from a measure and inspect its content directly in a report visual. This makes them valuable debugging tools when we need to verify the content of intermediate table variables.

The default limit of 10 rows is adequate for most debugging scenarios, but it can be increased when needed. Be mindful that very large outputs can be difficult to read and potentially slow to compute. For large-scale data exploration, dedicated tools such as DAX Studio and Tabular Editor 3 remain the better options.

The debugging technique itself is straightforward: temporarily replace the RETURN expression of your measure with a TOCSV or TOJSON call targeting the variable you want to inspect. Check the output, identify the problem, fix the measure, and restore the original RETURN. It is an effective workflow that requires no external tools and works entirely within Power BI.