【powerBI_001】DAX函数之RELATED和RELATEDTABLE用法

最近在研究powerBI
发现了RELATED和RELATEDTABLE这俩函数,虽然在SQL里解决完再上传到powerBI里更省事,但是还是得感叹一下微软底层设计的强大!

假设有两张表,一张销售明细表,一张商品信息表。

展示一下源数据:

  • 销售明细表在这里插入图片描述
  • 商品信息表
    在这里插入图片描述
RELATED

用于多端找一端,可以理解为excel中的vlookup

显而易见,销售明细表是多端,商品信息表是一端。

如果想要在销售明细表中新增一列销售金额,可以利用RELATED函数
首先先建一个度量值

总销售数量 = SUM('销售明细'[销售数量] )

然后在销售明细表中敲入公式:

销售金额 = [总销售数量]*RELATED('商品信息'[单价]) 

Tips: 度量值是自带筛选功能的
在这里插入图片描述

RELATEDTABLE

用于一端找多端,可以理解在多端中汇总完了再Vlookup到一段

如果想要在商品信息表中新增一列已售月数,可以利用RELATEDTABLE函数

Tips: RELATEDTABLE返回的是一张表

已售月份 = COUNTROWS(RELATEDTABLE('销售明细'))

在这里插入图片描述

写在最后,以上分享了两个DAX函数用法,以上操作仅为演示函数使用,不建议在源数据中增加新列。
SQL聚合完了直接上传或者用度量值它不香嘛!
度量值不占内存,新增列占内存!而且在商品信息表中这样的信息表中汇总数据真的是大可不必!

  • 0
    点赞
  • 8
    收藏
  • 打赏
    打赏
  • 2
    评论
<p><span style="color: #000000;">本课程是PowerBI系列课程DAX函数专题讲解,包含以下内容</span></p> <p> </p> <p class="MsoNormal" style="margin-left: 18.0pt; text-indent: -18.0pt; mso-list: l0 level1 lfo1;"><span style="color: #000000;"><!-- [if !supportLists]--><strong><span lang="EN-US" style="font-family: 宋体;"><span style="mso-list: Ignore;">1.<span style="font: 7.0pt 'Times New Roman';">  </span></span></span></strong><!--[endif]--><strong><span lang="EN-US" style="font-family: 宋体;">DAX</span></strong><strong><span style="font-family: 宋体;">函数基础知识</span></strong></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">什么是<span lang="EN-US">DAX</span>函数</span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">数学函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">ABS</span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">DIVIDE</span>、<span lang="EN-US">MOD</span>、<span lang="EN-US">RAND</span>、<span lang="EN-US">ROUND</span>、<span lang="EN-US">FIXED</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">日期时间函数: </span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">CALENDAR</span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">CALENDARAUTO</span>、<span lang="EN-US">MONTH</span>、<span lang="EN-US">YEAR</span>、<span lang="EN-US">DATE</span>、<span lang="EN-US">DT</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">信息函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">USERNAME</span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">USERPRINCIPALNAME</span>、<span lang="EN-US">HASONEFILTER</span>、<span lang="EN-US">HASONEVALUE</span>、<span lang="EN-US">ISFILTERED</span>、<span lang="EN-US">ISCROSSFILTERED</span>、<span lang="EN-US">ISINSCOPE</span>、<span lang="EN-US">ISBLANK</span>、<span lang="EN-US">SELECTEDMEASURE</span>、<span lang="EN-US">SELECTEDMEASURENAME</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">逻辑函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">AND</span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">OR</span>、<span lang="EN-US">IF</span>、<span lang="EN-US">IFERROR</span>、<span lang="EN-US">SWITCH</span>、<span lang="EN-US">TRUE</span>、<span lang="EN-US">FALSE</span>、<span lang="EN-US">COALESCE(</span>官方文档含糊不清<span lang="EN-US">-</span>结合实例<span lang="EN-US">)</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">关系函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">CROSSFILTER</span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">RELATED</span>、<span lang="EN-US">RELATEDTABLE</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">筛选器函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">FILTER</span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">CALCULATE</span>、<span lang="EN-US">ALL</span>、<span lang="EN-US">ALLEXCEPT</span>、<span lang="EN-US">ALLSELECTED</span>、<span lang="EN-US">EARLIER</span>、<span lang="EN-US">KEEPFILTERS</span>、<span lang="EN-US">REMOVEFILTERS</span>、<span lang="EN-US">SELECTEDVALUE</span>、<span lang="EN-US">LOOKUPVALUE</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体; background: yellow;">父子函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体; background: yellow;">PATH</span><span style="font-size: 9pt; font-family: 宋体; background: yellow;">、<span lang="EN-US">PATHCONTAINS</span>、<span lang="EN-US">PATHITEM</span>、<span lang="EN-US">PATHLENGTH</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">统计函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">AVERAGE</span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">COUNT</span>、<span lang="EN-US">MAX</span>、<span lang="EN-US">MIN</span>、<span lang="EN-US">SUM</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">迭代统计函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">AVERAGEX</span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">COUNTX</span>、<span lang="EN-US">MAXX</span>、<span lang="EN-US">MINX</span>、<span lang="EN-US">SUMX</span>、<span lang="EN-US">RANKX</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">表函数: </span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">FILTERS </span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">ADDCOLUMNS</span>、<span lang="EN-US"> SELECTCOLUMNS</span>、<span lang="EN-US"> CROSSJOIN</span>、<span lang="EN-US"> EXCEPT</span>、 <span lang="EN-US">GENERATE</span>、<span lang="EN-US"> GROUPBY</span>、 <span lang="EN-US">SUMMARIZE</span>、<span lang="EN-US"> SUMMARIZECOLUMNS</span>、<span lang="EN-US"> TOPN</span>、<span lang="EN-US"> TREATAS</span>、 <span lang="EN-US">UNION</span>、<span lang="EN-US"> VALUES</span>、<span lang="EN-US">DISTINCT</span>、<span lang="EN-US">DATATABLE</span>、<span lang="EN-US">NATUALINNERJOIN</span>、<span lang="EN-US">NATRUALLEFTOUTERJOIN</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">文本函数:</span><span style="font-size: 9pt; font-family: 宋体;"> <span lang="EN-US">EXACT</span>、<span lang="EN-US">MID</span>、 <span lang="EN-US">FIND</span>、 <span lang="EN-US">LEN</span>、<span lang="EN-US"> REPT</span>、 <span lang="EN-US">LOWER</span>、 <span lang="EN-US">UPPER</span>、<span lang="EN-US"> UNICHAR</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">时间智能函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">DATEADD</span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">DATESMTD</span>、<span lang="EN-US">FIRSTDATE</span>、<span lang="EN-US">LASTDATE</span>、<span lang="EN-US">SAMEPERIODLASTYEAR</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">财务函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">2020.7</span><span style="font-size: 9pt; font-family: 宋体;">后发布的,<span lang="EN-US">Excel</span>中财务函数相似,网页<span lang="EN-US">demo pbix</span>简单介绍</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; line-height: 13.5pt; mso-pagination: widow-orphan; background: #FFFFFE;" align="left"><span style="color: #000000;"><span style="font-family: 宋体;">其他函数:</span><span lang="EN-US" style="font-size: 9pt; font-family: 宋体;">BLANK</span><span style="font-size: 9pt; font-family: 宋体;">、<span lang="EN-US">ERROR</span>、<span lang="EN-US">IFERROR</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"> </p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数初体验:</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">Max</span><span style="font-family: 宋体;">、<span lang="EN-US">Sum</span>、<span lang="EN-US">Divide</span>、<span lang="EN-US">if</span>、<span lang="EN-US">Values</span>等</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">值函数函数以及表列的概念</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数术语、语法、运算符</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">运算符引擎中字母大小写问题</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">编程注释快捷键</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">与<span lang="EN-US">Excel</span>函数的共同点区别(<span lang="EN-US">PPT</span>)</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">、<span lang="EN-US">xmSQL</span>与<span lang="EN-US">SQL</span>表达式的区别(<span lang="EN-US">PPT</span>)</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数的自学途径</span></span></p> <p class="MsoNormal"><span lang="EN-US" style="font-family: 宋体; color: #000000;"> </span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-indent: -18.0pt; mso-list: l0 level1 lfo1;"><span style="color: #000000;"><!-- [if !supportLists]--><strong><span lang="EN-US" style="font-family: 宋体;"><span style="mso-list: Ignore;">2.<span style="font: 7.0pt 'Times New Roman';">  </span></span></span></strong><!--[endif]--><strong><span lang="EN-US" style="font-family: 宋体;">PowerBI</span></strong><strong><span style="font-family: 宋体;">中数据建模知识</span></strong></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">维度建模</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">关系传递交叉筛选器方向<span lang="EN-US">-</span></span><span style="font-family: 宋体;">理解表关系<span lang="EN-US">(1v1, 1vM, Mv1,MvM)</span></span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">两个方向上应用安全筛选器</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">关闭关系自动检测</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">新建计算列</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">新建度量值</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">新建计算表:辅助表(五种方式)、日历表</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">数据类型讲解</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">数据格式控制:<span lang="EN-US">%</span>、<span lang="EN-US">$</span>、千位分隔符、小数位、日期格式</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">Format</span><span style="font-family: 宋体;">函数自定义数据格式</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">Convert</span><span style="font-family: 宋体;">函数数据类型转换</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">解决中文数字单位 万 的显示问题</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">Date</span><span style="font-family: 宋体;"><span lang="EN-US">DT</span>函数定义固定日期值</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">显示隐藏列</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">代码分析器</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">阅读<span lang="EN-US">DAX</span>表达式方法:从上至下、由内到外(注意<span lang="EN-US">Calculate</span>的计算顺序)</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">调试<span lang="EN-US">DAX</span>表达式方法:分布输出或<span lang="EN-US">VAR</span>输出</span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-indent: -18.0pt; mso-list: l0 level1 lfo1;"><span style="color: #000000;"><!-- [if !supportLists]--><strong><span lang="EN-US" style="font-family: 宋体;"><span style="mso-list: Ignore;">3.<span style="font: 7.0pt 'Times New Roman';">  </span></span></span></strong><!--[endif]--><strong><span lang="EN-US" style="font-family: 宋体;">DAX</span></strong><strong><span style="font-family: 宋体;">函数原理 </span></strong></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">Vertipaq</span><span style="font-family: 宋体;">列式数据库原理</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">理解度量值计算列</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">理解行上下文筛选上下文:<span lang="EN-US">Calculate</span>示意图</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">行上下文中使用<span lang="EN-US">VAR</span>替代<span lang="EN-US">EARLIER</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">VAR</span><span style="font-family: 宋体;">变量在定义时的上下文中计算</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">VAR</span><span style="font-family: 宋体;">变量是采用惰性计算(使用时计算)</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">理解扩展表<span lang="EN-US">RELATED</span>函数</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">理解数据沿袭<span lang="EN-US">Lineage</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span lang="EN-US" style="font-family: 宋体; color: #000000;"> </span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-align: left; text-indent: -18.0pt; line-height: 13.5pt; mso-pagination: widow-orphan; mso-list: l0 level1 lfo1; background: #FFFFFE;" align="left"><span style="color: #000000;"><!-- [if !supportLists]--><strong><span lang="EN-US" style="font-family: 宋体;"><span style="mso-list: Ignore;">4.<span style="font: 7.0pt 'Times New Roman';">  </span></span></span></strong><!--[endif]--><strong><span style="font-family: 宋体;">开始感知<span lang="EN-US">DAX</span>函数的强大</span></strong></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数实现特殊符号的使用</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数实现切片器默认当前月或天</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数使切片器默认代表无任何选择</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数使切片器仅显示有数据的选项</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数使切片器反向筛选计算</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数使切片器间取并集</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数使关系中多端的切片器筛选一端的切片器</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span lang="EN-US" style="font-family: 宋体; color: #000000;"> </span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数实现年月共同决定数据排序</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数实现动态图表标题</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数实现动态图表配色图标</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数实现动态纵坐标</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span lang="EN-US" style="font-family: 宋体;">DAX</span><span style="font-family: 宋体;">函数实现动态横坐标</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-indent: -18.0pt; mso-list: l0 level1 lfo1;"><span style="color: #000000;"><!-- [if !supportLists]--><strong><span lang="EN-US" style="font-family: 宋体;"><span style="mso-list: Ignore;">5.<span style="font: 7.0pt 'Times New Roman';">  </span></span></span></strong><!--[endif]--><strong><span style="font-family: 宋体;">理解重点<span lang="EN-US">DAX</span>函数</span></strong></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">重中重</span><span lang="EN-US">FILTER </span><span style="font-family: 宋体;"></span><span lang="EN-US"> CALCULATE</span><span style="font-family: 宋体;"></span><span lang="EN-US">CALCULATETABLE</span><span style="font-family: 宋体;">详解</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">调节器</span><span lang="EN-US">REMOVEFILTERS</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"></span><span lang="EN-US">ALL</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、</span><span lang="EN-US">ALLEXCEPT</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">函数</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">调节器</span><span lang="EN-US">ALL</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、</span><span lang="EN-US">ALLSELECTED</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"></span><span lang="EN-US">ISINSCOPE</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">占比分析</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">调节器</span><span lang="EN-US">AllSELECTED</span><span style="font-family: 宋体;"></span><span lang="EN-US">KEEPFILTERS</span><span style="font-family: 宋体;">的比较</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">调节器</span><span lang="EN-US">USERELATIONSHIP</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">激活关系</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">调节器</span><span lang="EN-US">TREATAS</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">动态建立关系</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">调节器</span><span lang="EN-US">CROSSFILTER</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">改变筛选器方向</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">重点</span><span lang="EN-US">ISFILTERED</span><span style="font-family: 宋体;"></span><span lang="EN-US">ISCROSSFILTERED</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重点</span><span lang="EN-US">HASONEVALUE</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"></span><span lang="EN-US">ISINSCOPE</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">的区别</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重点函数</span><span lang="EN-US">SELECTEDCOLUMNS</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"></span><span lang="EN-US">ADDCOLUMNS</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">重点函数</span><span lang="EN-US">NATUALINNERJOIN</span><span style="font-family: 宋体;"></span><span lang="EN-US">NATRUALLEFTOUTERJOIN</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">重点函数</span><span lang="EN-US">FILTERS</span><span style="font-family: 宋体;"></span><span lang="EN-US">VALUES</span><span style="font-family: 宋体;">比较</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重点</span><span lang="EN-US">VALUES</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"></span><span lang="EN-US">DISTINCT</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">的区别</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重点分组函数</span><span lang="EN-US">SUMMARIZECOLUMNS</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">详解</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重点函数</span><span lang="EN-US">LOOKUPVALUE vs RELATED vs VLOOKUP <br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重点集合函数</span><span lang="EN-US">UNION</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、</span><span lang="EN-US">INTERSECT</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、</span><span lang="EN-US">EXCEPT<br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重点集合函数</span><span lang="EN-US">CROSSJOIN</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"></span><span lang="EN-US">GENERATE </span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">笛卡尔积</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重点值合并、列合并、表合并</span><span lang="EN-US">CONCATENATEX<br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重点</span><span lang="EN-US">BLANK</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">行产生的原因</span><span lang="EN-US">BLANK</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">相关函数</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">重点</span><span lang="EN-US">COALESCE</span><span style="font-family: 宋体;">函数处理空</span><span lang="EN-US"><br /></span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重点</span><span lang="EN-US">FIRSTNOBLANK</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"></span><span lang="EN-US">FIRSTNOBLANKVALUE</span><span style="font-family: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">函数</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">重点使用</span><span lang="EN-US">VAR</span><span style="font-family: 宋体;">变量表中的列</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">重点</span><span lang="EN-US">Error</span><span style="font-family: 宋体;"></span><span lang="EN-US">IfError</span><span style="font-family: 宋体;">函数</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-indent: -18.0pt; mso-list: l0 level1 lfo1;"><span style="color: #000000;"><!-- [if !supportLists]--><strong><span lang="EN-US" style="font-family: 宋体;"><span style="mso-list: Ignore;">6.<span style="font: 7.0pt 'Times New Roman';">  </span></span></span></strong><!--[endif]--><strong><span style="font-family: 宋体;">实际案例<span lang="EN-US">-</span>日期时间时间智能相关</span></strong></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">关键点<span lang="EN-US">-</span>日期表事实表关联问题</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">时间智能<span lang="EN-US">-</span>同比环比分析</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">时间智能<span lang="EN-US">-</span>累计聚合、滚动聚合、移动平均</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">时间智能<span lang="EN-US">-</span>期初期末库存分析</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>计算任意所选月份的环比</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>周的同比环比周聚合</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>指定月份的同比环比季度环比</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>计算季末或季末月份的数据</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>趋势图中根据最近月份取<span lang="EN-US">TopN</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>动态指定某个日期区间分析</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>动态任意区间段做数据对比</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>实现两个日期列的范围筛选</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>按工作日计算日期差</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>计算最近两次购买日期差</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期分析<span lang="EN-US">-</span>根据历史数据做销售预测</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">日期时间函数时间智能函数使用总结</span></p> <p class="MsoNormal" style="margin-left: 18.0pt; text-indent: -18.0pt; mso-list: l0 level1 lfo1;"><span style="color: #000000;"><!-- [if !supportLists]--><strong><span lang="EN-US" style="font-family: 宋体;"><span style="mso-list: Ignore;">7.<span style="font: 7.0pt 'Times New Roman';">  </span></span></span></strong><!--[endif]--><strong><span style="font-family: 宋体;">实际案例<span lang="EN-US">-DAX</span>函数进阶</span></strong></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>解决列排序对计算的影响</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>实现切片器筛选间的<span lang="EN-US">OR</span>逻辑</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>矩阵<span lang="EN-US">Matrix</span>中高亮显示最大值最小值</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-DAX</span>列转行 <span lang="EN-US">vs </span>矩阵列转行逆透视</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>非日期类型的累计聚合</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>排名逻辑的<span lang="EN-US">4</span>种实现<span lang="EN-US">-RANKX</span>详解</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>分组内排名的实现理解迭代函数</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">进阶<span lang="EN-US">-TopN/BottomN</span><span lang="EN-US">Others</span>的实现</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">进阶<span lang="EN-US">-</span></span><span lang="EN-US" style="font-family: 宋体;">TopN</span><span style="font-family: 宋体;">中实现动态指标</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">进阶<span lang="EN-US">-</span></span><span lang="EN-US" style="font-family: 宋体;">TopN</span><span style="font-family: 宋体;">中实现<span lang="EN-US">N</span>的动态</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">进阶<span lang="EN-US">-</span></span><span style="font-family: 宋体;">分组内动态<span lang="EN-US">TopN</span><span lang="EN-US">Others</span></span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span lang="EN-US" style="font-family: 宋体; color: #000000;"> </span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">进阶<span lang="EN-US">-</span></span><span style="font-family: 宋体;">商品折上折<span lang="EN-US">-</span>迭代函数<span lang="EN-US">SUMX</span>详解</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span lang="EN-US" style="font-family: 宋体; color: #000000;"> </span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">进阶<span lang="EN-US">-</span></span><span style="font-family: 宋体;">分析客户购买行为</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="color: #000000;"><span style="font-family: 宋体;">进阶</span><span lang="EN-US" style="font-family: 宋体;">-</span><span style="font-family: 宋体;">找出无购买行为的客户</span></span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>客户购买商品关联度分析 </span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>新客户分析</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>流失客户分析</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>回流客户分析</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>客户购买频次区间分析</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-RFM</span>客户价值分析</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>帕累托分析</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">进阶<span lang="EN-US">-</span>盈亏平衡分析</span></p> <p class="MsoNormal" style="margin-left: 18.0pt;"><span style="font-family: 宋体; color: #000000;">报表性能优化思路<span lang="EN-US">(PPT)</span></span></p> <p> </p> <p> </p>

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:数字20 设计师:CSDN官方博客 返回首页
评论 2

打赏作者

Haaaley

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值