计算组

适用于:SQL Server 2019 及更高版本 Analysis Services Azure Analysis Services Power BI Premium

通过将常见度量值表达式分组为“计算项”,计算组可显著减少冗余度量值的数量 。 1500 及更高 兼容级别的表格模型中支持计算组。

本文介绍:

  • 好处
  • 计算组的工作原理
  • 动态格式字符串
  • 优先顺序
  • 侧向递归
  • 中间件排序
  • 创建方法
  • 限制

好处

计算组解决了复杂模型中的问题,其中可能会使用相同的计算来激增冗余度量值,最常见的是时间智能计算。 例如,销售分析师希望按月 (MTD) 、季度 (QTD) 、年到年 (YTD) 、上一年 (PY) 的订单等按月查看销售总额和订单。 数据建模器必须为每个计算创建单独的度量值,这可能导致数十个度量值。 对于用户来说,这可能意味着必须对同样多的度量值进行排序,并将它们单独应用于报表。

首先,让我们看看计算组在报表工具(如Power BI)中的显示方式。 然后,我们将了解构成计算组的内容,以及如何在模型中创建它们。

计算组在报告客户端中作为具有单个列的表显示。 该列不像典型的列或维度,而是表示一个或多个可重用计算,或者可应用于已添加到可视化效果的值筛选器的任何度量值的 计算项

在以下动画中,用户正在分析 2012 年和 2013 年的销售数据。 在应用计算组之前,常见的基本度量值 Sales 计算每月总销售额的总和。 然后,用户希望应用时间智能计算来获取每月销售额、季度到日期、年份到日期等。 如果没有计算组,用户必须选择单个时间智能度量值。

使用计算组,在此示例中名为 “时间智能”时,当用户将 时间计算 项拖动到 “列” 筛选器区域时,每个计算项将显示为单独的列。 每行的值是从基本度量值 Sales 计算得出的。

Calculation group being applied in Power BI

计算组使用 显式 DAX 度量值。 在此示例中, Sales 是已在模型中创建的显式度量值。 计算组不适用于隐式 DAX 度量值。 例如,当用户将列拖到视觉对象上查看聚合值时,会在Power BI隐式度量值中创建,而无需创建显式度量值。 目前,Power BI为内联 DAX 计算编写的隐式度量值生成 DAX -这意味着隐式度量值不能与计算组一起使用。 “表格对象模型” (TOM) 中可见的新模型属性已引入 “DiscourageImplicitMeasures”。 目前,若要创建计算组,此属性必须设置为 true。 如果设置为 true,Power BI Live Connect 模式下的 Desktop 将禁用隐式度量值的创建。

计算组还支持多维数据表达式 (MDX) 查询。 这意味着,Microsoft Excel使用 MDX 查询表格数据模型的用户可以充分利用工作表数据透视表和图表中的计算组。

工作原理

现在,你已了解计算组如何使用户受益,接下来让我们看看如何创建时间智能计算组示例。

在深入了解详细信息之前,让我们介绍一些专门针对计算组的新 DAX 函数:

SELECTEDMEASURE - 由表达式用于计算项来引用当前处于上下文中的度量值。 在此示例中,Sales 度量值。

SELECTEDMEASURENAME - 由表达式用于计算项以确定按名称在上下文中的度量值。

ISSELECTEDMEASURE - 用于计算项的表达式用于确定上下文中的度量值在度量值列表中指定。

SELECTEDMEASUREFORMATSTRING - 由表达式用于计算项来检索上下文中度量值的格式字符串。

时间智能示例

表名称 - 时间智能
列名称 - 时间计算
优先级 - 20

时间智能计算项

Current

SELECTEDMEASURE()

MTD

CALCULATE(SELECTEDMEASURE(), DATESMTD(DimDate[Date]))

QTD

CALCULATE(SELECTEDMEASURE(), DATESQTD(DimDate[Date]))

YTD

CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))

PY

CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(DimDate[Date]))

PY MTD

CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR(DimDate[Date]),
    'Time Intelligence'[Time Calculation] = "MTD"
)

PY QTD

CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR(DimDate[Date]),
    'Time Intelligence'[Time Calculation] = "QTD"
)

PY YTD

CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR(DimDate[Date]),
    'Time Intelligence'[Time Calculation] = "YTD"
)

YOY

SELECTEDMEASURE() -
CALCULATE(
    SELECTEDMEASURE(),
    'Time Intelligence'[Time Calculation] = "PY"
)

YOY%

DIVIDE(
    CALCULATE(
        SELECTEDMEASURE(),
        'Time Intelligence'[Time Calculation]="YOY"
    ),
    CALCULATE(
        SELECTEDMEASURE(),
        'Time Intelligence'[Time Calculation]="PY"
    )
)

若要测试此计算组,请在 SSMS 或开源 DAX Studio 中执行 DAX 查询。 注意:从此查询示例中省略 YOY 和 YOY%。

时间智能查询

EVALUATE
CALCULATETABLE (
    SUMMARIZECOLUMNS (
        DimDate[CalendarYear],
        DimDate[EnglishMonthName],
        "Current", CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "Current" ),
        "QTD",     CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "QTD" ),
        "YTD",     CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "YTD" ),
        "PY",      CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY" ),
        "PY QTD",  CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY QTD" ),
        "PY YTD",  CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY YTD" )
    ),
    DimDate[CalendarYear] IN { 2012, 2013 }
)

时间智能查询返回

返回表显示应用的每个计算项的计算。 例如,请参阅 QTD for 2012 年 3 月是 2012 年 1 月、2 月和 3 月的总和。

Time intelligence query return

动态格式字符串

具有计算组的动态格式字符串允许条件应用格式字符串以度量值,而无需强制它们返回字符串。

表格模型使用 DAX 的 FORMAT 函数支持度量值的动态格式设置。 但是,FORMAT 函数具有返回字符串的缺点,强制将其他为数值的度量值作为字符串返回。 这可以有一些限制,例如,不处理大多数Power BI视觉对象,具体取决于数值,如图表。

用于时间智能的动态格式字符串

如果查看上面所示的时间智能示例, 除 YOY% 之外的所有计算项都应使用上下文中当前度量值的格式。 例如,在 Sales base 度量值上计算 的 YTD 应为货币。 如果这是一个计算组,类似于 Orders 基度量值,则格式将为数值。 但是,无论基本度量值的格式如何,YOY%都应是百分比。

对于 YOY%,可以通过将格式字符串表达式属性设置为 0.00%;-0.00%;0.00% 来替代格式字符串。 若要了解有关设置字符串表达式属性格式的详细信息,请参阅 MDX 单元格属性 - FORMAT STRING 内容

在此矩阵视觉对象Power BI中,可以看到 Sales Current/YOYOrders Current/YOY 保留其各自的基本度量值格式字符串。 但是,销售 YOY%订单 YOY%替代格式字符串以使用百分比格式。

Time intelligence in matrix visual

用于货币转换的动态格式字符串

动态格式字符串提供简单的货币转换。 请考虑以下 Adventure Works 数据模型。 它针对由转换类型定义的一对多货币转换建模。

Currency rate in tabular model

FormatString 列将添加到 DimCurrency 表,并使用相应货币的格式字符串填充。

Format string column

对于此示例,以下计算组随后定义为:

货币换算示例

表名 - 货币转换
列名 - 转换计算
优先级 - 5

货币转换的计算项

无转换

SELECTEDMEASURE()

转换后的货币

IF(
    //Check one currency in context & not US Dollar, which is the pivot currency:
    SELECTEDVALUE( DimCurrency[CurrencyName], "US Dollar" ) = "US Dollar",
    SELECTEDMEASURE(),
    SUMX(
        VALUES(DimDate[Date]),
        CALCULATE( DIVIDE( SELECTEDMEASURE(), MAX(FactCurrencyRate[EndOfDayRate]) ) )
    )
)

