PBI ON REFRESH - 'The MAX function only accepts a column reference as the argument number 1.

Thankyou, Ritaf1983, for your response.

Hi FabricNZ,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

From my understanding, the error message indicates an incorrect DAX syntax involving the use of MAX(<expression>), where MAX is applied to a scalar or measure instead of a column reference. This issue does not appear in visuals, is not flagged within Power BI Desktop, and only occurs during Semantic Model refresh. This suggests that the problem likely lies within a calculated column, calculated table, or a hidden expression within the model.

Moreover, the memory overflow indicates that a complex or improperly scoped calculation — possibly recursive or involving a very large row context — is attempting to process extensive datasets during the refresh.

Kindly follow the steps below to help resolve this issue:

1. The error is caused by using MAX() with a measure or a missing column (for example, [Total]). Since this is not evident in the UI, you can locate the erroneous MAX usage by:
   a. Opening your .pbip project folder.
   b. Navigate to expressions.json. Search for MAX( and Total. You will likely find a broken expression such as MAX([Total]), where [Total] is not a column.

2. Ensure that MAX() is used exclusively with column references to correct the DAX expression.
   The expression MAX([Total]) — if [Total] is a measure or a deleted column — is incorrect. The correct usage should be MAX(Sales[TotalAmount]).

3. To avoid memory overflow issues, apply the following changes:
   a. In Power Query, apply row filters such as Table.SelectRows(Source, each [Date] >= #date(2024,1,1]) and use Table.FirstN(Source, 100) for testing purposes.
   b. Disable Auto Date/Time from File > Options > Data Load.
   c. Remove unused columns and tables, and disable table load if not required.

4. Test the changes in Power BI Desktop, re-publish to Power BI Service / Fabric, and re-enter the data source credentials. If memory limits persist, request a per-query memory increase from the capacity administrator (applicable for Premium/Fabric SKUs).

Additionally, please refer to the following links for further information:
MAX function (DAX) - DAX | Microsoft Learn
Optimization guide for Power BI - Power BI | Microsoft Learn

Should you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who face similar issues.

For any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.