locked
Use of two Earlier functions causes calculation to run forever RRS feed

  • Question

  • Number of rows in table: 1.8MM

    Single earlier statement:

    =calculate(
        countrows(distinct(timesheet[proj_name]))
        ,all(timesheet)
        ,timesheet[week_end_date]=earlier(timesheet[week_end_date])

                )

    The above takes about 15 seconds to calculate

    Double earlier statement:

    =calculate(
        countrows(distinct(timesheet[proj_name]))
        ,all(timesheet)
        ,timesheet[week_end_date]=earlier(timesheet[week_end_date])
        ,timesheet[resource_number]=earlier(timesheet[resource_number])

                )

    this should return for each week and each resource, the number of projects worked on

    Never waited for it to finish calculating (over 90 minutes before I killed it).  For reference sake, the second formula (against 1.8MM records) should produce 342,872 distinct values.

    Am I asking Powerpivot to do too much, or am I approching the solution in the wrong way? 

    Any thoughts?

     

    Thanks,

    Dave

    Thursday, January 3, 2013 10:31 PM

Answers

  • What I would suggest is to concatenate the columns and then just do one earlier function. Something like

    1) Create a new calculated column called temp which concatenates timesheet[week_end_date] and timesheet[resource_number] and hide it.

    =timesheet[week_end_date] + ',' + timesheet[resource_number]

    2) Create the calculated measure now

    =calculate(    countrows(distinct(timesheet[proj_name]))    ,all(timesheet)    ,timesheet[temp]=earlier(timesheet[temp])            )

    This should get rid of the unnecessary iteration. I didnt check the syntax but I am guessing this should work.

    Update

    I was thinking about it and I thought a quicker and elegant way would be

    =CALCULATE(countrows(distinct(timesheet[proj_name])) , ALLEXCEPT( ‘timesheet’, timesheet[week_end_date], timesheet[resource_number] ) )
    Again, didnt check the syntax but you get the idea!



    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter


    Friday, January 4, 2013 3:08 PM
    Answerer
  • The following calculated columns should return the expected result:

    =CALCULATE(
         DISTINCTCOUNT(timesheet[proj_name]))
         ,ALLEXCEPT(
            timesheet
           , timesheet[week_end_date]
           , timesheet[resource_number]
          )
    )


    The Data Specialist (Blog)

    • Marked as answer by goatdude66 Friday, January 4, 2013 8:58 PM
    Friday, January 4, 2013 5:01 PM

All replies

  • Hi Dave -

    Do you have to have this as a column?  Instead of calculating this for every single row in a calculated column, you could accomplish this in a calculated measure. 

    Use SUMMARIZE to group by the week and resource and get the project count like this:

    =SUMX(SUMMARIZE(Timesheet ,Timesheet[resource_number] ,Timesheet[week_end_date] ,"CountProjects",CALCULATE(DISTINCTCOUNT(Timesheet[proj_name]))
    ) ,[CountProjects] )

    That should be much faster.  Let me know if that helps.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Friday, January 4, 2013 3:26 AM
    Answerer
  • If it has to be a calculated column, you should be able to use FILTER instead of ALL. 

    =CALCULATE(DISTINCTCOUNT(Timesheet[proj_name])
                               ,FILTER(Timesheet,
                                               (Timesheet[resource_number] = EARLIER(Timesheet[resource_number])
                                                 && Timesheet[week_end_date] = EARLIER(Timesheet[week_end_date]))
                                             )
                                )


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Friday, January 4, 2013 3:46 AM
    Answerer
  • Hi Brent.

    Thanks for your response.  undortunately, this needs to be a column because I want to use it as a dimension to create a distribution of how many projects on which our resources work per week.

    I'll give your (next) reply a try to see if it works any faster.  In the meantime, I'm gonna experiment with your measure formula above simply because I don't understand it :-)

    Thanks again for your help!

    Dave

    Friday, January 4, 2013 2:25 PM
  • Hi Brent,

    As i mentioned in my prior response, I'll give this a try.  Fingers crossed.

    Thanks!!

    Dave

    Friday, January 4, 2013 2:25 PM
  • What I would suggest is to concatenate the columns and then just do one earlier function. Something like

    1) Create a new calculated column called temp which concatenates timesheet[week_end_date] and timesheet[resource_number] and hide it.

    =timesheet[week_end_date] + ',' + timesheet[resource_number]

    2) Create the calculated measure now

    =calculate(    countrows(distinct(timesheet[proj_name]))    ,all(timesheet)    ,timesheet[temp]=earlier(timesheet[temp])            )

    This should get rid of the unnecessary iteration. I didnt check the syntax but I am guessing this should work.

    Update

    I was thinking about it and I thought a quicker and elegant way would be

    =CALCULATE(countrows(distinct(timesheet[proj_name])) , ALLEXCEPT( ‘timesheet’, timesheet[week_end_date], timesheet[resource_number] ) )
    Again, didnt check the syntax but you get the idea!



    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter


    Friday, January 4, 2013 3:08 PM
    Answerer
  • The following calculated columns should return the expected result:

    =CALCULATE(
         DISTINCTCOUNT(timesheet[proj_name]))
         ,ALLEXCEPT(
            timesheet
           , timesheet[week_end_date]
           , timesheet[resource_number]
          )
    )


    The Data Specialist (Blog)

    • Marked as answer by goatdude66 Friday, January 4, 2013 8:58 PM
    Friday, January 4, 2013 5:01 PM
  • Hi Jason,

    Youir updated folmula is rocket fast.  Took only aout 5 seconds (compared to 18 hours).  Now I have to figure out how it works!

    Thanks very much!!

    Dave

    Friday, January 4, 2013 8:57 PM
  • Hi Laurent,

    Same suggestion as Jason.  Works rocket fast!!

    Thanks for your help!

    Dave

    Friday, January 4, 2013 8:59 PM