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 a shortcut that forces the VAL passing mode and accepts any scalar value. 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”. Indeed, writing ANYREF without EXPR, or EXPR without ANYREF, 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.

How to 𝗱𝗶𝘀𝗽𝗹𝗮𝘆 𝗶𝗻𝗷𝘂𝗿𝘆 𝗱𝗮𝘁𝗮 𝗼𝗻 𝗮 𝗵𝘂𝗺𝗮𝗻 𝗯𝗼𝗱𝘆 𝗦𝗩𝗚 𝗺𝗮𝗽 by using 𝗦𝘆𝗻𝗼𝗽𝘁𝗶𝗰 𝗣𝗮𝗻𝗲𝗹 in Power BI. This video shows how to import an SVG, map each body area to fields in your model, and make the visual respond to filters and selections.

𝗜𝗻𝘀𝗶𝗱𝗲 𝘁𝗵𝗲 𝗰𝗮𝘀𝗲 𝘀𝘁𝘂𝗱𝘆, 𝘆𝗼𝘂 𝘄𝗶𝗹𝗹 𝗳𝗶𝗻𝗱:
– How Synoptic Panel binds SVG elements to your dataset
– How filters update the visualization in real time
– How to prepare or convert SVG drawings
– When to automate the mapping process
– Why this technique applies to many scenarios, such as medical data, safety reporting, ergonomics, and more

𝗙𝘂𝗹𝗹 𝗰𝗮𝘀𝗲 𝘀𝘁𝘂𝗱𝘆 𝗮𝗻𝗱 𝗶𝗺𝗽𝗹𝗲𝗺𝗲𝗻𝘁𝗮𝘁𝗶𝗼𝗻 𝗱𝗲𝘁𝗮𝗶𝗹𝘀
https://okviz.com/usecase/injuries-synoptic-panel/

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

Share your thoughts or use cases in the comments: we’d love to see what you build!

The new DATEADD parameters in DAX add greater flexibility to the calendar-based time intelligence in Power BI. Learn how they work!

The new calendar-based time intelligence functions offer greater flexibility than the classic time intelligence functions. This article describes the DATEADD parameters for controlling different granularity shifts.

The primary reason to adopt the new calendar-based time intelligence in Power BI is its flexibility (read more on Introducing calendar-based time intelligence in DAX). Classic time intelligence functions work out of the box and deliver meaningful results in most scenarios. However, to do so, they make assumptions about the calendar structure and the desired outcomes. Sometimes, the choices are not aligned with the user requirements, and developers need to author their own time intelligence calculations.

The new calendar-based time intelligence functions provide greater flexibility by allowing developers to configure parameters that drive the internal algorithms to meet diverse requirements. Using these parameters requires a precise understanding of the scenario for which they were built, which requires some attention to detail.

Most of the article focuses on understanding the complex scenarios you may encounter when performing time-intelligence calculations. Choosing the right set of arguments is a very simple step if you understand the scenario well, but it may be a frustrating experience if you just try these arguments without having already gained the required knowledge. In this article, we focus on a specific function: DATEADD, which shifts time intervals. Be mindful that DATEADD is internally used by many time intelligence calculations; therefore, the concepts explained for DATEADD apply to other time intelligence functions, like, for example, DATESINPERIOD.

Comparing months with different lengths

DATEADD shifts the original selection (the current filter context) back and forth by the specified number of intervals, where an interval can be DAY, WEEK, MONTH, QUARTER, or YEAR. For example: DATEADD ( ‘Gregorian’, -6, MONTH ) shifts the current filter context six months back in time. The interval is MONTH, -6 is the number of months to shift, Gregorian is the calendar to use.

Let us start investigating the scenario with a simple measure that computes the sales in the previous month, using classic time intelligence:

Sales PM Date = 
CALCULATE (
    [Sales Amount],
    DATEADD ( 'Date'[Date], -1, MONTH )
)

Using the measure in a report shows that the sales of January 2024 are reported correctly in February 2024.

Despite it looking natural and correct, the result requires a deeper understanding. February 2024 contains 29 days, because 2024 is a leap year. January 2024 contains 31 days. The report is currently comparing two periods of different lengths. We are so used to variable-length months that we seldom worry about the fact that the we compare entities that are different (in this case in number of days). For the purposes of this article, the key point is that the two months have different lengths.

The same scenario happens when comparing March 2024 with February 2024 or – in general – whenever we compare two months with a different number of days.

It is important to note that right now, the granularity of the values we are inspecting is the month, and the period used for the shift is also the month. When the granularity of the selection and the granularity of the period used for the shift are the same, numbers are easy to read.

If we expand the matrix at the day level, the selection granularity becomes the day, while the period-to-shift granularity is the month. The measure still reports correct results: each day in February is shifted back to the same day in January. As expected, Sales PM Date on the third of February reports the sales of the third of January.

The scenario quickly becomes more complex i\f, rather than looking at the beginning of the month, we go to the end of February, namely the 29^th of February.

The 29^th of February correctly shows the value of sales for the 29^th of January. However, January includes two additional days with sales: the 30^th and 31^st, which are not shown in February.

Let us repeat this simple concept: January 2024 has 31 days, with a total Sales Amount of 188,419.28. February 2024 contains only 29 days; Sales PM Date displays the exact sales amount of the corresponding day in the previous month. Two dates and their corresponding sales (9,534.40 and 9,445.42 for the 30^th and 31^st of January) are missing. Still, the total is the same: 188,419.28. In other words, the total shown is not the sum of the displayed daily rows.

Be mindful: this is not an incorrect behavior. It is a precise choice made by DAX to solve a problem that has no clear and simple solution: comparing two months with a different number of days, and showing the daily values at the same time, requires a compromise because we need to pack the total of $188,419.28 into only 29 rows rather than the original 31.

The problem is not new; it has always been there. Classic time intelligence functions addressed this scenario differently. The same formula to compute the sales in the previous month can be written using the new calendar-based time intelligence:

Sales PM Cal = 
CALCULATE (
    [Sales Amount],
    DATEADD ( 'Gregorian', -1, MONTH )
)

Quite surprisingly, the result is different.

As you can see, on the 29^th of February, the calendar-based time intelligence calculation returns 19,132.33 rather than 152.51. The difference between these two amounts is the exact sum of the two additional days that exist in January and that do not exist in February. In other words, the selection of one day in February (Feb 29^th) is transformed into the selection of three days in January (Jan 29^th, 30^th and 31^st).

This behavior is neither better nor worse: it is a different choice, and it is just a default choice that can be modified with additional parameters. The advantage is that the rows sum to the month total; the disadvantage is that day-level comparisons are inaccurate because we are comparing one day against three. As we anticipated, solving the problem requires a compromise, and the calendar-based time intelligence chose a different solution compared to the classic time intelligence.

This is a key difference between calendar-based time intelligence and classic time intelligence: in classic time intelligence, the DAX engine defines the behavior, and there is no way to change it. In calendar-based time intelligence, a set of additional parameters allows developers to specify how to handle time periods of varying lengths.

Understanding Extension and Truncation

Now that we have a clearer picture of the scenario, it is time to be more precise and define the rules of the game. DATEADD shifts the original selection (the current filter context) back and forth by the specified number of intervals, where an interval can be DAY, WEEK, MONTH, QUARTER, or YEAR.

In our example, we shifted Feb 29^th one month back; the original selection is Feb 29^th, the interval is MONTH, and the shift is -1.

When the original selection is at a finer grain than the interval, as in the examples we have shown so far, DATEADD uses the same-distance-from-parent technique. Because Feb 29^th is the 29^th day in February, it is shifted to the 29^th day in January, which (surprise, surprise!) is the 29^th of January. This algorithm works fine as long as the periods have the same length. If the periods have different lengths (like months in a Gregorian calendar), there are two possible scenarios, depending on which period is longer.

• Extension: The resulting period is larger than the original period. For example, shifting the 29^th of February one month back may return only one day (the 29th of January) or multiple days (from the 29th to the 31st of January), depending on the current end date of the selection. In other rows, the current selection may be extended to a larger selection.
• Truncation: The resulting period is shorter than the original period. For example, shifting the 30^th of March back one month makes it impossible to find the 30^th day of February. In this case, it is possible to produce either a blank (there is no 30^th of February) or to return the last day of February, anchoring the result to the end of the period.

Extension and Truncation are indeed the two additional parameters of DATEADD to handle these two scenarios.

Extension is effective when comparing the current period with a larger one; it changes only how the last day of the current period is shifted. Extension can be one of three values:

• EXTENDING: this is the default behavior. If the current selection contains the last day of the interval (as is the case for the 29^th of February), DATEADD returns all the dates from the 29^th of January up to the end of January. In this case, a single date is transformed into a selection of multiple dates.
• PRECISE: The 29^th of February is shifted back to the 29^th of January. The 30^th and 31^st of January are not shown in the report, because there is neither a 30^th nor a 31^st of February. This is the same behavior as the classic time intelligence.
• ENDALIGNED: the 29^th of February is shifted back to the 31^st of January (end of periods are aligned). In this scenario, neither the 29^th nor the 30^th of January are shown. ENDALIGNED can be useful in some fancy scenarios, as we are about to show later.