设置字符串表达式格式

SELECTEDVALUE(
    DimCurrency[FormatString],
    SELECTEDMEASUREFORMATSTRING()
)

格式字符串表达式必须返回标量字符串。 它使用新的 SELECTEDMEASUREFORMATSTRING 函数在筛选器上下文中存在多个货币时还原为基本度量值格式字符串。

以下动画显示了报表中 Sales 度量值的动态格式货币转换。

Currency conversion dynamic format string applied

优先顺序

优先级是为计算组定义的属性。 当有多个计算组时,它指定计算顺序。 较高的数字表示更高的优先级,这意味着它将在优先级较低的计算组之前进行评估。

对于此示例,我们将使用与上述时间智能示例相同的模型,但也添加 Averages 计算组。 Averages 计算组包含与传统时间智能无关的平均计算,因为它们不会更改日期筛选器上下文-它们只是应用其中的平均计算。

在此示例中,定义了每日平均计算。 石油和天然气应用程序常见的计算,例如日均桶石油。 其他常见业务示例包括零售店销售平均值。

虽然此类计算独立于时间智能计算进行计算,但可能需要将它们组合在一起。 例如,用户可能希望查看日石油桶/日,以查看从年初到当前日期的每日油价。 在这种情况下,应为计算项设置优先级。

平均值示例

表名称为 平均值
列名称为 “平均计算”。
优先级为 10

平均值的计算项

无平均值

SELECTEDMEASURE()

每日平均值

DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate))

下面是 DAX 查询和返回表的示例:

平均值查询

EVALUATE
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
        DimDate[CalendarYear],
        DimDate[EnglishMonthName],
        "Sales", CALCULATE (
            [Sales],
            'Time Intelligence'[Time Calculation] = "Current",
            'Averages'[Average Calculation] = "No Average"
        ),
        "YTD", CALCULATE (
            [Sales],
            'Time Intelligence'[Time Calculation] = "YTD",
            'Averages'[Average Calculation] = "No Average"
        ),
        "Daily Average", CALCULATE (
            [Sales],
            'Time Intelligence'[Time Calculation] = "Current",
            'Averages'[Average Calculation] = "Daily Average"
        ),
        "YTD Daily Average", CALCULATE (
            [Sales],
            'Time Intelligence'[Time Calculation] = "YTD",
            'Averages'[Average Calculation] = "Daily Average"
        )
    ),
    DimDate[CalendarYear] = 2012
)

平均值查询返回

Averages query return

下表显示了如何计算 2012 年 3 月的值。

列名 计算
YTD 2012 年 1 月、2 月、2 月销售额的总和
= 495,364 + 506,994 + 373,483
每日平均值 2012 年 3 月销售额除以 3 月天数
= 373,483 / 31
YTD 每日平均值 2012 年 3 月的 YTD 除以 1 月、2 月和 Mar 中的天数
= 1,375,841 / (31 + 29 + 31)

下面是以 20 为优先顺序应用的 YTD 计算项的定义。

CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))

下面是每日平均值,其优先级为 10

DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate))

由于时间智能计算组的优先级高于平均值计算组的优先级,因此尽可能广泛地应用该计算组。 YTD 每日平均值计算将一般计算应用于 numerator 和分母 (日平均值计算) 的天数计数。

这等效于以下表达式:

CALCULATE(DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate)), DATESYTD(DimDate[Date]))

不是此表达式:

DIVIDE(CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date])), COUNTROWS(DimDate)))

侧向递归

在上面的时间智能示例中,某些计算项引用同一计算组中的其他项。 这称为 侧向递归。 例如, YOY% 引用 YOYPY

