新知一下
海量新知
6 2 9 1 1 1 8

BI往事——Power BI 计算精要

BI使徒 | 传播商业智能的布道者 2021/11/10 09:35

Power BI V.s Excel

Power BI中使用的分析公式分为两大类:DAX(表格型公式)和MDX(多维分析公式)。相比下,DAX公式逻辑更为简化易于理解,因此近年DAX渐渐成为主流趋势,而且DAX语句可用于Analysis Services、Power BI和Excel中。有戏称,学会DAX,同时会了三种工具,本书内容也是围绕DAX分析语言展开。

DAX与Excel函数同属于分析语言,其中许多Excel公式在DAX环境下也是通用的。例如,LEFT()、IF()、SUM()等等。但是,DAX和Excel又存在明显的不同。以下将从三个角度比较它们的差异:

DAX度量(Measure)

依存于内存的列计算。Excel公式度量的计算依据为“单元格”,但在DAX概念中,“单元格”是不存在的。这样做极大地节约了计算资源,度量只有在被使用时候才会占用内存,也不占用磁盘空间资源。由于度量计算是基于列的计算,其计算引擎十分高效,几十万行数值的聚合计算运行在4G内存电脑上是很轻松的事情。同时,DAX还会优化压缩源文件数据,PBIX文件的大小往往比原有的Excel/文本文件更小。而Excel中单元格计算不仅消耗内存资源,而且需要硬盘空间资源,其计算效能远不如DAX,有时跑几万行的数据已经相当吃力,复杂的Excel工作簿运行时还会导致文件崩溃,强制关闭应用。

表关联

DAX另一个特点是允许表于表依据一定的规则进行关联,这样避免了使用Excel大表的必要。而众所周知,Excel大表是导致Excel性能问题的一个重要因素,因为大表中使用Vlookup公式会产生大量的数据冗余,资源消耗会随着表复杂程度几何级增长,而DAX的关联很好地弥补了Excel此方面的局限。

引用变量

Excel公式中经常使用“单元格”引用,这种方式虽然很方便,但也有极大的隐患,一旦单元格位置发生变化,可能导致公式错误。因为DAX度量是列计算,不存在引用单一单元格的情景,除非整列字段被删除,否则DAX公式不受表格字段变动影响。DAX公式还可以相互嵌套使用,实现许多复杂的业务逻辑。DAX公式有“写一次,永久运行”的美名。

说了这么多DAX的好处,那么DAX是否没有弱项呢?并不是,DAX不是编程语言,DAX不仅没有单元格也没有像While,Loop循环逻辑。因此当遇到非使用单元格不可或复杂循环逻辑计算的场景,仍然需要通过VBA、SQL等编程语言解决,这不属于商务智能范畴。

2

度量 V.S 计算列

DAX的计算公式可以分为两大类:度量(Measure)与计算列(Calculated Columns),下图列举了二者的主要区别。

新知达人, BI往事——Power BI 计算精要

另外,由于数量庞大的计算列会拖慢模型的性能,因此,在既可以使用计算字段又可以使用计算列的情况下,一般优先使用计算字段,如果需要使用计算列,必须清楚是什么原因不能使用度量替代,后文会有具体案例。

注意

度量为列计算、而计算列为行计算,列计算与计算列不是同一样事物。计算列公式与Excel公式都是相通的,运算方式也与Excel中的表运算相同,如图所示,图中的“@”符合表示其为Excel表(Table)。

新知达人, BI往事——Power BI 计算精要

3

行上下文 V.S 筛选上下文

什么是上下文?比如朋友说今晚吃鸡。如果此刻你们在餐厅,那你会理解他想点份鸡肉。如果此刻你们在玩手机,那你会理解他想玩荒野生存。这就是上下文的通俗比喻:根据当前不同语境下所指的不同事物。DAX中的上下文分为两种:行上下文(Row Context)和筛选上下文(Filter Context):

行上下文(Row Context)

行上下文比较容易理解,即进行“当前”行的操作,图 2.2.3是一个行上下文的例子,公式中虽然没有标明具体的行,但Excel只对“当前”进行求和运算。本质上,Excel表与Power BI中的计算列的运算原理都是依据行上下文操作的。

筛选上下文(Filter Context)

筛选上下文是指所有作用于DAX度量的筛选。彼得将其筛选逻辑分为三个筛选层次,帮助读者更理解:

1.   外部筛选:任何存在于可视化层级的上下文筛选,包括任何图表本身、视觉级、页面级和报表级筛选器。外部筛选通过外部可视化操作对度量进行筛选操作。外部筛选也称之为隐性筛选,筛选设置不依存在度量中。

2.   DAX筛选: DAX筛选的指DAX公式内部自身的筛选设置。例如,CALCULATE公式中的FILTER参数就是典型的DAX筛选。通过FILTER定义的筛选条件,可覆盖外部筛选的结果。DAX筛选也被称为显性筛选,因为筛选条件直接依存于公式自身,后文有具体介绍。

