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. 

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.