PowerBI_Chapter6：DAX

文章目录前言一、DAX Overview / DAX 概述一What is DAX? / 什么是DAX1. Definition 定义2. Data Types 数据类型3. Key Capabilities 核心能力二Why Learn DAX? / 为什么要学DAX二、DAX Syntax / DAX 语法一Formula Structure / 公式结构1. Example 示例2. Elements 组成元素3. Plain Language Reading 通俗解读三、Types of DAX Expressions / DAX 表达式类型一Calculated Columns / 计算列二Measures / 度量值三Calculated Tables / 计算表四Row-Level Security Expressions / 行级安全表达式四、Common DAX Functions by Category / 常用DAX函数分类一Aggregation Functions / 聚合函数二Logical Functions / 逻辑函数三Text Functions / 文本函数四Date and Time Functions / 日期与时间函数五Filter Functions / 筛选函数六Mathematical Functions / 数学函数七Information Functions / 信息函数八Time Intelligence Functions / 时间智能函数五、Calculated Columns in Detail / 计算列详解一Mathematical Functions / 数学函数示例二Date Functions / 日期函数示例三Text Functions / 文本函数示例四Logical Functions / 逻辑函数示例五Information Functions / 信息函数示例六、DAX Measures / DAX 度量值一Definition 定义二Types of Measures / 度量类型1. Implicit Measures / 隐式度量2. Explicit Measures / 显式度量三Aggregation Functions Used in Measures / 度量中常用的聚合函数七、CALCULATE Function / CALCULATE 函数一Purpose 作用二Adding Filters with CALCULATE / 使用CALCULATE添加筛选三Adding Filters with FILTER() / 使用FILTER()添加筛选四Removing Filters / 移除筛选五Updating Filters / 更新筛选八、Time Intelligence Functions / 时间智能函数一Prerequisites / 前提条件二Key Functions / 关键函数1. DATESYTD() / 年初至今日期表2. DATESMTD() / 月初至今日期表3. TOTALYTD() / 年初至今总计4. DATESINPERIOD() / 指定周期日期表5. SAMEPERIODLASTYEAR() / 去年同期日期表6. PREVIOUSYEAR() / 前一年九、Replace Implicit Measures with Explicit Measures / 用显式度量替换隐式度量一Why Replace? / 为什么要替换二Best Practice / 最佳实践十、Characteristics of Measures / 度量的特性一Additive Measures / 可加性度量二Semi-Additive Measures / 半可加性度量三Non-Additive Measures / 不可加性度量十一、Quick Measures / 快速度量总结 / Summary核心考点Top 5前言本文是Power BI Desktop第六章“DAX数据分析表达式”的期末复习笔记结合教材与考试重点整理。内容涵盖DAX基础语法、计算列、度量值、计算表、常用函数聚合、逻辑、文本、日期、信息、CALCULATE筛选操纵、时间智能函数、度量特性及隐式替换。一、DAX Overview / DAX 概述一What is DAX? / 什么是DAX1. Definition 定义DAX (Data Analysis Expressions)is aformula expression language used in Power BI, Excel, and other BI tools for creating customcalculations and aggregations on data models.DAX数据分析表达式是一种公式表达式语言用于在Power BI、Excel等BI工具中对数据模型创建自定义计算和聚合。DAX is also known asfunction language, where the full code is kept inside a function.a collection of functionsDAX也被称为函数语言完整代码都放在函数内部。2. Data Types 数据类型Numeric: integers, currency, decimals. / 数值型整数、货币、小数。Other: string, binary object. / 其他字符串、二进制对象。3. Key Capabilities 核心能力Perform Calculations: Use built-in functions for math, stats, logic, and text operations. / 执行计算使用内置函数进行数学、统计、逻辑和文本操作。Aggregate Data: Use SUM, AVERAGE, COUNT, etc., including running totals and moving averages. / 聚合数据使用SUM、AVERAGE、COUNT等包括运行总和和移动平均。Time Intelligence: Year-over-year, cumulative totals, date-based filtering. / 时间智能同比、累计总计、基于日期的筛选。Create Relationships: Work across multiple tables. / 创建关系跨多个表计算。Filter Context: Dynamic analysis based on user selections. / 筛选上下文基于用户选择的动态分析。二Why Learn DAX? / 为什么要学DAXExtractactionable insightsfrom data. / 从数据中提取可操作的见解。Solve business problems affecting decision-making and bottom line. / 解决影响决策和利润的业务问题。Create sophisticated calculations, dynamic reports, and advanced capabilities (time intelligence, conditional logic). / 创建复杂计算、动态报表和高级功能时间智能、条件逻辑。二、DAX Syntax / DAX 语法一Formula Structure / 公式结构1. Example 示例T o t a l S a l e s S U M ( S a l e s [ S a l e s A m o u n t ] ) Total Sales SUM(Sales[SalesAmount])TotalSalesSUM(Sales[SalesAmount])2. Elements 组成元素Measure Name:Total Sales– name that appears in report. / 度量名称。Equals sign (): Indicates beginning of formula. / 等号表示公式开始。DAX Function:SUM– defines the calculation. / DAX函数定义计算。Parentheses (): Surround arguments. / 括号包围参数。Table Reference:Sales– table containing data. / 表引用。Column Reference:[SalesAmount]– column to aggregate. / 列引用。3. Plain Language Reading 通俗解读“For the measure namedTotal Sales, calculate theSUMof values in the[SalesAmount]column in the Sales table.”“对于名为总销售额的度量计算Sales表中[SalesAmount]列的SUM。”三、Types of DAX Expressions / DAX 表达式类型一Calculated Columns / 计算列Row-by-rowcomputation, stored in the table. / 逐行计算存储在表中。Use cases: Filters/slicers, intermediate steps, specific conditions (e.g., categorizing sales as “High/Medium/Low”). / 用途筛选/切片器、中间步骤、特定条件判断。Example:Status IF(Sales[Total_Sales] 1000, Good, Bad)二Measures / 度量值Dynamiccalculations evaluated on the fly based on filter context. Not stored. / 动态计算基于筛选上下文实时评估不存储。Use cases: Aggregations like totals, averages, percentages. / 用途求和、平均、百分比等聚合。Example:Total Sales SUM(Sales[SalesAmount])三Calculated Tables / 计算表DAX expressions that returnentire tables, used for modeling or creating subsets. / 返回整个表的DAX表达式用于建模或创建子集。Example:TopSales FILTER(Sales, Sales[SalesAmount] 1000)四Row-Level Security Expressions / 行级安全表达式Define filters in security roles to restrict data access. / 在安全角色中定义筛选器以限制数据访问。Example:[Region] North America对比维度DAX FunctionsDAX函数DAX ExpressionsDAX表达式定义预定义的操作或计算单元是公式的基本构件。由函数、运算符、常量及引用组合而成的完整计算公式。作用执行特定动作如求和、判断、日期计算等。定义如何计算、返回或转换数据产生最终结果。组成单一功能块如SUM、IF、CALCULATE。可包含多个函数、运算符、列引用和常量如SUM(Sales[Amount]) * 1.1。返回类型通常返回标量值或表取决于函数类型。可返回标量、表或布尔值取决于表达式用途度量、计算列、计算表等。使用场景作为表达式的一部分被调用不能独立存在。可独立创建为度量、计算列、计算表或行级安全规则。示例SUM、AVERAGE、IF、CALCULATE、DATESYTD。Total Sales SUM(Sales[SalesAmount])度量表达式Status IF(Sales[Amount] 1000, High, Low)计算列表达式。是否可单独使用否必须嵌入表达式中。是可独立保存在模型中如度量、计算列。核心关系函数是表达式的“原材料”表达式是函数的“容器”和“调用者”。表达式通过调用一个或多个函数并搭配运算符和引用完成具体业务逻辑。四、Common DAX Functions by Category / 常用DAX函数分类一Aggregation Functions / 聚合函数SUM(),AVERAGE(),MIN(),MAX(),COUNT(),DISTINCTCOUNT(),COUNTROWS()二Logical Functions / 逻辑函数IF(),AND(),OR(),SWITCH()三Text Functions / 文本函数CONCATENATE(),LEFT(),RIGHT(),LEN(),UPPER(),LOWER(),REPLACE(),SUBSTITUTE(),SEARCH(),FIND(),FORMAT(),TRIM(),CLEAN()四Date and Time Functions / 日期与时间函数YEAR(),MONTH(),DAY(),TODAY(),NOW(),DATEDIFF(),DATE()五Filter Functions / 筛选函数CALCULATE(),FILTER(),ALL(),ALLEXCEPT()六Mathematical Functions / 数学函数ROUND(),CEILING(),FLOOR(),MOD(),POWER(),DIVIDE()七Information Functions / 信息函数ISBLANK(),ISERROR(),ERROR(),ISNUMBER(),ISTEXT(),ISNONTEXT(),ISEVEN(),ISODD()八Time Intelligence Functions / 时间智能函数TOTALYTD(),DATESYTD(),DATESMTD(),DATESINPERIOD(),SAMEPERIODLASTYEAR(),PREVIOUSYEAR()五、Calculated Columns in Detail / 计算列详解一Mathematical Functions / 数学函数示例Sales Sales[SellingPrice] * Sales[Quantity]Avg_Sales DIVIDE(Sales[Sales], Sales[SellingPrice])Power POWER(Sales[Quantity], 2)二Date Functions / 日期函数示例Extract components:Year Sales[SaleDate].[Year],Month Sales[SaleDate].[Month],Day Sales[SaleDate].[Day]Current date:Current_date TODAY(),CurrentDateTime NOW()Difference:DaysOpen DATEDIFF(Customer[StartDate], Customer[EndDate], DAY)Age:Age DATEDIFF(Sales[SaleDate], TODAY(), DAY)Create date:Custom_Date DATE(2025,3,1)Format:Custom_Date FORMAT(DATE(2025,3,1), yyyy-mm-dd)三Text Functions / 文本函数示例Concatenate:Merge CONCATENATE(product[Product], product[Category])orMerge product[Product] | product[Category]Case:Lower LOWER(product[Product]),Upper UPPER(product[Product])Length:len LEN(product[Product])Extract:short_name LEFT(product[Product], 3),RIGHT(...)Search:Search IFERROR(SEARCH(Oak, Customer[Address]), BLANK())Replace:NewName REPLACE(Customer[Name], 1, 4, Mr.)Substitute:NewCol SUBSTITUTE(Table[description], laptop, tablet)四Logical Functions / 逻辑函数示例Simple IF:Status IF(Sales[Sales] 1000, Good, Bad)AND:Flag IF(Sales[SellingPrice] 1000 Sales[Quantity] 2, Valid, Invalid)OR:Status IF(Sales[Sales] || Sales[SellingPrice] 1000, Good, Bad)SWITCH (multiple conditions):Grade SWITCH(TRUE(), Sales[Sales] 3000, High Profit, Sales[Sales] 2000, Profit, Sales[Sales] 1000, No Profit, Loss )五Information Functions / 信息函数示例ISBLANK(Customer[Name])ISNUMBER(Customer[CustomerID])ISTEXT(Customer[CustomerID])ERROR(Customer[Address])六、DAX Measures / DAX 度量值一Definition 定义AMeasureis acalculated fieldwritten in DAX that performs calculations dynamically based on user interactions (filters, slicers, visuals).度量是用DAX编写的计算字段根据用户交互筛选器、切片器、视觉对象动态执行计算。Measuresdo not store data; they calculate resultson the fly.度量不存储数据而是即时计算结果。二Types of Measures / 度量类型1. Implicit Measures / 隐式度量Automatically created by Power BI when you drag a numeric column into a visual. / 将数字列拖入视觉对象时Power BI自动创建。Limitations: No custom logic, not reusable, not visible in Fields pane. / 局限无自定义逻辑不可重用不在字段窗格显示。2. Explicit Measures / 显式度量Manually created using DAX formula bar. / 使用DAX公式栏手动创建。Advantages: Reusable, full control over logic and formatting, visible with calculator icon. / 优点可重用完全控制逻辑和格式带计算器图标显示。三Aggregation Functions Used in Measures / 度量中常用的聚合函数SumOfSales SUM(Sales[Sales])Average AVERAGE(Sales[Sales])Total_count COUNT(Sales[SaleID])Dis_count DISTINCTCOUNT(Sales[ProductID])七、CALCULATE Function / CALCULATE 函数一Purpose 作用CALCULATE()modifies thefilter contextof a calculation. It allows you to add, remove, or override filters dynamically.CALCULATE()用于修改计算的筛选上下文允许动态添加、删除或覆盖筛选器。二Adding Filters with CALCULATE / 使用CALCULATE添加筛选Single filter:Laptop_Sale CALCULATE([SumOfSales], Sales[ProductID] P001)Multiple values (IN):Sales_Laptop_Phone CALCULATE([SumOfSales], Sales[ProductID] IN {P001, P002})Multiple columns (AND):MotorCycle Sales 2023 CALCULATE([TotalOfSales], products[productLine] Motorcycles, orders[year] 2023)三Adding Filters with FILTER() / 使用FILTER()添加筛选FILTER()returns a table of filtered data and is used inside CALCULATE.JanSales CALCULATE([SumOfSales], FILTER(sales, Sales[Month] January))Multiple conditions:JanSalesmorethan1000 CALCULATE([Total Sales], FILTER(sales, Sales[salesprice] 1000 Sales[Month] January))四Removing Filters / 移除筛选Remove filter on asingle column:ProfitAllproducts CALCULATE([Laptop_mobile], ALL(Sales[ProductID]))Remove all filters from awhole table:Profit All Sales Territories CALCULATE([Total Profit], ALL(Sales))五Updating Filters / 更新筛选Abbreviated filter:City[SalesTerritory] New EnglandEquivalent to:FILTER(ALL(City[SalesTerritory]), City[SalesTerritory] New England)八、Time Intelligence Functions / 时间智能函数一Prerequisites / 前提条件Need aDate tablewith unique date values, marked as a date table. / 需要一个具有唯一日期值的日期表并标记为日期表。Most time intelligence functions returntables of datesused as filters in CALCULATE. / 大多数时间智能函数返回日期表用作CALCULATE中的筛选器。二Key Functions / 关键函数1. DATESYTD() / 年初至今日期表Returns all dates from Jan 1st to the current date in context. / 返回从1月1日到当前上下文中日期的所有日期。DatesYTD CALCULATE([SumOfSales], DATESYTD(Sales[SaleDate]))2. DATESMTD() / 月初至今日期表Returns all dates from the start of the month to the current date. / 返回从月初到当前日期的所有日期。DatesMTD CALCULATE([SumOfSales], DATESMTD(Sales[SaleDate]))3. TOTALYTD() / 年初至今总计Directly calculates YTD total without CALCULATE wrapper. / 直接计算年初至今总计无需CALCULATE包裹。SalesYTD TOTALYTD([SumOfSales], Sales[SaleDate])4. DATESINPERIOD() / 指定周期日期表Returns a table of dates for a specified period before/after a given date. / 返回给定日期前后指定时间段的日期表。Last10days CALCULATE([SumOfSales], DATESINPERIOD(Sales[SaleDate], 04-10-2025, -10, DAY))5. SAMEPERIODLASTYEAR() / 去年同期日期表Returns dates from the same period in the previous year. / 返回去年同期的日期。Sales LY CALCULATE(SUM(Sales[SellingPrice]), SAMEPERIODLASTYEAR(Sales[SaleDate]))6. PREVIOUSYEAR() / 前一年Returns all dates of the previous year based on current context. / 基于当前上下文返回前一年的所有日期。PreviousYr CALCULATE([SumOfSales], PREVIOUSYEAR(Sales[SaleDate]))九、Replace Implicit Measures with Explicit Measures / 用显式度量替换隐式度量一Why Replace? / 为什么要替换Implicit measureslack control(no custom filtering, formatting, or logic). / 隐式度量缺乏控制。Theycan’t be reusedacross visuals. / 不可跨视觉对象重用。They’renot visiblein the Fields pane. / 在字段窗格中不可见。Explicit measures make your modelcleaner,more powerful, andmaintainable. / 显式度量使模型更干净、强大且可维护。二Best Practice / 最佳实践Always create explicit measures for any calculation that will be used in multiple visuals or needs complex logic. / 任何将在多个视觉对象中使用或需要复杂逻辑的计算都应创建显式度量。十、Characteristics of Measures / 度量的特性一Additive Measures / 可加性度量Can be summed acrossall dimensions(time, geography, product, etc.). / 可跨所有维度时间、地理、产品等求和。Examples: Sales Amount, Units Sold, Revenue, Cost. / 示例销售额、销量、收入、成本。二Semi-Additive Measures / 半可加性度量Can be added acrosssome dimensions(e.g., region, product) butnot across time. / 可跨某些维度如地区、产品求和但不能跨时间。Examples: Inventory (take last value), Bank Balance, Account Balance. / 示例库存取最后值、银行余额、账户余额。三Non-Additive Measures / 不可加性度量Cannot be added across any dimension; must use special aggregations like average, ratio, or distinct count. /不能跨任何维度求和必须使用平均、比率、非重复计数等特殊聚合。Examples: Profit Margin (%), Conversion Rate, Average Price, Customer Satisfaction Score. / 示例利润率%、转化率、平均价格、客户满意度评分。十一、Quick Measures / 快速度量In Power BI,Quick Measuresare predefined DAX calculations that allow you to easily create common and complex measureswithout manually writing DAX code.在Power BI中快速度量是预定义的DAX计算允许你无需手动编写DAX代码即可轻松创建常见和复杂的度量。They are a great starting point for learning DAX syntax. / 它们是学习DAX语法的良好起点。总结 / Summary核心考点Top 5DAX 三大表达式类型– 计算列逐行存储、度量动态即时、计算表返回表。CALCULATE 核心用法– 修改筛选上下文添加/删除/覆盖结合FILTER、ALL等函数。时间智能函数– TOTALYTD, DATESYTD, DATESMTD, DATESINPERIOD, SAMEPERIODLASTYEAR需日期表。度量特性– 可加性全维度求和、半可加性不跨时间、不可加性用平均/比率。显式 vs 隐式度量– 显式可重用、可控隐式快捷但局限。