3.   关联筛选:通过表之间的关联关系进行查询传递,DAX中的USERELATIONSHIP语句就是一个很好的例子,关联方式会改变外部筛选和DAX筛选的结果。

新知达人, BI往事——Power BI 计算精要

相比Tableau,DAX中没有类似表计算和LOD这样的专门术语,但DAX专门函数可以实现相同的功能。例如,LOD中的FIXED、INCLUDE和EXCLUDE功能与DAX中的ALL、ALLEXCEPT、ALLSELECTED功能对应。示例中会有更加详尽的演示。DAX度量的核心能力之一在于通过筛选上下文转换,DAX将查询范围缩小至满足筛选条件的子集表中,在其之内完成指定的聚合计算。

4

DAX精华公式

在上本书的知识基础上,此处对DAX公式介绍进行延伸,为大家介绍几款功能强大又有趣的公式,以下公式大部分来自微软官网,读者还可通过关键字在网上查找更详细解释。

CALCULATETABLE – CALCULATE的姐妹

公式:

CALCULATETABLE(<表达式>,<筛选器1>,<筛选器2>,…)

解释:

“众所周知,CALCULATE是DAX的核心函数,可将计算列和度量进行上下上下文转换的工具。通过DAX筛选返回满足条件的子数据集,再进行聚合运算并返回结果度量。作为CALCULATE的姐妹公式CALCULATETABLE,其工作原理于CALCULATE相似,只是CALCULATETABLE返回的为表,而非单一度量。

示例:

=SUMX( CALCULATETABLE('InternetSales_USD','DateTime'[CalendarYear]=2006), [SalesAmount_USD])

上述例子中,CALCUCLATETABLE返回了一张日期为2006年的'InternetSales_USD'表中的子表。

CROSSJOIN - DAX中的笛卡尔积的表

公式:

CROSSJOIN(<表格>, <表格>[, <表格>]…)

解释:

CROSSJOIN用于返回使用表格中的所有字段的所有行的笛卡尔积的表。

示例:

CROSSJOIN( Colors, Stationery)

SUMMARIZE - 强大的依组分类摘要表

公式:

SUMMARIZE(<表格>, <分类字段>[, <分类字段>]…[, <命名列名>, <表达式>]…)

解释:

SUMMARIZE的作用在于对表进行,依据字段信息进行分类聚合的计算分析,返回的结果为信息摘要表。

示例:

SUMMARIZE(ResellerSales_USD, DateTime[CalendarYear]

, ProductCategory[ProductCategoryName]

, "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD])

)

上述例子中,SUMMARIZE依据【CalendarYear】和【ProductCategoryName】维度对字段【SalesAmount_USD】进行求和。最终返回摘要表。熟悉SQL语句的读者对此不会感到模式,SUMMARIZE的作用相当于SQL的GROUP By作用。

SUMMARIZECOLUMNS – 跨表的摘要表

公式:

SUMMARIZECOLUMNS( <分类字段> [, < 分类字段 >]…, [<筛选器表格>]…[, <命名列名>, <表达式>]…)

解释:

与SUMMARIZE相似,SUMMARIZECOLUMNS也是对数据进行摘要,但是不同的地方在于,SUMMARIZECOLUMNS所依据的维度字段可不局限于一张表。

示例:

SUMMARIZECOLUMNS ( 'Sales Territory'[Category], 'Customer' [Education], FILTER('Customer', 'Customer'[First Name] = “Alicia”) )

上述公式中,公式依据源自不同的表中的字段[Category]、[Education]维度对满足条件'Customer'[First Name] = “Alicia”的表子集进行摘要分析。

ADDCOLUMNS – 为表添加字段列

公式:

ADDCOLUMNS(<表格>, <命名列名>, <表达式>[, <命名列名>, <表达式>]…)

解释:

将表达式以计算列的形式添加到指定的表中。

示例:

ADDCOLUMNS(ProductCategory,

, "Internet Sales", SUMX(RELATEDTABLE(InternetSales_USD), InternetSales_USD[SalesAmount_USD])

, "Reseller Sales", SUMX(RELATEDTABLE(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD]))

上述公式中,将字段列【Internet Sales】和【Reseller Sales】分别添加至表ProductCategory中。

LOOKUPVALUE –DAX中的VLOOKUP

公式:

LOOKUPVALUE( <结果栏字段>, <匹配栏字段>, <匹配值>[, <匹配栏字段>, <匹配值>]…)

解释:

为满足由<匹配栏字段>和 <匹配值>指定的所有标准的行返回 <结果栏字段>中的值。值得提示的是匹配栏字段可以是多个字段选项。

公式: LOOKUPVALUE(Product[SafetyStockLevel], [ProductName], " Mountain-400-W Silver, 46")

上述例子中,通过字段[ProductName]的值Mountain-400-W Silver, 46 匹配所对应的【Product】表中的[SafetyStockLevel](安全库存)。

更多“Power BI”相关内容

更多“Power BI”相关内容

新知精选

更多新知精选