Forum Discussion
Calculate Average based on multiple filters in a DirectQuery
Hi,
I have the following problem.
I want to calculate an Average per Participant in a survey so each Participant has been given an average throughout the survey based on a label. For instance - I have column A-D and I am looking for column F (column E rounded).
The constraint is that I am using a DirectQuery.
I have more labels so when the label changes to '2' each participant should be given a new average based on the label.
I hope someone can help me!
Thank you very much 🙂
When using DirectQuery,there are some limitations in calculated columns.Please refer to:
Limitations in calculated columns: Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. Additionally, the DAX scalar functions, such as
LEFT(), that are allowed, are limited to those functions that can be pushed to the underlying source. The functions vary depending upon the exact capabilities of the source. Functions that aren't supported aren't listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used.But you can create measure with ALLEXCEPT filter function.
4 Replies
Anonymous , Try a new column like
averagex(Filter(Table, [Participant id] =earlier([Participant id]) ), [Answer])
When using DirectQuery,there are some limitations in calculated columns.Please refer to:
Limitations in calculated columns: Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. Additionally, the DAX scalar functions, such as
LEFT(), that are allowed, are limited to those functions that can be pushed to the underlying source. The functions vary depending upon the exact capabilities of the source. Functions that aren't supported aren't listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used.But you can create measure with ALLEXCEPT filter function.
