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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
JustinDoh1
Post Prodigy
Post Prodigy

Performance issue with AVERAGEX

I have a about 1.1 million rows of data with 7 columns.

 

I tried this measure called "Average" to filter my visual, PBI visual is having a big performance/memory issue.

 

Below is detail of that measure that is causing performance issue: 

 

Average = AVERAGEX
                   (
                    VALUES(DimDate[DateFormat]),
                        CALCULATE(
                                            [ClientD_M1]
                                           )
                    )

 

When I tried on the visual as 'Average is not 0', the visual is having a performance issue:

JustinDoh1_0-1738106615467.png

 

Bottom is detail of a measure called  [ClientD_M1]:
 
ClientD_M1 = count('TblCensus'[ClientID]) + 0
 
Is there any better way to optimize the DAX of "Average" by chance?
 
Or is there any way to apply the filter (value that is greater than 0) in other possibly way?
 
Thanks.
1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

@JustinDoh1 Instead of using VALUES, you can use SUMMARIZE to create a summarized table that might perform better.

 

Average =
AVERAGEX(
FILTER(
SUMMARIZE(
'TblCensus',
DimDate[DateFormat],
"ClientD_M1", [ClientD_M1]
),
[ClientD_M1] > 0
),
[ClientD_M1]
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

6 REPLIES 6
JustinDoh1
Post Prodigy
Post Prodigy

@Anonymous Thank you for your help. I think applying additional filter later to the visual would still slow down the performance so I chose "bhanu_gautam"'s soltuion. I have a question. You mentioned about "pre-aggregate data in Power Query". How do I go about doing this?

 

 

Anonymous
Not applicable

Hi @JustinDoh1 ,

For example, you can group your data in Power Query to reduce the amount of data you need to process and complete some calculations in advance. This is just one of the ways to aggregate data in Power Query, and you can explore other methods on your own.
How to GROUP BY or summarize rows - Power Query | Microsoft Learn

Best Regards,
Dino Tao

@Anonymous  Regards to my case where I need to filter by "Average" is not 0, how do I group by? "Average" is a measure. Would Power Query work with a measure, not just column? Thanks.

bhanu_gautam
Super User
Super User

@JustinDoh1 Instead of using VALUES, you can use SUMMARIZE to create a summarized table that might perform better.

 

Average =
AVERAGEX(
FILTER(
SUMMARIZE(
'TblCensus',
DimDate[DateFormat],
"ClientD_M1", [ClientD_M1]
),
[ClientD_M1] > 0
),
[ClientD_M1]
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






@bhanu_gautam I have a question. I am experimeting with performance. It appears that when I try the same query in Bookmark, it is really slow or even it crashes. It says "There's not enough memory to complete this operation." Why is that? 

Anonymous
Not applicable

Hi @JustinDoh1 ,

The AVERAGEX function combined with VALUES may cause performance issues because of the excessive number of rows and context transitions it creates. If you can simplify the context in which AVERAGEX operates, it might help. You can try using the following DAX:

Average = 
AVERAGEX(
    ALLSELECTED(DimDate[DateFormat]),
    [ClientD_M1]
)

If possible, pre-aggregate data in Power Query or in the data source to reduce the number of rows processed in DAX and reduce context switching.

Another approach is to use SUMMARIZE to create a table with the precomputed values, and then use AVERAGEX on that summary table.

Average = 
VAR SummarizedTable = 
    SUMMARIZE(
        'TblCensus',
        'TblCensus'[DateFormat],
        "ClientCount", COUNT('TblCensus'[ClientID])
    )
RETURN
    AVERAGEX(
        SummarizedTable,
        [ClientCount]
    )

Hope this will help you!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.