Visual Has Exceeded Available Resources

DAX DAX Calculations
1

This post is related to a previous post called “Project Revenue Forecasting”. As a refresher, I was attempting to allocate revenue on a daily basis over two dates within one table. The solution (of which a few videos have been created and were helpful) was to create a virtual date table in order to allocate between dates. I have been able to get that to work. However, I have been trying to take it further in order to create cumulative totals on a monthly and yearly basis to produce charts and table showing projected revenue forecast. The issue comes with the yearly cumulative forecast and getting the message in Power BI “Visual has exceeded available resources”. Below are the formulas I’m using in order to create the cumulative totals:

The first formula comes from the previous post and is used to allocate the revenue on a daily basis:
Est. Wt. Revenue per Day =
CALCULATE( SUMX( ‘Practice Participations’, ‘Practice Participations’[Daily Revenue] ),
FILTER( VALUES( Opportunities[Est. Project Start Date] ), Opportunities[Est. Project Start Date] <= MAX( ‘Virtual Dates’[Date] ) ),
FILTER( VALUES( Opportunities[Est. Project End Date] ), Opportunities[Est. Project End Date] >= MIN( ‘Virtual Dates’[Date] ) ) )

I then created a formula that counts the days of the project and can be broken down based on context:
Duration (Days) =
CALCULATE( COUNTAX( ‘Virtual Dates’, [Est. Wt. Revenue per Day] ),
FILTER( VALUES( ‘Virtual Dates’ ), ‘Virtual Dates’[Date] < MAX( Opportunities[Est. Project End Date] ) ) )

Then I created a product of the two in order to obtain the project revenue sum that has now been assigned per day and can therefore yield partial results based on context:
Total Est. Wt. Revenue = [Duration (Days)] * [Est. Wt. Revenue per Day]

Lastly, I use this in order to create the cumulative totals formula:
Yearly Opp. Revenue = CALCULATE( SUMX( ‘Practice Participations’, [Total Est. Wt. Revenue] ), DATESYTD( ‘Virtual Dates’[Date] ) )

The issue I have is this calculation takes too long and I get the error message. Initially the first time I run it, I have no issue and am able to obtain a result. After any changes though, it will then proceed to give me the error message. My understanding of the main problem is the virtual relationship being rather resource intensive. I’m looking to help simplify this in order to get a result quicker. Also, I have filtered the data to the smallest possible set I can, but it is still too large for the method I have listed above.

After searching within Microsoft forums, I came across a promising solution. If I were to type those measures as variables and create a single measure, I am able to produce a result with virtually no delay. The result however doesn’t make sense and I suspect it has to do with the nature of having a virtual relationship within a variable. Is there a way to type the above measures as variables and get the cumulative result? Or are there simpler measures that are less resource intensive that can yield a result in a reasonable amount of time without exceed the resource limit?

Thank you for any insights you might be able to provide.

2

Are you able to post a demo model of the scenario. I don’t think the answer will be too difficult, it just about understanding everything at play here.

I’m not sure about the virtual date table? Why do you think you need to create this one.

The first formula you are using is just the events in progress pattern and that can be used across a standard date table no problem.

Also it seems to me as well you’re using iterating function like SUMX when an aggragating function like SUM is fine. Like for you revenue calculation.

This formula here does do a lot of computation. That’s just the reality. So if you’re trying to do this over a huge date set and the visula is filtered to look over a large amount of time, it is just unfortunately the reality of using this pattern.

Est. Wt. Revenue per Day =
CALCULATE( SUMX( ‘Practice Participations’, ‘Practice Participations’[Daily Revenue] ),
FILTER( VALUES( Opportunities[Est. Project Start Date] ), Opportunities[Est. Project Start Date] <= MAX( ‘Virtual Dates’[Date] ) ),
FILTER( VALUES( Opportunities[Est. Project End Date] ), Opportunities[Est. Project End Date] >= MIN( ‘Virtual Dates’[Date] ) ) )

Sam

3

I have attempted to recreate the scenario with the sample data that is attached. However, my cumulative totals don’t seem to be adding up correctly even though they are copied exactly the same from my full data set.

The virtual date table is just a naming convention to differentiate another date table I have in the model used for other date columns in other tables. The virtual date table is linked to the start date and end date by non-active relationships for these measures.

You mentioned using SUM instead of SUMX. How would I go about doing that since I am attempting to SUM a measure and not a column? I agree this would help reduce computation, but I’m unclear on how to go about doing this with measures.

Forecast Model.7z (331.2 KB)

4

@spanishyoshi
*That file has a weird extension, can you upload a sample pbix file?
*Have you tried using the “Performance Analyzer” in PBI with Dax Studio? This can be a tremendous help in finding bottlenecks
*Yes, variables can and do improve performance and readability.

