Excel DAX - Formula Errors

You can get errors when you write DAX formulas with wrong syntax. Calculated fields and calculated columns can contain DAX functions that require a specific type of arguments. Arguments of DAX functions can be tables, columns, or other DAX functions (nested DAX functions). As DAX functions can return tables and columns, care should be taken to check that the right type of arguments are passed to the DAX functions.

DAX formula errors can be either syntax errors or semantic errors. The errors can occur either at design time or at run time.

In this chapter, you will learn about some common DAX errors, their causes, and how to fix those errors.

DAX Error: Calculation Aborted

The following error can occur when attempting to create (design-time) or use (run-time) a calculated field with a DAX time-intelligence function. In each case, a noncontiguous date range is being passed to the time intelligence function.

“DAX Error: CALCULATION ABORTED: MdxScript (instance) (00, 0) Function ‘DATEADD’ only works with contiguous date selections.”

Cause at Run-time

This error can be displayed when a calculated field with a DAX time intelligence function is placed in the VALUES area of a PivotTable and date fields such as the month or the quarter are selected as slicers or filters before selecting a year. For example, if you have data of three years – 2014, 2015, and 2016 and you try to use only the month March without selecting the Year field, then the values are not contiguous data values and you will get an error.

How to Fix the Error at Run-time?

In the above example,

  • First add Year as a slicer or a filter and select a year.

  • Then, add Month or Quarter as a slicer or a filter.

  • Then, select one or more months or quarters to slice or filter on for the year selected.

Cause at Design-time

DAX time intelligence functions require a date column specified for the date argument. The date column must have a contiguous range of dates. This error can be returned, if there is a date value in one or more rows in the date column that is not contiguous with the data values in the previous and successive rows.

If you imported your table containing dates from a data source, remember that many organizations run special processes that scan tables in databases for invalid values and replace those with a particular value. That is, if an invalid date is found, it is assigned a particular date value that may not be contiguous with other data values in the column.

How to Fix This Error at Design-time?

Do the following to fix the error at design time −

  • If your date table is imported from a data source, use Refresh in Power Pivot window to reimport any changes found at the source.

  • Check the values in your date column to make sure they are in a contiguous order. If any value is found not to be in place, it will have to be corrected at the source and the date table will have to be refreshed.

  • Create a separate date table and date column in your Data Model. Specify the new date column as the date argument in the formula causing the error. Date tables are easy to create and add to a Data Model.

DAX Semantic Error - An Example

The following DAX error is a semantic error −

“A function ‘CALCULATE’ has been used in a true-false expression that is used as a table filter expression. This is not allowed.”

Cause

This error can appear when one or more filter expressions cannot be used in context of the calculated field or calculated column expression.

In most of the cases, this error is caused by a filter expression specified as an argument to the DAX CALCULATE function. The CALCULATE function requires filters defined as a Boolean expression or a table expression.

How to Fix Such Errors?

You can fix such errors by using the DAX FILTER function to define filters as a table expression, which can then be used as an argument to the DAX CALCULATE function.

Advertisements