Calculate Average based on multiple filters in a DirectQuery

[Anonymous]

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 🙂

[V-lianl-msft · Accepted solution]

When using DirectQuery,there are some limitations in calculated columns.Please refer to:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#implications-of-usi... 

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. 

[Anonymous]

Okay. I will look for a solution in the back-end then. Thank you.

[amitchandak]

@Anonymous ,  Try a new column like

averagex(Filter(Table, [Participant id] =earlier([Participant id]) ), [Answer])

[Anonymous]

Hi amitchandak,

Thank you very much for your response.

Unfortunately I cannot create a column with AVERAGEX() because I am using a DirectQuery.

When trying to make a measure I get the following where it cannot find ParticipantID: