Explaining Lineage in DAX | Towards Data Science

Introduction

In DAX, lineage is a crucial idea, and it is important to know easy methods to work with and manipulate it.

As I did in previous articles, I’ll use DAX queries to clarify this idea and its results.

I begin with a easy question to get the order rely for the product of the model “Journey Works”:

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

That is an extract of the outcome from the question:

Determine 1 – Results of the bottom question (Determine by the Writer)

This question returns 180 rows. Hold it in thoughts, as it will likely be necessary in a while.

Subsequent, I’ll introduce a filter for a particular month and present the lineage’s position.

Set the lineage

I’ll add a filter for April 2026:

DEFINE
    VAR YearMonthFilter = 202604

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

On this case, I outline a variable and set the worth to 202604.

Subsequent, I add it as a filter to the CALCULATETABLE() perform.

Nothing particular to this point.

That is the outcome:

Determine 2 – Question and results of the question with a easy filter (Determine by the Writer)

On this case, the lineage just isn’t necessary, as a scalar worth units the filter.

However we will set a lineage through the use of the TREATAS() perform:

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

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

As you’ll be able to see, the introduction of TREATAS() permits us to go the variable as a filter. CALCULATETABLE() makes use of the lineage set by TREATAS() as a filter on the column ‘Date'[MonthKey].

The outcome doesn’t change, however the question is easier, as I don’t must go the situation as “column equals the filter-value”.

Determine 3 – Results of the question which makes use of TREATAS() (Determine by the Writer)

Actually, Energy BI makes use of this manner on a regular basis when it passes filters set in a report back to the semantic mannequin.

However it does otherwise:

It defines variables, units the lineage and provides all filters on to SUMMARIZECOLUMNS():

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

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

Clearing the lineage

You may encounter conditions the place that you must clear the lineage.

The tactic for doing it varies relying on whether or not you’ve a single or a number of values as a filter.

For instance, take a look at the next code, the place I exploit VALUE() to take away the lineage on the earlier 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 Rely", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Journey Works"
                    ,YearMonthFilter_cleared
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

That is the error delivered by Energy BI:

Determine 4 – Error Message when clearing the lineage with VALUES(). With out Lineage, we would want so as to add an equal filter as proven above in Determine 2 (Determine by the Writer)

The engine can’t work with the filter in line 71 as a result of it not has lineage.

It’s going to work on this type:

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

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

As you’ll be able to see right here, the question returns the identical outcome as earlier than:

Determine 5 – Results of the question with the elimination of the lineage and the change of the filter (Determine by the Writer)

Discover the change of the filter argument in line 91.

However there’s a easier method of clearing the lineage when working with measures.

Take a look at the next question with the Measure [Order Count full year], which calculates the order rely for all the 12 months:

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 Rely", [Online Order Count]
                        ,"Order Rely full 12 months", [Order Count full year]
                        )
                    ,'Product'[BrandName] = "Journey Works"
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

That is an extract of the outcome:

Determine 6 – Extract of question to calculate the order rely for all the 12 months (Determine by the Writer)

Now I add a scalar worth 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 Rely", [Online Order Count]
						,"Order Rely full 12 months", [Order Count full year]
						)
					,'Product'[BrandName] = "Journey Works"
					)
		ORDER BY 'Date'[MonthKey]
						,'Product'[ProductCategoryName]

This operation clears the lineage, and the measure not works:

Determine 7 – Error message after clearing the lineage throughout the Measure (Determine by the Writer)

What I can nonetheless do is use an equal filter to get the earlier outcome:

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 Rely", [Online Order Count]
                                        ,"Order Rely full 12 months", [Order Count full year]
                                        )
                                  ,'Product'[BrandName] = "Journey Works"
                                  )
             ORDER BY 'Date'[MonthKey]
                                        ,'Product'[ProductCategoryName]

Now I get the identical outcome as earlier than:

Determine 8 – Outcome after switching to an equal filter after clearing the lineage (Determine by the Writer)

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

For instance, two months:

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

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

Right here is the results of this question:

Determine 6 – Results of the question with two values within the filter (Determine by the Writer)

One method to take away the lineage of a variable with a number of values is to make use of 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 Rely", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Journey Works"
                    ,YearMonthFilter_cleared
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

Sadly, this methodology removes the filter altogether, and all months are returned in 180 rows (The identical variety of rows as with the preliminary question):

Determine 7 – Results of utilizing SUMMARIZECOLUMN() with a column filter with a number of values. (Determine by the Writer)

Technically, the lineage just isn’t cleared as a result of the question nonetheless works, however the month filter is eliminated.

However while you strive utilizing the variable “YearMonthFilter_cleared” with an IN operator, it doesn’t work anymore:

Determine 8 – Error message of the question when making an attempt to make use of the variable “YearMonthFilter_cleared“ with an IN operator. This works when utilizing the variable “YearMonthFilter” (Determine by the Writer)

On this context, I attempted different features, like DISTINCT() and VALUES(). Whereas DISTINCT() had no impact, VALUES() has.

For instance, whereas this question 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 Rely", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Journey Works"
                    ,YearMonthFilter_cleared
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

Right here is the error message:

Determine 9 – Error when making use of VALUES() to a variable with lineage (Determine by the Writer)

This works when utilizing an IN operator, which signifies that the lineage is cleared when utilizing 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 Rely", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Journey Works"
                    ,'Date'[MonthKey] IN YearMonthFilter
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

Right here is the results of the question:

Determine 10 – When utilizing IN after making use of VALUES to a variable with lineage, it really works (Determine by the Writer)

The documentation for VALUES() states that this perform requires a desk or column reference.

However this behaviour exhibits that it will depend on how the variable is used within the question.

Because the variable has a lineage set, VALUES() settle for it as a column reference.

Manipulate the lineage

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

I wish to create a report exhibiting all on-line orders by nation, together with the orders served by shops in every nation.

For instance, I’ve 68 buyer orders from Germany in April 2026. I wish to see what number of orders have been served by shops in that nation, if any.

One thing like this:

Determine 11 – Record of orders made by clients of their nation and the orders served in different nations (Determine by the Writer)

I can do it by working with a variable:

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


EVALUATE
    CALCULATETABLE(
        SUMMARIZECOLUMNS('Date'[Year]
                        ,'Date'[Month]
                        ,'Date'[MonthKey]
                        ,'Buyer'[RegionCountryName]
                        ,"Order Rely", [Online Order Count]
                        ,"Order Rely Nation Verify", [Order Count Country Check]
                        )
                    ,'Product'[BrandName] = "Journey Works"
                    )
            ORDER BY 'Date'[Year]
                        ,'Date'[Month]
                        ,'Buyer'[RegionCountryName]

On this method, I retailer the present nation in a variable. Then I take away the filter from Buyer[RegionCountryName]. And I substitute it with a filter on ‘Retailer'[RegionCountryName].

That is the outcome:

Determine 12 – Extract of the results of the primary question, utilizing a variable and an equal filter for the Shops nation (Determine by the Writer)

Or I can do it on this method through the use of TREATAS():

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


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

On this method, I once more take away the filter from Buyer[RegionCountryName]. However then I exploit TREATAS() to change the filter lineage for the present nation to ‘Retailer'[RegionCountryName].

On this method, I don’t want a variable; I can straight filter the shop desk by the present nation.

This code is far shorter however could be tougher to know for readers who don’t know the way TREATAS() works.

Working with tables

Since we will create advert hoc tables in DAX, we’d run into points as a result of these tables lack lineage.

I can begin writing code about this, however SQLBI already did this, and you may learn their article, which could be very effectively defined:

You will discover it right here:

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

Conclusion

The idea of lineage could be onerous to know, despite the fact that we use it on a regular basis when working with filters in DAX.

Energy BI generates code utilizing TREATAS() on a regular basis when it applies report filters.

And generally it might probably result in easier DAX code when you understand how to control it effectively.

This could develop into very important once I create a desk with DAX from current tables. The desk will retain the lineage. This could result in points when I attempt to add relationships to the supply tables. With out clearing the lineage, I’ll encounter an error as a consequence of a round dependency.

I encourage you to begin experimenting with the ideas proven right here and to attempt to optimise your DAX code.

Though “optimise” is the incorrect phrase, as I didn’t discover any efficiency enhancements when utilizing the variants proven.

However having DAX code which is shorter and simpler to learn could be an optimisation by itself.

Have enjoyable working with it.

References

A SQLBI article about working with lineage:

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

Like in my earlier articles, I exploit the Contoso pattern dataset. You may obtain the ContosoRetailDW Dataset at no cost from Microsoft here.

The Contoso Knowledge can be utilized freely below the MIT License, as described in this document. I modified the dataset to shift the info to up to date dates.

Source link

Explaining Lineage in DAX | Towards Data Science

Baseline Enterprise RAG, From PDF to Highlighted Answer

RAG Is Burning Money — I Built a Cost Control Layer to Fix It

Why Gradient Descent Became Stochastic

Five Questions About Chronos-2, the Time Series Foundation Model

Why AI Still Can’t Solve Your Real Mathematical Optimization Problem

EmoNet: Speaker-Aware Transformers for Emotion Recognition — and What I’d Build Differently in 2026

smarter, more capable robot mower

Are your employees happy? 10 startups working to make teams feel better in the office

Hands-On With Gemini Spark: I Gave It Access to My Life and It Friend-Zoned My Boyfriend

Kalshi debuts political power index as regulation pressures rise

Featured Picks

The top 10 US tech billionaires collectively added $550B+ to their combined net worth in 2025, reaching $2.5T by December 24, up from $1.9T at the year’s start (Rafe Rosner-Uddin/Financial Times)

Innovative bike helmet reduces brain injury risk

The US Invaded Venezuela and Captured Nicolás Maduro. ChatGPT Disagrees

Explaining Lineage in DAX | Towards Data Science

Introduction

Set the lineage

Clearing the lineage

Manipulate the lineage

Working with tables

Conclusion

References

Related Posts