Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Can someone explain how a table expression is applied as filter argument in calculate. 2 measurea virtual table based on the filter condition is created , then the expression is evaluated
Solved! Go to Solution.
Table Expression as Filter
When you use a table expression as a filter argument in the CALCULATE function, DAX creates a virtual table based on the filter condition specified in the table expression. This virtual table is then used to modify the filter context of the calculation.
Example
Consider the following DAX expression:
TotalSalesFiltered = CALCULATE(
[TotalSales],
FILTER(
Sales,
Sales[Region] = "North"
)
)
In this example:
[TotalSales] is the measure or expression that calculates the total sales.
FILTER(Sales, Sales[Region] = "North") is the table expression used as a filter argument. This expression creates a virtual table that includes only the rows from the Sales table where the Region column has the value "North".
How It Works
Creating the Virtual Table: The FILTER function generates a virtual table that contains only the rows from the Sales table where the Region is "North".
Modifying the Context: This virtual table is then used to modify the filter context of the CALCULATE function. Essentially, it tells CALCULATE to consider only the rows in this virtual table for the calculation.
Evaluating the Expression: Finally, the [TotalSales] measure is evaluated in this new filter context, which includes only the rows where the Region is "North".
Proud to be a Super User! |
|
The first thing to note is that all filters are actually tables. The first example in your screenshot is internally translated to
Cases GT 300 #1 =
CALCULATE (
[Total Sales],
FILTER ( ALL ( 'Wine sales'[Cases sold] ), 'Wine sales'[Cases sold] > 300 )
)
The FILTER statement produces a single-column table including all the values which exist in the data which are greater than 300. This is then used as the filter.
The reason that it can act as a filter is lineage. DAX keeps track of where the values came from, so it knows that the list of numbers are actually from the 'Wine sales'[cases sold] column. That lineage will be preserved as long as the table you use as a filter only uses column references rather than any functions or expressions.
You can also use TREATAS to change the lineage of any tables you create, so you could write a measure to show the number of cases sold less than 5 with something like
Cases LT 5 =
VAR MyNumbers = { 1, 2, 3, 4 }
RETURN
CALCULATE ( [Total Sales], TREATAS ( MyNumbers, 'Wine sales'[cases sold] ) )
Tables created like this do not even have to be in the same table in the model. You could write a measure to show sales in the US of Contoso products like
US Contoso Sales =
CALCULATE (
[Sales Amount],
TREATAS (
{ ( "United States", "Contoso" ) },
'Customer'[Country],
'Product'[Brand]
)
)
That would apply a filter on the Customer[Country] column and the Product[Brand] column.
When I first understood that filters are tables it was a real eye opener. Once you get that, it opens up all sorts of possibilities as to how to perform various complex calculations.
Table Expression as Filter
When you use a table expression as a filter argument in the CALCULATE function, DAX creates a virtual table based on the filter condition specified in the table expression. This virtual table is then used to modify the filter context of the calculation.
Example
Consider the following DAX expression:
TotalSalesFiltered = CALCULATE(
[TotalSales],
FILTER(
Sales,
Sales[Region] = "North"
)
)
In this example:
[TotalSales] is the measure or expression that calculates the total sales.
FILTER(Sales, Sales[Region] = "North") is the table expression used as a filter argument. This expression creates a virtual table that includes only the rows from the Sales table where the Region column has the value "North".
How It Works
Creating the Virtual Table: The FILTER function generates a virtual table that contains only the rows from the Sales table where the Region is "North".
Modifying the Context: This virtual table is then used to modify the filter context of the CALCULATE function. Essentially, it tells CALCULATE to consider only the rows in this virtual table for the calculation.
Evaluating the Expression: Finally, the [TotalSales] measure is evaluated in this new filter context, which includes only the rows where the Region is "North".
Proud to be a Super User! |
|
So suppose I write an express calcalute( countrows(regions), filter(sales, sales [qty] >300)) how does that filter argument (table) used to evaluate the expression. In this case, "regions" table is part of the expanded table of sales. Could you explain how the table as filter argument used as filter context to evaluate the expression.
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
26 | |
15 | |
13 | |
10 |