技术笔记:PowerBI之DAX

简介: 技术笔记:PowerBI之DAX

计算表、计算字段和度量


计算表


返回值是一个二维表,比如下面返回一个只有一个时间列的表。时间是连续的,结束于6月。会扫描模型里的最大时间和最小时间,然后涵盖掉。


Due Date = CALENDARAUTO(6)


计算列


单行内计算,非聚合。和我们的非聚合型计算字段类似。


Due Fiscal Year =


"FY"


& YEAR('Due Date'【Due Date】)


+ IF(


MONTH('Due Date'【Due Date】) > 6,


1


)


Due Month =


FORMAT('Due Date'【Due Date】, "yyyy mmm")


注意,单行内的计算,是不能跨表的,这点和我们是一样的。但是也允许通过RELATED 、 RELATEDTABLE和LOOKUPVALUE函数引用其他关联表的字段,比如下面这样,跨sales表和product两张表的字段:


Discount Amount =


(


Sales【Order Quantity】


RELATED('Product'【List Price】)


) - Sales【Sales Amount】


度量


聚合后的字段称为度量。度量分为隐式度量和显式度量,用DAX创建的叫显示度量,在报表里展示的数值,叫隐式度量。显式度量是聚合的数值,类似于我们的聚合型计算字段、高级计算。尤其要提到的是,dax中,所有的高级计算,都是一个单独的度量,在建模时就创建好的。


在不麻烦的情况下,PowerBI推荐建模者在建模的时候就创建好显式度量,并且把隐式度量隐藏掉,防止做报表的人做出错误的聚合度量。


Revenue = SUM(Sales【Sales Amount】)


Context


Power BI最最最重要的概念,可以说,Power BI的一切计算,都是围绕Context进行的。


记住,在DAX中,是没有group by的概念的。pbi在计算一个单元格或者表达时的值时,总是根据这个单元格或表达时所处的上下文来执行计算的。


Row Context


行上下文的意思就是当前行,一般在非聚合型计算字段上使用这个概念,只在行内计算。或者在迭代函数上使用这个概念,代表内外循环的两个“当前行”。


需要注意的是,行上下文默认是不跨关联表的。比如你有Product 和Sales两张表通过order_id关联在一起,你在Product表上新建一个totalCost=sum(Sales【shipping_cost】)计算字段,那么所有行上的totalCost都是同一个值:shipping_cost总计值。它不会根据Product表上的维度做分组聚合。如果需要的话,必须通过RELATED 之类的函数引用关联表字段,或者使用CALCULATE函数,让Row Context转为Filter Context(下面会讲)。


Query Context


查询上下文就是在报表查询时确定一个单元格的上下文,它由单元格上的行头列头、报表上的各类过滤器、切片器共同决定,然后单元格上的表达式(可能是一个最简单的聚合)就是在这些上下文中执行的。


Filter Context


这是所有context中最灵活最强大的。可以先把它看成是对数据的过滤条件,比如制作报表的时候,filter context就是添加的各种过滤器比如切片器、查询控件,更为强大的是,他可以在创建度量的时候,直接写到表达式中,影响表达式计算的范围。更重要的是,你可以通过各种函数去改变动态修改filter context。由于filter context的优先级比其他context都要高,所以它可以改变聚合、计算的结果。


context转移


指的是将Row Context 和Query Context转为Filter Context,可以直白的理解为图表上的单元格所在行的维度值(row context)变成了(转移成)该单元格上表达式执行时的过滤条件(filter context)。发生了转移之后,就能够通过修改函数去修改表达式的context了,这也是dax能够实现各种高级计算的秘诀。


写好dax的关键是掌握context


写好dax,尤其是复杂的dax,关键是在理解各种dax概念(context)的基础上,用好各种聚合函数和filter context修改函数。


1.理解filter context 的概念,以及它的作用机制


2.掌握何时以及用什么方法去改变filter context以获得正确的表达式结果