In the following report you can see the dates returned by DATEADD. You can see that the 29^th of February is shifted back to three different periods, depending on the value used for the Extension argument.

Truncation is effective when the current period is larger than the resulting period, for example, when moving back one month from March to February. Truncation changes how the last days of the current period (those that do not exist in the previous month) are shifted. Truncation can be:

• ANCHORED: the 29^th, 30^th, and 31^st of March are shifted back to the 29^th of February. In other words, all the dates from the current month, that do not exist in the previous month are routed to the last date of the previous month, possibly repeating the same value multiple times.
• BLANKS: the 30^th and 31^st of March result in BLANK, because there is no corresponding date in the previous month. One important note: if EndAligned is specified for Extension, it also applies to Truncation, thus moving the last day to the last day of the previous period.

In the following diagram, you can see the effect of different Truncation and Extension values:

Choosing the correct set of values for Extension and Truncation is up to the developer. There is no “correct” solution: each has its own advantages and disadvantages that
need to be discussed with users to produce useful, easily-understandable reports.

A more practical scenario

We want to compute a moving average over the last six months. We create two versions of the same calculation: one using classic time intelligence and one using calendar-based time intelligence. The two versions yield different results; we need to understand the problem and identify the correct parameter to use. As is often the case, understanding the issue is much harder than fixing the problem.

Here are the two measures:

Moving Average Calendar =
CALCULATE (
    DIVIDE (
        [Sales Amount],
        COUNTROWS ( SUMMARIZE ( Sales, 'Date'[Year Month Number] ) )
    ),
    DATESINPERIOD ( 'Gregorian', MAX ( 'Date'[Date] ), -6, MONTH )
)

Moving Average Classic =
CALCULATE (
    DIVIDE (
        [Sales Amount],
        COUNTROWS ( SUMMARIZE ( Sales, 'Date'[Year Month Number] ) )
    ),
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -6, MONTH )
)

As you can see, the two measures differ only in how they use DATESINPERIOD. When used in a matrix, the two measures always provide the same result, with some noticeable exceptions.

Two months in 2023 show a difference. Given the significant increase in sales in Jun 2023, the Classic version appears to produce the correct result. The Calendar version produces a smaller value, which is inconsistent with the increase in sales.

Let us elaborate on what happened in Jun 2023. DATESINPERIOD needs to shift the end of the month back by 6 months. To do so, it moves the provided date back by 6 months, then starts on the next day. The end of June 2023 is the 30^th of June. Moving the 30^th of June 6 months back produces the 30^th of December 2023. DATESINPERIOD considers valid dates from the 31^st of December (the day after the 30^th) through to the 30^th of June.

As you may already see, there is an error. DATESINPERIOD should start from the first of January, not from the 31^st of December. However, because June has 30 days and December has 31, DATESINPERIOD (which internally uses DATEADD) returns the wrong start of the shifted period.

The error has two side effects: it adds an extra day and, worse, it counts an extra month (there are seven months from December to June, compared to the six months required by our calculation).

Recapping the considerations: when a month with 30 days is shifted back to a month with 31 days, our formula adds an extra day and month. We need to make sure that the last day of the month is moved back to the last day of the corresponding month. As it turns out, EndAligned is the algorithm to use. Indeed, DATESINPERIOD accepts the Extension parameter as an additional argument, thus allowing developers to choose between PRECISE and ENDALIGNED.

Changing the measure to the following definition using ENDALIGNED makes the measure compute the correct result:

Moving Average Calendar = 
CALCULATE (
    DIVIDE (
        [Sales Amount],
        COUNTROWS ( SUMMARIZE ( Sales, 'Date'[Year Month Number] ) )
    ),
    DATESINPERIOD ( 'Gregorian', MAX ( 'Date'[Date] ), -6, MONTH, ENDALIGNED )
)

Conclusions

Classic time intelligence functions work out of the box because the complexity of the time intelligence calculations is hidden in the internal logic. Classic time intelligence relies on the assumption that the calendar under consideration is the standard Gregorian calendar.

The new calendar-based time intelligence functions do not rely on calendar knowledge; developers must tag columns and provide the required metadata for these functions to work. As a consequence, some of the automated behavior that simplifies the lives of developers had to be removed, and calendar-based time intelligence calculations may produce unexpected results if parameters are not properly aligned with the report requirements.

Your task, as a wise DAX developer, is to know the details about how these functions work and provide the correct set of arguments and/or metadata to the DAX engine to provide the correct results.

Calendar-based time intelligence gives developers greater power, which in turn requires more responsibility and control.

How to show in a single column of a matrix information from multiple columns in a business entity or transaction.

This article shows how to create a DAX measure that displays information from multiple columns in a business entity or transaction, into a single column of a matrix.

A common challenge in Power BI reporting is how to display several pieces of information about a single item (such as sales transactions, product details, or customer details) without dedicating a separate column to each attribute. Using individual columns for each detail can consume space, especially for fields that are often empty. This article explores techniques to consolidate multiple fields from a business entity or transaction into a single column in a matrix visual, thus presenting transaction details in a space-efficient way.

Dealing with multiple columns

Presenting detailed information about a business entity often requires displaying values from multiple columns, which can take up too much space in Power BI reports and make them harder to read. Indeed, a consequence of there being too many columns is that we now have to use a horizontal scrollbar. So instead of using a separate column for each detail, it would be better to combine relevant fields and show them together in a single column, especially in a matrix visual. This section introduces the challenge and presents approaches to representing multiple attributes more efficiently, thus leading to advanced solutions using DAX measures in the next sections.

Including multiple columns in a table

To display transaction details across multiple columns in a table, we could create a separate table visual that shows the relevant fields for the selected item. This table must be synchronized with the matrix visual, typically by selecting an order number or unique identifier in the matrix, which then filters the table to display the associated details. In this example, the Order Number selection displays the customer details in a separate table.

While this approach enables you to show all the details, it consumes additional space on the report canvas because you need both the matrix and the table visual. To partially address the space issue, we created a visual calculation that collapses multiple columns into a single Details column. This Details column can display concatenated information from several fields, which saves space (as illustrated by the green arrow). However, this solution comes with limitations: the synchronization between the matrix and the table is manual, and it requires users to click on the desired item in the matrix. Additionally, the columns used in the visual calculation cannot be hidden in the table; the common workaround is to set their width to 0, but this is not ideal, and in general, it is not a fully-integrated solution within the matrix visual because it requires a separate visual.

Including multiple columns in a matrix

Including multiple detail columns in a matrix visual adds hierarchical levels, making navigation difficult and potentially hurting query performance. The layout can also become unclear, as shown when several customer attributes appear under the order number (152200 in this example).

Just adding columns to the matrix is not the way to go.

Collecting data in a measure

When presenting a unique business identifier, such as a Product Code, Customer Code, or Order Number, you may want to show extra details in a Details column that, in reality, is a measure appearing after other measures in the matrix. This allows you to display transaction information directly, which eliminates the need for additional drill-down actions. Here is an example of what we want to obtain.

To create this example, we defined a prototype measure to validate the design:

Details prototype = 
IF (
    SELECTEDVALUE ( Sales[Order Number] ) = 152200, 
    "Description of the customer"
)

The Details prototype measure displays a fixed string when a specific order is selected. However, a measure can navigate the internal structure of the model to retrieve the required data. There are multiple possible approaches, because we can obtain the same result with different techniques. The goal of the article is to present several of these techniques, along with considerations regarding the performance and maintainability of the solution.

If you are interested in using the best solution, just skip to the final section. However, it is worth spending time learning the pros and cons of different approaches, because there is always a chance that you encounter scenarios where one of the other alternatives is better for you.

Details measure – version 0

This section is purely educational and shows how to explore the individual features we will use in the following complete implementations. The first measure we implement displays the currency and exchange rate for the transaction, all of which are available in the Sales table:

Details v0.A = 
VAR CurrencyCode = SELECTEDVALUE ( Sales[Currency Code] ) 
VAR ExchangeRate = SELECTEDVALUE ( Sales[Exchange Rate] )
RETURN 
    IF (
        NOT ISBLANK ( CurrencyCode ),
        CurrencyCode & "/USD=" & ExchangeRate
    )

The first issue to address is that the information is also visible when multiple orders share the same currency code. This can be solved by ensuring that only one order is visible: using ISINSCOPE guarantees that the Order Number is also displayed in the matrix visual, not just filtered outside of the visual. By checking that there is a row visible in Sales, we can also use VALUES instead of the more expensive SELECTEDVALUE, which relies on an underlying DISTINCTCOUNT:

Details v0.B = 
IF (
    ISINSCOPE ( Sales[Order Number] ) && NOT ISEMPTY ( Sales ),
    VAR CurrencyCode = VALUES ( Sales[Currency Code] ) 
    VAR ExchangeRate = VALUES ( Sales[Exchange Rate] )
    RETURN CurrencyCode & "/USD=" & ExchangeRate
)

The second issue is that adding columns requires writing a much longer expression, which would become more complex if we wanted to filter out blank values and to avoid displaying separators in the result when there is no value. For example, we should write something like that to ensure that both Currency Code and Exchange Rate are available:

