当前位置：Gxlcms > 数据库问题 > DAX基础入门 – 30分钟从SQL到DAX — PowerBI 利器

DAX基础入门 – 30分钟从SQL到DAX — PowerBI 利器

时间：2021-07-01 10:21:17 帮助过：16人阅读

有没有面对着稀奇古怪的DAX而感到有点丈八金刚摸不着头脑或者干瞪眼？！

有没有想得到某个值想不出来DAX怎么写而直跳脚！？

看完这篇文章，你会恍然大悟，捂脸偷笑。呼呼呼~

 

前言：

这篇文章对于具有一点SQL查询基础人会十分容易理解，譬如：掌握SELECT，SUM，GROUP BY等。

技术分享

 

技术分享

 注：此文不涉及到Filter Context（筛选上下文）的介绍。

 

正文：

对于对SQL有一定了解的人来说，咋看DAX，怎么都不习惯。 但是，如果理解以下几个后，DAX学起来就得心应手一些。

  • SUMMARIZE
  • FILTER
  • CALCULATE 与 CALTULATETABLE

注：这里不会对这些语法详细的讲解，而是从SQL的角度，看看那些DAX的等价相似语句。

 

欢迎转载，请保留原文链接和作者信息。O(∩_∩)O谢谢。 DAX基础 – 30分钟掌握从SQL到DAX 作者：马丁叔叔
链接：http://www.cnblogs.com/lizardbi/p/DAX-FOUNDATION-DAX-FOR-SQL-DEVELOPER-IN-30-MINUTES.html 

 

先来看一个例子，

查询Products表里的所有行： 

  1. <span style="font-size: 16px; color: #3366ff"><strong>　DAX</strong></span>
 
  1. <span style="font-size: 16px; color: #3366ff"><strong>SQL</strong></span>
 
  1. <span style="color: #008080">--</span><span style="color: #008080"> list all the Products<br></span><span style="color: #000000"><span style="color: #0000ff">EVALUATE</span>
  2. Product</span>
 
  1. <span style="color: #008080">show all the Products</span>
  2. <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span>
  3. <span style="color: #0000ff">FROM</span> tblProduct

 

DAX Filter vs SQL Filter

