SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

DAX IsFiltered Function

The DAX IsFiltered function returns True when <columnName> is filtered directly and false if there is no direct filter on the column.  The syntax is IsFiltered(<columnName>).  More details regarding this function can be found on MSDN.

I recently used the IsFiltered function to create a measure that is only valid when a user filters by fiscal month of year.  The measure calculates a fill rate based on month end inventory values, the general calculation is:

Fill Rate = SUM(In Stock) / SUM(Count)

Where ‘In Stock’ is an integer (0 or 1) that indicates if a warehouse has a specific product in stock at the end of the month and Count is an integer set to 1 for each warehouse that stocks the product.

If five warehouses had a product in stock at the end of June 2012 and there are seven warehouses in total that stock the product, then the product fill rate for June 2012 is 5 / 7 or 71%.

The PowerPivot includes the following tables. 


The [Date] table consists of the columns: DateKey, Fiscal Month of Year, Fiscal Year and Fiscal Month Sort. 


Item Branch

The [Item Branch] table stores attributes regarding warehouses and products stocked at each warehouse.


Inventory Month End

The [Inventory Month End] table stores foreign keys to the [Date] and [Item Branch] tables.  The table also contains an [In Stock] column and [Counter] column.


A measure called [Fill Rate – I ME] is defined as follows:

Fill Rate – I ME:=IF(ISFILTERED(‘Date’[Fiscal Month of Year]),SUM([InStock]) / SUM([Counter]))

When the user filters to a specific [Fiscal Month of Year], the fill rate value is calculated; otherwise, it is Null.

Some examples are shown below:

In this case, we are filtering by [Fiscal Year] = 2012 and the [Fill Rate – I ME] value is Null.



Now, we will filter by [Fiscal Month of Year] = Jun 2012 and the [Fill Rate – I ME] is calculated.



We can also filter by a year and place the [Fiscal Month of Year] on the rows.


In addition, the user may filter by a specific product or group of products in addition to a fiscal month of year and the fill rate is calculated.

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.


Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...