3.将各种表达式组合成更复杂的dax语句。


迭代函数


所有的聚合函数基本上都有一个迭代函数版本,函数名是聚合函名加上X后缀,比如 SUMX, COUNTX, MINX, MAXX 等等迭代函数是迭代一个表的所有行,他的声明如下:


SUMX( table , expression)


第一个入参是一个表或者能够返回一个表的表达式,第二个入参是一个表达式。迭代函数的做法是迭代table的每一行,并且在每一行的row context下执行expression,然后对每一行结果做入参执行聚合计算,得到一个出参。如果第一个入参是返回表的表达式,这个表达式是在当前filter context中执行的。


实际上,普通的聚合函数其实是迭代聚合的语法糖,在pbi中,简单聚合都会变成迭代聚合函数,比如sum聚合


Revenue = SUM(Sales【Sales Amount】)


其实是下面这个SUMX迭代函数的语法糖,它迭代了Sales表。


Revenue =


SUMX(


Sales,


Sales【Sales Amount】


)


迭代函数的作用


创建复杂聚合的计算字段


Discount =


SUMX(


Sales,


Sales【Order Quantity】


(


RELATED('Product'【List Price】) - Sales【Unit Price】


)


)


这个表达式迭代了Sales表,并且以Sales表每一行的row context去计算单行的折扣量,最后汇总成总的折扣。需要注意的是,这里用了一个RELATED函数,这是因为pbi的row context默认是不能跨关联表的,如果不加这个函数,Product'【List Price】的值是不会受到Sales表当前行的维值约束的。


实现高阶聚合


比如想实现每类商品的平均利润,我们可能这样写


Revenue Avg =


AVERAGEX(


Sales,


Sales【Order Quantity】 Sales【Unit Price】 (1 - Sales【Unit Price Discount Pct】)


)


但这其实不对,他计算的是每一行的的平均利润,但是同一类商品可能会有很多很多行。我们真正想实现的,其实是先计算每一类商品的利润,然后相加起来,再除以商品种类。


Revenue Avg Order =


AVERAGEX(


VALUES('Product'【Product Type】),


【Revenue】


)


这里的VALUES('Product'【Product Type】)返回了所有的产品类型作为一个表,这个表只有一列,那就是产品类型,然后AVERAGEX函数迭代了这个表,在每一行(每种产品类型)以改行的row context去计算Revenue,最后算平均值。这里我们引用了Revenue度量,如果我们不是用引用,直接写成下面这样:


Revenue Avg Order =//代码效果参考:http://hnjlyzjd.com/hw/wz_24723.html


AVERAGEX(


VALUES('Product'【Product Type】),


SUM(Sales【Sales Amount】)


)


结果也是不一样的,因为row context是不会跨关联表的,所以产品类型不会影响SUM(Sales【Sales Amount】),那么每一行的SUM(Sales【Sales Amount】)结果都是一样的。如果要让产品类型作用与SUM(Sales【Sales Amount】),则需要让row context转移成SUM(Sales【Sales Amount】)表达式的filter context


Revenue Avg Order =


AVERAGEX(


VALUES('Product'【Product Type】),


CALCULATE(SUM(Sales【Sales Amount】))


)


calculate函数


calculate函数的用于修改filter context,实现强大的dax计算,他的基本格式如下