DAX SQL 
  1. <span><span style="color: #3366ff">EVALUATE
  2. FILTER</span> (
  3.     Product,
  4.     RELATED ( Category</span><span style="color: #ff0000">[Product Category </span><span style="color: #ff0000">Name</span><span style="color: #ff0000">]</span> ) <span style="color: #808080">=</span><span><span style="color: #ff0000"> "Bike"</span>
  5. )</span>
 
  1. <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span><span> Product P
  2. </span><span style="color: #808080">JOIN</span> Category c <span style="color: #0000ff">on</span> P.<span style="color: #ff0000">[</span><span style="color: #ff0000">Category_KEY</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> c.<span style="color: #ff0000">[</span><span style="color: #ff0000">Category_KEY</span><span style="color: #ff0000">]</span>
  3. <span style="color: #0000ff">WHERE</span><span>
  4.     c.</span><span style="color: #ff0000">[Product </span><span style="color: #ff0000">Category Name</span><span style="color: #ff0000">]</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Bikes</span><span style="color: #ff0000">‘</span>

FILTER是一个MUST know的语句

 

从Transaction表中统计销售数目 – SUM-GRUOP BY：

DAX SQL 
  1. <span style="color: #008080">--</span><span style="color: #008080"> 显示每个产品销售数目</span>
  2. <span style="color: #000000"><span style="color: #0000ff">EVALUATE</span>
  3. <span style="color: #0000ff">SUMMARIZE</span> (
  4. </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">Transaction</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  5. </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">Transaction</span><span style="color: #ff0000">‘[</span><span style="color: #ff0000">ProductId</span><span style="color: #ff0000">]</span><span style="color: #000000">,
  6. "<span style="background-color: #ccffff">Total qty</span>", </span><span style="color: #ff00ff">SUM</span> ( <span style="color: #ff0000">‘</span><span style="color: #ff0000">Transaction</span><span style="color: #ff0000">‘[</span><span style="color: #ff0000">Quantity</span><span style="color: #ff0000">]</span><span style="color: #000000"> )
  7. )</span>
 
  1. <span style="color: #008080">--</span><span style="color: #008080"> 显示每个产品销售数目</span>
  1. <span style="color: #0000ff">SELECT</span><span style="color: #000000"> ProductId, </span><span style="color: #ff00ff">SUM</span>(Quantity) <span style="color: #0000ff">AS</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Total Qty</span><span style="color: #ff0000">‘</span> <br><span style="color: #0000ff">FROM</span><span style="color: #000000"> tblTransaction <br></span><span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> ProductId

 

SUMMERIZE

选择一个表中指定的列： 

DAX SQL 
  1. -- list selected columns
  2. <span>EVALUATE
  3. SUMMARIZE(
  4. Product,
  5. Product[Name]<span>,
  6. Product[Size])</span></span>

 

 
  1. -- show selected columns
  2. SELECT<span>
  3. Name,
  4. Size
  5. FROM tblProduct</span>

 

 排序：

DAX SQL 
  1. -- show products in name order
  2. <span>EVALUATE
  3. Product
  4. ORDER BY Product[ProductName]</span>
 
  1. -- show products by name
  2. SELECT *
  3. FROM<span> tblProduct
  4. ORDER BY ProductName</span>

选择前几行：

DAX SQL 
  1. <span style="color: #008080">--</span><span style="color: #008080"> show 5 most expensive products</span>
  2. <span style="color: #000000">EVALUATE
  3. TOPN ( </span><span style="color: #800000; font-weight: bold">5</span>, Product, Product<span style="color: #ff0000">[</span><span style="color: #ff0000">FullPrice</span><span style="color: #ff0000">]</span> )

 

 
  1. <span style="color: #0000ff">SELECT</span> <span style="color: #0000ff">TOP</span> <span style="color: #800000; font-weight: bold">5</span>
  2. <span style="color: #0000ff">FROM </span>Product <br><span style="color: #3366ff">ORDER BY </span>FullPrice

 

SUMMERIZE vs Group BY

SUMMARIZE是一个比较重要语句：

记住：这个跟SQL极为相似，学习过程中只要想想SQL就容易理解很多了。

 

  1. <span style="color: #000000"><span style="color: #0000ff">EVALUATE</span> 
  2. <span style="color: #0000ff">SUMMARIZE</span>( 
  3.       源表名, 
  4. </span><span style="color: #0000ff">      Group</span> <span style="color: #0000ff">by</span> 列 <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">, 
  5.       ...,
  6. </span><span style="color: #0000ff">      Group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> 列 N, 
  7.       汇总列名1, 
  8.       汇总列名1所对应的表达式, 
  9.       ...,
  10.       汇总列名N, 
  11.       汇总列名N所对应的表达式 
  12. )</span>

 

 再来一个例子：

对产品的分类，颜色，产品名字进行统计：交易单量，销售数目

MSDN的例子

DAX
 SQL 
  1. <span style="color: #000000"><span style="color: #0000ff">EVALUATE</span> 
  2. <span style="color: #0000ff">SUMMARIZE</span>(‘Internet Sales‘  
  3.       , <span style="color: #0000ff">ROLLUP</span>(‘Date‘[Calendar Year], ‘Product Category‘[Product Category Name])  
  4.       , <span style="color: #ff0000">"Sales Amount"</span>, <span style="color: #0000ff">SUM</span>(‘Internet Sales‘[Sales Amount])  
  5.       , <span style="color: #ff0000">"Discount Amount"</span>, <span style="color: #0000ff">SUM</span>(‘Internet Sales‘[Discount Amount])  
  6. )  </span>
 
  1. <span style="color: #0000ff">SELECT</span> D.<span style="color: #000000">[Calendar Year]</span>, PC.<span style="color: #000000">[Product Category Name]</span><span style="background-color: #ccffff"><span style="color: #000000">
  2.      , </span><span style="color: #ff00ff">SUM</span>(F.<span style="color: #000000">[Sales Amount]</span>)  <span style="color: #ff0000">‘</span><span style="color: #ff0000">Sales Amount</span><span style="color: #ff0000">‘</span><span style="color: #000000">
  3.      , </span><span style="color: #ff00ff">SUM</span>(F.<span style="color: #000000">[Discount Amount]</span>) <span style="color: #ff0000">‘</span><span style="color: #ff0000">Discount Amount</span><span style="color: #ff0000">‘</span></span>
  4. <span style="color: #0000ff">FROM</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Internet Sales</span><span style="color: #ff0000">]</span><span style="color: #000000"> F
  5. </span><span style="color: #808080">JOIN</span> DATE D <span style="color: #0000ff">ON</span> <span style="color: #000000">S.[DAY_KEY] = F.[DAY_KEY]</span>
  6. <span style="color: #808080">JOIN</span> <span style="color: #000000">[Product Category]</span> PC <span style="color: #0000ff">ON</span> <span style="color: #000000">PC.[Category_KEY] = F.[Category_KEY]</span>
  7. <span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span><span style="background-color: #ccffff">
  8.     D.<span style="color: #ff0000">[</span><span style="color: #ff0000">Calendar Year</span><span style="color: #ff0000">]</span>,PC.<span style="color: #ff0000">[</span><span style="color: #ff0000">Product Category Name</span><span style="color: #ff0000">]</span></span>
Date[Calendar Year] Product Category[Product Category Name] [Sales Amount] [Discount Amount]
2005 Bikes 6958251.043 4231.1621
2006 Bikes 18901351.08 178175.8399
2007 Bikes 24256817.5 276065.992
2008 Components 2008052.706 39.9266
… … … …

等价的SQL如下，如果你只看浅蓝色的部分，是不是很好理解呢？

SUMMARIZE还有其他的Option，这里就不做详细介绍。

SUMMARIZE详情参考：https://msdn.microsoft.com/en-us/library/gg492171.aspx 

CALCULATETABLE vs Sub Query

 

下面语句统计Bike这个类别的产品的销售数目。

