【DAX新手必学】：2小时掌握Excel和Power BI中的DAX函数

# 摘要 DAX（Data Analysis Expressions）函数是Power BI等数据分析工具中用于创建复杂计算的关键技术。本文旨在提供对DAX函数基础、核心概念、实战演练以及性能优化的全面介绍。通过深入探讨DAX公式的工作原理、数据模型和关系、度量值与列的应用，本文指导读者掌握DAX函数的分类和使用场景。实战演练章节通过示例和错误处理方法，使读者能够有效地运用DAX进行数据分析。此外，文章还分享了优化DAX公式性能的策略，并探讨了DAX在Power BI中的高级应用，如关系建模、预测分析和自动化集成，从而帮助数据分析师提升工作效率和分析深度。 # 关键字 DAX函数；数据模型；度量值；性能优化；数据分析；Power BI 参考资源链接：[Power BI与Excel DAX函数官方指南：快速入门与详解](https://wenku.csdn.net/doc/6401aba0cce7214c316e8eda?spm=1055.2635.3001.10343) # 1. DAX函数基础入门 DAX，全称Data Analysis Expressions，是专为数据分析和业务逻辑设计的一组函数。它是Power BI、Power Pivot和SQL Server Analysis Services Tabular模型中使用的表达式语言。本章节将为初学者提供DAX函数的入门指导。 ## 1.1 DAX函数简介 DAX函数非常强大，能够执行计算、逻辑判断、条件聚合等操作。学习DAX函数，可以帮助用户创建自定义计算列和度量值，从而增强报告和数据模型的功能。 ## 1.2 基本概念理解 要掌握DAX函数，首先需要理解两个基本概念：计算列和度量值。计算列是在表中计算并存储结果的公式，而度量值是在报告中动态计算的公式。 ## 1.3 开始使用DAX 例如，创建一个简单的计算列可以使用如下DAX表达式： ```dax Total Sales Amount = SUMX(Sales, Sales[Quantity] * Sales[Price]) ``` 这个公式计算了每行销售记录的总销售额，并将其汇总起来。了解这一点是利用DAX的强大功能的第一步。 # 2. DAX函数的核心概念 ### 2.1 DAX公式的工作原理 DAX（Data Analysis Expressions）是一种用于计算和查询模型数据的公式语言。它允许你创建新的计算列、度量值（测量值）以及动态集。理解DAX公式的工作原理对于有效利用Power BI、Power Pivot或SQL Server Analysis Services Tabular模型至关重要。 #### 2.1.1 公式和表达式的区别 在DAX中，术语“公式”和“表达式”虽然经常交替使用，但它们之间存在细微差别。一个“表达式”可以是一个单一的值、一个DAX函数，或者是一系列DAX函数和操作符组合而成的复杂计算。而“公式”通常指创建的计算列或度量值。 DAX公式的基本构成如下： ```dax 公式名称 = 表达式 ``` 这里的“公式名称”是新创建的列或度量值的标识，而“表达式”是计算逻辑的实现。例如，创建一个基于“销售额”列的计算列： ```dax 销售利润 = 销售额 - 成本 ``` 在这个例子中，“销售利润”是公式名称，“销售额 - 成本”是表达式。在创建度量值时，表达式通常包含聚合函数，如SUM、AVERAGE等。 #### 2.1.2 计算上下文的理解 DAX公式运行在一个由行上下文和滤波上下文组成的环境中。行上下文是指当前行的行级细节，而滤波上下文是指对数据模型进行过滤的条件集合。 假设我们有一个“产品”表和一个“销售”表，两者通过“产品ID”连接。下面的公式计算每个产品的销售总额： ```dax 产品销售总额 = SUM(销售[销售额]) ``` 在这里，“销售[销售额]”中的行上下文是“产品”表中的每一行。如果我们有一个产品类别作为过滤条件，滤波上下文将只包含该类别下的销售总额。 ### 2.2 数据模型和关系 #### 2.2.1 数据表和关系的概念 在DAX中，数据模型是由一个或多个表组成的，每个表包含相关的列。表之间的关系定义了如何连接数据，以便可以跨表进行查询和计算。 创建关系时，需要注意以下几点： - 表中的列必须具有相同的数据类型。 - 关系可以通过主键和外键的匹配建立。 - 一个表可以与多个表有关系，但一个列只能被用于一个关系。 - 有向关系：通常是从“多”的一侧到“一”的一侧。 #### 2.2.2 理解一对多关系和多对多关系 在DAX的数据模型中，关系可以分为两类：一对多关系和多对多关系。 - **一对多关系**是最常见的关系类型，如“产品”到“销售”关系，一个产品可以有多个销售记录。 - **多对多关系**更为复杂，通常需要通过一个关联表或交叉过滤来实现。例如，一个产品可以有多个颜色，一个颜色可以对应多个产品。 在多对多关系中，DAX提供了一些特殊的函数，如`CROSSFILTER`，来控制过滤方向。这允许我们在进行数据分析时灵活地定义这些关系。 ### 2.3 度量值和列的使用 #### 2.3.1 何时使用度量值与列 在DAX中，度量值和列用于不同的分析场合。 - **列**是表中固定的数据列，用于存储特定的数据点。它们是静态的，并且在分析时保持不变。列可以用于筛选、分组或作为其他计算的基础。 - **度量值**是根据列数据计算得出的动态值。它们通常用于聚合数据，如求和、平均值等。度量值可以引用表中的列，但其值是在查询时计算的，而不是静态存储的。 度量值在视觉化对象中特别有用，如图表或数据透视表，因为它们提供了一种快速聚合数据的方式。 #### 2.3.2 列和度量值在数据模型中的应用 在Power BI等工具中，列和度量值可以被拖放以创建视觉化对象，从而展示数据的分析结果。 列通常用于轴或切片器中，为可视化提供特定的值或进行数据分组。例如，在一个饼图中，列可以表示每个产品的销售量。 度量值则用于汇总数据，比如在图表中计算总销售额或平均利润。它们能够自动适应所选的数据范围，并根据数据模型中的关系和上下文进行过滤和计算。 下面是一个简单的例子，展示了如何在Power BI中使用列和度量值： ```dax 销售额 = SUM(销售表[销售额]) ``` 这个度量值会为所有选择的行计算总销售额。 在实际应用中，了解何时使用列和度量值，可以帮助我们有效地分析数据，并在Power BI等工具中创建强大的报告和仪表板。 # 3. DAX函数实战演练 在了解了DAX函数的基础和核心概念之后，我们现在可以进入实战演练阶段，将理论知识转化为实践应用。本章节将介绍DAX函数的分类、实现数据分析的公式示例、以及在公式中可能出现的错误处理方法。 ## 3.1 常用DAX函数的分类介绍 DAX函数的丰富性和强大功能是我们分析数据的关键。DAX函数可以大致分为几个类别，包括时间智能函数、筛选器函数和聚合函数等。了解这些函数的分类，可以帮助我们更好地构建数据分析模型。 ### 3.1.1 时间智能函数 时间智能函数专门用于处理时间相关的数据，例如计算年、季度、月等的时间范围。这些函数能够帮助用户轻松实现复杂的日期计算，这对于财务分析和趋势预测尤为重要。 **示例代码**: ```dax Sales YTD := CALCULATE( SUM(Sales[Amount]), DATESYTD(Sales[Date], "31/12") ) ``` 这段代码通过`CALCULATE`函数结合`DATESYTD`计算了销售数据的年累计值。其中，`DATESYTD`函数接受两个参数，分别是日期列和年度截止日期的格式。 ### 3.1.2 筛选器函数 筛选器函数允许用户对数据集进行选择和过滤。它们在创建动态计算和改变上下文时特别有用。其中`FILTER`函数就是实现这一目的的典型例子。 **示例代码**: ```dax Filtered Sales := CALCULATE( SUM(Sales[Amount]), FILTER( ALL(Sales), Sales[Quantity] > 10 ) ) ``` 在这个例子中，`FILTER`函数用于创建一个过滤条件，它与`CALCULATE`结合使用，以便只计算销售数量大于10的总销售额。 ### 3.1.3 聚合函数 聚合函数用于执行聚合计算，如求和、平均、最大值、最小值等。这些函数是数据分析的核心工具，也是实现复杂度量值的基础。 **示例代码**: ```dax Average Sales Price := AVERAGE(Sales[Price]) ``` 这里简单地使用`AVERAGE`函数计算了平均售价，是聚合函数的一个直接应用示例。 ## 3.2 实现数据分析的DAX公式示例 使用DAX函数进行数据分析是Power BI中的核心操作。我们将通过以下两个示例来展示如何应用这些函数进行具体的数据分析。 ### 3.2.1 计算销售总额与平均销售额 在商业智能中，计算销售总额和平均销售额是常见的需求。DAX提供了多种方式来实现这样的分析。 **示例代码**: ```dax Total Sales := SUM(Sales[Amount]) Average Sales := AVERAGE(Sales[Amount]) ``` 这两个简单的度量值分别通过`SUM`和`AVERAGE`函数来计算总额和平均值。 ### 3.2.2 创建百分比占比和排名 当分析多个维度的数据时，创建百分比占比和排名可以帮助我们更好地理解数据之间的关系和差异。 **示例代码**: ```dax Sales Percentage := DIVIDE( [Total Sales], CALCULATE([Total Sales], ALLSELECTED(Sales)) ) Sales Rank := RANK.EQ([Total Sales], ALLSELECTED(Sales), DESCENDING) ``` 这里`DIVIDE`函数用于计算一个占另一个的百分比，而`RANK.EQ`函数则用于计算销售总额的排名。`ALLSELECTED`函数是必要的，它在计算百分比占比时排除了可能影响排名的其他筛选器。 ## 3.3 DAX公式中的错误处理 在创建DAX公式时，我们可能会遇到各种错误。正确地识别和处理这些错误对于确保数据模型的稳定性和准确性至关重要。 ### 3.3.1 常见错误类型及解决方法 DAX公式中可能遇到的错误类型有很多，例如语法错误、计算错误等。理解这些错误的来源和解决方法对于提升数据分析能力非常有帮助。 **示例代码**: ```dax -- 示例: 使用ERROR函数进行错误处理 Safe Division := IFERROR( DIVIDE([Numerator], [Denominator], 0), BLANK() ) ``` 上面的代码使用了`IFERROR`函数，当除法运算出现错误时（如除数为零），将返回空白值而非错误信息。 ### 3.3.2 使用ERROR函数和ISERROR函数 `ERROR`函数可以用来显示错误信息，而`ISERROR`函数则用于检查一个公式是否返回错误。 **示例代码**: ```dax -- 示例: 使用ISERROR函数进行错误检查 Check For Errors := IF( ISERROR([SomeCalculation]), "There was an error in the calculation", "No errors found" ) ``` 这段代码通过`ISERROR`检查某个计算是否出错，并输出相应的提示信息。 通过对这些实用的DAX函数和错误处理的介绍，我们可以更加熟练地构建复杂的数据分析模型，并确保在出现错误时能够迅速定位并解决。在实际工作中，结合本章的内容与第二章中提到的计算上下文概念，将能更有效地运用DAX函数，实现精确的数据分析和报告。 # 4. 优化DAX公式性能 ### 4.1 DAX公式性能优化原则 DAX公式性能优化是确保数据模型在查询时响应迅速的关键因素。理解性能优化原则有助于我们构建高效的计算逻辑，并确保最终用户能够快速得到他们需要的分析结果。 #### 4.1.1 减少计算量和过滤器的应用 在构建DAX公式时，我们要尽量减少不必要的计算，优化查询性能。每增加一个过滤器，DAX引擎就需要在数据模型中筛选符合特定条件的数据行。这样做虽然可以精确地获取所需数据，但也可能显著降低查询效率。因此，合理使用过滤器是提高性能的重要环节。 一个实用的技巧是使用EARLIER或EARLIEST函数来存储计算中的过滤器上下文，这样可以在不需要重复计算的情况下多次使用过滤器。 #### 4.1.2 使用快速计算表和聚合列 快速计算表（Calculated Table）和聚合列（Aggregated Columns）是两种提高性能的有效工具。快速计算表允许我们在数据模型中预先计算并存储结果，这样在实际查询时就可以快速访问这些结果，而不是每次都进行计算。聚合列通过预先计算的聚合值（如求和、计数、平均等）来加速查询，特别是在处理大型数据集时。 ### 4.2 分析和调试DAX公式 #### 4.2.1 使用DAX Studio进行性能分析 DAX Studio是一个强大的工具，可以用来监控和分析DAX查询的性能。它可以连接到Power BI Desktop、Excel的PivotTable以及SQL Server Analysis Services（SSAS）Tabular模型。通过DAX Studio我们可以查看查询的执行时间、分析查询执行计划和检测潜在的性能瓶颈。 当分析DAX查询时，我们需要检查是否触发了不必要的计算，以及是否可以优化过滤逻辑。DAX Studio提供了执行逻辑和性能指标的可视化展示，帮助开发者快速定位和解决问题。 #### 4.2.2 调试技巧与常见问题排查 DAX Studio还提供了调试功能，允许我们逐步执行DAX查询，检查公式中的每个步骤和表达式。通过调试，我们可以理解公式的执行过程，发现其中的错误并进行修正。 当遇到性能问题时，常见的排查步骤包括： - 验证DAX公式逻辑的正确性。 - 检查是否正确地使用了上下文转换。 - 分析是否有可能通过创建快速计算表或聚合列来优化。 - 查看是否可以在公式的不同部分使用变量来减少重复计算。 ### 4.3 实践：优化一个复杂的DAX公式 #### 4.3.1 识别性能瓶颈 在优化复杂的DAX公式之前，首先需要识别性能瓶颈。一个常用的方法是通过DAX Studio的“Profiler”功能来监控查询的性能。使用DAX Studio可以查看执行过程中的时间消耗，并确定哪些DAX公式或者公式的一部分需要优化。 #### 4.3.2 应用优化策略和方法 在确定了性能瓶颈后，可以应用一系列优化策略。例如，对于重复使用的复杂计算，可以使用变量来存储中间结果，避免重复计算。对于涉及大量数据过滤的公式，可以考虑使用预计算的聚合列或者快速计算表来减少实时计算的负载。 优化过程中，还可以使用EXPLAIN语句来查看DAX公式是如何被解析和优化的。这样，我们就可以根据解析树提供的信息来调整公式结构。 例如，若要优化以下DAX公式： ```dax Total Sales = SUMX(FILTER(Products, Products[Category] = "Electronics"), Products[Sales Amount]) ``` 我们可以重新组织公式以减少使用FILTER函数： ```dax Total Sales Optimized = CALCULATE( SUM(Products[Sales Amount]), Products[Category] = "Electronics" ) ``` 优化后的公式通过 CALCULATE 和 SUM 函数直接使用Category列的过滤器，而非FILTER函数，这样可以提高性能。 通过实际案例展示优化过程，我们可以看到在不同的情况下，如何应用不同的策略来提高DAX公式的性能。在进行优化时，始终要确保逻辑的正确性，并在优化前后进行彻底的测试。 # 5. DAX函数在Power BI中的高级应用 在Power BI中，DAX函数不仅仅是计算和展示数据的基础工具，它还能帮助我们构建高级的数据模型，执行复杂的分析，并将自动化任务融入我们的工作流中。接下来，我们将深入探讨DAX函数在Power BI中的高级应用。 ## 5.1 高级关系建模技巧 随着数据模型的复杂度增加，有效地处理多对多关系和利用虚拟表变得至关重要。Power BI 中的DAX允许我们创建更加复杂的逻辑来处理高级关系模型。 ### 5.1.1 更复杂的多对多关系处理 在第五章的第一部分，我们将探讨如何处理在传统数据模型中处理起来较为复杂的多对多关系。 ```mermaid graph LR A[开始数据建模] --> B[确定实体] B --> C[识别关系] C --> D[创建关联表] D --> E[应用DAX关系函数] E --> F[测试模型完整性] ``` 在多对多关系中，两个实体可能会通过一个或多个中间表进行连接。Power BI 允许我们通过创建关联表来管理这种复杂的关系。在Power BI Desktop中，可以使用“关系”视图来直观地管理这些关系。 ### 5.1.2 使用虚拟表和关系函数 DAX 提供了创建虚拟表（也称为表表达式）的功能，这使得能够应用自定义的过滤和分组逻辑。虚拟表可以在数据模型中被引用，就像物理表一样。 ```dax VirtualTableExample = SUMMARIZE( Sales, Customer[Region], "TotalSales", SUM(Sales[Amount]) ) ``` 在上面的示例中，`SUMMARIZE`函数创建了一个虚拟表，其中包含了客户所在区域和该区域的总销售额。关系函数如`RELATEDTABLE`可以用来访问与当前筛选上下文中的行相关联的表中的所有行。 ## 5.2 高级数据分析应用 使用DAX进行复杂的数据分析能够帮助我们洞悉数据背后的模式和趋势。我们可以通过编写特定的DAX公式来实现预测分析、趋势分析等。 ### 5.2.1 使用DAX进行预测和趋势分析 DAX 提供了 `AVERAGEX` 和 `SUMX` 等迭代函数，能够进行复杂的计算。结合时间智能函数，我们可以进行销售预测等操作。 ```dax SalesForecast = VAR CurrentYear = SELECTEDVALUE('Date'[Year]) RETURN AVERAGEX( FILTER( ALL('Date'[Year]), 'Date'[Year] < CurrentYear ), [TotalSales] ) ``` 在上述示例中，`SalesForecast` 度量值计算了基于过去年份销售总额的平均值，从而得出一个简单的销售预测值。 ### 5.2.2 利用DAX进行复杂的数据分组和汇总 DAX还允许我们按照自定义的分组进行汇总，例如基于时间段、年龄范围或其他业务逻辑。 ```dax SalesByAgeRange = CALCULATE( SUM(Sales[Amount]), SWITCH( TRUE(), AND(AGE(Sales[BirthDate]) >= 18, AGE(Sales[BirthDate]) < 30), "18-30", AND(AGE(Sales[BirthDate]) >= 30, AGE(Sales[BirthDate]) < 50), "30-50", "Other" ) ) ``` 在 `SalesByAgeRange` 计算中，我们根据客户的年龄范围汇总销售金额。通过 `SWITCH` 和 `TRUE()` 函数组合，我们对年龄进行了条件判断，创建了不同的年龄分组。 ## 5.3 集成和自动化DAX函数 随着分析需求的增长，自动化和集成成为了提高效率和质量的关键。 ### 5.3.1 将DAX函数集成到数据刷新和报告中 Power BI 提供了丰富的API来将DAX集成到数据刷新和报告的自动化中。我们可以使用DAX来定义刷新策略，自动更新报告。 ```json { "report": { "datasets": [ { "queries": [ { "query": "evaluate", "command": "RefreshDataset", "parameters": { "datasetId": "78b406a6-c06b-4311-8151-309d5910960d" } } ] } ] } } ``` 上述JSON配置可用于自动化Power BI报告的刷新过程。 ### 5.3.2 使用Power Automate自动化DAX任务 Power Automate 是一个流程自动化工具，可以和Power BI无缝集成，通过它我们可以设置DAX任务的自动化。 ```mermaid graph LR A[创建Power Automate流程] --> B[选择触发器] B --> C[添加Power BI操作] C --> D[配置DAX操作] D --> E[设置条件和分支] E --> F[部署和测试流程] ``` 通过上述步骤，我们可以为许多重复的数据处理和分析任务创建自动化的流程，如数据整理、报告生成等。 ### 结语 本章节展示了DAX函数在Power BI中的高级应用，通过复杂关系建模、高级数据分析以及自动化任务的集成，DAX函数展示了强大的数据处理能力。下一章节将继续深化这些概念，并展开讨论如何在实践中进一步优化和应用DAX函数。