关于powerbi:DAPBIDAX指南上

上篇: Power BI 概览笔记

DAX 根本语法

• 援用表中的列（如果不是数字结尾，不蕴含空格，也不是保留字，可省略单引号）例：

  'Sales'[Quantity]

最佳实际：在列援用中始终应用表名，在度量值援用中始终防止应用表名。以此在浏览代码时区分度量值和列。

• 正文：单行正文（//或--）
• 数据类型:Integer,Decimal,Currency,DateTime（平年谬误, 须要≥1900-03-01）,Boolean,String,Variant,Binary
• 运算符 (),+-*/, 比拟运算符,&&,||,IN,NOT

• 运算符重载, 强制转换

  • 工夫，例: Sales[OrderDatePlusOneWeek] = Sales[Order Date] + 7
  • 文本链接，例：= 5 & 4返回 ”54″
  • 运算符，例：= "5" + "4"返回 9。
• 表结构器: 单列{"Red","Blue","White"}, 多列{(1,2,3),}
• 条件语句: IF(exp,true,false)

• 计算列和度量值

  • 计算列：在内存数据库刷新时进行逐行运算，占用空间减慢运行速度。个别用来开发时察看两头值。

  • 度量值：对多行聚合计算。（在视觉对象筛选上下文或 DAX 查问上下文计算）

    最佳实际

    以下操作必须定义计算列

    • 将计算结果置于切片器中，透视表或矩阵的行区域、列区域（而并不是值区域），或应用计算列作为 DAX 查问的筛选条件
    • 定义严格绑定到以后行的表达式（例：价格×数量）
    • 对文本或数值做分类时（例：客户的年龄范畴：0~18,18~25）

    在筛选报表查看后果时定义度量值

    • 基于报表的筛选条件计算利润率
    • 有变分和地区筛选器的状况计算一个产品销售额占所有产品销售额的比例

• 变量 VAR ... RETURN ... 防止反复写表达式, 进步可读性和性能

  • 变量范畴的规定（块级作用域）：

    • 一个变量在其所在的 VAR/RETURN 代码块的 RETURN 局部可用，并且在 VAR/RETURN 代码块中，在这个变量之后定义的所有变量也能够应用它。VAR/RETURN 代码块能够替换任何 DAX 表达式，在这些表达式中能够读取变量。
    • 变量在定义其本身的 VAR/RETURN 代码块之外不可用。

• 错误处理（转换谬误，计算错误，空值缺失值）

  • 捕捉谬误

    • IFERROR(Sales[Quantity] * Sales[Price], BLANK())
    • ISERROR，ISBLANK
  • 抛出谬误ERROR()
  • 格式化 DAXFormatter.com

• 聚合函数和迭代函数

  • 聚合函数：在列上计算。个别对数值或日期，MIN/MAX 可对文本。(SUM,AVERAGE,MIN,MAX,STDEV)

    Sales[DaysToDeliver] = INT(Sales[Delivery Date] - Sales[Order Date])
    AvgDelivery:=AVERAGE(Sales[DaysToDeliver])

  • 迭代函数：聚合表达式。(SUMX,MINX,FILTER,ADDCOLUMNS,GENERATE)

    • 个别至多包含 2 个参数。1. 扫描的表，2. 为表的每一行计值得表达式。

      AvgDelivery:=AVERAGEX(
      Sales , 
      INT(Sales[Delivery Date] - Sales[Order Date])
      )

• 表函数（FILTER,ALL,ALLEXCEPT

  • 迭代表，例：

    Sales Amount Multiple Items :=SUMX(FILTER(Sales,Sales[Quantity]>1),
     Sales[Quantity] * Sales[Net Price]
    )

    [DEFINE {MEASURE <tableName>[<name>] = <expression>}]
    EVALUATE <table>
    [ORDER BY {<expression>} [{ASC|DESC}]} [, ...]]

  • 空值解决

    • ALL 所有行，疏忽任何筛选器。反对多列
    • VALUES 所有行，会思考现有筛选器，空行无效，反对单列
    • DISTINCT 返回不同值，会思考现有筛选器，不返回空行，反对单列
    • ALLNOBLANKROW 反对多列

    将表用作标量值：具备单行和单列的表 key 像标量值一样应用

    Brand Name:= IF(COUNTROWS (VALUES(Product[Brand]))=1,
     VALUES(Product[Brand])
    )
    ---
    Brand Name:= IF(HASONEVALUE (Product[Brand]),
     VALUES(Product[Brand])
    )
    Brand Name:= SELECTEDVALUE (Product[Brand], "Mutiple brands" )

    ALLSELECTED 在以后报表中可见且只思考以后视觉对象之外的所有筛选器

    Sales Pct :=DIVIDE(SUMX( Sales , Sales[Quantity] * Sales[Net Price] ),
     SUMX(ALL(Sales) , Sales[Quantity] * Sales[Net Price] )
    )
    ---
    Sales Pct :=DVIDE(SUMX( Sales , Sales[Quantity] * Sales[Net Price] ),
     SUMX(ALLSELECTED(Sales) , Sales[Quantity] * Sales[Net Price] )
    )

    计值上下文

  • 筛选上下文: 筛选模型
  • 行上下文: 迭代表

  • 嵌套多个表的行上下文

    SUMX(
     'Product Category',
     SUMX(RELATEDTABLE('Product'),
     SUMX(RELATEDTABLE(Sales),
       Sales[Quantity] * Product[Unit Price] * 'Product Category'[discount]
     )
     )
    )
    ---
    SUMX(
     Sales,
     Sales[Quantity] * RELATED(Product[Unit Price]) * RELATED('Product Category'[Discount])
    )

  • 同一个表上的多层嵌套行上下文

    Product[UnitPriceRankDense] = 
     VAR PriceOfCurrentProduct = Product[Unit Price]
     VAR HigherPrices = 
     FILTER (VALUES(Product[Unit Price]),
       Product[Unit Price] > PriceOfCurrentProduct
     )
     RETURN COUNTROWS(HigherPrices) + 1
    ---
     Product[UnitPriceRankDense] = COUNTROWS(
     FILTER(VALUES(Product[Unit Price]),
       Product[UnitPrice] > EARLIER(Product[Unit Price])
     )
     )

    • 多个表:

    • 须要用多个 RELATED 函数能力拜访关系“一”端的多个表

      Sales[UnitPriceVariance]=Sales[Unit Price] - RELATED(Product[Unit Price])

    • 当迭代产生在关系的“一”端时，应用 RELATEDTABLE, 它返回对应“多”端的所有行

      Product[NumberOfSales]=COUNTROWS(RELATEDTABLE(Sales))

