Introduction
In DAX, lineage is an important concept, and it is important to understand how it works and how to use it.
As I have done in previous articles, I will use DAX queries to explain this concept and its results.
I start with a simple question to find out the order value of the “Adventure Works” product:
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 a snippet of the result from the query:
This query returns 180 rows. Keep it in mind, as it will be important later.
Next, I will introduce the month filter and show the list role.
Set up a genealogy
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:
In this case, the lineage is irrelevant, as the scalar value sets the filter.
But we can set the list 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 a variable as a filter. CALCULATETABLE() uses the array set by TREATAS() as a filter on the 'Date' column[MonthKey].
The result doesn't change, but the question is simple, since I don't need to pass a condition like “column equals filter value”.
In fact, Power BI uses this form every time it passes the filters set in the semantic model report.
But do something different:
It defines the variables, sets the list and adds all the filters directly in 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]Erasing the lineage
You may come across situations where you need to delete a genealogy.
The way to do it varies depending on whether you have one or more values as a filter.
For example, look at the following code, where I use VALUE() to remove the parentage from 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 returned by Power BI:
The engine cannot work with the filter on line 71 because it no longer has a pedigree.
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:
Note the modification of the filter argument in line 91.
But there is an easy way to remove the genealogy when working in steps.
Look at the next question about Measurement [Order Count full year]which calculates the order figure 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 a snippet of the result:
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 list, and the measure is no longer valid:
What I can still do is use an equals 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:
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:
Another way to remove a list of multi-valued variables 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 completely removes the filter, and all months are returned in 180 rows (Same number of rows as the first query):
Technically, the pedigree is not deleted because the query is still active, but the month filter is removed.
But if you try to use the variable “YearMonthFilter_cleared” with the IN operator, it no longer works:
In this context, I tried other functions, such as DISTINCT() and VALUES(). While DISTINCT() had no effect, VALUES() did.
For example, while this query is not working:
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:
This works when you use the IN operator, which indicates that the array is cleared when VALUES() is used:
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:
The VALUES() documentation says that this function requires a table or column reference.
But this behavior shows that it depends on how the variable is used in the query.
Since the variable has an array set, VALUES() accepts it as a column reference.
Manage the genealogy
Next, let's change the way we use the filter by manipulating the genealogy.
I want to create a report that shows all online orders by country, as well as orders delivered by stores in each country.
For example, I have 68 customer orders from Germany in April 2026. I want to see how many orders were given to stores in that country, if any.
Something like this:
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 way, I keep the current world in a dynamic state. Then I remove the filter on Customer[RegionCountryName]. And I change it with the filter 'store'[RegionCountryName].
This is the result:
Or I can do it 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]This way, I also remove the filter from the Customer[RegionCountryName]. However I use TREATAS() to change the current state's filter list to 'store'.[RegionCountryName].
This way, I don't need variables; I can directly sort the store table by current country.
This code is very short but can be difficult to understand for readers who don't know how TREATAS() works.
Working with tables
Since we can create temporary tables in DAX, we may run into problems because those tables do not have a pedigree.
I could start writing code for this, but SQLBI has already done this, and you can read their article, which is very well explained:
You can find it here:
The conclusion
The concept of a list can be difficult to understand, even though we use it all the time when working with filters in DAX.
Power BI generates code using TREATAS() every time it uses report filters.
And sometimes it can lead to simple DAX code if you know how to handle it properly.
This can be important if I create a table with DAX from existing tables. The table will store the genealogy. This can lead to problems when I try to add relationships to the source tables. Without clearing the array, I will encounter an error due to circular dependencies.
I encourage you to start exploring the concepts shown here and try to improve your DAX code.
Although the word “optimize” is a misnomer, as I did not notice any performance improvement when using the indicated variant.
But having DAX code that is short and easy to read can be an improvement in itself.
A pleasure to work with.
References
SQLBI article about performance and lineage:
As in my previous articles, I am using the Contoso sample data. You can download the ContosoRetailDW Dataset for free from Microsoft here.
Contoso data can be freely used under the MIT license, as described in this document. I changed the dataset to change to more modern dates.
