Rob Collie takes us through a good use of FILTER() in DAX:
The thing both of those formulas have in common is that they are using a measure in the filter argument of the CALCULATE function. In both examples here, I’ve highlighted the offending measure in yellow.
CALCULATE([Sightings per Year], [Avg Sighting Length in Mins]>6)
CALCULATE([Sightings per Year],
Observations[TTL Min]>[Avg Sighting Length in Mins])In the first formula, I was trying to use a measure on the left side of the comparison, and in the second, I was trying to use a measure on the right side of the comparison. Both are illegal.
Read on to see why and how you can use FILTER() to solve these problems.