Forum Discussion

JustinDoh1's avatar
JustinDoh1
Icon for Post Prodigy rankPost 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 ,

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

JustinDoh1's avatar
JustinDoh1
Icon for Post Prodigy rankPost Prodigy
1 year ago

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.