Forum Discussion

spider_Monkey's avatar
spider_Monkey
Helper I
3 years ago

Automatically Split One Table into Smaller Tables based on a row value (Name)

Hello,    I am working with a dataset with 15mm+ rows, so needless to say I am running into issues while writing some formulas. I have found if I make the data set smaller the formulas work.    S...
General Comment
spider_Monkey's avatar
spider_Monkey
Helper I
3 years ago

Hey Pat,

 

Below is the formula, currently it's just on the table, I was wondering if I did it at the query level that would help.  If it helps when I limit the table to about 16-20k rows the formula works no problem. 

 

Data set is about 15mm rows and 26 columns. 

 

Previous Action = CALCULATE(MAX('Test'[Date]),
(Filter('Test',EARLIER('Test'[ID])='Test'[ID] &&
EARLIER('Test'[Date])>'Test'[Date])

spider_Monkey's avatar
spider_Monkey
Helper I
3 years ago

Do you know of a way to write that formula to make a custom column in the query?

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    Please try this column expression instead.

    Previous Action =
VAR thisdate = 'Test'[Date]
RETURN
    CALCULATE (
        MAX ( 'Test'[Date] ),
        ALLEXCEPT ( 'Test', 'Test'[ID] ),
        'Test'[Date] < thisdate
    )

     

    Pat

    • spider_Monkey's avatar
      spider_Monkey
      Helper I
      3 years ago

      Morning ppm1 ,

       

      No luck it tells me not enough memory to complete the operation. I tried a version of that formula first and got the same error. 

       

      Is there anyway to write this in Mquery?