Forum Discussion

JRHans09's avatar
JRHans09
Icon for Resolver II rankResolver II
4 years ago

Trying to create a budget table - process is timing out

I am trying to create a budget table that leverages existing data from previous years to project averages across multiple parameters:

1. Year

2. Month in Year

3. Branch

4. Cost Center (Line of Business)

5 Sales Rep

 

This is working, however when I add one more parameter, Revenue Type, the process is timing out.

 

This code works:

*Sales Rep data redacted

 

Then, when removing the comment tags to apply RemProd[RevType] to the CrossJoin section, as here, the process times out:

 

The below formula times out:

 

Here is the timeout error message:

 

Task Manager screen shots:

 

And here:

 

Here are the specs for my machine:

 

Is this issue related to my machine capabilities? Is there any way to accomplish the same goal with a different table formula or otherwise?

Thanks in advance.

 

Need Help

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    This code is generating every possible combination of the columns you've specified, even combinations which aren't possible - presumably a branch is associated with a limited number of cost centres, and a sales person with a limited number of branches.

    In the ideal situation where you have a chain of 1-to-many relationships you can SUMMARIZE on the last many table to get just the valid combinations. e.g. if you have many sales people to 1 branch and then many branches to 1 cost centre you could do

    Base Table = SUMMARIZE( 'Sales person', 'Sales person'[sales person ID], 'Branch'[Branch ID], 'Cost Centre'[Cost centre ID])

    and use that with either CROSSJOIN or GENERATE.

    Also, depending on how large your date table is it might be worth filtering that before doing the CROSSJOIN - no point generating the additional rows just to filter them out.