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

JustinDoh1's avatar
JustinDoh1
Post Prodigy
1 year ago

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'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
      Post 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.