Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
Cyriackpazhe
Helper I
Helper I

table as calculate argument

Can someone explain how a table expression is applied as filter argument in calculate. 

2 measure2 measurea virtual table based on the filter condition is created , then the expression is evaluated

 

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

@Cyriackpazhe 

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".




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

3 REPLIES 3
johnt75
Super User
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.

bhanu_gautam
Super User
Super User

@Cyriackpazhe 

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".




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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. 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.