CALCULATE([/span>expressionfilter1filter2

表达式部分必须返回一个具体的值(数值、文本、时间等)。


筛选器部分,每个筛选器必须返回bool值或者一个数据表,各个filter之间是and关系。


bool筛选器


bool筛选器比较简单,但是它有以下限制


每个filter只能引用一个列


不能引用度量


不能用聚合函数


举几个引用bool筛选器的例子:


Revenue Red = //代码效果参考:http://hnjlyzjd.com/hw/wz_24721.html

CALCULATE(【Revenue】, 'Product'【Color】 = "Red")

Revenue Red or Blue = CALCULATE(【Revenue】, 'Product'【Color】 IN {"Red", "Blue"})


Revenue Expensive Products = CALCULATE(【Revenue】, 'Product'【List Price】

表筛选器


表筛选器是一个表,可以直接用一个数据表充当表筛选器,但更常见的是用一个函数去创建表筛选器,最常用的函数就是FILTER函数。这是一个迭代器函数,有两个入参:表和过滤条件,申明如下


FILTER(,)


FILTER函数会返回一个表,结构和传入的表一模一样,这个返回表的数据是经过filter表达式运算后为true的那些行。


比如下面这样,FILTER函数的返回结果就是Product表中所有满足售价大余成本2倍的行。


Revenue High Margin Products =


CALCULATE(


【Revenue】,


FILTER(


'Product',


'Product'【List Price】

)


)


把这个表筛选器用于CALCULATE函数,Revenue的计算就是在筛选出来的Product行数据组成的Filter Context下计算的。


其实所有的过滤器最终都会化成表过滤器,即使是bool过滤器,在pbi的内部,也会转为表过滤器,bool过滤器可以看做是一种语法糖,只是为了便于使用。比如上面的bool过滤器例子,其实会转为一下表过滤器:


Revenue Red =


CALCULATE(


【Revenue】,


FILTER(


'Product',


'Product'【Color】 = "Red"


)


)


筛选器表达式与Filter Context的关系


一共有两种情况:


如果Filter Context已经有了某一列(或者某个表)的约束条件,那么筛选器会直接覆盖掉这个列(或者表)的条件。


如果Filter Context没有了某一列(或者某个表)的约束条件,那么筛选器会把这列(或者表)的筛选条件加上。


比如上面写的Revenue Red度量,只计算红色的收入,那如果拖入颜色和Revenue Red,可以看到所有行的Revenue Red都是一样的,这是在Filter Context中因为发生了Color列上的条件覆盖,筛选器覆盖了行维度。


筛选器修改函数


除了写死筛选器求覆盖Filter Context中的条件,还可以用修改函数去修改Filter Context。


REMOVEFILTERS 可以删除Filter Context中的过滤条件,它可以从一个或多个列或从单个表的所有列中删除筛选器。在算总小计的时候,这个修改器函数很有用


KEEPFILTERS 可以在保留已有筛选条件的基础上增加新的筛选条件,比如把它用在Revenue Red中


Revenue Red =


CALCULATE(


【Revenue】,


KEEPFILTERS('Product'【Color】 = "Red")


)


那么其他颜色行的数据就会为空,只有Red行有数据


原因是行维度的值作为Filter Context的条件和表达式中的条件是and关系,所以只有Color='Red' and Color='Red'为true,其他的行都是false。


USERELATIONSHIP是个非常有特色的函数,因为它体现了一个Power BI数据建模中的一个隐藏限制:表之间的关联关系只能是单个字段的关联,但是允许有多组关联关系(都是单字段关联),但只有其中一组为活跃关联,其他为非活跃关联。 如果要引用非活跃关联关系,那么就必须用USERELATIONSHIP函数指定一个非活跃关联。


Revenue Shipped =


CALCULATE (


【Revenue】,


USERELATIONSHIP('Date'【DateKey】, Sales【ShipDateKey】)


)


CROSSFILTER 能够影响关联关系的影响方向(维表到事实表,事实表到维表),甚至删除关联关系,更强大。


快捷计算


为了减轻写dax的难度,pbi提供了快捷方式创建度量,分成以下几类


分类聚合


分类聚合其实就是聚合的聚合,有下面这些


过滤


时间智能


前提:


必须要有时间表,时间表是一个连续的时间字段,且数据跨度满1年。


限制:


不支持自定义财年,否则需要自己写复杂的过滤函数。


同环比计算


Revenue YoY % =


VAR RevenuePriorYear = CALCULATE(【Revenue】, SAMEPERIODLASTYEAR('Date'【Date】))


RETURN


DIVIDE(


【Revenue】 - RevenuePriorYear,


RevenuePriorYear


)


xtd计算


Revenue YTD =


TOTALYTD(【Revenue】, 'Date'【Date】, "6-30")


计算拉新数


New Customers =


VAR CustomersLTD =


CALCULATE(


DISTINCTCOUNT(Sales【CustomerKey】),


DATESBETWEEN(


'Date'【Date】,


BLANK(),


MAX('Date'【Date】)


),


'Sales Order'【Channel】 = "Internet"


)


VAR CustomersPrior =


CALCULATE(


DISTINCTCOUNT(Sales【CustomerKey】),


DATESBETWEEN(


'Date'【Date】,


BLANK(),


MIN('Date'【Date】) - 1


),


'Sales Order'【Channel】 = "Internet"


)


RETURN


CustomersLTD - CustomersPrior


快照计算(库存等等,其实就是最后一天聚合)


Stock on Hand =


CALCULATE(


SUM(Inventory【UnitsBalance】),


LASTDATE('Date'【Date】)


)


度量嵌套


在定义一个度量的DAX中,可以引用其他已经存在的度量,还能创建变量,这就使得DAX非常复杂了,像一门编程语言。


和LOD区别


DAX是基于context的,LOD是基于聚合的。举个例子,计算每个商品类型的平均售价。


DAX


在DAX中,可以直接创建 price average per product_type这样一个度量。


price average per product_type =


AVERAGEX(


KEEPFILTERS(VALUES('quickbi_test company_sales_record'【product_type】)),


CALCULATE(SUM('quickbi_test company_sales_record'【price】))


)


如果不想编辑,还可以通过快捷按钮方式直接创建常见的度量。


将度量拖到交叉表中,这时候PowerBI会根据row context,在表格上的每行都做一次品类平均值的计算。比如我们把area维度和度量price average per product_type拖入交叉表中,这时候row context是区域的值,比如第一行,就是在area=东北这个条件下,计算商品类型的售价均值。注意,度量的聚合方式是不可以更改的。


LOD


再看在LOD中,我们没有办法直接创建一个 price average per product_type度量,但是我们可以创建按照product_type聚合的计算字段,然后在使用该计算字段时,选择average聚合。


首先,创建 扩展 product_type聚合的计算字段sum price per product_type:


{INCLUDE 【product_type】:SUM(【price】)}


然后,把这个计算字段拖到交叉表中,并且选择 average聚合:


这里的计算逻辑就是:


首先,根据area和product_type两个维度做sum聚合。


然后,因为聚合的维度比图表上展示的多(也就是LOD聚合粒度比图表聚合粒度更细),这样每行下面会有多个聚合值,所以需要进行二次聚合,这时候根据所选的聚合方式average再聚合一次。


最终结果就是在每个区域下计算商品类型的售价均值,和DAX异曲同工。


可以看到,结果和在PowerBI中用dax创建的price average per product_type是一样的。

相关文章
|
数据采集 SQL 数据可视化
大数据可视化技巧:借助PowerBI提升数据故事讲述力
【4月更文挑战第8天】Power BI助力大数据可视化,支持多种数据源连接,如SQL Server、Excel,提供数据清洗与转换功能。通过选择合适图表类型、运用颜色和大小强化表达,创建交互式仪表板。讲述数据故事时,注重故事主线设计,利用叙事技巧引导观众,并添加文本说明。分享已完成报告,提升数据驱动决策能力。动手实践,体验Power BI的强大与易用。
463 0
|
8月前
|
人工智能 自然语言处理 前端开发
VSCode AI提效工具,通义灵码前端开发体验
通义灵码2.0是一款强大的VS Code插件,安装简便,图标易记。其亮点包括接入deepseek-v3/r1模型,支持智能问答、AI编程、代码优化及贴图提问;多语言和编辑器支持;个性化使用满足不同需求。个人版完全免费,节省12%开发时间。对比1.0版本,2.0在功能实现上更加完善,尤其在前端项目中表现出色,根据需求描述生成完整项目结构和详细代码,极大提升开发效率。
545 0
|
6月前
|
SQL 数据可视化 关系型数据库
Quick BI 测评报告
Quick BI是阿里云推出的零代码可视化分析工具,适合个人开发者与小微团队使用。其核心优势在于轻量化启动(免费试用+按量付费)、多源接入(MySQL、MongoDB等)及敏捷分析能力(拖拽式仪表板)。实测显示,它支持智能CSV解析、语法高亮SQL编辑器和25+基础图表类型,具备图表联动交互功能。尽管缺少3D地图和自定义JS插件支持,但凭借低学习成本、OpenAPI扩展性以及移动端报表查看功能,Quick BI在个人项目展示、团队协作和轻量级数据分析中表现出色。不过,复杂计算需依赖SQL,移动端编辑和PDF导出存在局限性。
236 3
|
SQL 分布式计算 数据可视化
数据分析案例-数据分析师岗位招聘信息可视化
数据分析案例-数据分析师岗位招聘信息可视化
363 0
|
11月前
|
SQL 监控 数据可视化
数据可视化:Power BI在商业智能中的强大作用
【10月更文挑战第28天】在信息爆炸的时代,数据成为企业决策的重要依据。Power BI作为微软开发的强大数据可视化工具,集数据整合、报表制作和数据可视化于一体,通过简单拖放操作即可生成交互式图表和报表,提高决策效率,实现数据整合与实时监控,助力企业挖掘数据价值,提升竞争力。
329 3
|
数据采集 存储 数据可视化
SharePoint List
【6月更文挑战第10天】
234 1
|
12月前
|
监控 数据可视化 数据挖掘
一文带你了解如何通过数据可视化与仪表盘提升工作效率?
在数据驱动的时代,快速、准确地从海量信息中提取有用部分成为核心挑战。**数据可视化**和**仪表盘**是解决这一问题的有效工具。它们将复杂数据转化为直观图表,帮助用户快速掌握关键指标、跟踪进展,并做出更好决策。本文将介绍数据可视化的常见方法、仪表盘的作用,并通过经典案例展示这些工具的实际应用。
249 0
|
存储 人工智能 自然语言处理
知识库优化增强,支持多种数据类型、多种检索策略、召回测试 | Botnow上新
Botnow近期对其知识库功能进行了全面升级,显著提升了数据处理能力、检索效率及准确性。新版本支持多样化的数据格式,包括PDF、Word、TXT、Excel和CSV等文件,无需额外转换即可直接导入,极大地丰富了知识来源。此外,还新增了细致的文本分片管理和编辑功能,以及表格数据的结构化处理,使知识管理更为精细化。 同时,平台提供了多种检索策略,包括混合检索、语义检索和全文检索等,可根据具体需求灵活选择,有效解决了大模型幻觉问题,增强了专业领域的知识覆盖,从而显著提高了回复的准确性。这些改进广泛适用于客服咨询、知识问答等多种应用场景,极大提升了用户体验和交互质量。
1039 4
|
机器学习/深度学习 人工智能 安全
Azure Databricks实战:在云上轻松进行大数据分析与AI开发
【4月更文挑战第8天】Databricks在大数据分析和AI开发中表现出色,简化流程并提高效率。文中列举了三个应用场景:数据湖分析、实时流处理和AI机器学习,并阐述了Databricks的一体化平台、云原生弹性及企业级安全优势。博主认为,Databricks提升了研发效能,无缝集成Azure生态,并具有持续创新潜力,是应对大数据挑战和加速AI创新的理想工具。
1285 1