﻿ Salvo(z) - DAX IsFiltered Function

### DAX IsFiltered Function

##### Wednesday, October 10, 2012
###### by jsalvo

The DAX IsFiltered function returns True when is filtered directly and false if there is no direct filter on the column.  The syntax is IsFiltered().  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.

Date

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.