Explaining Lineage in DAX | Towards Data Science

Contents

Introduction Set the lineage Clearing the lineage Manipulate the lineage Working with tables Conclusion References

Introduction

In DAX, lineage is an important concept, and it is vital to understand how to work with and manipulate it.

As I did in past articles, I will use DAX queries to explain this concept and its effects.

I start with a simple query to get the order count for the product of the brand “Adventure Works”:

EVALUATE
       CALCULATETABLE(
              SUMMARIZECOLUMNS('Date'[Year]
                                         ,'Date'[MonthShortName]
                                         ,'Date'[MonthKey]
                                         ,'Product'[ProductCategoryName]
                                         ,"Order Count", [Online Order Count]
                                         )
                                  ,'Product'[BrandName] = "Adventure Works"
                                  )
              ORDER BY 'Date'[MonthKey]
                                         ,'Product'[ProductCategoryName]

This is an extract of the result from the query:

Figure 1 – Result of the base query (Figure by the Author)

This query returns 180 rows. Keep it in mind, as it will be important later on.

Next, I will introduce a filter for a specific month and show the lineage’s role.

Set the lineage

I will add a filter for April 2026:

DEFINE
    VAR YearMonthFilter = 202604

EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[MonthShortName]
                        ,'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]
                        ,"Order Count", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    ,'Date'[MonthKey] = YearMonthFilter
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

In this case, I define a variable and set the value to 202604.

Next, I add it as a filter to the CALCULATETABLE() function.

Nothing special so far.

This is the result:

Figure 2 – Query and result of the query with a simple filter (Figure by the Author)

In this case, the lineage is not important, as a scalar value sets the filter.

But we can set a lineage by using the TREATAS() function:

DEFINE
    VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])

EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[MonthShortName]
                        ,'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]
                        ,"Order Count", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    ,YearMonthFilter
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

As you can see, the introduction of TREATAS() allows us to pass the variable as a filter. CALCULATETABLE() uses the lineage set by TREATAS() as a filter on the column ‘Date'[MonthKey].

The result doesn’t change, but the query is simpler, as I don’t need to pass the condition as “column equals the filter-value”.

Figure 3 – Result of the query which uses TREATAS() (Figure by the Author)

In fact, Power BI uses this form all the time when it passes filters set in a report to the semantic model.

But it does differently:

It defines variables, sets the lineage and adds all filters directly to SUMMARIZECOLUMNS():

DEFINE
    VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])
    
    VAR SelectedBrand = TREATAS( { "Adventure Works" }, 'Product'[BrandName])

EVALUATE
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[MonthShortName]
                        ,'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]
                        ,YearMonthFilter
                        ,SelectedBrand
                        ,"Order Count", [Online Order Count]
                        )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

Clearing the lineage

You might encounter situations where you need to clear the lineage.

The method for doing it varies depending on whether you have a single or multiple values as a filter.

For example, look at the following code, where I use VALUE() to remove the lineage on the previous expression:

DEFINE
    VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])
    
    VAR YearMonthFilter_cleared = VALUE(YearMonthFilter)

EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[MonthShortName]
                        ,'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]
                        ,"Order Count", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    ,YearMonthFilter_cleared
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

This is the error delivered by Power BI:

Figure 4 – Error Message when clearing the lineage with VALUES(). Without Lineage, we would need to add an equal filter as shown above in Figure 2 (Figure by the Author)

The engine cannot work with the filter in line 71 because it no longer has lineage.

It will work in this form:

DEFINE
    VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])
    
    VAR YearMonthFilter_cleared = VALUE(YearMonthFilter)

EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[MonthShortName]
                        ,'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]
                        ,"Order Count", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    ,'Date'[MonthKey] = YearMonthFilter_cleared
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

As you can see here, the query returns the same result as before:

Figure 5 – Result of the query with the removal of the lineage and the change of the filter (Figure by the Author)

Notice the change of the filter argument in line 91.

But there is a simpler way of clearing the lineage when working with measures.

Look at the following query with the Measure [Order Count full year], which calculates the order count for the entire year:

DEFINE
    MEASURE 'All Measures'[Order Count full year] =
            VAR SelYear = TREATAS({ SELECTEDVALUE('Date'[Year]) }, 'Date'[Year])
            
        RETURN
            CALCULATE([Online Order Count]
                        ,REMOVEFILTERS('Date')
                        ,SelYear
                        )

EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[MonthShortName]
                        ,'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]
                        ,"Order Count", [Online Order Count]
                        ,"Order Count full year", [Order Count full year]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

This is an extract of the result:

Figure 6 – Extract of query to calculate the order count for the entire year (Figure by the Author)

Now I add a scalar value to the variable:

DEFINE
	MEASURE 'All Measures'[Order Count full year] =
			VAR SelYear = TREATAS({ SELECTEDVALUE('Date'[Year]) }, 'Date'[Year])
			
			VAR SelYear_Plus1 = SelYear + 0
			
		RETURN
			CALCULATE([Online Order Count]
						,REMOVEFILTERS('Date')
						,SelYear_Plus1
						)

EVALUATE
	CALCULATETABLE(
		SUMMARIZECOLUMNS('Date'[Year]
						,'Date'[MonthShortName]
						,'Date'[MonthKey]
						,'Product'[ProductCategoryName]
						,"Order Count", [Online Order Count]
						,"Order Count full year", [Order Count full year]
						)
					,'Product'[BrandName] = "Adventure Works"
					)
		ORDER BY 'Date'[MonthKey]
						,'Product'[ProductCategoryName]

This operation clears the lineage, and the measure no longer works:

Figure 7 – Error message after clearing the lineage within the Measure (Figure by the Author)

What I can still do is use an equal filter to get the previous result:

DEFINE
       MEASURE 'All Measures'[Order Count full year] =
                    VAR SelYear = TREATAS({ SELECTEDVALUE('Date'[Year]) }, 'Date'[Year])
                    VAR SelYear_Plus1 = SelYear + 0
             RETURN
                    CALCULATE([Online Order Count]
                                        ,REMOVEFILTERS('Date')
                                        ,'Date'[Year] = SelYear_Plus1
                                        )

EVALUATE
       CALCULATETABLE(
             SUMMARIZECOLUMNS('Date'[Year]
                                        ,'Date'[MonthShortName]
                                        ,'Date'[MonthKey]
                                        ,'Product'[ProductCategoryName]
                                        ,"Order Count", [Online Order Count]
                                        ,"Order Count full year", [Order Count full year]
                                        )
                                  ,'Product'[BrandName] = "Adventure Works"
                                  )
             ORDER BY 'Date'[MonthKey]
                                        ,'Product'[ProductCategoryName]

Now I get the same result as before:

Figure 8 – Result after switching to an equal filter after clearing the lineage (Figure by the Author)

And now, let’s use multiple values as a filter.

For example, two months:

DEFINE
    VAR YearMonthFilter = TREATAS({ 202604, 202605 }, 'Date'[MonthKey])

EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[MonthShortName]
                        ,'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]
                        ,"Order Count", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    ,YearMonthFilter
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

Here is the result of this query:

Figure 6 – Result of the query with two values in the filter (Figure by the Author)

One way to remove the lineage of a variable with multiple values is to use SUMMARIZECOLUMNS():

DEFINE
    VAR YearMonthFilter = TREATAS({ 202604, 202605 }, 'Date'[MonthKey])
    
    VAR YearMonthFilter_cleared = SUMMARIZECOLUMNS(YearMonthFilter)

EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[MonthShortName]
                        ,'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]
                        ,"Order Count", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    ,YearMonthFilter_cleared
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

Unfortunately, this method removes the filter altogether, and all months are returned in 180 rows (The same number of rows as with the initial query):

Figure 7 – Result of using SUMMARIZECOLUMN() with a column filter with multiple values. (Figure by the Author)

Technically, the lineage is not cleared because the query still works, but the month filter is removed.

But when you try using the variable “YearMonthFilter_cleared” with an IN operator, it doesn’t work anymore:

Figure 8 – Error message of the query when trying to use the variable “YearMonthFilter_cleared“ with an IN operator. This works when using the variable “YearMonthFilter” (Figure by the Author)

In this context, I tried other functions, like DISTINCT() and VALUES(). While DISTINCT() had no effect, VALUES() has.

For example, while this query doesn’t work:

DEFINE
    VAR YearMonthFilter = TREATAS({ 202604, 202605 }, 'Date'[MonthKey])
    
    VAR YearMonthFilter_cleared = VALUES(YearMonthFilter)

EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[MonthShortName]
                        ,'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]
                        ,"Order Count", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    ,YearMonthFilter_cleared
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

Here is the error message:

Figure 9 – Error when applying VALUES() to a variable with lineage (Figure by the Author)

