报表设计技艺

图片 22
数据库

DAX
表达式主要用于创建度量列(Measure),度量值是根据用户选择的Filter和公式,计算聚合值,DAX表达式基本上都是引用对应的函数,函数的执行有表级(Table-Level)上下文和行级(Row-Level)上下文之别;其交互行为都是通过表之间的关系实现的,用户选择的Filter,会通过关系对数据进行过滤,是PowerBI报表呈现的数据具有动态交互的特性。在我开发的PowerBI报表项目中,经常使用的DAX表达式函数,其实并不是很多,本文分享一些入门级的常用函数,附上简单的注释,希望对新手设计PowerBI报表有所帮助。

最近做了几个PowerBI报表,对PowerBI的设计有了更深的理解,对数据的塑形(sharp
data),不仅可以在Data
Source中实现,例如在TSQL查询脚本中,而且可以在PowerBI中实现,例如,向数据模型中添加自定义字段,或者在报表数据显示时,根据数据表之间的关系做数据的统计。本文主要介绍数据的塑形和UI设计的微调。

 

 

我的PowerBI开发系列的文章目录:PowerBI开发

我的PowerBI开发系列的文章目录:PowerBI开发

一,常用的操作符

在DAX表达式中,常用的操作符是:

  • 文本使用双引号作为界定符,
  • 等号是“=”号,不等号是:<>
  • 赋值使用“=”号,
  • 布尔值使用 TRUE()和FALSE()函数,
  • 空值使用BLANK()函数表示,
  • 集合使用大括号{}表示,例如,包含三个item的集合: {1,2,3}
  • 字符的连接符号是:&
  • 逻辑运算符号是:逻辑与是 &&,逻辑或是: ||

一,创建数据列

PowerBI报表的数据分为数据源(Data Source)和数据模型(Data
Model)。数据源(Data Source)的逻辑视图是Query,默认情况下,Data
Source和Query的结构(Schema)相同。用户可以通过Power Query
M语言增加自定义列修改Query的结构,Power Query M语言不会影响Data
Source,只会修改Query导出的数据。默认情况下下,PowerBI按照Query把数据加载到Data
Model中,默认情况下,Data
Model和Query的结构(Schema)相同,用户可以通过DAX表达式在Data
Model上创建计算列(Calculated Column)和度量(Measure)。

1,自定义数据列