Also,
Does this error out in your desktop or on pbi service? If it is the latter, there is 1 gig limit on the pro version vs premium ( while desktop just uses your ram)

image
image.png1170×202 7.21 KB

I feel like there’s got to be a better way to use those functions. There’s a lot of iterators going on there and depending on the data model that can really bogg things down.

5

Hi @spanishyoshi, we’ve noticed that no response has been received from you since December 21, 2019. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arises.

6

The previous file uploaded is a zip file containing the pbix file. I have attached to this reply as well.

I am aware of the analyzer, and I plan to recreate the file in order to reduce the query. Additionally, we are attempting to setup an Azure SQL database since refreshing the query directly from the Dynamics CRM is extremely slow. At that time I plan to clean up the query, which overall should help speed things up. I don’t think though that this will eliminate the issues with running out or resources just given the sheer size of the data.

I am operating on desktop and my computer exceeds the minimum required resources. The available resources on this computer isn’t an issue; the limitation, according to forums, seems to be on Microsoft’s end. This is also how I found the apparent solution of using VAR instead of separate measures. I’m just unable to get those to work as VAR and wondering what may be wrong in the formula.

Forecast Model.pbix (192.4 KB) Opportunity.xlsx (43.3 KB) Practice Participation.xlsx (122.0 KB)

7

replaced in attached with VAR …please check whether it solves it
Yearly Opp. Revenue =
VAR Est_Wt_Revenue_per_Day =
CALCULATE( SUMX( ‘Practice Participation’, ‘Practice Participation’[Daily Revenue] ),
FILTER( VALUES( Opportunity[Est. Project Start Date (Org)] ), Opportunity[Est. Project Start Date (Org)] <= MAX( Dates[Date] ) ),
FILTER( VALUES( Opportunity[Est. Project End Date] ), Opportunity[Est. Project End Date] >= MIN( Dates[Date] ) ))
VAR Duration_Days =
CALCULATE( COUNTAX( Dates, [Est. Wt. Revenue per Day] ),
FILTER( VALUES( Dates ), Dates[Date] < MAX( Opportunity[Est. Project End Date] ) ) )
RETURN
CALCULATE( SUMX( ‘Practice Participation’, CALCULATE( COUNTAX( Dates, Est_Wt_Revenue_per_Day ),
FILTER( VALUES( Dates ), Dates[Date] < MAX( Opportunity[Est. Project End Date] ) ) )* Est_Wt_Revenue_per_Day), DATESYTD( Dates[Date]))
Forecast Model1.pbix (197.5 KB)

8

Please also refer to https://docs.microsoft.com/en-us/dax/var-dax as it explains benefits and limitations

9

The changes in the formula are still yielding results that are too high as they should end up in the tens of millions instead of billions.

However, I made a modification that seems to be yielding results that are close to correct. It appears that the virtual table reference doesn’t work properly as a VAR formula. The holdup appears to be the duration formula that is burning through the resources. With the change below the results are close, but they are still not entirely correct. I will attempt to see what may be the issue, but if you might have some insights as to the logic and context that seems to yield slightly different aggregations.

Yearly Opp. Revenue (Variables) =
CALCULATE( SUMX( ‘Practice Participation’, CALCULATE( COUNTAX( Dates, [Est. Wt. Revenue per Day] ),
FILTER( VALUES( Dates ), Dates[Date] < MAX( Opportunity[Est. Project End Date] ) ) )* [Est. Wt. Revenue per Day] ), DATESYTD( Dates[Date]) )

Thank you all again for your help! It seems this change might finally yield usable results that do not max out resources.

Forecast Model2.pbix (197.8 KB)

10

It is not related to variables, as you are not using it …
You are comparing between
Yearly Opp. Revenue (Variables) =
CALCULATE( SUMX( ‘Practice Participation’, CALCULATE( COUNTAX( Dates, [Est. Wt. Revenue per Day] ),
FILTER( VALUES( Dates ), Dates[Date] < MAX( Opportunity[Est. Project End Date] ) ) )* [Est. Wt. Revenue per Day] ), DATESYTD( Dates[Date]) )

…and

Yearly Opp. Revenue = CALCULATE( SUMX( ‘Practice Participation’, [Duration (Days)] * [Est. Wt. Revenue per Day]
), DATESYTD( Dates[Date] ) )

Duration (Days) =
CALCULATE( COUNTAX( Dates, [Est. Wt. Revenue per Day] ),
FILTER( VALUES( Dates ), Dates[Date] < MAX( Opportunity[Est. Project End Date] ) ) )

so you just substituted Duration(Days) … I think this is related to some rounding as Duration(Days) is a calculated whole number

11

Hi @spanishyoshi, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!