Power BI Calculation Group

优点

计算组解决了复杂模型中的问题，在这种模型中，可以使用相同的计算（最常见的时间智能计算）来增加冗余度量值。例如，销售分析人员想要按月份截止日期（MTD）、季度截止到现在（QTD）、年初至今（YTD）、本年迄今订单（PY）等来查看销售总额和订单，等等。数据建模器必须为每个计算创建单独的度量值，这可能会导致多个度量值。对于用户而言，这可能意味着必须按多个度量值进行排序，并将它们分别应用到报表中。

首先，让我们看一下如何在 Power BI 的报表工具中向用户显示计算组。接下来，我们将介绍如何组成计算组，以及如何在模型中创建计算组。

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

在下面的动画中，用户正在分析销售数据2012年和2013年。在应用计算组之前，通用基础度量值销售额计算每月总销售额的总和。然后，用户希望应用时间智能计算，以获取本月至今、季度截止到目前、本年迄今等的销售总额。如果没有计算组，则用户必须选择单个时间智能度量值。

对于计算组，在此示例中，在名为Time 情报的情况下，当用户将时间计算项拖到 "列" 筛选区域时，每个计算项都显示为一个单独的列。每行的值都是从基本度量值Sales计算得出的。

正在应用 Power BI 的计算组

计算组使用显式DAX 度量值。在此示例中， Sales是已在模型中创建的显式度量值。计算组不能与隐式 DAX 度量值一起使用。例如，在 Power BI 隐式度量值是在用户将列拖动到视觉对象上以查看聚合值时创建的，而无需创建显式度量值。目前，Power BI 为作为内联 DAX 计算编写的隐式度量值生成 DAX-这意味着隐式度量值不能与计算组一起使用。已在表格对象模型（TOM）中看到一个新的模型属性， DiscourageImplicitMeasures。目前，若要创建计算组，必须将此属性设置为true。如果设置为 true，则在 "实时连接" 模式下 Power BI Desktop 将禁用隐式度量值的创建。

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

工作原理

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

在深入了解详细信息之前，让我们先介绍一些用于计算组的新 DAX 函数：

SELECTEDMEASURE -用于计算项以引用当前上下文中的度量值的表达式。在此示例中，为 "销售额" 度量值。

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

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

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

时间智能示例

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

时间智能计算项

当前

DAX

SELECTEDMEASURE()

MTD

DAX

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

QTD

DAX

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

累计

DAX

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

DAX

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

PY MTD

DAX

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

PY QTD

DAX

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

PY YTD

DAX

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

同比变化率

DAX

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

同比变化率

DAX

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

若要测试此计算组，请在 SSMS 或开源DAX Studio中执行 DAX 查询。注意：此查询示例中省略了同比变化率和同比变化率%。

时间智能查询

DAX

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 }
)

时间智能查询返回

返回表显示应用的每个计算项的计算。例如，请参阅3月2012日的 QTD 是二月份和三月2012之和。

查询返回

动态格式字符串

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

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

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

如果我们看一下上面所示的时间智能示例，则除同比变化率% 之外的所有计算项应在上下文中使用当前度量值的格式。例如，在销售基准度量值上计算的YTD应为货币。如果这是一个计算组，而这种计算组类似于 Orders 基本度量值，则格式将为数值。不过，同比变化率% 应为百分比，而不考虑基础度量值的格式。

对于同比变化率%，我们可以通过将格式字符串表达式属性设置为0.00%;-0.00%; 0.00%，来覆盖格式字符串。若要详细了解格式字符串表达式属性，请参阅MDX 单元属性-格式字符串内容。

在 Power BI 中的此矩阵视觉对象中，你会看到Sales current/同比变化率和ORDERS 当前/同比变化率保留各自的基本度量值格式字符串。不过， SALES 同比变化率% 和Orders 同比变化率% 将覆盖格式字符串以使用百分比格式。

矩阵视觉对象中的时间智能

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

动态格式字符串提供了简单的货币换算。请考虑以下艾德作品数据模型。它针对转换类型定义的一对多货币换算建模。

表格模型中的货币费率

"格式字符串" 列将添加到DimCurrency表中，并使用各自货币的格式字符串填充。

格式字符串列

在此示例中，将以下计算组定义为：

货币换算示例

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

用于货币换算的计算项

无转换

DAX

SELECTEDMEASURE()

