DAX IsFiltered Function
Wednesday, October 10, 2012
The DAX IsFiltered function returns True when
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.
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.