在Data View->Query Editor中,创建自定义数据列,使用的是M
公式(M formula
,M公式语言用于创建灵活性数据查询,M公式对大小写敏感。用户添加自定义数据列,这会修改数据模型(Data
Model)的架构,PowerBI向Data Model中添加数据列。

例如,创建MonthKey列,通过使用M公式,把DateKey(格式是:yyyyMMdd)转换成MonthKey(格式是:yyyyMM)。

图片 1

在对数据进行排序时,有时不能使用DAX表达式,此时必须使用M公式,例如,对班级(Class)进行排序,使用DAX的IF函数,按照班级(Class)名称新建一个字段(Class
Ordinal),

Class Ordinal = IF(Schools[Class]="一年级",1,IF(Schools[Class]="二年级",2,3))

设置Class按照Class Ordinal排序,PowerBI会抛出错误:

图片 2

在这种情况下,必须使用M公式,在Schools Query中新增字段:

= Table.AddColumn(KustoQuery, "Class Ordinal", 
each if [Class]="一年级" then 1
    else if [Class]="二年级" then 2
    else if [Class]="三年级" then 3
    else 4) 

2,计算列(Calculated Column)

在Report
View中,计算列用于从已经加载到数据模型(Model)中的数据,根据公式计算的数据列,这跟在Data
Model中增加计算列是不同的,计算列是从数据模型中计算数据,不会修改数据模型,因此,计算列的值,只会出现在Report
View 和Data
View中。计算列使用DAX定义字段的数据值,基于加载到数据模型的数据和公式计算结果。计算列只计算一次,跟Report没有交互行为,这意味着,计算列不会根据用于在Report
Page上选择的Filter,而动态计算表达式的值。

计算列的值是基于当前数据行,进行计算,每行有一个计算列的值。举个例子,显示最近一年的日期:

图片 3

3,度量列(Measure)

度量值是在报表交互时对报表数据执行的聚合计算,度量值使用DAX定义字段的数据值,从数据模型中计算数据,不会修改数据模型,因此,度量值只会出现在Report
View 和Data
View中。度量值通常是用于聚合统计,基于用户选择的Filter,以显示不同的聚合值,由于度量值是聚合值,不是每行都有一个聚合值。举个例子,创建度量值
Answer Rate,其公式是:

Answer Rate = DISTINCTCOUNT(CloudThreads[AnsweredThreadID])/DISTINCTCOUNT(CloudThreads[ThreadID])

度量列能够引用其他表的数据列,根据数据模型中的关系,能够完成很多交互性的数据统计,非常强大,但是,也很绕、绕、绕……

二,过滤函数

过滤函数能够操作数据的上下文,以实现数据的动态计算,功能非常强大。

1,关联函数

 Related函数返回跟当前的数据行有关系的表的单个值,

RELATED(related_table[column]) 

Related函数要求当前表和关联表之间存在关系(Relationship),当前表和关联表之间存在many-to-one的关系,从关联表中返回单个值。

Related函数运行在行上下文(row context),因此,只能用于计算列的表达式。

示例:表InternetSales_USD和SalesTerritory之间存在关系,从表SalesTerritory获取SalesTerritoryCountry的值,并通过FILTER函数进行过滤,统计表InternetSales_USD中字段[SalesAmount_USD]的加和:

= SUMX(FILTER( 'InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry]) <>"United States" )  ,'InternetSales_USD'[SalesAmount_USD])  

RELATEDTABLE
用于one-to-many的关系,用于返回被过滤的所有数据行组成的表。

2,过滤函数

Filter函数,返回被过滤之后的表,是表数据的子集:

FILTER(<table>,<filter>)  

通过过滤条件,获取表的子集,过滤函数返回的表只能用于计算。过滤函数不是独立的,必须嵌入(embedded)到其他函数中作为一个表值参数。

3,计算函数

Calculate函数,在过滤器的上下文中计算表达式,返回单个值:

CALCULATE(<expression>,<filter1>,<filter2>…)  

参数expression是计算的表达式,受到filter参数的上下文的影响,如果filter参数改变数据的上下文,那么在新的上下文中计算表达式。计算函数的最大特点是能够移除过滤器,在过滤参数中,如果数据列上已经存在一个过滤器,那么移除该过滤器,应用当前的过滤器。

For each column used in a filter argument, any existing filters on that
column are removed, and the filter used in the filter argument is
applied instead.

示例:在计算比例关系时,使用计算函数的移除过滤器的功能:

=( SUM('ResellerSales_USD'[SalesAmount_USD]))  
 /CALCULATE( SUM('ResellerSales_USD'[SalesAmount_USD])  
           ,ALL('ResellerSales_USD'))  

表达式中的分母,使用计算函数,其中,第一个过滤器参数使用ALL过滤函数,使得sum表达式统计所有的数据行,这种行为重写对数据表的隐式过滤器。

4,移除过滤器

ALL函数返回表中的所有数据行,清理任意过滤器,用于对全表执行聚合运算。ALL函数主要用于计算比例关系,常用于分母中。

ALL( {<table> | <column>)

5,去重函数

Distinct
返回单列的表,包含无重复的值,也就是说,从表中移除重复值,只返回列的唯一值。

DISTINCT(<column>) 

6,过滤器的值

函数Values返回被过滤的唯一值,在同一个表中的其他Column被过滤,那么返回被过滤的当前Column的唯一值。Values函数和Distinct函数很相似,唯一的不同是Values函数会返回Unknown,这是因为关联的Table中包含部匹配的数据行,和Left
Join的右表中包含NULL值很相似。

VALUES(<TableNameOrColumnName>)  

When you use the VALUES function in a context that has been filtered,
 the unique values returned by VALUES are affected by the filter.

结合CONCATENATEX函数,能够把所有过滤器的值连接成字符串。注意,DAX表达式使用
& 进行字符串的连接。

7,探测直接过滤(filtered directly)

如果指定的Column被直接过滤(filtered
directly)
,函数返回True;当同一个Table中的其他Column被过滤,切片器(Slicer)中被关联的Column,默认设置是全部直接过滤,函数返回True;如果Column上没有直接的过滤,或者同一个Table中的其他Column被过滤,或者被有关系的Table过滤,函数返回False

ISFILTERED(<columnName>)  

直接过滤器的数值通过函数Filters返回:

FILTERS(<columnName>)  

8,探测关联过滤

如果同一个Table中的其他Column被过滤,或者被有关系的Table过滤,函数返回True

ISCROSSFILTERED(<columnName>)  

9,保持过滤器

函数 KeepFilters 的作用是保持过滤器,用于计算函数(CALCULATE 和
CALCULATETABLE)中。默认情况下,计算函数的过滤器参数(filter
argument)会对过滤表数据,当在相同的字段上设置过滤器参数(filter
argument)时,该参数会替换已经存在的过滤器;当相同的字段上没有过滤器参数(filter
argument)时,已经存在的过滤器不受影响。函数KeepFilters会改变计算函数的行为,当在计算函数使用函数KeepFilters时,表的上下文是过滤器参数(filter
argument)和已经存在的过滤器的交集,也就是说,表的上下文同时受到已经存在的过滤器和计算函数的过滤器参数(filter
argument)的影响。计算函数替换已经存在的过滤器,而函数
KeepFilters会添加已经存在的过滤,求交集。

KEEPFILTERS(<expression>) 

二,报表可视化控件的设计

在显示报表数据时,PowerBI提供多种方式,能够对数据的显示进行微调,使数据显示的效果更合理。

1,层次结构( Hierarchy)

PowerBI 支持在Report
View中创建字段的层次结构(Hierarchy),在同一个Query中,拖动一个字段到另一个字段下,PowerBI自动创建一个层次结构,并以父层次字段的名称命名,例如:

图片 4

PowerBI内置一个可视化控件HierarchySlicer,能够显示字段的层次结构,在Fields中设置一个层次结构:

图片 5

控件显示的结构是一个树形结构,点击“三角”,能够展开,以树形结构显示子级别的数据,HierarchySlicer支持逐层展开,如下图所示:

 图片 6

2,在Table控件中显示超链接(HyperLink)

在Product View中,选中Query的某一个字段,如下图,选中字段 ProfileLink,

图片 7

 在Modeling菜单下,该字段的Data Type为Text,设置文本的Data
Category为Web URL:

图片 8

Table可视化控件的视图属性中,设置Values的URL Icon属性为On,

 图片 9

在Table控件中,Web
URL的显示如下所示,点击LInk,能够直接打开浏览器,跳转到指定的网址:

图片 10

3,数字的小数位的控制

可以在PowerBI中设置字段的数据类型,选中一个字段,打开Modeling菜单,

图片 11

 选择字段的数据类型,Format为 Decimal number,选择货币符号($),
显示百分比(%),千位分隔符(,),或小数位数(0-N),这里设置
显示的小数位数是1,只显示一位小数。

 图片 12

显示的效果如下,Score 保留一位小数点,并使用千位分割符号:

 图片 13

4,字段值的过滤

可视化(Visual)控件只显示排名靠前的TOP
N行数据,这可以通过为字段设置过滤条件来实现,在FIELDS列表中,点击字段后面的”…“
,添加过滤条件(Add filter),按照特定字段的值(By
value),过滤当前字段的值:

图片 14

例如,在一个Table visual中,把Filter Type设置为Top N,把Show
items设置为Top 20,把By Value设置为度量值 Contribution
Score,PowerBI按照度量值降序排列,只显示排名前20的数据行:

图片 15

PowerBI 支持两种显示的项目(Show
item)类型:Top和Bottom,PowerBI按照排序值(by value)降序排名。

字段的过滤类型,共有三种,如下图所示,可以根据需要,创建适合业务逻辑的过滤器:

图片 16

三,统计函数

统计函数是最强力的函数,同时也是最复杂的函数,为数据的分析,提供非常强力的工具,同时,在使用统计函数时,必须考虑到数据模型,表之间关系,数据重复等因素,一般都会搭配过滤函数实现数据的提取和分析。

1,求和函数

从表中计算每一个行的加和,只有数值会被加和,忽略空值,date,逻辑值或文本值:

SUMX(<table>, <expression>)  

 示例,第一个参数是过滤器返回的表值,计算[Freight]的加和:

=SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])  

可以把SUMX函数,转换为CALCULATE函数:

=CALCULATE( SUM(InternetSales[Freight]), FILTER(InternetSales, InternetSales[SalesTerritoryID]=5))  

2,计数函数

计算函数分为:COUNTX和COUNTAX,COUNTX只统计数值,忽略空值(blank),date,逻辑值或文本值;COUNTAX函数统计非空值(blank),包含数值,date,逻辑值或文本值。

COUNTX(<table>,<expression>)  
COUNTAX(<table>,<expression>)  

例外:如果列中包含表达式,而表达式的结果是空值,但是,COUNTAX/COUNTX函数把包含公式的列值作为非空(nonblank)看待,在这种情况下,计数函数会增加计数值。

如果COUNTAX函数没有数据列做计数,返回blank;如果COUNTAX函数聚合的数据列都是blank,返回0。

3,唯一值计数

统计列的唯一值计数,参数是表列,允许是任意数据类型,当找不到任何数据行时,返回BLANK,否则,统计唯一值的数量。

DISTINCTCOUNT(<column>)

4,分组聚合函数

DAX
中功能最强大的函数,对相互关联的Table按照特定的字段,分组聚合,由于分组列是唯一的,通过SUMMARIZE函数,可以获得多列的唯一值。

SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…) 

示例:对数据表 ResellerSales_USD ,按照字段 DateTime[CalendarYear]
和 ProductCategory[ProductCategoryName]分组,计算 SUM(ResellerSales_USD[SalesAmount_USD])和 SUM(ResellerSales_USD[DiscountAmount])

SUMMARIZE(ResellerSales_USD  
      , DateTime[CalendarYear]  
      , ProductCategory[ProductCategoryName]  
      , "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD])  
      , "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])  
      ) 

分组聚合函数,能够利用关系,引用相关表的字段,也就是说,SUMMARIZE函数能够对有关系的表执行连接(join)运算,计算笛卡尔乘积,对Join的结果集执行分组聚合,例如:

summarize
 (
     'Internet Sales',
     'Product'[Product Name],
     'Date'[Calendar Year],
     "Total Sales Amount", sum('Internet Sales'[Sales Amount])
 )

从数据表Date中获取字段Calendar Year的前提是,数据表Date和Internet
Sales之间存在关系,从数据表Product中获取字段Product
Name的前提是:数据表Product和Internet Sales之间存在关系。

分组聚合函数用于创建新表,在Modeling菜单中,通过“New
Table”从DAX表达式中创建新的Table:

图片 17

参考文档:SUMMARIZE – groupping in data models (DAX – Power Pivot,
Power
BI)

三,根据当前的数据导出数据

在数据建模时,需要创建两个表之间的关系,PowerBI要求跟关系相关的两个数据列,必须有一列是唯一值,不允许存在重复值。在DimCalendar表中,存在DateKey列,该列是以int表示的日期类型,例如,2017年10月1日,用DateKey表示是20171001,从DimCalendar表中导出MonthKey,公式是MonthKey=DateKey/100。需要根据MonthKey列新建一个Query,做法是:

1,添加新的查询(Query)

在查询编辑器(Query Editor)中,选中列
MonthKey,右击弹出快捷菜单,选择“Add as New Query”,从当前列中新建查询

图片 18

2,把List转换成Table

此时,新建的Query命名为MonthKey,是一个List类型,需要把List转换成Table,选中该List,打开主菜单Transform,点击“To
table Convert”,把List转换成Table

图片 19

从一个List创建Table,PowerBI需要用户选择界定符,该List没有任何界定符,选择None:

图片 20

3,修改数据

新表的数据列名是Column1,右击弹出快捷菜单,点击“Rename”,把该列重命名为MonthKey,点击“Change
Type”把该列的数据类型修改为“Whole Number”,点击“Remove
Duplicates”,删除重复的数据值

图片 21

4,查看导出数据表的实现步骤

在右侧的查询设置(Query
Settings)中查看实现的步骤,选择某一个Step,点击Step 名称前的“×”,能够把该Step删除。

图片 22

 

 

附:鉴于本人接触PowerBI的时间不长,cover的内容有限,后续有新的设计技巧,我会持续更新

 

参考文档:

Tutorial: Create calculated columns in Power BI
Desktop

Tutorial: Create your own measures in Power BI
Desktop

Power Query M
Reference

Hyperlinks in
tables

Measures in Power BI
Desktop

Calculated columns in Power BI
Desktop

四,文本函数

在DAX表达式中,字符串使用双引号界定。

1,格式函数

按照指定的格式把值转换成文本

FORMAT(<value>, <format_string>)  

2,空值

在DAX中,空值(Blank)
和数据库的NULL值是相同的,通过函数ISBLANK(value)判断当前的字段值是否是空值。

BLANK()
ISBLANK(<value>) 

3,查找函数

在一段文本中查找字符串时,从左向右读取文本,查找函数返回第一次匹配的字符的序号,序号从1开始,依次递增。search函数不区分大小写,而find函数区分大小写。

FIND(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]]) 
SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]]) 

