Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ldwf
Helper III
Helper III

DAX formula causes 'Resource Governing' Error message

Hello,  I have a DAX formula for a measure that has been working fine but I need to modify it.  The .pbix file is a data model that gets published and the dataset is used by Report Builder reports.  After I make the change to the measure and test it out in the .pbix file locally, it works fine, but once I publish the dataset and use the formula in a Report Builder report, I get the message 'Resource Governing: This query uses more memory than the configured limit'.  The intent of the formula is to apply a format (i.e, decimal places) to the prior month's revenue.  The user selects a date from the dropdown.  The formula currently looks like this, where _a is the date the user selects, and _b is the prior month, and Revenue_Format is a field that is in the result set of the SQL statement that identifies the number of decimal places: 

                                       Prior Month Revenue Format = 

                                       var _a = MAXX(ALLSELECTED('Date Dim Table'[Date_BK]), 'Date Dim Table'[Date_BK])

                                       var _b = EOMONTH(MAXX(ALLSELECTED('Date Dim Table'[Date_BK]), 'Date Dim Table'[Date_BK]), -1)

                                       var _result = CALCULATE(MAX('Fact Table'[Revenue Format]), 'Date Dim Table'[Date_BK] = _b

                                                    )

                                       RETURN

                                            _result

I want to change the formula to check if the current month revenue is null, and if it is, I want to format the prior month revenue based on the format defined for the pror month, and if the current month is not null, I want to format the prior month revenue based on the format defined for the current month.  The format is the measure called 'Revenue Format', and comes from the SQL statement.  The change I make is to redefine the _result variable like this:

                                      var _result = SWITCH(TRUE(),

                                              ISBLANK('Fact Table[Current Month Revenue]),

                                                CALCULATE(MAX('Fact Table[Format Pattern]),'Date Dim Table'[Date_BK]=_b),

                                                CALCULATE(MAX('Fact Table[Format Pattern]),'Date Dim Table'[Date_BK]=_a)

                                             )

This change works fine when I test it out locally, but when I publish the model and use the formula in Report Builder I get the above message.   If I change the DAX formula back to the original it works fine.  Not sure why this minor change makes a big change to what is happening behind the scenes.  Thanks

                                                          

 

 

0 REPLIES 0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.