• Marek Vavrovic

Filter Functions, part two.

Updated: Sep 21

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 !




16 views0 comments

Recent Posts

See All

Index

Information functions Filter functions Table manipulation functions Grouping and summarizing Time intelligence functions OPENINGBALANCEYEAR OPENINGBALANCEYEAR(<Expression>,<Dates> [,<Filter>][,<YearEn