Details v0.C = 
IF (
    ISINSCOPE ( Sales[Order Number] ) && NOT ISEMPTY ( Sales ),
    VAR CurrencyCode = VALUES ( Sales[Currency Code] ) 
    VAR ExchangeRate = VALUES ( Sales[Exchange Rate] )
    RETURN 
        IF ( 
            NOT ISBLANK ( CurrencyCode ) && NOT ISBLANK ( ExchangeRate ),
            CurrencyCode & "/USD=" & ExchangeRate
        )
)

However, with more columns to display, the code to write and maintain would be much longer. To simplify that, we could look for an approach that just provides a list of displayed values, with a standard separator between them, to obtain the following result.

The code uses a list of values in a table constructor, which CONCATENATEX iterates over to produce the output string. FILTER makes sure to remove empty values, so that the measure returns BLANK if the list is empty (without the filter, we would always have a list of two blank values):

Details v0.D = 
IF (
    ISINSCOPE ( Sales[Order Number] ),
    CONCATENATEX (
        FILTER ( 
            {
                VALUES ( Sales[Currency Code] ), 
                VALUES ( Sales[Exchange Rate] )
            },
            [Value] <> ""
        ),
        [Value],
        ", "
    )
)

Details measure – version 1

Having introduced the method of iterating over a list of values utilizing CONCATENATEX and FILTER, we can now tackle the subsequent challenge: presenting data from various tables, for instance, the Customer table.

To use the same approach as what we introduced in the previous example, we must propagate the filter from Sales to Customer; this is because the filter context only filters one order, but not the related customer. We can wrap the CONCATENATEX expression in a CALCULATE function where CROSSFILTER enables the filter propagation from Sales to Customer:

Details V1 = 
IF (
    ISINSCOPE ( Sales[Order Number] ),
    CALCULATE (
        CONCATENATEX ( 
            FILTER ( 
                { 
                    VALUES ( Customer[Country Code] ), 
                    VALUES ( Customer[State Code] ), 
                    VALUES ( Customer[City] ), 
                    VALUES ( Customer[Name] ),  
                    VALUES ( Customer[Age] ) 
                },
                [Value] <> "" 
            ),
            [Value],
            ", "
        ),
        CROSSFILTER ( Sales[CustomerKey], Customer[CustomerKey], BOTH )
    )
)

The result shows customer information for each order.

A quick look at the query plan shows that this approach generates 27 storage engine queries, with a maximum of 1,681 rows processed by the formula engine. The execution time is completely irrelevant for this amount of data; it is more interesting to consider that the physical query plan has 254 rows (not displayed in the screenshot), which is another indicator of the query plan’s complexity.

We will compare these metrics with other approaches in the following measure to evaluate the more efficient approach in terms of execution time, even though we want to balance the complexity of the code to write and maintain.

Details measure – version 2

One issue with using VALUES for each column to display is that the code is verbose. The syntax is simpler (and potentially more efficient) when accessing column values in a row context. In the first complete solution, we create an external row context to access the rows in the required table (Customer or Sales, hopefully only one row!) and we iterate the column values with the inner iterator. In the following measure, an external CONCATENATEX iterates the Customer table, so that the inner CONCATENATEX iterates the values obtained by using simple column references:

Details V2.A = 
IF (
    ISINSCOPE ( Sales[Order Number] ),
    CONCATENATEX ( 
        CALCULATETABLE (
            Customer,
            CROSSFILTER ( Sales[CustomerKey], Customer[CustomerKey], BOTH )
        ),
        CONCATENATEX ( 
            FILTER ( 
                { 
                    Customer[Country Code], 
                    Customer[State Code], 
                    Customer[City], 
                    Customer[Name], 
                    Customer[Age] 
                },
                [Value] <> "" 
            ),
            [Value],
            ", "
        ),
        " - " // This should never be used as long 
              // as ISINSCOPE ( Sales[Order Number] ) is True
    )
)

Because the Customer table must be filtered to return only the row corresponding to the selected order, we use CALCULATETABLE and CROSSFILTER to propagate the filter from Sales to Customer. Because we know a filter is active on Sales (thanks to the initial ISINSCOPE), we can be confident that only one customer is filtered, even if the order has multiple rows in Sales.

The outer CONCATENATEX should only iterate one row in Customer. Therefore, the “ – “ separator should never be used. The result of the Details V2.A measure is identical to Details V1.

From a performance point of view, this approach reduces the number of storage engine queries (10 instead of 27), the number of rows in the physical execution plan (186 instead of 251), and the maximum number of rows iterated by the formula engine (31 instead of 1,681).

Despite the nested iterators, this approach should be faster because we only iterate over one row of the outer iterator. More importantly, this approach is the best for maintenance, as it requires only a simple list of columns to achieve the required results. Later, we will see that this simplest approach also makes it possible to move most of the code into a user-defined function (UDF).

Given the requirements, we wanted to get the details for the entire order. However, if the requirement concerned the details of each order, we could iterate through the Sales table and use RELATED to access columns in related tables. For example, the following measure displays the details of each of the products in the selected order, and it separates each row (and therefore each product) with a new line character:

Details V2.B = 
IF (
    ISINSCOPE ( Sales[Order Number] ),
    CONCATENATEX ( 
        Sales,
        CONCATENATEX ( 
            FILTER ( 
                { 
                    Sales[Quantity],
                    RELATED ( 'Product'[Product Name] ), 
                    RELATED ( 'Product'[Weight] ), 
                    RELATED ( 'Product'[Weight Unit Measure] )
                },
                [Value] <> "" 
            ),
            [Value],
            ", "
        ),
        UNICHAR ( 10 )
    )
)

Here are the results we can obtain with this measure: for each row of the order, we see the quantity, the product name, the weight (if present), and the weight unit. We did not implement a logic to hide the weight unit if the weight amount is not present, like for the first row related to WWI Desktop PC1.80 product.

We do not make performance considerations in this case because the example is different from the one we use to compare the approaches. But we believe it was interesting from an educational perspective to show this other scenario.

Details measure – version 3

The last version has the same structure as version 2, but in this case, we obtain a row context to use column references with SUMMARIZE. This way, we do not have to rely on the bidirectional filter and we can also combine columns from different tables:

Details V3 = 
IF (
    ISINSCOPE ( Sales[Order Number] ),
    CONCATENATEX ( 
        SUMMARIZE ( 
            Sales, 
            Customer[Country Code], 
            Customer[State Code], 
            Customer[City], 
            Customer[Name], 
            Customer[Age]  
        ),
        CONCATENATEX ( 
            FILTER ( 
                { 
                    Customer[Country Code], 
                    Customer[State Code], 
                    Customer[City], 
                    Customer[Name],  
                    Customer[Age] 
                },
                [Value] <> "" 
            ),
            [Value],
            ", "
        ),
        " - " // This should never be used as long 
               // as ISINSCOPE ( Sales[Order Number] ) is True
    )
)

The result is the same as for the previous two versions.

To be honest, this solution is not ideal from a maintenance point of view, because it requires duplicating the list of columns both in SUMMARIZE and in the table constructor used as FILTER argument. However, we wanted to show this approach because it could be more efficient from a query execution perspective.

We have a further reduction of the number of storage engine queries (7 instead of 10), a larger number of rows in the physical execution plan (337 instead of 186, the largest number among all the versions of this measure), and the same maximum number of rows iterated by the formula engine (31) as in version 2.

Thus, is this approach worth using? It really depends on the performance impact measured in a real environment. Our suggestion is to refrain from applying potential optimizations that could make the code longer or harder to read and maintain. Therefore, we suggest using the approach described in version 2; implement version 3 only when you can prove that it achieves faster performance and is necessary for the scenario you are facing.

Details measure – final version

In this final section, we show how to implement our favorite approach (version 2) by moving the repetitive code into a function, thus making it easier to create other detail functions in your reports. The Local.ConcatenateNonEmpty function receives a table with a list of values to display, and a column reference pointing to the column to use (which is [Value] for table constructors with a single column):

Local.ConcatenateNonEmpty = ( 
    fieldsListTable : TABLE VAL, 
    fieldColumn : ANYREF EXPR 
) => 
CONCATENATEX ( 
    FILTER ( 
        fieldsListTable,
        fieldColumn <> "" 
    ),
    fieldColumn,
    ", "
)

The measure reduces its verbosity by invoking the Local.ConcatenateNonEmpty function to implement the inner CONCATENATEX iteration:

Details V2.A fx = 
IF (
    ISINSCOPE ( Sales[Order Number] ),
    CONCATENATEX ( 
        CALCULATETABLE (
            Customer,
            CROSSFILTER ( Sales[CustomerKey], Customer[CustomerKey], BOTH )
        ),
        Local.ConcatenateNonEmpty (
            { 
                Customer[Country Code], 
                Customer[State Code], 
                Customer[City], 
                Customer[Name], 
                Customer[Age] 
            },
            [Value]
        ),
        " - " // This should never be used as long 
               // as ISINSCOPE ( Sales[Order Number] ) is True
    )
)

You can find function-based versions of all the measures in the sample file you can download. The role of the function is to just reduce the amount of code to write, without impacting the performance obtained.

Conclusions

