Blog Post

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. 

Date

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

image

Item Branch

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

SNAGHTML667781

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.

image

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.

image

image

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

image

image

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

image

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating