Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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.
User | Count |
---|---|
104 | |
92 | |
88 | |
78 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |