之前我们详细介绍了Excel 的 CUBE类函数(参见这篇)。可以说这是我们充分返回数据模型和超级透视表的重要途径。 但是CUBE类函数只能通过CUBEVALUE函数从数据模型中返回一个度量值! 这就导致在制作报表时,只能自己写公式逐个返回所需要的结果。 当然,这里有一个变通的方式,那就是我们可以使用Excel函数式编程,也可以用一个公式完成整个报表。 不过这么做没有办法充分发挥数据模型的强大能力。因为设计统计分析的有些计算,使用DAX函数更加方便。 比如,假设我们有一个数据模型(其实我们用的还是之前介绍CUBE类函数时使用的模型), 我们可以使用DAX公式轻松生成一个统计报表, 在Excel中要做到这个报表,可以使用两种方法:
这两种方法都不太完美,尤其对于某些复杂报表来说。 但是明明在数据模型中,我们可以用简单的DAX公式得到这个报表:
用CUBE函数得到报表 很多人可能会问,能否使用上面的公式创建一个度量值呢? 这是包括我在内的很多人的期望,可惜不行。因为度量值只能是一个值,绝不可能是一个表格。 之前需要很麻烦的完成这类需求,导致一般情况下会回避这种问题。 现在就简单了。 方法就是使用微软去年推出的一个DAX函数: TOCSV
这个函数的作用是返回一个用逗号分割的表格(就跟CSV文件一样)。 在Excel中打开一个CSV文件时,就像一个普通的表格一样。但是如果用文本文件打开,就会发现这个文件内容其实是一个字符串,中间用逗号或者其他符号隔开。 TOCSV函数的作用就是将一个表格:Table变成一个用Delimiter隔开的字符串,比如, 1,2,3 就代表三列,第一列值为1,第二列值为2,第三列值为3。 另外的两个参数: MaxRows代表转换后的字符串包括表格数据中的多少行,缺省情况下是10行。 IncludeHeaders代表转换后的字符串是否包含标题行,缺省情况下是包含。 另外,CSV数据表示行分隔符的是换行符。 现在,只要稍作修改,上面的DAX公式就可以生成一个返回CSV格式表格的度量值了:
我们将原来的表格返回值作为一个中间变量,statByRegionTbl,然后使用TOCSV处理后作为一个字符串返回: 下面就在Excel中返回这个结果报表。 首先,采用之前这篇文章介绍的方法,用Excel连接Power BI Desktop数据模型。 然后使用CUBEVALUE函数返回这个度量值(根本不需要CUBESET函数):
得到结果, 显然,这不是我们想要的。没关系,再嵌套一个TEXTSPLIT函数即可:
其中,”,"是列分隔符,CHAR(10) 是行分隔符。 结果如下: 配合数据模型切片器,效果更好。 视频已在视频号和B站同时更新 详情咨询客服(底部菜单-知识库-客服) Excel+Power Query+Power Pivot+Power BI 自定义函数 底部菜单:知识库->自定义函数 面授培训 底部菜单:培训学习->面授培训 Excel企业应用 底部菜单:企业应用 |
|