We have seen several ways to display transaction details in a compact manner, by using DAX measures. Different approaches can result in different levels of performance, but we must balance performance gains with code readability and maintainability. Counter-intuitively, using nested iterators could be ideal in this scenario because the outer row context gives access to column references, without the additional burden of retrieving each value from the filter context. Using a function to hide repetitive tasks can help you reuse this approach across different measures in your reports.

In mid-January 2026, many Power BI Desktop users began noticing a strange visual regression: several icons in the interface—including the Data, Model, and DAX icons—appeared as solid black shapes instead of their normal outlines. The issue appeared suddenly, even on machines where Power BI Desktop had not been updated. Because it happened the same week that Power BI Desktop January 2026 was released, this led to understandable confusion.

The root cause was not Power BI Desktop itself but an unexpected regression in the WebView2 runtime, one of the external dependencies used to render parts of the UI. Microsoft confirmed the issue and is preparing an official fix.

Until the update is released, there is a safe temporary workaround that restores the proper icons. This is useful if you need to record videos (like me!), run demos, or simply work without visual glitches.

Below is the full procedure.

Why the issue appeared

Power BI Desktop uses Microsoft Edge WebView2 to render several UI components. A recent WebView2 update introduced a regression that broke the rendering of some vector-based icons, causing them to show up as filled black shapes.

Even older versions of Power BI Desktop were affected because WebView2 updates independently from Desktop.

Until the fixed version is published and distributed, affected users will continue to see black icons unless they override WebView2 manually.

Temporary workaround: pin Power BI Desktop to a previous WebView2 fixed version

This workaround forces Power BI Desktop to use a known-good WebView2 build rather than the most recent (faulty) one. It requires no system-wide changes and is fully reversible.

IMPORTANT: this workaround stops automatic updates of WebView2; you should remove this workaround once the bug is fixed by Microsoft. Set a reminder to check that every week.

Step 1 — Download a previous WebView2 Fixed Version Runtime

1. Visit:
   https://developer.microsoft.com/en-us/microsoft-edge/webview2
2. Locate the Fixed Version section.
3. Choose an earlier version (for example: 143.0.3650.139).
4. Select x64 and download the package.

Step 2 — Extract the CAB file

The downloaded file is a CAB package. Extract it into a folder, such as:

C:\Uti\Microsoft.WebView2.FixedVersionRuntime.143.0.3650.139.x64

(You must extract it; Power BI cannot use the CAB directly.)

Step 3 — Create the WEBVIEW2_BROWSER_EXECUTABLE_FOLDER environment variable

This step tells Power BI Desktop to load WebView2 from the folder you extracted earlier. If you have never created an environment variable before, follow the full click-by-click process below.

1. Open the Windows System Properties window
   • Press Windows Key, type “environment variables”, and select “Edit the system environment variables”.
     Alternatively:
     • Open Control Panel → System and Security → System → Advanced system settings.
2. Open the Environment Variables dialog
   • In the System Properties window, go to the Advanced tab (usually already selected).
   • Click Environment Variables… at the bottom-right (this is the button indicated by the left arrow in the bitmap).
3. Create a new user variable
   • In the top section (User variables for ), click New… (the button indicated by the right arrow in the bitmap).
4. Enter the variable details
   • Variable name:

     WEBVIEW2_BROWSER_EXECUTABLE_FOLDER
     

   • Variable value:
     Paste the full path of the folder where you extracted the WebView2 runtime, for example:

     C:\Uti\Microsoft.WebView2.FixedVersionRuntime.143.0.3650.139.x64
     

5. Click OK to save, then OK again to close Environment Variables, and finally OK to close System Properties.
6. Close Power BI Desktop if it is running, then open it again so the environment variable is applied.

This completes the configuration. Power BI Desktop will now load WebView2 from that specific folder instead of the system runtime.

Step 4 — Launch Power BI Desktop

Start Power BI Desktop as usual.
The icons should now display correctly.

Removing the Workaround

Once Microsoft ships the patched WebView2 release (and the fix propagates), you should:

1. Remove the environment variable
   Delete WEBVIEW2_BROWSER_EXECUTABLE_FOLDER.
2. Delete the temporary folder you extracted earlier.

Power BI Desktop will then return to using the standard system WebView2 runtime.

Final Notes

This workaround does not modify your Power BI installation and does not affect Windows globally. It simply redirects Power BI Desktop to use a stable WebView2 runtime until Microsoft finalizes the fix.

If you are preparing tutorials, training content, or video recordings, applying this workaround ensures the UI appears correctly and avoids unnecessary confusion.

Once the official fix is available, remove the override and return to the standard configuration.

DISCLAIMER: While I wrote a draft of the workaround for internal use, I used ChatGPT to create a longer, more detailed step-by-step description to make it easier to apply.

This video introduces a 𝘀𝘂𝗽𝗲𝗿𝗺𝗮𝗿𝗸𝗲𝘁 𝗽𝗿𝗼𝗱𝘂𝗰𝘁 𝗽𝗹𝗮𝗰𝗲𝗺𝗲𝗻𝘁 𝗮𝗻𝗮𝗹𝘆𝘀𝗶𝘀 𝘂𝘀𝗲 𝗰𝗮𝘀𝗲 by using 𝗦𝘆𝗻𝗼𝗽𝘁𝗶𝗰 𝗣𝗮𝗻𝗲𝗹 in Power BI. We look at how supermarkets can understand 𝘄𝗵𝗲𝗿𝗲 𝗽𝗿𝗼𝗱𝘂𝗰𝘁𝘀 𝗮𝗿𝗲 𝗽𝗹𝗮𝗰𝗲𝗱 and 𝗵𝗼𝘄 𝗽𝗹𝗮𝗰𝗲𝗺𝗲𝗻𝘁 𝗶𝗺𝗽𝗮𝗰𝘁𝘀 𝘀𝗮𝗹𝗲𝘀 𝗮𝗻𝗱 𝗺𝗮𝗿𝗴𝗶𝗻𝘀. Traditional tables and charts fail to accurately reflect the store layout, whereas this approach lets you visualize KPIs directly on the supermarket floor plan.

𝗬𝗼𝘂 𝗰𝗮𝗻:
– Analyze product placement performance on a real store map
– Display sales and margin metrics directly on areas and shelves
– Switch measures (for example, margin %) and instantly spot high- and low-performing zones
– Drill down from category areas to individual shelves
– Isolate specific zones and analyze them in detail
– Work with multiple SVG maps and levels of detail on the same report page

The data shown is fictitious, but the use case reflects real-world scenarios already adopted by customers to improve store layout and commercial decisions.

Supermarket product placement 𝘂𝘀𝗲 𝗰𝗮𝘀𝗲: https://okviz.com/usecase/supermarket-products-placement-synoptic-panel/

𝗦𝘆𝗻𝗼𝗽𝘁𝗶𝗰 𝗣𝗮𝗻𝗲𝗹 𝘃𝗶𝘀𝘂𝗮𝗹 for Power BI: https://okviz.com/synoptic-panel/

If you build similar analyses with Synoptic Panel, 𝘀𝗵𝗮𝗿𝗲 𝘆𝗼𝘂𝗿 𝗿𝗲𝘀𝘂𝗹𝘁𝘀 with us or 𝗳𝗼𝗿𝘄𝗮𝗿𝗱 𝘁𝗵𝗶𝘀 𝘃𝗶𝗱𝗲𝗼 to anyone who could benefit from this approach.

Compare the performance of regular versus limited relationships in DAX to understand the implications of using limited relationships in a Power BI semantic model.

This article analyzes the performance of regular versus limited relationships in DAX to understand the implications of using limited relationships.

A relationship in a semantic model can be either regular or limited. Regular relationships are the most common: a one-to-many relationship between two tables on the same data island is almost always a regular relationship. If the tables involved in the relationship are stored in different data islands, then the relationship is limited. We analyzed the semantic differences between the two types of relationships in previous articles: Understanding blank row and limited relationships and Regular and limited relationships in Power BI.

Relationships between different data islands are the most common case of limited relationships. In that scenario, performance depends on multiple factors, most of which are not under the control of a DAX developer. Indeed, when mixing data from different data islands, the DAX formula engine must act as a bridge between them, resulting in complex execution plans. Besides, when two tables reside in different data islands, only limited relationships can connect them. Therefore, a performance comparison would not make sense, as there are no alternative options to link the tables.

However, a model can have limited relationships in the very special case of two tables stored in the same data island and connected by a many-to-many cardinality relationship. By nature, many-to-many cardinality relationships are limited. While they seem like a convenient way to link two tables when the key used to link them together is not unique in both tables, many-to-many cardinality relationships are extremely expensive, and a wise data modeler should limit their use strictly to cases where they are absolutely necessary. In this article, we analyze the differences between regular and limited relationships, focusing solely on performance.

Introduction

Choosing the most efficient type of relationship is important in data models because relationships are used every time the DAX engine needs to scan a table and group by a related table. A slow relationship slows down every measure that traverses it.

Limited relationships are shown in Power BI by displaying small gaps at the edges of the line that connects two tables. In the following figure, the relationship between Product and Sales is regular (no gaps), whereas the relationship between Sales and Customer is limited (gaps are present).

