top of page
  • Marek Vavrovic

Filter Functions, part two.

Updated: Sep 21, 2021



CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])

Every filter argument can be either a filter removal (such as ALL, ALLEXCEPT, ALLNOBLANKROW), a filter restore (ALLSELECTED), or a table expression returning a list of values for one or more columns or for an entire expanded table.

Example 1

with SUMX function

In this measure, CALCULATETABLE returns a filtered table where Item="Pen". SUMX function calculates SUM of all ItemSold for each row.

Total number of pen sold = SUMX( CALCULATETABLE(StoreA,StoreA[Item]="Pen") ,StoreA[ItemsSold])

Example 2

with FILTER(ALL()...)

When a filter argument has the form of a predicate with a single column reference, the expression is embedded into a FILTER expression that filters all the values of the referenced column.

Average Male Salary = AVERAGEX( CALCULATETABLE(DimCustomer, FILTER( ALL(DimCustomer), DimCustomer[Gender]="M")) ,DimCustomer[Salary])

this function returns the average male's salary for each row.

Example 3

This function calculates the salary for each row where birthdate is not null.


Returns the value in the column prior to the specified number of table scans (default is 1).


EARLIER(<column>, <number>)

Example 1

adding a column to an existing table

in this example, I calculate the number of transactions in one particular day.

Example 2

EARLIER in measure. can use SELECTCOLUMNS function instead of ADDCOLUMNS.

Example 3

Calculate cumulative total

Earlie RT = CALCULATE( SUM(tblOrders[Quantity]), FILTER(tblOrders,EARLIER(tblOrders[Date],1)>=tblOrders[Date]))


Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.



Example 1

Gill sales = CALCULATE(SUM(StoreA[ItemsSold]), KEEPFILTERS(StoreA[Person]="Gill"))

Another way how to write this measure is using a combination of FILETER(VALUES(<table>)

Example 2

I want to calculate a measure for the Product_A where Quantity > 5

Result = CALCULATE ( SUM ( tblOrders[Quantity] ), KEEPFILTERS ( FILTER ( ALL ( tblOrders[Product], tblOrders[Quantity] ), tblOrders[Product] = "Product_A" ) ), tblOrders[Quantity] > 5 )


Returns the value when there’s only one value in the specified column, otherwise returns the alternate result.


SELECTEDVALUE(<columnName>[, <alternateResult>])

alternatively you can use


works nice with categorical data !

118 views0 comments

Recent Posts

See All

Bình luận

bottom of page