DAX SQL 
  1. <span style="color: #000000"><span style="color: #3366ff">EVALUATE
  2. SUMMARIZE</span> (</span>
  3. <span style="color: #000000"><span style="background-color: #ccffff"><strong><span style="color: #0000ff">    CALCULATETABLE</span> </strong></span>(
  4.         </span><span style="background-color: #ccffff"><span style="color: #ff0000">‘Internet Sales</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  5.         ‘Product Category‘[Product Category Name] = "</span><span style="color: #ff0000">Bikes</span><span style="color: #000000">"</span></span><span style="color: #000000"> ),
  6.     </span><span style="color: #008080">--</span><span style="color: #008080"> field to group by</span>
  7.     Product<span style="color: #ff0000">[</span><span style="color: #ff0000">Product Name</span><span style="color: #ff0000">]</span><span style="color: #000000">,</span>
  8. <span style="color: #000000">    "Quantity sold",
  9.     </span><span style="color: #ff00ff">SUM</span> ( <span style="color: #ff0000">‘Internet Sales</span><span style="color: #ff0000">‘[Order </span><span style="color: #ff0000">Quantity</span><span style="color: #ff0000">]</span><span style="color: #000000"> )
  10. )</span>

 

 
  1. <span style="color: #0000ff">SELECT</span>
  2.     <span style="color: #000000">P.<span style="color: #ff0000">[Product Name]</span>,
  3.     </span><span style="color: #ff00ff">SUM</span>(Fact.<span style="color: #ff0000">[Quantity]</span>) <span style="color: #0000ff">as</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Quantity sold</span><span style="color: #ff0000">‘</span>
  4. <span style="color: #0000ff">FROM</span><span style="color: #000000">
  5.     (</span><span style="background-color: #ccffff"><span style="color: #0000ff">SELECT</span> F.<span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> <span style="color: #ff0000">[Transaction]</span><span style="color: #000000"> F
  6.         </span><span style="color: #808080">JOIN</span> Category c</span><span style="color: #000000"><span style="background-color: #ccffff"> ON F.[Category_Key] = C.[Category_Key]<br>             WHERE C.[Product Category Name] = ‘<span style="color: #ff0000">Bikes</span>‘
  7.    </span> ) Fact
  8.     join Product P ON P.[Product_Key] = Fact.[Product_Key]</span>
  9. <span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span><span style="color: #000000"> 
  10.     P.</span><span style="color: #000000">[Product Name]</span>

 

上述的SQL语句有很多种写法。

高亮部分CalculateTable里面筛选了Bikes这个类别，正如SQL的sub Query一样。

 技术分享

 

DAX ADDCOLUMNS vs SQL Derived Column

注：ADDCOLUMNS跟Calculated Column类似：即给指定的表加入计算列。不一样的地方在于Addcolumn所加的只在它所在的语句有效。

 DAX  SQL 
  1. <span style="color: #000000"><span style="color: #0000ff">EVALUATE</span>
  2. <span style="background-color: #ccffff; color: #0000ff">ADDCOLUMNS </span>(
  3.     </span><span style="color: #ff0000">‘</span><span style="color: #ff0000">Product Category</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  4.     "</span><span style="color: #0000ff">Number</span> transactions", COUNTROWS ( RELATEDTABLE ( <span style="color: #ff0000">‘</span><span style="color: #ff0000">Internet Sales</span><span style="color: #ff0000">‘</span><span style="color: #000000"> ) )
  5. )</span>

 

 
  1. <span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Product Category Name</span><span style="color: #ff0000">]</span>, <span style="color: #ff00ff">count</span>(t.Id) <span style="color: #0000ff">AS</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Number transactions</span><span style="color: #ff0000">‘</span>
  2. <span style="color: #0000ff">FROM</span> <span style="color: #0000ff">Transaction</span><span style="color: #000000"> F
  3. </span><span style="color: #808080">JOIN</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Product Category</span><span style="color: #ff0000">]</span> C <span style="color: #0000ff">on</span> <span style="color: #000000">F.[Category_Key] = C.[Category_Key]</span>
  4. <span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span><span style="color: #000000"> 
  5.     C.</span><span style="color: #ff0000">[</span><span style="color: #ff0000">Product Category Name</span><span style="color: #ff0000">]</span>

 

技术分享

 

 

小结：

DAX语法十分灵活，有些看起来晦涩难懂，但是，如果能够以SQL为基础的角度去切入会事半功倍。
还有，此文没有介绍的上下文（Filter Context）是一个重要的概念，如果要真正掌握DAX和一些高级的用法，深刻理解上下文是必须的。

希望有时间好好讲讲这个。

 

以上就是DAX基础入门 – 30分钟从SQL到DAX — PowerBI 利器的全部内容了，更多内容请关注：CPP学习网_CPP大学
本文固定链接：CPP学习网_CPP大学-DAX基础入门 – 30分钟从SQL到DAX — PowerBI 利器

DAX基础入门 – 30分钟从SQL到DAX — PowerBI 利器

标签：ros   固定   mic   bsp   san   msdn   list   uri   rollup   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！