Excel Dax 简明教程

Excel DAX - Scenarios

在之前的章节中，您已了解 DAX 语法、DAX 运算符和 DAX 函数的用法。您可能知道，DAX 是一种用于数据建模和数据分析的公式语言。

You have learnt DAX syntax, the usage of DAX operators and DAX functions in the previous chapters. As you are aware, DAX is a formula language used for data modeling and data analysis.

DAX 可用于各种场景。基于 DAX 场景，DAX 优化了性能并产生了准确、有效的结果。在本章中，您将了解一些 DAX 场景。

DAX can be used in various scenarios. Based on the DAX scenarios, DAX optimizes the performance and produces accurate and effective results. In this chapter, you will get to know some of the DAX scenarios.

Performing Complex Calculations

DAX 公式可以执行涉及自定义聚合、筛选以及使用条件值在内的复杂计算。您可以使用 DAX 执行以下操作：

DAX formulas can perform complex calculations that involve custom aggregations, filtering, and the use of conditional values. You can do the following with DAX

  1. Create custom calculations for a PivotTable.

  2. Apply a filter to a formula.

  3. Remove filters selectively to create a dynamic ratio.

  4. Use a value from an outer loop.

有关详细信息，请参阅“场景 - 执行复杂计算”一章。

For details, refer to the chapter Scenarios - Performing Complex Calculations.

Working with Text and Dates

DAX 可用于涉及文本处理、提取和组合日期时间值或基于条件创建值的场景。您可以使用 DAX 执行以下操作：

DAX can be used in the scenarios involving working along with text, extracting and composing date and time values, or creating values based on a condition. You can do the following with DAX −

  1. Create a key column by concatenation.

  2. Compose a date based on date parts extracted from a text date.

  3. Define a custom date.

  4. Change data types using a formula. Convert real numbers to integers.Convert real numbers, integers, or dates to strings.Convert strings to real numbers or dates.

有关详细信息，请参阅“场景 - 处理文本和日期”一章。

For details, refer to the chapter Scenarios - Working with Text and Dates.

Conditional Values and Testing for Errors

DAX 函数可以测试数据中的值，并根据条件返回不同的值。测试值的 DAX 函数对于检查值的范围或类型也很有帮助，可防止意外数据错误中断计算。您可以使用 DAX 执行以下操作：

DAX functions enable to test values in the data and return a different value based on a condition. DAX functions that test values are also useful for checking the range or type of values, to prevent unexpected data errors from breaking calculations. You can do the following with DAX −

  1. Create a value based on a condition.

  2. Test for errors within a formula.

有关详细信息，请参阅“场景 - 条件值和错误测试”一章。

For details, refer to the chapter Scenarios - Conditional Values and Testing for Errors.

Using Time Intelligence

您已在“了解 DAX 时间智能”一章中了解有关 DAX 时间智能函数的信息。

You have learnt about DAX time intelligence functions in the chapter – Understanding DAX time intelligence.

DAX 时间智能函数包括可以帮助您从数据中检索日期或日期范围的函数。然后，您可以使用这些日期或日期范围计算类似期间的值。时间智能函数还包括使用标准日期间隔的函数，使您能够比较月、年或季度的值。您还可以创建 DAX 公式来比较指定期间内第一个和最后一个日期的值。

DAX time intelligence functions include functions to help you retrieve dates or date ranges from your data. You can then use those dates or date ranges to calculate values across similar periods. The time intelligence functions also include functions that work with standard date intervals, to allow you to compare values across months, years, or quarters. You could also create a DAX formula that compares values for the first and the last date of a specified period.

您还可以了解有关 DAX intelligence 函数的更多信息以及它们可以用于以下方面的内容：

You can learn more about DAX intelligence functions and what they can do for the following −

  1. Calculate Cumulative Sales.

  2. Compare Values over Time.

  3. Calculate a Value over a Custom Date Range.

有关详细信息，请参阅“情景 - 使用时间智能”一章。

For details, refer to the chapter Scenarios - Using Time Intelligence.

Ranking and Comparing Values

如果您只想显示列或数据透视表中前面 n 项，您有以下选项：

If you want to show only the top n number of items in a column or a PivotTable, you have the following options −

  1. Apply a filter to show only the top or bottom few items.

  2. Create a DAX formula that dynamically ranks values and apply a filter.

每个选项都有利有弊。

Each of these options have pros and cons.

有关详细信息，请参阅“情景 - 对值进行排名和比较”一章。

For details, refer to the chapter Scenarios - Ranking and Comparing Values.