转换货币

DAX

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]) ) )
    )
)

格式字符串表达式

DAX

SELECTEDVALUE(
    DimCurrency[FormatString],
    SELECTEDMEASUREFORMATSTRING()
)

格式字符串表达式必须返回标量字符串。如果筛选器上下文中有多个货币，则使用新的SELECTEDMEASUREFORMATSTRING函数恢复为基本度量值格式字符串。

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

应用的货币换算动态格式字符串

优先级

优先级是为计算组定义的属性。它指定了多个计算组时的计算顺序。数字越大，表示优先级越高，这意味着在优先级较低的计算组之前计算。

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

在此示例中，将定义每日平均计算。对于石油和天然气应用程序而言，一般的计算（例如每日的 barrels）都很常见。其他常见业务示例包含零售的商店销售平均值。

虽然这种计算是独立于时间智能计算计算的，但也有可能需要将它们组合在一起。例如，用户可能想要查看 YTD 的 barrels，以查看从该年开始到当前日期的每日燃油费率。在此方案中，应为计算项设置优先级。

平均示例

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

计算平均值的项

无平均值

DAX

SELECTEDMEASURE()

每日平均

DAX

DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate))

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

平均查询

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
)

平均查询返回

查询返回

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

平均查询返回
列名称	计算
YTD	Jan，二月，三月2012的销售总额 = 495364 + 506994 + 373483
每日平均	三月2012的销售额除以3月份的天数 = 373483/31
每日 YTD 平均值	三月2012的 YTD 除以一月、二月和三月的天数 = 1375841/（31 + 29 + 31）

下面是已应用优先级为20的 YTD 计算项的定义。

DAX

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

下面是应用优先级为10的每日平均值。

DAX

DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate))

由于时间智能计算组的优先级高于平均计算组的优先级，因此它会尽可能广泛地应用。 YTD 每日平均计算同时应用于每日平均计算的分子和分母（天数的计数）。

这等效于以下表达式：

DAX

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

不是此表达式：

DAX

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

侧向递归

在上述时间智能示例中，某些计算项引用相同计算组中的其他项。这称为 "侧向递归"。例如，同比变化率% 引用同比变化率和PY。

DAX

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

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

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

在我们的时间智能示例中， PY YTD计算项具有一个计算表达式：

DAX

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

计算（）函数的 YTD 参数重写筛选器上下文，以重用已在 YTD 计算项中定义的逻辑。不能将 PY 和 YTD 应用于单个评估中。仅当计算组中的单个计算项在筛选器上下文中时，才应用计算组。

排序

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

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

要排序的列

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

Ordinal 属性

若要了解详细信息，请参阅对计算项进行排序。

创建计算组

在 Visual Studio 中支持计算组，Analysis Services 项目 VSIX 更新2.9.2 和更高版本。还可以通过使用表格模型脚本语言（TMSL）或开源表格编辑器来创建计算组。

使用 Visual Studio 创建计算组

在表格模型资源管理器中，右键单击 "计算组"，然后单击 "新建计算组"。默认情况下，新的计算组将具有单个列和一个计算项。
使用属性来更改名称，并输入计算组、列和默认计算项目的描述。
若要为默认计算项输入 DAX 公式表达式，请右键单击，然后单击 "编辑公式" 以打开 DAX 编辑器。请输入有效的表达式。
若要添加其他计算项，请右键单击 "计算项"，然后单击 "新建计算项"。

订购计算项

在表格模型资源管理器中，右键单击某个计算组，然后单击 "添加列"。
命名列序号（或类似的内容），输入说明，然后将Hidden属性设置为 True。
对于要进行排序的每个计算项，请将Ordinal属性设置为正数。每个数字都是连续的，例如，序号属性为1的计算项将首先出现，属性2将显示第二个，依此类推。排序中不包含默认值为-1 的计算项，但会显示在报表中排序项之前。

限制

不支持对计算组表定义的对象级别安全性（OLS）。但是，可以在同一模型中的其他表上定义 OLS。如果计算项指的是 OLS 保护的对象，则会返回一般错误。

不支持行级别安全性（RLS）。在同一模型中的表上定义 RLS，而不是在计算组本身上定义（直接或间接）。

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

posted @ 2020-07-20 09:42 Javi 阅读(870) 评论(0) 编辑收藏举报

刷新页面返回顶部

Javi