CALCULATE 函数

需要

Contoso GM :=
VAR ContosoSales=
    FILTER (
      Sales,
      RELATED(Product[Brand]) = "Contoso"
    )
VAR ContosoMargin =
  SUMX (
    ContosoSales,
    Sales[Quantity] * (Sales[Net Price] - Sales[Unit Cost] )
  )
VAR ContosoSalesAmount =
  SUMX(
    ContosoSales,
    Sales[Quantity] * Sales[Net Price]
  )
RETURN DIVIDE (ContosoMargin,ContosoSalesAmount)

用 CALCULATE 实现

Gross Margin: = SUMX (Sales, Sales[Quantity] * (Sales[Net Price] - Sales[Unit Cost] ))
Sales Amount: = SUMX (Sales, Sales[Quantity] * Sales[Net Price] )
GM %: = DIVIDE ([Gross Margin], [Sales Amount] )

Contoso GM   :=CALCULATE([Gross Margin],Product[Brand]="Contoso")
Contoso GM % :=CALCULATE([GM %],Product[Brand]="Contoso")

• CALCULATE 承受 2 种类型的筛选器

  • 表模式的值列表

  • 布尔条件，如：

    Sales Amount Red Products :=
    CALCULATE([Sales Amount],
      Product[Color]="Red"
    )

dax 会转化为值列表的模式。如：```dax
[Sales Amount Red Products] :=
CALCULATE (SUM(Sales[SalesAmount]),
  FILTER(ALL(Product[Color]),
    Product[Color]="Red"
  )
)
```

• CALCULATE 规定总结

  • 概览

    • CALCULATE 在计值上下文中执行。该上下文蕴含一个筛选上下文，可能蕴含 一个 | 多个 行上下文。这是公式计值的初始环境
    • CALCULATE 创立一个新的筛选上下文，并在其中计值第一个参数。新的筛选上下文只蕴含筛选上下文。因为上下文转换的作用，所有行上下文在筛选上下文中都隐没了。

    • CALCULATE 承受三种类型的参数：

      • 在新的筛选上下文中计值的表达式。（总是做为第一个参数）
      • 操作原始筛选上下文的一组显示筛选器参数。每个筛选器参数都可能应用调节器（Modiier），比方 KEEPFILTERS
      • CALCULATE 调节器，通过删除一些筛选器或者更改关系构造，能够批改模型、调整原始筛选上下文的范畴。
    • 当原始上下文蕴含 一个 | 多个 行上下文时，CALCULATE 执行上下文转换，增加不可见的隐式筛选器。如果提供行上下文的是应用 KEEPFILTERS 的表表达式，那么隐式筛选器的行为也会被 KEEPFILTERS 批改。

  • 算法

    1. CALCULATE 在初始值上下文环境中计算所有的显式筛选器参数，包含原始行上下文（如果有的话）和原始筛选上下文。所有的显式筛选器参数在这个初始计值上下文环境中独立计算，计算实现后，CALCULATE 开始构建新的筛选上下文。
    2. CALCULATE 复制原始筛选上下文，以筹备新的筛选上下文。在这个过程中会抛弃原始行上下文，因为新的计值上下文将不蕴含任何行上下文。
    3. CALCULATE 执行上下文转换。它应用列在原始行上下文中的以后值，为正在迭代的所有列提供一个具备惟一值得筛选器。值得注意的是，此筛选器可能蕴含也可能不蕴含单个行，因为上下文转换并不保障新的筛选上下文只蕴含一行。如果没有流动的行上下文，则跳过此步骤。一旦通过上下文转换创立的所有隐式筛选器都位于新的筛选上下文，CALCULATE 就会进入步骤 4
    4. CALCULATE 计算调节器参数 USERELATIONSHIP、CROSSFILTER 和 ALL*（ALL 类函数）。这一步产生在步骤 3 之后。这十分重要，意味着咱们能够通过应用 ALL 来打消上下文转换的影响。
    5. CALCULATE 在初始计值上下文环境中计值所有的显式筛选器参数，并将其后果利用于步骤 4 之后生成的新的筛选上下文。一旦产生了上下文转换，这些筛选器参数就会被利用到新的筛选上下文中笼罩转换生成的上下文。这个过程产生在步骤 4 之后，也就是 ALL* 调节器移除上下文和模型关系构造更新之后，所以这一步生成的上下文不会被 ALL* 所影响。共事，筛选器参数的计算产生在原始上下文中，不受同一个 CALCULATE 函数中任何其余调节器或筛选器的影响。
    6. 最终，CALCULATE 在步骤 5 生成的筛选上下文中计值第一个参数。