When Power BI creates a matrix like the following, grouping by Customer[Country] and computing the Sales Amount measure, it needs to scan Sales and group by the columns in Customer, using the relationship to perform the join.

To test the difference in performance between the two types of relationships, we execute queries that traverse the relationship using a regular relationship first and a limited relationship next. We are not going to change anything: the relationship being used is the same as the one between Customer and Sales. The only difference will be the type of relationship: either regular or limited.

Choosing the test database

Quick heads up before we move forward with the test – the downloadable demo for this article uses the usual Contoso 10K model, which has around 4,000 rows in the Sales table. The tests executed on such a tiny model are useless because the execution time is so fast that any noticeable difference may depend on external factors. Therefore, the timing shown in this article is related to the same model, but with 1.4 billion rows in the fact table. The conclusions are the same, but the numbers are so much larger that the difference is clear.

As an example, this simple query runs in three milliseconds on the small model, and it runs in 15,000 milliseconds on the large model:

EVALUATE
SUMMARIZECOLUMNS (
    Customer[Country],
    "Sales", [Sales Amount]
)

However, multiple executions of the same query may vary in timing: it can range from 3 to 7 milliseconds on the small model (more than double the time), whereas it stays around 15,000 milliseconds (+/- 300) on the large model. Bear that in mind when measuring performance: small models are pretty much useless for assessing the quality of your DAX or modeling choices. Always test on large models, with tens of millions of rows on the many side and millions of rows on the one side of the relationship!

Testing simple measures

Let us start measuring the baseline: the time required to compute Sales Amount without any relationship being involved. This measures the time needed to scan the Sales table:

EVALUATE
SUMMARIZECOLUMNS (
    "Sales", [Sales Amount]
)

Here are the server timings.

The interesting number is the storage engine CPU (SE CPU): more than 7 seconds. Because of parallelism, those 7 seconds are reduced to 134 milliseconds of execution waiting time (SE). However, when evaluating performance, we are interested in the SE CPU rather than the SE timings, because SE CPU provides a better picture of the real processing cost of an operation.

We now run the same query, grouping by Customer[Country], and we evaluate the cost of traversing the relationship:

EVALUATE
SUMMARIZECOLUMNS (
    Customer[Country],
    "Sales", [Sales Amount]
)

As you can see, the execution time nearly doubled.

You can see that the relationship is used within the VertiPaq engine, where there is a join between Sales and Customer.

In all its simplicity, this demo already shows how expensive relationships are. Scanning the Sales table takes 7 seconds, whereas scanning the same Sales table using the relationship to group by Customer[Country] adds another 7 seconds. Be mindful that the cost of a relationship strongly depends on the number of rows in the table on the one-side (Customer, in this case). Customer contains 1.8M rows. Using a smaller table like Product produces a faster execution time:

EVALUATE
SUMMARIZECOLUMNS (
    Product[Brand],
    "Sales", [Sales Amount]
)

Instead of 15 seconds, using Product as the one-side of the relationship produces a better result.

Now that we have a better understanding of the baseline and the main factors to consider in a relationship, let us change the relationship between Customer and Sales from one-to-many to many-to-many, thus obtaining a limited relationship.

If we execute the query that groups by Customer[Country] again, we notice important details:

• There are now multiple storage engine queries executed in a batch. A many-to-many cardinality relationship cannot be pushed down to the storage engine as a single query.
• The execution time is noticeably longer.

Here are the server timings.

The SE CPU skyrocketed from 15,000 to 138,000 milliseconds. What is most relevant is to understand what this number comprises. We know that out of 15,000 milliseconds, around 7,000 are needed to scan the Sales table. Therefore, the additional cost of the relationship is around 8,000 milliseconds. Similarly, out of 138,000 milliseconds, 7,000 are needed to scan the table. Therefore, the additional cost is 131,000 milliseconds. In other words, the many-to-many cardinality relationship makes the calculation almost 20 times more expensive compared to a regular relationship.

Be mindful that this price must be paid whenever the table is scanned. In a regular report with dozens of different measures being computed, all these additional costs add up, further slowing the report.

Testing multiple measures

An important consideration about many-to-many cardinality relationships is that they cannot be pushed down to the storage engine as simple joins. This can limit the optimization options available to the DAX engine. Let us see an example of this in the context of Fusion optimization. If you are not familiar with Fusion, you can find further information here: Introducing horizontal fusion in DAX, and here: Optimizing fusion optimization for DAX measures.

The following query needs to compute Sales Amount for male and female customers, using two different columns for the result:

EVALUATE
SUMMARIZECOLUMNS (
    Customer[Country],
    "Male Sales", CALCULATE ( [Sales Amount], Customer[Gender] = "Male" ),
    "Female Sales", CALCULATE ( [Sales Amount], Customer[Gender] = "Female" )    
)

Thanks to Fusion optimization, with a regular relationship, the entire query can be computed through a single storage engine query that retrieves sales grouped by country and gender.

If the relationship is set to many-to-many cardinality, Fusion is disabled because the VertiPaq engine cannot retrieve the values for both male and female in a single query by using a limited relationship. The same query with a many-to-many cardinality relationship generates these timings.

The storage engine needs to execute two complex batches: one for male and one for female. Needless to say, the more measures you have that cannot be merged with Fusion, the more complicated the entire plan becomes.

Conclusions

Many-to-many cardinality relationships are a great modeling feature, but they significantly slow down every query executed by the DAX engine. A wise DAX developer needs to know the price of many-to-many cardinality relationships and limit their use to only the scenarios where they are strictly necessary.

Specifically, because the price of a relationship is linked to the number of unique values in the column used for the relationships, many-to-many cardinality should be avoided as much as possible with large cardinalities (millions). At the same time, they may be a good solution for smaller cardinalities (preferably thousands or fewer), where the limited number of unique values involved reduces the additional overhead.

A recap of the news in the DAX and SQLBI world in 2025 and a preview of what is coming in 2026.
Happy New Year!

All links in blog post: https://www.sqlbi.com/blog/marco/2025/12/29/what-happened-in-the-dax-world-in-2025/

It is time for our annual review of what happened in the DAX and SQLBI worlds, and what we should expect in 2026!

New DAX features in 2025

This was a big year for DAX, with the public preview that started in September for two new features: user-defined functions (UDFs) and calendar-based time intelligence.

The user-defined functions are the most significant update to the DAX language since 2015, when Microsoft introduced variables. Can you believe that for more than five years we had to write DAX code without variables? Well, the user-defined functions we just mentioned will have an even bigger impact in the long term.

In five years from now, when you open a semantic model and find measures longer than 20 lines without functions invoked, you will immediately think that such code has to be “old”, or that it’s “weird”. The same feeling you may have today when you see no variables being used in a 40-line-long measure. More importantly, while you do need to understand variables to declare and use them, you do not need to understand all the details about functions to “consume” them. Therefore, I expect a proliferation of libraries of any kind. In large companies, complex algorithms and calculations will be written by a small number of expert developers and used by a large number of data analysts who may not have the same advanced DAX skills.

Calendar-based time intelligence is a fantastic feature we should have received many years ago. It’s wonderful; it works well, even though it requires a little patience and an initial investment in time and effort. For a new model with non-trivial calendar requirements, it is the way to go. But if you have existing models and reports… You should not rush to migrate. There are performance advantages in specific scenarios, but overall, many people may not find the additional investment (in learning and refactoring of existing models and reports) worth the benefits. Expect more tooling coming to help you benefit from this feature, but more about this later.

New DAX functions in 2025

The 21 new functions introduced in 2025 do not introduce new concepts; most are logical consequences of other features released this year. For example, the new Calendar-based time intelligence updated the parameters of all time intelligence functions (which now accept a Calendar argument) and included new functions that work with weeks and require a Calendar argument: CLOSINGBALANCEWEEK, DATESWTD, ENDOFWEEK, NEXTWEEK, OPENINGBALANCEWEEK, PREVIOUSWEEK, STARTOFWEEK, TOTALWTD.

We have several new information functions to investigate the type of an expression, which are helpful in DAX user-defined functions (UDFs) that receive an argument without type enforcement. Because of the new names and aliases for the data types, we have multiple functions for the same type (e.g., INTEGER and INT64; they are really the same). These are ISCURRENCY, ISINTEGER, ISBOOLEAN, ISDATETIME, ISDECIMAL, ISDOUBLE, ISINT64, ISNUMERIC, ISSTRING. In the same group, we also have the new INFO.CSDLMETADATA and INFO.DEPENDENCIES functions.

The only function added to the visual calculations is LOOKUPWITHTOTALS. Finally, EXTERNALMEASURE has been documented officially this year, but it had been in use since 2020, as it is used for composite models.

What SQLBI delivered in 2025

People are coming back to in-person events. This is a fact. While we don’t have as many local community events as we did before the pandemic, the trend is that they are growing. However, the consumption of technical content is moving in different directions. As we announced last year, we limited our in-person delivery time during 2025 because we had to produce new content:

• We completed the third edition of the book, The Definitive Guide to DAX. It is an entirely new book; we barely reused any parts from the previous edition. Why? Years of experience teaching DAX have given us more ideas for explaining core concepts to both newbies and experienced DAX practitioners. The book also includes many new chapters and covers the latest DAX features, including Calendar-based time intelligence and User-defined functions.
• We have already started delivering the updated content of the Mastering DAX classroom course, which will have a new form for all deliveries in 2026.
• We published new content inside SQLBI+, our subscription service for advanced content for developers who create semantic models for Power BI, Analysis Services, and Fabric. In 2025, we released two courses inside SQLBI+: Security in Tabular Semantic Models and AI Workflows and agentic development for Power BI. Both courses offer a whitepaper along with hours of videos. We also released a new whitepaper, Understanding SUMMARIZECOLUMNS, which includes valuable, hard-to-find-elsewhere information that is vital if you plan to query a semantic model from outside Power BI or want to understand the inner workings of this powerful function.
• We published more than 30 new articles and blog posts at sqlbi.com. These represent high-quality, free learning content on SQLBI, which we produce to support the community and help students who cannot afford the paid content.
• We released DAX Lib, first repository for libraries of DAX user-defined functions. It is free, open-source, and driven by community contributions. We are so happy about the enthusiastic adoption of the tool! There are already more than 30 ready-to-use libraries on DAX Lib, and we are talking about a feature released only 3 months ago and that is still in preview. This is definitely something that will grow a lot over the next year.
• Our partner company, Tabular Tools, released DAX Optimizer Basic for general availability. This is a new version of the full DAX Optimizer service. Everyone can evaluate their DAX code in a semantic model: no email, no login, no password, no installation.
• In 2025, the SQLBI YouTube channel surpassed 125,000 subscribers. We continued to publish a video for each corresponding article every other week, plus a few other videos with interviews and other content. That’s another source of free learning for students that may not be ready to invest in a video course just yet.
• The SQLBI Newsletter is now being sent out to 275,000 subscribers! This year we also celebrated 300 newsletters with a special number dedicated to AI in BI and a new comics that is still alive after several months. Will it survive until next year? We hope so!
• Our sister company, OKVIZ, released Synoptic Panel v2 which turned out to be a huge hit. There are already 13 use cases showing very different solutions based on this visual: take a look at them, they are inspiring regardless of the use of custom visuals!

What’s coming in 2026

• New Mastering DAX video course: We are currently recording the new Mastering DAX video course that will be available by the end of March 2026. Everyone with an active license for the current Mastering DAX video course on the day the new content is released will be automatically upgraded.
• Classroom courses in 2026: We will offer more dates in new cities. This spring, we will be back in Houston and New York City, and we’ll go to Baltimore for the first time. We are also going back to Copenhagen, Zürich, Amsterdam, and for the first time to Berlin. We are still planning the second half of 2026 also for other continents (!). Stay tuned and let us know where you would like us to deliver in-person classes!
• We are planning new content to deliver inside the SQLBI+ subscription. We do not have exact dates to share, but time intelligence and relationships are two topics that deserve white papers and video courses.
• Regular content: expect new articles and videos every other week!
• Tools: we will update existing free tools like Bravo for Power BI, because of DAX Lib, and because of the new calendar-based time intelligence. There could also be something new. Well, not entirely new, but certainly welcome for those who cannot install external tools. Ok, no more spoilers!

Make sure to receive regular updates about SQLBI by registering for our newsletter and subscribing to our YouTube channel.

Enjoy DAX and see you in 2026!

How to analyze a dynamic pricing model for aircraft seat sales by using Synoptic Panel in Power BI.
See how seat-level revenue data, usually shown in a table, can be turned into an interactive plane seat map. By binding data to an SVG drawing, Synoptic Panel makes it easy to spot high- and low-performing seats, analyze patterns by row or class, and interactively select seats to aggregate results.

The complete use case provides more details about how to implement this solution and obtain the result shown in the video.

Inside the case study, you will find:

• How seat revenue data is modeled in Power BI
• How an SVG plane layout is bound to data
• How dynamic coloring highlights revenue and ticket classes
• How selections on the visual drive analysis

Full case study and implementation details: https://okviz.com/usecase/plane-seats-synoptic-panel/

Try Synoptic Panel: https://okviz.com/synoptic-panel

This is a simple demo, but it reflects how many companies already use Synoptic Panel to visualize complex layouts in a clear, intuitive way.

Share this post with teams or partners who could benefit!

How to analyze the effectiveness of campaigns by separating attributed sales (directly linked to a campaign) from influenced sales (all sales of products participating in campaigns, regardless of attribution). The video describes the data model and DAX measures required.

This article describes the data model and DAX measures to analyze the effectiveness of campaigns, by separating attributed sales (directly linked to a campaign) from influenced sales (all sales of products participating in campaigns, regardless of attribution).

Marketing campaigns drive sales, but not all sales can be directly attributed to a specific campaign. A customer might see a promotion and later purchase the product without using any discount code or campaign link. How do you measure this broader influence? This article presents data modeling and DAX measures to analyze campaign effectiveness by separating attributed sales (transactions for which we can clearly identify the one campaign that generated the sale) from influenced sales (all sales of products participating in one or more campaigns, regardless of whether the sale can be attributed to one exact campaign). The solution includes several measures using a many-to-many relationship between products and campaigns, combined with dynamic CROSSFILTER manipulation, to provide a complete view of the campaign’s impact.

DISCLAIMER: The last two sections (“Interpreting the results” and “Other scenarios”) of this article have been written with the support of AI to assess the business scenarios where the data model and the measures described in this article can be used. The text has been manually edited and reviewed by the authors.

Introduction

Marketing campaigns are designed to drive sales, but measuring their actual impact is challenging. When a customer redeems a discount code or clicks through a promotional link, that sale is clearly attributed to the campaign. But what about the customers who see an advertisement, visit the store a week after the campaign is over, and then purchase the product at full price? The campaign influenced the purchase decision, yet the transaction has no direct link to it.

Traditional attribution models only count directly-tracked conversions, thus underestimating the full value of the campaign. To understand the whole picture, we need to answer several questions:

• How much revenue came directly from campaign transactions?
• How much did campaign products sell in total, regardless of attribution?
• Are there products benefiting from campaigns we are not currently analyzing?
• How are products that are not the object of any campaign, performing?

We will build a set of measures to identify:

• Attributed Sales: Transactions explicitly linked to one campaign.
• Influenced Sales: All sales of products participating in campaigns.
• Unattributed Sales: Campaign product sales without direct attribution.
• Organic Sales: Sales of products that have never participated in any campaign.

These measures provide a complete view of campaign effectiveness beyond simple attribution.

IMPORTANT: For the sake of simplicity, this article does not include the period of validity for a campaign. Depending on your feedback, we may consider including a more complete template in a future article in DAX Patterns. Here, we wanted to focus on the data modeling and DAX code involved in moving from attributed to influenced sales, which can be helpful in situations described in the “Other scenarios” section later in the article.

The data model

The model is based on the standard Contoso dataset, focusing on the Product and Sales tables for this scenario. We added two additional tables: Campaigns and CampaignProducts. The following diagram shows this part of the semantic model.

The Sales table includes a Campaign column that stores which campaign, if any, was used for each transaction. This column has a direct relationship to the Campaigns table, defining campaign attribution at the individual transaction level. When a sale is completed through a campaign (e.g., via a discount code or promotional link), the Sales[Campaign] column stores the campaign identifier. When a sale has no campaign involvement, the column is blank.

Campaign is a simple dimension that lists available campaigns. Because the relationship between Campaign and Sales is a regular one-to-many relationship, transactions in Sales with a blank value in Sales[Campaign] generate an additional blank row in the Campaign table. This is intentional. An alternative approach would be creating a fictitious “Not Assigned” campaign to avoid the blank row, but this would complicate the measures: we would need to reference that specific value whenever we want to isolate unattributed sales. Using the blank row instead allows us to rely on the standard ISBLANK function, making the DAX code easier to maintain.

CampaignProducts is a bridge table implementing the many-to-many relationship between Product and Campaign: a product can participate in multiple campaigns, and a campaign can include multiple products. The relationship directions are set to filter from Product to CampaignProducts and from Campaign to CampaignProducts, but not the other way around. This means filtering a campaign does not automatically filter products through the bridge table. The measures we build will selectively activate bidirectional filtering using CROSSFILTER when we need to include all products participating in a campaign, regardless of transaction attribution in Sales.

Building the measures

In the introduction, we raised several questions about measuring campaign effectiveness. We now build a set of measures to answer them, starting from a simple base measure and progressively adding complexity to isolate different perspectives on sales.

Sales Amount

Sales Amount is a standard measure in the Contoso sample model we use and serves as the base measure for our analysis. It calculates total revenue by multiplying quantity and net price for each transaction:

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

Each subsequent measure will modify the filter context around Sales Amount to isolate different types of sales.

Attributed Sales

Attributed Sales isolates transactions directly linked to a campaign:

Attributed Sales =
CALCULATE (
    [Sales Amount],
    KEEPFILTERS ( NOT ISBLANK ( Campaign[Campaign] ) )
)

The measure filters Sales Amount to include only transactions where the Campaigns[Campaign] column is not blank, meaning a campaign was explicitly used for the sale. By checking Campaigns[Campaign] rather than Sales[Campaign], we ensure that transactions assigned to unavailable campaigns are included. If you use Sales[Campaign] instead, data quality issues will result in numbers that do not correspond to Sales Amount by summing the following Unattributed Sales measure.

