locked
How to use earlier function RRS feed

  • Question

  • Hello. I'm using this function from a book applied to my data set. When I run it, it freezes up my file (I have 800,000 rows). Why is this? 

    Product[UnitPriceRank] = 

    COUNTROWS (

    FILTER (

    Product,

    Product[UnitPrice] > EARLIER (Product[UnitPrice])

    )

    ) +1

    Thursday, August 4, 2016 5:24 PM

Answers

All replies

  • There's nothing wrong with this formula as far as I can see, but for the purpose you use it for, there's a function that does this: RANKX.

    Product[UnitPriceRank] = RANKX(Product, Product[UnitPrice])

    • Proposed as answer by Charlie Liao Saturday, August 6, 2016 7:32 AM
    Friday, August 5, 2016 7:38 AM
    Answerer
  • Hi Alex,

    Your formula is correct which similar with the sample formula in the document below.
    https://msdn.microsoft.com/en-us/library/ee634551.aspx

    So the issue could be caused by insufficient memory. PowerPivot for Excel is an Add-In which uses a local version of Analysis Services (SSAS) to process data and make calculation to respond to user queries made using Excel 2010. The SSAS engine is loaded in-memory in the process of Excel. The memory available for PowerPivot in a 32 bit version of Excel is just above 1Gb. To avoid this issue, you can use a 64 bit PowerPivot for Excel instead of 32 bit.
    http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by AlexMartini Monday, August 22, 2016 4:43 PM
    Saturday, August 6, 2016 7:40 AM
  • Hi Charlie. I have a 64 bit excel. It's strange though sometimes it runs very slow on things that it shouldn't be running slow on. Not sure how to test or ask IT why. Any thoughts? 
    Friday, August 19, 2016 10:58 PM
  • Alex, please create a new question on this with specifics. What calculation do you use, in which context, etc.
    Monday, August 22, 2016 7:37 AM
    Answerer