Favorite Quick & Easy DAX: Multiple FILTER Criteria

Recently, while discovering how to use the EARLIER function to calculate moving averages, I came upon syntax in DAX to enable passing multiple criteria to filter a calculated value in a column or measure.  In my early days of DAX, I got by with using the AND function.

=calculate ( average (mobiledevice[# Activations]), filter( mobiledevice, AND(
mobiledevice[Date] >= earlier(mobiledevice[Prior7 Days]), mobiledevice[Date] <=
earlier (mobiledevice[End Date]) ) )

When I tried to add another filter criteria, of course I’d get the “Too many arguments were passed” error message. There are just too many cases where you need 3 or more filter criteria for a sum, average, or count function.

But thanks to Alberto Ferrari and others, I’ve confirmed that it’s possible to add several filter criteria. In the example below, note the use of && to add multiple FILTER criteria, as in the formula I posted on calculating moving averages.

=calculate ( average (mobiledevice[# Activations]), filter( mobiledevice,
mobiledevice[Date] >= earlier(mobiledevice[Prior7 Days]) && mobiledevice[Date]
<= earlier (mobiledevice[End Date]) && mobiledevice[Key] = earlier (mobiledevice
[Key]) ) )
Thanks for reading! Questions? Comments? Contact us for more information or with other questions. 
Phone: 312-602-4000
Email: marketing@westmonroepartners.com
222 W. Adams
Chicago, IL 60606
Show Buttons
Share On Facebook
Share On Twitter
Share on LinkedIn
Hide Buttons