We use KEEPFILTERS to preserve any campaign selection made in the report. Without KEEPFILTERS, the filter NOT ISBLANK ( Campaigns[Campaign] ) would override the existing campaign filter, showing all attributed sales regardless of which campaign is selected. With KEEPFILTERS, the two filters intersect: we see only attributed sales for the selected campaign(s).

Unattributed Sales

Unattributed Sales captures the opposite: transactions with no campaign attribution:

Unattributed Sales = 
IF (
    ISINSCOPE ( Campaign[Campaign] ),
    CALCULATE ( 
        [Sales Amount],
        KEEPFILTERS ( ISBLANK ( Campaign[Campaign] ) )
    ),
    CALCULATE ( 
        [Sales Amount],
        REMOVEFILTERS ( Campaign ),
        ISBLANK ( Campaign[Campaign] )
    )
)

This measure filters for the blank row in the Campaign table to isolate transactions where Sales[Campaign] is blank or corresponds to a non-existing campaign. These are sales that occurred without involving any tracked, valid campaign.

The ISINSCOPE function determines whether Campaign is part of the report’s grouping hierarchy. When Campaign[Campaign] is in scope – for example when viewing individual campaigns within a brand, even when you look at the data by Campaign[Campaign Name] because it is sorted by Campaign[Campaign] – the measure uses KEEPFILTERS to intersect the blank campaign filter with any existing selection. This returns a value only on the blank row itself.

When Campaign[Campaign] is not in scope (for example, when viewing only at the brand level), the measure uses REMOVEFILTERS to ignore any campaign selection and then filters for the blank campaign. This ensures that Unattributed Sales displays a value at the brand level even when the user has selected specific campaigns in a slicer. Without this logic, selecting one or more campaigns would hide unattributed sales at aggregated levels, making it harder to understand the complete picture.

Attributed Sales and Unattributed Sales are complementary. For any given product, their sum equals the total Sales Amount, as shown in the following picture. This makes them helpful for understanding which portion of revenue is directly trackable to campaigns and which is not.

The Campaign table can filter the measures we showed so far. However, if you filter a campaign, the Unattributed Sales will always be blank, as shown in the following picture.

Total Sales

Total Sales shows the complete revenue, ignoring any campaign filter applied in the report:

Total Sales =
CALCULATE ( 
    [Sales Amount],
    REMOVEFILTERS ( Campaign )
)

The measure uses REMOVEFILTERS to remove any selection on the Campaign table, ensuring we always see the full sales amount regardless of any selected campaign.

The following report illustrates how Total Sales differs from Sales Amount. The matrix shows Product[Brand] on rows with Campaign[Campaign Name] as a nested hierarchy. Looking at the Contoso brand: at the brand level, both Sales Amount and Total Sales show 581,299.33, which is the total sales for all Contoso products.

When we expand Contoso to see individual campaigns, the behavior diverges. The blank row (highlighted with a green dashed border) represents unattributed sales: Sales Amount shows 206,609.47, while Total Sales remains at 581,299.33. For the individual campaigns (Promo A, Bundle B, and Mailing C, highlighted with red solid borders), Sales Amount shows only the transactions attributed to each campaign, while Total Sales consistently displays the brand total of 581,299.33.

The report also highlights that Attributed Sales shows the value of products associated with selected campaigns; the measure is additive because each product can be attributed to only one campaign. Unattributed Sales shows the value you see in Sales Amount when the blank campaign is selected.

This pattern makes Total Sales a helpful reference point. When analyzing a specific campaign, you can compare Attributed Sales against Total Sales to understand what fraction of overall brand revenue came through that campaign. The measure provides a stable context regardless of the filtered campaigns.

Influenced Sales

Influenced Sales is the most complex measure. It captures all sales of products participating in the selected campaigns, regardless of whether each transaction was attributed to a campaign. To understand how it works, it is helpful to look at a report that compares Sales Amount and Influenced Sales for different campaigns.

Looking at the Contoso brand, at the brand level, Influenced Sales shows 483,345.11, which is lower than Sales Amount (581,299.33). This is because Influenced Sales only includes products that participate in at least one campaign. Contoso products outside any campaign are excluded.

When we expand Contoso to see individual campaigns, the difference between the two measures becomes clear. For Promo A, Sales Amount shows 127,176.13, only the transactions directly attributed to that campaign. Influenced Sales shows 225,859.05, which corresponds to all sales of products participating in Promo A, regardless of whether the transaction was linked to the campaign. The gap between these two values represents potential campaign influence that is not captured by direct attribution.

Notice that the blank row shows no value for Influenced Sales. This is correct: since no campaign is selected for that row, there are no campaign products to evaluate. Total Influenced Sales shows the same value across all rows within a brand. This measure ignores the campaign filter and will be explained later in the article.

The Influenced Sales measure computes the Sales Amount value using the filter propagation described in the following diagram, where Campaign filters Sales by filtering the products (in Product) that belong to the selected campaigns (in CampaignProducts).

This is the implementation of the Influenced Sales measure:

Influenced Sales =
VAR HasCampaigns = COUNTROWS ( CampaignProducts ) > 0
VAR Result =
    CALCULATE (
        DIVIDE ( [Sales Amount] + 0, HasCampaigns ),
        VALUES ( Campaign[Campaign] ), -- Forces selected or all campaigns
        CROSSFILTER ( CampaignProducts[ProductKey], 'Product'[ProductKey], BOTH ),
        CROSSFILTER ( Sales[Campaign], Campaign[Campaign], NONE )
    )
RETURN Result

The measure requires two CROSSFILTER modifications to reshape how filters propagate through the model.

