Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AaronToth
Resolver I
Resolver I

How to write DAX that uses TOPN AVERAGEX and FILTER

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.

1 ACCEPTED SOLUTION

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]
)

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

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]
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

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]
)

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

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.