Explaining Lineage in DAX | In the direction of Knowledge Science

The Statistics of Token Choice: Logits, Temperature, and Prime-P Walkthrough

Studying From Pairwise Preferences: An Introduction to the Bradley Terry Mannequin

Introduction

In DAX, lineage is a crucial idea, and it is important to grasp the right way to work with and manipulate it.

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

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

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

That is an extract of the consequence from the question:

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

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

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

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 Depend", [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 up to now.

That is the consequence:

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

On this case, the lineage will not be essential, as a scalar worth units the filter.

However we will set a lineage by utilizing the TREATAS() perform:

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

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

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

The consequence doesn’t change, however the question is less complicated, as I don’t must move the situation as “column equals the filter-value”.

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

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

However it does in a different way:

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 Depend", [Online Order Count]
                        )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

Clearing the lineage

You would possibly encounter conditions the place you could clear the lineage.

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

For instance, have 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 Depend", [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 wish so as to add an equal filter as proven above in Determine 2 (Determine by the Writer)

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

It would 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 Depend", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Journey Works"
                    ,'Date'[MonthKey] = YearMonthFilter_cleared
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

As you possibly can see right here, the question returns the identical consequence 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 approach 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 depend for the whole 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 Depend", [Online Order Count]
                        ,"Order Depend full 12 months", [Order Count full year]
                        )
                    ,'Product'[BrandName] = "Journey Works"
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

That is an extract of the consequence:

Determine 6 – Extract of question to calculate the order depend for the whole 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 Depend", [Online Order Count]
						,"Order Depend 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 now 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 consequence:

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

Now I get the identical consequence as earlier than:

Determine 8 – Consequence 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 Depend", [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 solution 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 Depend", [Online Order Count]
                        )
                    ,'Product'[BrandName] = "Journey Works"
                    ,YearMonthFilter_cleared
                    )
        ORDER BY 'Date'[MonthKey]
                        ,'Product'[ProductCategoryName]

Sadly, this technique 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 will not be cleared as a result of the question nonetheless works, however the month filter is eliminated.

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

Determine 8 – Error message of the question when attempting 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 capabilities, 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 Depend", [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 Depend", [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 reveals that it relies 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 Depend", [Online Order Count]
                        ,"Order Depend Nation Verify", [Order Count Country Check]
                        )
                    ,'Product'[BrandName] = "Journey Works"
                    )
            ORDER BY 'Date'[Year]
                        ,'Date'[Month]
                        ,'Buyer'[RegionCountryName]

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

That is the consequence:

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 approach by utilizing 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 Depend", [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 strategy, I once more take away the filter from Buyer[RegionCountryName]. However then I exploit TREATAS() to modify the filter lineage for the present nation to ‘Retailer'[RegionCountryName].

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

This code is far shorter however may be more durable to grasp for readers who don’t understand how 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 may be very properly defined:

You will discover it right here:

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

Conclusion

The idea of lineage may be laborious to grasp, regardless 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 typically it may well result in easier DAX code when you know the way to control it effectively.

This may turn into very important after I create a desk with DAX from present tables. The desk will retain the lineage. This may result in points when I attempt to add relationships to the supply tables. With out clearing the lineage, I’ll encounter an error on account 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 improper 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 may 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 free of charge from Microsoft right here.

The Contoso Knowledge can be utilized freely underneath the MIT License, as described on this doc. I modified the dataset to shift the info to modern dates.