Forum Discussion
Averagex with a Criterion
Is it possible to nest a CALCULATE function in an AVERAGEX to create an equivalent to the Excel AVERAGEIF function? And also, the expression argument of the CALCULATE function, does it have to be a measure created already in DAX or that something like Average(Fieldname) will do?
TY
About point 2. It will depend on if you are using related dimension table column to filter data (or using more than 2 columns for criteria).
If not, you don't need Filter().
Though, in the background, it's being converted to Filter(ALL(table),Condition).
As well, when you use two different columns as condition, you'll need to write explicit expression using Filter() rather than relying on automatic conversion in the background.
Basically, I make it a habit to use FILTER(), so that I don't stumble with more complex conditions (i.e. all my Calculate() construct has same base syntax).
See link for more detailed explanation of filter argument in Calculate().
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
3 Replies
You can use following construct.
AvgIf = Calculate(Average(Table[Column]),Filter(Table,[CriteriaColumn]="Criteria"))
- 1. Good, so no need for an iterative function.
2. If I choose a boolean criteria, I don't have to use FILTER?About point 2. It will depend on if you are using related dimension table column to filter data (or using more than 2 columns for criteria).
If not, you don't need Filter().
Though, in the background, it's being converted to Filter(ALL(table),Condition).
As well, when you use two different columns as condition, you'll need to write explicit expression using Filter() rather than relying on automatic conversion in the background.
Basically, I make it a habit to use FILTER(), so that I don't stumble with more complex conditions (i.e. all my Calculate() construct has same base syntax).
See link for more detailed explanation of filter argument in Calculate().
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
