Forum Discussion

JustinDoh1's avatar
JustinDoh1
Post Prodigy
1 year ago
Solved

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...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.