参数 NotFoundValue
是可选的,当查找不到匹配的子串时,返回该参数的值,一般设置为0,-1或BLANK()。

如果不设置该参数,而查找函数查找不到匹配的子串时,函数返回错误。可以通过IFERROR函数处理错误,例如:

= IFERROR(SEARCH("-",[PostalCode]),-1)  

5,拼接函数

把表中的数据按照指定的分隔符拼接成字符串

CONCATENATEX(<table>, <expression>, [delimiter])  

示例,Employees表中包含[FirstName] 和
[LastName]两列,把这两列拼接成一个字符串:

CONCATENATEX(Employees, [FirstName] & “ “ & [LastName], “,”)

五,逻辑函数

1,逻辑判断函数

检查逻辑条件是否满足,如果满足,返回value_if_true,如果不满足,返回value_if_false。

IF(logical_test>,<value_if_true>, <value_if_false>)  

等于使用“=”表示,逻辑与使用“&&”表示,逻辑或使用“||”表示,而逻辑非,通常使用NOT()函数来实现:

NOT(<logical>) 

 2,布尔值函数

TRUE()  
FALSE() 

通常用于表示数据库的bit类型的值

3,错误函数

如果表达式返回错误,返回value_if_error;如果表达式不返回错误,返回表达式的值。

