Filter Functions, part two.

SELECTEDVALUE

KEEPFILTERS

EARLIER

CALCULATETABLE

syntax:

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.

EARLIER

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

syntax

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.

...you 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]))

KEEPFILTERS

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

syntax:

KEEPFILTERS(<expression>)

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 )

SELECTEDVALUE

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

syntax:

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

alternatively you can use

IF(HASONEVALUE(<columnName>),VALUES(<columnName>),<alternateResult>)

works nice with categorical data !