"Error: Query Has Exceeded the Available Resources and Issue with Thousand Separator in Power BI"

[MohanV125286]

Hi Power BI Community,

I am currently facing two issues while working in Power BI:

1. Error: Query Has Exceeded the Available Resources

   • I encounter this error when refreshing or processing a large dataset.
   • I have optimized my data model by reducing the number of columns and rows, ensuring relationships are efficient, and avoiding complex transformations in Power Query. However, the error persists.

2. Thousand Separator Issue with the FORMAT Function

   • I am using the FORMAT function in DAX to display numbers with a thousand separator, as shown below:
     

     WTD =
     VAR Metric = SELECTEDVALUE('Weekly DTC (detail)'[Metrics])
     VAR MetricValue = SELECTEDVALUE('Weekly DTC (detail)'[Metric Values])
     VAR AOV_Metrics = "AOV"

     
     Var AOVTY= (VAR LastDateAvailable = LASTDATE('Date Table'[Date]) -- Use the 'Date' column, not 'DayOfWeek'
     VAR LastFiscalYearMonthAvailable = MAX('Date Table'[CurrWeek End Date])
     VAR LastFiscalYear = MAX('Date Table'[Year])
     RETURN
     CALCULATE (
     [AOV],
     ALLEXCEPT('Date Table', 'Date Table'[Select a Calendar], 'Date Table'[CurrWeek End Date]),
     'Date Table'[Date] <= LastDateAvailable && -- Use the 'Date' column here
     'Date Table'[CurrWeek End Date] = LastFiscalYearMonthAvailable &&
     'Date Table'[Year] = LastFiscalYear
     ))
     VAR AOVLY= (VAR LastDateAvailable = LASTDATE('Date Table'[Date]) -- Use the 'Date' column, not 'DayOfWeek'
     VAR LastFiscalYearMonthAvailable = MAX('Date Table'[CurrWeek End Date])
     VAR LastFiscalYear = MAX('Date Table'[Year])-1
     RETURN
     CALCULATE (
     [AOV],
     ALLEXCEPT('Date Table', 'Date Table'[Select a Calendar], 'Date Table'[CurrWeek End Date]),
     'Date Table'[Date] <= LastDateAvailable && -- Use the 'Date' column here
     'Date Table'[CurrWeek End Date] = LastFiscalYearMonthAvailable &&
     'Date Table'[Year] = LastFiscalYear
     ))

     VAR AOVLLY= (VAR LastDateAvailable = LASTDATE('Date Table'[Date]) -- Use the 'Date' column, not 'DayOfWeek'
     VAR LastFiscalYearMonthAvailable = MAX('Date Table'[CurrWeek End Date])
     VAR LastFiscalYear = MAX('Date Table'[Year])-2
     RETURN
     CALCULATE (
     [AOV],
     ALLEXCEPT('Date Table', 'Date Table'[Select a Calendar], 'Date Table'[CurrWeek End Date]),
     'Date Table'[Date] <= LastDateAvailable && -- Use the 'Date' column here
     'Date Table'[CurrWeek End Date] = LastFiscalYearMonthAvailable &&
     'Date Table'[Year] = LastFiscalYear
     ))

     
     
     VAR percvsLYAOV =IF(
     AOVLY = 0,
     BLANK(),
     DIVIDE((AOVTY -AOVLY), AOVLY)
     )
     VAR PercvsLLYAOV = IF(
     AOVLLY = 0,
     BLANK(),
     DIVIDE((AOVTY- AOVLLY), AOVLLY)
     )

     Var AOVMetrics = SWITCH(
     MetricValue,
     "TY",FORMAT(AOVTY,"0"),
     "LY", FORMAT(AOVLY,"0"),
     "LLY", FORMAT(AOVLLY,"0"),
     "% vs. LY", FORMAT(percvsLYAOV,"0"),
     "vs. LY", FORMAT( (AOVTY - AOVLY),"0"),
     "% vs. LLY",FORMAT(PercvsLLYAOV,"0"),
     "vs. LLY",FORMAT( (AOVTY- AOVLLY),"0")
     )

     RETURN
     SWITCH(
     TRUE(),
     
     
     Metric = AOV_Metrics,
     AOVMetrics
     
     
     )
     
     

     I have tried using Dynamic option also, but it can't help.
     When i used format i am getting an error like can't convert numberic value to text.
     Original Value: 2,00,44,033  Expected value: 20,044,033
     

[mark_endicott]

@MohanV125286 - did we resolve your issue? If we did, please select the appropriate solution, it helps with visibility for others and for SuperUsers to keep their status! Thanks!

[mark_endicott]

@MohanV125286 - I dont think it's the formatting that's the problem here, although I would recommend you take that out and put it into a Dynamic Format String. 

The issue is likely to be with your original [AOV] calcualtion. 

However, there are many things you can do to cut time off this calculation. For instance, your 'LastDateAvailable' variable is created and stored 4 times - you should only need to do this once.  You also dont need to create 4 'LastFiscalYear' variables, you can just create 1 and -1, or -2 off of it in the relevant calculate statement. 

Removing all the repeated code and creating each calculation variable like:

VAR AOVLLY = 
CALCULATE (
                [AOV],
                ALLEXCEPT (
                    'Date Table',
                    'Date Table'[Select a Calendar],
                    'Date Table'[CurrWeek End Date]
                ),
                'Date Table'[Date] <= LastDateAvailable
                    && -- Use the 'Date' column here
                'Date Table'[CurrWeek End Date] = LastFiscalYearMonthAvailable
                    && 'Date Table'[Year] = LastFiscalYear - 2
            )

Will provide a layer of optimisation, but you will then need to go back and check [AOV]. 

[MohanV125286]

Hello @mark_endicott ,
Thanks for the response , Actually i have tried in different ways like as you mentioned i have added LastFiscalYear only once but still i am getting the error.
Note: I am using Matrix visual and adding AOV, AUR..... i am using 16 measures for those 16 measures i am calculatingTY, LY,LLY,$Ly,%LY,%LLY,$LLY and adding to the visual. 

[mark_endicott]

@MohanV125286 - A matrix with 16 measures on a large data model will always struggle, particularly if you're asking it to calculate over many rows. 

Does this WTD measure work when it is the only measure in the table?

[lbendlin]

Use DAX Studio to examine the query plan and find ways to refactor the query to reduce the cardinality.

[MohanV125286]

Hello @lbendlin ,
Thanks for response, I'll try Dax Studio.