topic Re: How to write DAX that uses TOPN AVERAGEX and FILTER in DAX Commands and Tips

How to write DAX that uses TOPN AVERAGEX and FILTER

AaronToth — Wed, 26 Oct 2022 20:54:33 GMT

I have a DAX measure that is able to look at a table and get me the TOPN/AVERAGEX results

TOPN_AVG:= AVERAGEX(TOPN(151, Table1, Table1[column], DESC), Table1[column])

This is working fine but fails when I tried to add expression conditions or a filter to the DAX code. If I add the condition in the expression, or a filter, I see the following error(s)

#errors

TOPN_AVG:= AVERAGEX(TOPN(151, Table1, Table1[column], DESC), Table1.Column2 = 'RED')

TOPN_AVG:= AVERAGEX(TOPN(151, Table1, Table1[column], DESC), FILTER(Table1, Table1.Column2 = 'RED')

AVERAGEX cannot work with type BOOLEAN

Multiple columns cannot be converted to a scaler

How do I add a two column filter/condition to this code? I'm trying to capture the "SELECTEDVALUE" from two slicers, and have them added as filters to the code.

Re: How to write DAX that uses TOPN AVERAGEX and FILTER

Jihwan_Kim — Thu, 27 Oct 2022 03:10:24 GMT

Hi,

I am not sure how your desired outcome looks like, but please try the below.

TOPN_AVG := AVERAGEX ( TOPN ( 151, FILTER ( Table1, Table1[Column2] = 'RED' ), Table1[column], DESC ), Table1[column] )

Re: How to write DAX that uses TOPN AVERAGEX and FILTER

AaronToth — Thu, 27 Oct 2022 11:00:16 GMT

Thanks for your answer, but I was looking for something like this ->
In your example, you omitted the "SELECTEDVALUE" part. I have added that below. Together, we have solved this.

TOPN_AVG :=
AVERAGEX (
TOPN ( 151, FILTER ( Table1, Table1[Column2] = FILTER(Table1, Table1[column2] = SELECTEDVALUE[column2])), Table1[column], DESC ),
Table1[column]
)

Re: How to write DAX that uses TOPN AVERAGEX and FILTER

v-easonf-msft — Wed, 02 Nov 2022 05:50:32 GMT

Hi, AaronToth

If your problem has been solved, please accept your reply as solution to close this thread.

So that other community members will easily find the solution when they get the same issue.

If you stiil need help, please share more details.

Best Regards,
Community Support Team _ Eason

Re: How to write DAX that uses TOPN AVERAGEX and FILTER

AaronToth — Wed, 02 Nov 2022 13:22:44 GMT

I have sent a message to the person who *thinks* they solved my issue. As you can see, they didn't entirely answer it (SELECTEDVALUE) and I did. I was not sure about approving my own solution, but now I will.

Thanks!