IFERROR(expression, value_if_error) 

错误函数等价于:

IFERROR(A,B) := IF(ISERROR(A), B, A)

4,包含逻辑

表(Table)表达式是由大括号构成的集合:{value1,value2,,vlaueN}

IN操作符的用法是:

<scalarExpr> IN <tableExpr> 

包含行函数的用法是:

CONTAINSROW(<tableExpr>, <scalarExpr>[, <scalarExpr>, …]) 

示例,以下两个表达式是等价的:

[Color] IN { "Red", "Yellow", "Blue" }
CONTAINSROW({ "Red", "Yellow", "Blue" }, [Color])

六,关系

使用DAX表达式创建两个查询之间的关系。

1,为指定的关系指定查询时(query-time)的方向(cross-filtering
direction)

函数CrossFilter使用已经存在的关系,重写的关系设置只在查询时有效。

CROSSFILTER(<columnName1>, <columnName2>, <direction>)  

2,使用关系

函数UseRelationship只能使用已经存在的关系,通过关系两个端点(endpoint)来指定关系,关系的状态是不重要的,通常,使用该函数的目的是在Calculate函数中使用不活跃(Inactive)的关系。

USERELATIONSHIP(<columnName1>,<columnName2>) 

七,自然连接(NaturalJoin)

DAX表达式支持自然连接操作,分为:自然内连接和自然左外连接,函数的两个参数分别是表表达式。自然连接要求两个表中必须有同名列,并且公共列的数据类型必须相同,按照公共列做连接操作。

NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>) 
NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>) 

自然连接函数的两个参数是表表达式,可以是使用函数SUMMARIZE()函数派生的表,例如:

AttendeeCohortShare = NATURALINNERJOIN(
SUMMARIZE(AttendeeCohort,AttendeeCohort[Cohort_Last],"#LastObjectIDs",DISTINCTCOUNT(AttendeeCohort[ObjectId]),"Cohort",MAX(AttendeeCohort[Cohort_Last])) 
,SUMMARIZE(AttendeeCohort,AttendeeCohort[Cohort_Current],"#CurrentObjectIDs",DISTINCTCOUNT(AttendeeCohort[ObjectId]),"Cohort",MAX(AttendeeCohort[Cohort_Current]))) 

 

参考文档:

DAX basics in Power BI
Desktop

Data Analysis Expressions (DAX)
Reference

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图