Updating the Results of DAX Formulas



DAX formulas are used in calculations involving large data, including data from external data sources. The data can be subjected to changes from time to time as the DAX calculations are meant for live data as well.

The results of DAX formulas need to get updated on two occasions −

  • Data Refresh − When the data is refreshed.

  • Recalculation − When there are changes in the DAX formula.

Understanding Data Refresh vs. Recalculation

Data refresh and recalculation are two separate but related operations.

  • Data refresh is the process of updating the data in the Data Model in your workbook obtaining up-to-date data from external data sources.

  • Recalculation is the process of updating all the columns, tables, and PivotTables in your workbook that contain DAX formulas, to reflect the changes in the underlying data that result from the changes to DAX formulas themselves.

You should not save or publish the workbook until the DAX formulas in it have been recalculated.

Different Ways to Update Data in Data Model

Power Pivot does not automatically detect changes in external data sources.

  • You can refresh data manually from the Power Pivot window at intervals that you can specify.

  • You can schedule an automatic data refresh from external sources, if you have published the workbook to a SharePoint site.

For details on these, refer to the chapter – Updating Data in Data Model.

Recalculation of DAX Formulas

Recalculation of a DAX formula is an important task, because during recalculation, column dependencies are checked and you will be notified if a column has changed, if the data is invalid, or if an error has appeared in a DAX formula that used to work.

Recalculation can affect performance in the following ways −

  • For a calculated column, the result of DAX formula should always be recalculated for the entire column, whenever you change the DAX formula.

  • For a calculated field, the result of DAX formula is not calculated until the calculated field is placed in the context of a PivotTable or a PivotChart. The DAX formula will be recalculated when you change any row or column heading that affects the filters on the data or when you manually refresh the PivotTable.

In DAX, recalculating formulas can be done automatically or manually.

To learn more about recalculation, refer to the chapter – Recalculating DAX Formulas.

Advertisements