When switching to DirectQuery mode, the following limitations cause calculated columns to break or slow down.
Why It Breaks or Slows Down
DAX Limitations in DirectQuery.
Most row-by-row calculations will not be supported, as these cannot be translated into SQL under existing DAX functions that operate in DirectQuery mode using an SQL query run directly against the data source.
e.g., RELATED() or LOOKUPVALUE() fail upon execution in SQL because they require a relationship to be present.
Performance issues
As calculated columns in DirectQuery are not stored like in Import mode, they will be recomputed whenever a query runs, which is often the case for large data loads.
This can add to the query load generated inside the source system, which eventually affects the performance of the report.
How to Fix It
Move the Calculation to the Data Source
Ideally, the column would be created in the database (SQL, data warehouse, or data lake) instead of in Power BI.
Example: Instead of DAX in Power BI
NewColumn = 'Sales'[Quantity] * 'Sales'[Price]
Do this in SQL:
SELECT Quantity * Price AS NewColumn FROM Sales;
- This ensures the calculation is performed at the data source level before reaching Power BI.
Use Measures Instead of Calculated Columns
- If you only need the result for visualizations, replace the calculated column with a measure
Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price])
- Measures are dynamically calculated only when needed, improving performance.
Ensure Relationships Are Optimized
- If using functions like RELATED(), ensure the relationship exists in the data model and is one-to-many.
