Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Visual resources exceeded error when only minor changes to measure and page filters are made

    Posted 28 days ago
    Edited by Michelle Merchak 28 days ago
    I DO HAVE A PBIX FILE WITH THE DATA SCRUBBED, BUT IT WILL NOT LET ME AN ATTACHMENT TO THIS POST SO I HAVE ADDED IT TO A REPLY BELOW.

    I have a table that is getting a "resources exceeded" error once uploaded to the Service on the Expenses Breakdown page (shared capacity not premium, so there is no way for me to increase the per-query memory limit as far as I am aware).

    The thing that has me scratching my head is that an almost identical page (Revenue Breakdown) is rendering just fine. The measure and page filter have just a minor tweak from the one that is working to the one that is not working (marked in orange boxes in the pictures that have the measures).

    My data set is rather large, it has data going back to 2014. If I reduce the data to go back only to 2020 I do not have this issue. I would like to be able to keep all of the data unless a solution is not found. I just do not understand why the Revenue Breakdown page and measure work, and the Expenses Breakdown page and measure don't work. Well, I will also mention that the Expense Breakdown measure works in the table, but not the matrix.​
    Measures:
    Gross Revenue Breakdown - Year = IF(SELECTEDVALUE('Year Budget Values'[Year]) = "Budget - Latest Year"
    , CALCULATE([Budget],AcctDesc[Financial Acct Type] = "Rev",FILTER('Budget Table', 'Budget Table'[Year] = MAX('Month Year Values'[Year])))
    , CALCULATE([Actuals],AcctDesc[Financial Acct Type] = "Rev",FILTER('GL Table', YEAR('GL Table'[GLDate]) <= SELECTEDVALUE('Month Year Values'[Year]) && YEAR('GL Table'[GLDate]) >= SELECTEDVALUE('Month Year Values'[Year]) - ([Rolling Years Value] - 1)), USERELATIONSHIP('Year Budget Values'[Year],'Date'[Year - Text])))​
    Net Expense OpExp Breakdown - Year = IF(SELECTEDVALUE('Year Budget Values'[Year]) = "Budget - Latest Year"
    , CALCULATE([Budget],AcctDesc[Financial Acct Type] = "Equipment & Supplies",FILTER('Budget Table','Budget Table'[Year] = MAX('Month Year Values'[Year])))
    , CALCULATE([Actuals],AcctDesc[Financial Acct Type] = "Equipment & Supplies",FILTER('GL Table', YEAR('GL Table'[GLDate]) <= SELECTEDVALUE('Month Year Values'[Year]) && YEAR('GL Table'[GLDate]) >= SELECTEDVALUE('Month Year Values'[Year]) - ([Rolling Years Value] - 1)), USERELATIONSHIP('Year Budget Values'[Year],'Date'[Year - Text])))
    Budget = CALCULATE(-SUM('Budget Table'[TRANSACTIONCURRENCYAMOUNT]), 'Budget Table'[Exclude 64000] = 1)
    Actuals = CALCULATE(-SUM('GL Table'[TRANSACTIONCURRENCYAMOUNT]), 'GL Table'[Exclude 64000] = 1)


    Expenses Breakdown page error in service:


    Relationships in report:



    Revenue Breakdown page and measure (colored boxes correspond to the columns/values that go together - orange boxes are what are changed in the Expenses Breakdown page and measure):



    Expenses Breakdown page and measure (colored boxes correspond to the columns/values that go together - orange boxes are what were changed from the Revenue Breakdown page and measure):



    ------------------------------
    Michelle Merchak
    Business Intelligence Analyst
    ------------------------------


  • 2.  RE: Visual resources exceeded error when only minor changes to measure and page filters are made

    Posted 28 days ago
    Edited by Michelle Merchak 28 days ago
      |   view attached
    Here is the PBIX file that is having issues.

    ------------------------------
    Michelle Merchak
    Business Intelligence Analyst
    ------------------------------

    Attachment(s)

    pbix
    Financials Slim - ISSUES.pbix   109.53 MB 1 version


  • 3.  RE: Visual resources exceeded error when only minor changes to measure and page filters are made

    Top Contributor
    Posted 28 days ago
    Hi @Michelle Merchak,

    I had a look at this. Just looking at Task Manager, the Expenses page uses an enormous amount of ​memory to refresh. The significant difference between the two pages is that one's summing values in the Budget table, the other the GL table. The budget table has 278K rows but the GL table has 8.2M. I threw a date slicer on the page and filtered down to 1 Jan 2020, published it up and it find that it will display there.

    Clearly if you want to retain more years then that's not really a solution. The detail in your GL table looks very granular, but the matrix you're having trouble with is a very high level. I would suggest you create a summary table to drive this visual. You can do this with a view query in your source system, a query in Power BI or with a DAX table.

    Hope that helps a bit. Best of luck.

    ------------------------------
    Simon Lamb
    IT Consultant, Method Excel Ltd.
    ------------------------------



  • 4.  RE: Visual resources exceeded error when only minor changes to measure and page filters are made

    Posted 24 days ago
    Edited by Michelle Merchak 24 days ago
    Thank you, @Simon Lamb!!!

    Well, both measures query both the Budget table and the GL table, but there are a lot more Expense line items than Revenue line items... Like A LOT more. So that very well could be the issue here.

    But you ROCK! I don't know why I didn't think to create a summary table. Sometimes I am so deep in the weeds that the best ideas just escape me. That worked like a charm! Thank you! :)

    ------------------------------
    Michelle Merchak
    Business Intelligence Analyst
    ------------------------------​​