First, CROSSFILTER ( CampaignProducts[ProductKey], ‘Product'[ProductKey], BOTH ) enables bidirectional filtering between CampaignProducts and Product. When a campaign is selected, the filter flows from Campaigns → CampaignProducts → Product → Sales, capturing all transactions for products in that campaign.

Second, CROSSFILTER ( Sales[Campaign], Campaign[Campaign], NONE ) disables the direct relationship between Sales and Campaigns. Without this, the campaign filter would also flow directly to Sales via the Campaign[Campaign] column, limiting results to only attributed transactions. By disabling this path, we ensure the filter reaches Sales only through the Product relationship.

VALUES ( Campaign[Campaign] ) enforces a filter based on the selected campaigns. This is necessary when no campaigns are selected: in that case, there would be no filter to propagate from Campaign, therefore all products would be visible. In this condition, for this measure, we want to filter only products that have at least one campaign.

Finally, the DIVIDE pattern handles an edge case: when a product participates in a campaign but has no sales, we want to return zero rather than blank. The expression DIVIDE ( [Sales Amount] + 0, HasCampaigns ) returns 0 when Sales Amount is blank and campaigns exist for the selected products; when the products are not present in any of the selected campaigns, then the result is blank (because of a division by zero) and the product will not be removed from the report if all the other measures displayed are blank. This ensures that products in a campaign with zero sales remain visible in reports when analyzing campaign reach.

Total Influenced Sales

Total Influenced Sales extends the previous measure to show influenced sales across all campaigns, ignoring the current campaign selection:

Total Influenced Sales =
CALCULATE ( 
    [Influenced Sales],
    REMOVEFILTERS ( Campaign ),
    NOT ISBLANK ( Campaign[Campaign] )
)

The NOT ISBLANK ( Campaigns[Campaign] ) filter selects all campaigns, overriding any specific campaign filter in the report. The measure then calculates Influenced Sales for this broader selection.

This is useful for comparison: if Total Influenced Sales is significantly higher than Influenced Sales for the selected campaign, it indicates that other campaigns are also driving sales of overlapping products. Analysts can use this to identify cross-campaign effects or to ensure they are not missing important campaign interactions.

The following report shows the Contoso brand expanded to the product level, with the Mailing C campaign selected. This view reveals how to interpret Influenced Sales and Total Influenced Sales and highlights several interesting scenarios.

Point 1 (black dashed border) shows products with neither attributed sales nor influenced sales for Mailing C. The Cigarette Lighter Adapter products do not participate in the Mailing C campaign, yet their sales appear in Total Sales (79.97, 491.20, 381.45). Sales Amount is blank because none of these transactions were attributed to Mailing C, and Influenced Sales is also blank because these products are not part of the selected campaign. The difference emerges in Total Influenced Sales: the Black variant shows blank, while the Red and White variants show values matching Total Sales. This distinction occurs because the Red and White variants belong to at least one campaign (just not Mailing C), so their Total Influenced Sales equals their Total Sales. For any single product, if Total Influenced Sales has a value, it will match Total Sales. The Black variant does not participate in any campaign, which is why Total Sales has a value but Total Influenced Sales is blank.

Point 2 (green solid border) shows products where all measures align. The Contoso 16GB Mp5 Player M1600 Blue and Green variants have Sales Amount, Influenced Sales, and Total Influenced Sales all showing the same value. This indicates that these products are exclusively in Mailing C, all their sales were attributed to the campaign, and no other campaigns include them.

Point 3 (red dashed border) shows products in the campaign with zero sales. These products participate in at least one campaign other than Mailing C but have no transactions in Sales. Total Influenced Sales shows 0.00 rather than blank, making these products visible in the report. If the products were also included in the Mailing C campaign, they would show 0.00 also for the Influenced Sales measure. This is the edge case handled by the DIVIDE pattern in the Influenced Sales measure: we want to distinguish between “no sales” (0.00) and “not in campaign” (blank).

Point 4 (blue dashed border) shows products where Influenced Sales exceeds Sales Amount. For the Contoso 16GB New Generation MP5 Player M1650 White, Influenced Sales is 1,765.43, but Sales Amount is only 1,004.47. The Contoso 2G MP3 Player E200 Black shows a similar pattern: Sales Amount is 19.41, while Influenced Sales is 180.33. These gaps reveal the difference between attributed and unattributed sales for products in the selected campaign. When a product belongs to the selected campaign (Mailing C in this example), Total Sales, Influenced Sales, and Total Influenced Sales will always match at the product level, as shown for these highlighted products. However, at the Contoso brand level, the values differ because they aggregate products with different conditions, as illustrated in the previous points.

Organic Sales

Organic Sales isolates revenue from products that have never participated in any campaign:

Organic Sales = 
IF (
    NOT ISINSCOPE ( Campaign[Campaign] ),
    VAR ProductsWithoutCampaigns =
        CALCULATETABLE (
            FILTER (
                'Product',
                ISEMPTY ( RELATEDTABLE ( CampaignProducts ) )
            ),
            REMOVEFILTERS ( Campaign )
        )
    VAR Result = 
        CALCULATE (
            [Sales Amount],
            ProductsWithoutCampaigns,
            REMOVEFILTERS ( Campaign )
        )
    RETURN Result
)

The measure first identifies products with no campaign participation (ProductsWithoutCampaigns). The CALCULATETABLE expression iterates over Product, keeping only those where there are no campaigns. We apply REMOVEFILTERS on Campaign to ensure this evaluation considers all campaigns, not just those selected in the report. Product sales should be regarded as “organic” only if the product has never appeared in any campaign.

The second CALCULATE then computes Sales Amount for these products only and stores the value in Result. In this case, REMOVEFILTERS on Campaign ensures we ignore any campaign selection.

The initial ISINSCOPE check ensures that the measure returns a value only when Campaign is not part of the report’s grouping hierarchy. When users filter campaigns through a slicer or other external filter, the measure still displays results because it ignores the campaign selection. However, when Campaign appears as a row or column in the visual, the measure returns a blank value to avoid confusion, since organic products, by definition, do not belong to any campaign.

Conceptually, Organic Sales equals the difference between Total Sales and Total Influenced Sales when using additive measures like Sales Amount, as demonstrated in the following report, where Check Organic is computed using a visual calculation:

Check Organic = [Total Sales] - [Total Influenced Sales]

However, it is a good idea to know how to compute it correctly for any measure, including non-additive ones, using the code shown in the Organic Sales measure.

Organic Sales serves as a baseline. These products have no campaign exposure, so their performance reflects purely organic demand. Comparing Organic Sales trends to Influenced Sales for a group of products can help quantify the overall lift generated by campaign activity. For example, consider the following report.

The report shows all measures together for two campaigns: Bundle B and Mailing C. At the brand level (like Contoso), each measure provides a distinct perspective. Total Sales shows overall revenue regardless of campaign filters. Organic Sales isolates products outside any campaign, serving as a baseline for non-promoted performance. Unattributed Sales captures transactions with no campaign link, displayed even though specific campaigns are selected in the slicer. Attributed Sales shows revenue directly tracked to the selected campaigns, while Influenced Sales expands this to include all sales of products participating in those campaigns. Total Influenced Sales provides a reference across all campaigns, which helps identify whether products in the selected campaigns also belong to other campaigns.

Expanding the Contoso brand reveals campaign-level detail. Organic Sales and Unattributed Sales display blank at this level because they are not meaningful when a specific campaign is in scope. Total Sales repeats the brand total since it ignores campaign filters. Attributed Sales and Influenced Sales show campaign-specific values, while Total Influenced Sales remains constant and reflects the broader amount influenced by all existing campaigns.

Reading across the columns, analysts can quickly assess campaign performance: the gap between Attributed Sales and Influenced Sales reveals untracked potential impact, Organic Sales provides a control group for comparison, and Total Influenced Sales highlights cross-campaign overlap.

Interpreting the results

The measures we built provide different perspectives on campaign effectiveness. Reading them together reveals insights that no single measure could provide on its own.

How to read the measures together

Start with Sales Amount as your baseline: this is the total revenue under the current filter context, including any campaign selection. When a campaign is selected, Sales Amount shows only the transactions attributed to that campaign. However, when you combine different measures in the same report, you may want to use Attributed Sales instead of Sales Amount to simplify comparisons with other measures.

Compare Attributed Sales to Influenced Sales to understand the broader reach. Influenced Sales includes all revenue from products participating in the selected campaign, regardless of attribution. If Influenced Sales is significantly higher than Attributed Sales, the campaign products are selling well, but many transactions are not being tracked as campaign sales. This could indicate that customers are exposed to the campaign but complete purchases through other channels.

Use Total Sales as a reference point. This measure ignores campaign filters entirely, showing the complete revenue picture. Comparing Attributed Sales to Total Sales reveals the fraction of overall revenue directly attributable to campaigns.

Total Influenced Sales helps identify cross-campaign effects. If Total Influenced Sales is higher than Influenced Sales for a selected campaign, other campaigns also include some of the same products. This overlap might indicate redundant campaign targeting or deliberate cross-promotion strategies.

Finally, Organic Sales isolates products outside any campaign activity. This provides a control group for evaluating whether campaigns are genuinely lifting sales or simply capturing demand that would have occurred anyway.

Example analysis: comparing Attributed vs Influenced

Consider a scenario where a brand shows the following values for a selected campaign.

Attributed Sales represent only 23% of total brand revenue (Total Sales) and 59% of Influenced Sales. This means that 41% of campaign product sales occurred without direct attribution. Several interpretations are possible: customers may have seen the campaign and purchased later without using a promo code, or the campaign may have raised awareness that led to purchases across different channels. The gap between Attributed Sales and Influenced Sales quantifies this untracked potential impact.

If the business goal is to maximize tracked conversions, this gap suggests an opportunity to improve attribution mechanisms. If the goal is brand awareness, the higher Influenced Sales figure may be the more relevant success metric.

Using Organic Sales as a baseline

Organic Sales measures revenue from products that have never participated in any campaign. This baseline serves two purposes.

First, it provides a comparison point for campaign product performance. If Influenced Sales for campaign products grows faster than Organic Sales over time, campaigns may be generating incremental lift. If both grow at similar rates, the campaigns might simply be capturing existing demand rather than creating new sales.

Second, Organic Sales helps calibrate expectations. Products outside campaigns represent unassisted performance. Comparing per-product averages between campaign and non-campaign products can reveal whether campaign selection criteria are effective. If products without campaigns outperform those with campaigns, the targeting strategy may need revision.

Other scenarios

The pattern described in this article applies to any situation where you need to measure both direct attribution and broader influence. The key requirement is a many-to-many relationship between products (or other entities) and some grouping that may drive sales without explicit tracking. Here are some examples.

Store events or in-store displays. Retailers often feature products in special displays or promotional events. A customer might notice a product at an endcap display but purchase it later during a regular shopping trip, with no record linking the sale to the display. Comparing attributed sales (transactions during the event) to influenced sales (all sales of featured products) reveals the display’s broader impact.

Email marketing. Products highlighted in newsletters may generate immediate clicks, but they also build awareness that leads to purchases days or weeks later through other channels. The influenced sales measure captures this delayed effect that click-through attribution misses.

Influencer partnerships. When influencers promote products, followers often purchase through general channels rather than tracked affiliate links. Measuring total sales of promoted products against attributed affiliate conversions quantifies the influencer’s true reach.

Seasonal or thematic collections. Retailers group products into collections like “Summer Essentials” or “Back to School.” These collections drive traffic and awareness, but customers may buy individual items without any collection-level tracking. Influenced Sales for a collection shows its overall contribution to revenue.

B2B account-based marketing. In B2B contexts, marketing campaigns target specific accounts. Deals closed by sales teams may not carry campaign attribution even when marketing activity influenced the relationship. Comparing the value of attributed deals to total revenue from targeted accounts measures marketing’s broader contribution.

Loyalty program analysis. Featured products in loyalty programs may see increased purchases from members who never redeem points or rewards for those specific items. Influenced Sales shows total member purchases of featured products, regardless of whether rewards are redeemed.

Conclusions

This article presented a pattern for measuring campaign effectiveness beyond direct attribution. The solution relies on a many-to-many relationship between products and campaigns, implemented through a bridge table, combined with the selective use of CROSSFILTER to control how filters propagate through the model.

The article includes several measures that serve different questions:

• Attributed Sales answers “What did the campaign directly generate?”
• Influenced Sales answers “How did campaign products perform overall?”
• Organic Sales provides a baseline for products outside campaign activity.

You do not have to use all the measures presented; choose only those that meet your requirements!