Excel Dax 简明教程
Excel DAX - Formula Errors
使用具有错误语法的 DAX formulas 时，可能会出错。计算字段和计算列可能包含需要特定类型参数的 DAX 函数。DAX 函数的参数可以是表、列或其他 DAX 函数（嵌套 DAX 函数）。由于 DAX 函数可以返回表和列，因此应仔细检查是否将正确类型的参数传递给 DAX 函数。
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 公式错误可以是语法错误或语义错误。这些错误可以在设计时或运行时发生。
DAX formula errors can be either syntax errors or semantic errors. The errors can occur either at design time or at run time.
在本章中，将了解一些常见的 DAX 错误，其原因以及如何修复这些错误。
In this chapter, you will learn about some common DAX errors, their causes, and how to fix those errors.
DAX Error: Calculation Aborted
当尝试创建（设计时）或使用（运行时）带有 DAX 时间智能函数的计算字段时，可能会发生以下错误。在每种情况下，都会向时间智能函数传递一个非连续日期范围。
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 错误：计算中止：MdxScript（实例）（00，0）函数“DATEADD”仅适用于连续日期选择。”
“DAX Error: CALCULATION ABORTED: MdxScript (instance) (00, 0) Function ‘DATEADD’ only works with contiguous date selections.”
Cause at Run-time
当计算字段带 DAX 时间智能函数被放置在透视表的 VALUES 区域中，但在选择年份之前选择了诸如月份或季度的日期字段作为切片器或筛选器时，可能会显示该错误。例如，如果您有 2014、2015 和 2016 年这三年数据，并且您尝试仅在不选择年字段的情况下使用 3 月份，那么这些值就不是连续的数据值，您将收到错误。
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 时间智能函数需要为日期参数指定一个日期列。日期列必须具有连续的日期范围。如果日期列中一行或多行中的日期值与前一行和后一行的日期值不连续，则可能会返回该错误。
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
以下 DAX 错误是语义错误−
The following DAX error is a semantic error −
“函数 ‘CALCULATE’ 已用于真假表达式，该表达式用作表筛选器表达式。不允许这样做。”
“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.
在大多数情况下，此错误是由指定为 DAX CALCULATE 函数参数的筛选器表达式导致的。CALCULATE 函数需要将筛选器定义为布尔表达式或表表达式。
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.