DIVIDE(
    CALCULATE(
        SELECTEDMEASURE(),
        'Time Intelligence'[Time Calculation]="YOY"
    ),
    CALCULATE(
        SELECTEDMEASURE(),
        'Time Intelligence'[Time Calculation]="PY"
    )
)

在这种情况下,这两个表达式都是单独计算的,因为它们使用不同的计算语句。 不支持其他类型的递归。

筛选器上下文中的单个计算项

在我们的时间智能示例中, PY YTD 计算项具有单个计算表达式:

CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR(DimDate[Date]),
    'Time Intelligence'[Time Calculation] = "YTD"
)

CALCULATE () 函数的 YTD 参数重写筛选器上下文,以重用在 YTD 计算项中定义的逻辑。 不能在单个评估中同时应用 PY 和 YTD。 仅当计算组中的单个计算项位于筛选器上下文中时 ,才会应用 计算组。

中间件排序

默认情况下,当计算组中的列放置在报表中时,计算项按名称在报表中按字母顺序排序。 通过指定 Ordinal 属性可以更改报表中显示的计算项的顺序。 使用序号属性指定计算项顺序不会更改 优先级,即计算项的计算顺序。 它还不会更改计算项在表格模型资源管理器中显示的顺序。

若要指定计算项的序号属性,必须将第二列添加到计算组。 与数据类型为 Text 的默认列不同,用于排序计算项的第二列具有整数数据类型。 此列的唯一用途是指定计算组中的计算项出现的数字顺序。 由于此列在报表中不提供任何值,因此最好将 Hidden 属性设置为 True。

Column for ordering

将第二列添加到计算组后,可以为要排序的计算项指定序号属性值。

Ordinal property

若要了解详细信息,请参阅 “订购计算项”。

创建计算组

Visual Studio 中支持 Analysis Services 项目 VSIX update 2.9.2 及更高版本的计算组。 也可以使用表格模型脚本语言 (TMSL) 或开放源代码表格编辑器来创建计算组。

使用 Visual Studio 创建计算组

  1. 在表格模型资源管理器中,右键单击 “计算组”,然后单击“ 新建计算组”。 默认情况下,新的计算组将具有单个列和单个计算项。

  2. 使用 “属性” 更改名称并输入计算组、列和默认计算项的说明。

  3. 若要输入默认计算项的 DAX 公式表达式,请右键单击并单击“ 编辑公式 ”以打开 DAX 编辑器。 输入有效的表达式。

  4. 若要添加其他计算项,请右键单击 “计算项”,然后单击“ 新建计算项”。

对计算项进行排序

  1. 在表格模型资源管理器中,右键单击计算组,然后单击“ 添加”列

  2. 将列序号 (或类似) 命名,输入说明,然后将 Hidden 属性设置为 True。

  3. 对于要排序的每个计算项,请将 Ordinal 属性设置为正数。 例如,每个数字都是顺序的,具有序号属性 1 的计算项将首先出现,2 的一个属性将显示第二个,依依如此。 默认 -1 的计算项不包括在排序中,但在报表中的有序项之前会显示。

限制

不支持对计算组表定义的对象级别安全性 (OLS) 。 但是,可以在同一模型中的其他表上定义 OLS。 如果计算项引用 OLS 保护的对象,则返回泛型错误。

不支持行级别安全性 (RLS) 。 在同一模型中对表定义 RLS,但不定义计算组本身 (直接或间接) 。

计算组不支持详细信息行表达式

计算组不支持Power BI中的智能叙事视觉对象。

具有计算组的模型不支持Power BI中的隐式列聚合。 目前,如果 DiscourageImplicitMeasures 属性设置为 false (默认) ,则显示聚合选项,但无法应用它们。 如果 DiscourageImplicitMeasures 设置为 true,则不会显示聚合选项。

使用 LiveConnection 创建Power BI报表时,动态格式字符串不会应用于报表级度量值。

另请参阅

表格模型中的 DAX
DAX 参考