This works when using an IN operator, which indicates that the lineage is cleared when using VALUES():

DEFINE
    VAR YearMonthFilter = TREATAS({ 202604, 202605 }, 'Date'[MonthKey])
    
    VAR YearMonthFilter_cleared = VALUES(YearMonthFilter)

EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[MonthShortName]
                        ,'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]
                        ,"Order Count", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    ,'Date'[MonthKey] IN YearMonthFilter
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

Here is the result of the query:

Figure 10 – When using IN after applying VALUES to a variable with lineage, it works (Figure by the Author)

The documentation for VALUES() states that this function requires a table or column reference.

But this behaviour shows that it depends on how the variable is used in the query.

As the variable has a lineage set, VALUES() accept it as a column reference.

Manipulate the lineage

Next, let’s change how we apply a filter by manipulating the lineage.

I want to create a report showing all online orders by country, along with the orders served by stores in each country.

For example, I have 68 customer orders from Germany in April 2026. I want to see how many orders have been served by stores in that country, if any.

Something like this:

Figure 11 – List of orders made by customers in their country and the orders served in other countries (Figure by the Author)

I can do it by working with a variable:

DEFINE
    MEASURE 'All Measures'[Orders served from Country] =
            VAR SelCountry = SELECTEDVALUE('Customer'[RegionCountryName])
            
            RETURN
                CALCULATE([Online Order Count]
                            ,REMOVEFILTERS(Customer[RegionCountryName])
                            ,'Store'[RegionCountryName] = SelCountry
                            )


EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[Month]
                        ,'Date'[MonthKey]
                        ,'Customer'[RegionCountryName]
                        ,"Order Count", [Online Order Count]
                        ,"Order Count Country Check", [Order Count Country Check]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    )
            ORDER BY 'Date'[Year]
                        ,'Date'[Month]
                        ,'Customer'[RegionCountryName]

In this approach, I store the current country in a variable. Then I remove the filter from Customer[RegionCountryName]. And I replace it with a filter on ‘Store'[RegionCountryName].

This is the result:

Figure 12 – Extract of the result of the first query, using a variable and an equal filter for the Stores country (Figure by the Author)

Or I can do it in this way by using TREATAS():

DEFINE
    MEASURE 'All Measures'[Orders served from Country] =
                CALCULATE([Online Order Count]
                            ,REMOVEFILTERS(Customer[RegionCountryName])
                            ,TREATAS(VALUES('Customer'[RegionCountryName])
                                        ,'Store'[RegionCountryName])
                            )


EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[Month]
                        ,'Date'[MonthKey]
                        ,'Customer'[RegionCountryName]
                        ,"Order Count", [Online Order Count]
                        ,"Orders served from Country", [Orders served from Country]
                        )
                    ,'Product'[BrandName] = "Adventure Works"
                    )
            ORDER BY 'Date'[Year]
                        ,'Date'[Month]
                        ,'Customer'[RegionCountryName]

In this approach, I again remove the filter from Customer[RegionCountryName]. But then I use TREATAS() to switch the filter lineage for the current country to ‘Store'[RegionCountryName].

In this approach, I don’t need a variable; I can directly filter the store table by the current country.

This code is much shorter but can be harder to understand for readers who don’t know how TREATAS() works.

Working with tables

Since we can create ad hoc tables in DAX, we might run into issues because those tables lack lineage.

I can start writing code about this, but SQLBI already did this, and you can read their article, which is very well explained:

You can find it here:

https://www.sqlbi.com/articles/understanding-data-lineage-in-dax

Conclusion

The concept of lineage can be hard to understand, even though we use it all the time when working with filters in DAX.

Power BI generates code using TREATAS() all the time when it applies report filters.

And sometimes it can lead to simpler DAX code when you know how to manipulate it efficiently.

This can become vital when I create a table with DAX from existing tables. The table will retain the lineage. This can lead to issues when I try to add relationships to the source tables. Without clearing the lineage, I will encounter an error due to a circular dependency.

I encourage you to start experimenting with the concepts shown here and to try to optimise your DAX code.

Although “optimise” is the wrong word, as I didn’t notice any performance improvements when using the variants shown.

But having DAX code which is shorter and easier to read can be an optimisation by itself.

Have fun working with it.

References

A SQLBI article about working with lineage:

https://www.sqlbi.com/articles/understanding-data-lineage-in-dax

Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be used freely under the MIT License, as described in this document. I changed the dataset to shift the data to contemporary dates.