power bi dax基礎(chǔ)教程數(shù)據(jù)建模最佳入門

1、Power BIDAX基礎(chǔ) BIPower BI DAX數(shù)據(jù)建模 應(yīng)對復(fù)雜業(yè)務(wù)邏輯的基礎(chǔ)（基礎(chǔ)篇）Excel120思考不同類別的總利潤率如何計算1*表和訂單表是兩個不同的表。 表和訂單表是一對多。 總利潤率無法簡單聚合。需要這樣的訂單利潤率合計 =SUM ( '訂單'利潤 ) / SUM ( '訂單'銷售額 )小提示也許一些BI工具也可以做到。考慮一個更復(fù)雜的：將A類客戶并且在4月1日后累計額超過10000的定義為VIP客戶，不同類目中各月VIP客戶利潤的整體有多大？（>100個類目，>10W客戶，>500W筆）Excel表做不到 業(yè)務(wù)常常用表

2、搞Excel 120定業(yè)務(wù)指標(biāo)計算 但很快就會遇到難題 數(shù)據(jù)量級瓶頸 復(fù)雜邏輯支持度不夠 無法可交互的進行數(shù)據(jù)探索 業(yè)務(wù)Power BI DAX 為復(fù)雜業(yè)務(wù)建模（自助BI）而生 個人可以使用 Power BI DAX 且無需依賴企業(yè) / ITExcel 120 （更好的：）如果企業(yè)已經(jīng)開始使用微軟BI系列： SQL Server / Azure SQL Server SSAS Power BI / Power BI Pro / Power BI Premium 業(yè)務(wù)可以使用 Power BI DAX 最大化生產(chǎn)力使用 Power BI 及 DAX 之前的傳統(tǒng)商務(wù)智能El 120你要什么？我要算

3、VIP客戶的同比增IT暈 這個指標(biāo)又沒有，傳統(tǒng)BI長，用哪個指標(biāo)？VIP客戶是怎么定義的？商業(yè)業(yè)務(wù)又得找開發(fā)了。價值就上會開會說的A類用戶并且累計消費超過10000的使用 Power BI 及 DAX 之后的自助式商務(wù)智能E還需要什么嗎？咦？應(yīng)該看看新上線后的留客能力對差不多了比老有多大提升?，F(xiàn)在我們可以有分析再找你自助BI可以寫個DAX來看看。好的IT業(yè)務(wù)商業(yè)價值沒有對比就沒有El 120傳統(tǒng)BI自助BIIT業(yè)務(wù)MORE VALUEVALUE業(yè)務(wù)xIT ce來了：Power BI 自助BI（DAX） 怎么玩E0DAXSimple but not easy.課程板塊Excel 120自助BI主

4、流程框架以不變，應(yīng)萬變。Power BI 自助商務(wù)智能分析主流程 理解業(yè)務(wù)：以 業(yè)務(wù)的形式描述，如：分析銷售業(yè)績。Excel 120 數(shù)據(jù)準(zhǔn)備：把與該業(yè)務(wù)有數(shù)據(jù)加載進入Power BI數(shù)據(jù)模型。 數(shù)據(jù)建模：將數(shù)據(jù)表通過強依賴組織成數(shù)據(jù)模型。 定義指標(biāo)：將業(yè)務(wù)細化為更小的可量化指標(biāo)并定義計算邏輯。 DAX實現(xiàn)：將指標(biāo)的計算邏輯用DAX三大構(gòu)件以函數(shù)運算的形式組織并實現(xiàn)。 數(shù)據(jù)分析：聯(lián)立不同的表列作為觀察角度以及指標(biāo)來對比以發(fā)現(xiàn)、規(guī)律、異常等。 業(yè)務(wù)分析：結(jié)合業(yè)務(wù)考慮數(shù)據(jù)分析的結(jié)果，給出分析報告或循環(huán)這個過程以優(yōu)化。Power BI DAX 通用業(yè)務(wù)思維模式 忘記DAX，專注于當(dāng)前關(guān)心的某些業(yè)務(wù)。

5、如：銷售業(yè)績。Excel 120 與該業(yè)務(wù)有數(shù)據(jù)表及屬性有哪些，它們的是怎樣的。如：，訂單，地區(qū)，日期等。 在關(guān)心的業(yè)務(wù)下，定義可以量化的指標(biāo)。如：銷售額，利潤，利潤率等。 考慮相數(shù)據(jù)如何按照業(yè)務(wù)邏輯計算出上述業(yè)務(wù)指標(biāo)。 轉(zhuǎn) Power BI DAX 通用建模思維模式。Power BI DAX 通用建模思維模式 DAX 數(shù)據(jù)模型 是 用串接在一起的表，這是自然的，它Excel 120們表示了業(yè)務(wù)的結(jié)構(gòu)。 DAX 數(shù)據(jù)建模 就是根據(jù)業(yè)務(wù)將相表用最合理的串接起來。（并考慮為后續(xù)的計算做準(zhǔn)備） DAX 數(shù)據(jù)計算 在數(shù)據(jù)模型中選定與某業(yè)務(wù)相列并從中過濾出有，然后這些行進行聚合計算。 DAX 數(shù)據(jù)分析

6、就是某業(yè)務(wù)，選擇一個或幾個觀察角度來觀察 DAX 數(shù)據(jù)計算 的結(jié)果。 轉(zhuǎn) Power BI DAX 的三種計算元素。Power BI DAX 的三種計算元素Excel 120課程板塊Excel 120簡介DAX - 數(shù)據(jù)分析表什么是 DAXDAX是一種編程語言，用于：Excel 1Power BI / Power Pivot / SSAS Tabular類似于Excel函數(shù)，如SUM誕生于Excel 2010 PowerPivot沒有 Excel 中 行或列 的概念比Excel處理數(shù)據(jù)邏輯的函數(shù)專為數(shù)據(jù)模型及商業(yè)智能計算而生Simple but NOT easy用DAX就是：寫函數(shù)DAX是一種

7、函數(shù)語言，因此執(zhí)行邏輯Excel 1與函數(shù)調(diào)用直接相關(guān)：男性 VIP客戶 銷售額 =SUMX (FILTER ( 'tb客戶', 'tb客戶'= "男" ),IF ( 'tb客戶'類型 = "VIP", 累計銷售額 * 100, 累計銷售額 * 90 )由于是函數(shù)形式，使用起來非常簡單。課程板塊Excel 120返回值的計算關(guān)注一個屬性或一個指標(biāo)的值用 DAX 計算后返回一個值ExcDAeX實l現(xiàn) 度1量值20DAX實現(xiàn) 計算列增加觀察的角度：計算列 計算列：Calculated ColumnsExcel

8、12 使用DAX計算 總是計算當(dāng)前行 例如：tb訂單銷售額的意思是： 【tb訂單】表中【銷售額】列的值 tb訂單指定了表名(可選的)當(dāng)前行(隱式的) 每行都不同計算復(fù)雜的業(yè)務(wù)指標(biāo)：度量值 度量值：MeasuresExcel 12 使用DAX編寫 并非按行運算 使用表及聚合運算 沒有當(dāng)前行的概念例如：累計銷售額 = SUM( 'tb訂單'銷售額 )該用 計算列 還是 度量值 計算列 通常用作 觀察的角度 使用Excel 120 度量值通常用于計算復(fù)雜的業(yè)務(wù)指標(biāo)，如： 計算百分比，如：總利潤率，流失率，同比增長率等 復(fù)雜的聚合運算，如：VIP客戶中前20%的平均額 考慮性能與靈活性

9、 計算列占用內(nèi)存，不占CPU 度量值占用CPU，不占內(nèi)存 對于某業(yè)務(wù)指標(biāo)感覺算得慢，優(yōu)先考慮建立輔助的計算列 對于某整個文件感覺太大了，優(yōu)先考慮使用度量值計算業(yè)務(wù)指標(biāo) 總體上，優(yōu)先選擇度量值使用DAX應(yīng)該有的好習(xí)慣度量值不屬于任何一個表Excel 120使用時，不需要帶表名使用 累計銷售額 而不是 tb訂單累計銷售額存放時歸位到一個表（不依賴性），可以是任意表約定：計算列的寫法：tb訂單銷售額度量值的寫法：累計銷售額度量值應(yīng)考慮放置于空表，如：指標(biāo)表實現(xiàn)聚合類運算的常用函數(shù) 用于對值進行聚合運算，：Excel 120 SUM 計算總銷售額 AVERAGE 計算平均單價 MIN 計算最小客單價

10、MAX 計算最大客單價 只能與數(shù)字類型的 表列 配合使用 只能與 一個表列 配合使用，例如： SUM( 訂單銷售額 ) 是合理的 SUM( 訂單單價 * 訂單數(shù)量 ) 是不合理的彌補聚合運算函數(shù)的不足：聚合類運算擴展 擴展后的聚合函數(shù)如下：Excel 120 SUMX / AVERAGEX / MINX / MAXX 等 迭代整個表并基于每行對給定表計算再聚合 使用兩個參數(shù)： 參數(shù)1：表，按行迭代，如：訂單 參數(shù)2：表，基于迭代到的當(dāng)前行完成計算，如：訂單金額 * 折扣 返回：對參數(shù)1的整個表迭代并在每行計算參數(shù)2表最后按 SUMX /AVERAGEX / MINX / MAXX 等進行聚合運

11、算，返回最終值SUMX 示例 對于訂單的每一筆，從業(yè)務(wù)邏輯上：Excel 120 單筆利潤率 = 利潤 / 銷售額 總利潤率 = 所有訂單的利潤 / 所有訂單的銷售額。 DAX 實現(xiàn)方式1：總利潤率 =SUM ( '訂單'利潤 ) / SUM ( '訂單'銷售額 )方式2：總利潤率 =SUMX ( '訂單', '訂單'利潤 ) / SUMX ( '訂單', '訂單'銷售額 )對復(fù)雜的計算可以一步步來：VAR 對于簡化計算非常有用，每個VAR可以完成一步Excel 120 最后用 RETURN 返回最

12、終的結(jié)果總利潤率 =VAR x =SUMX ( '訂單', '訂單'利潤 )VAR y =SUMX ( '訂單', '訂單'銷售額 )RETURNDIVIDE ( x, y )日期函數(shù) 日期函數(shù)完成對日期的簡單計算，如：某天是幾Excel 120 DATE, DATEVALUE, DAY, EDATE EOMONTH, HOUR, MINUTE MONTH, NOW, SECOND, TIME TIMEVALUE, TODAY, WEEKDAY WEEKNUM, YEAR, YEARFRA 時間智能函數(shù)完成基于日期的分析邏輯計算：

13、 時間智能函數(shù)不是日期函數(shù) 計算 同比 環(huán)比 最近7日 等業(yè)務(wù)邏輯 單獨課程板塊Excel 120返回表的計算也許不是關(guān)注的結(jié)果，但卻是得到結(jié)果的前提計算表計算表的位置Excel 12 返回表的函數(shù) Power BI Desktop 新功能 Excel Power Pivot 中叫回表返回表的常用函數(shù) 與返回表有常用函數(shù)：Excel 120 FILTER ALL VALUES DISTINCT RELATEDTABLE 返回表作為結(jié)果并常用于繼續(xù)作為其他函數(shù)的參數(shù) 可以混合使用以計算復(fù)雜的業(yè)務(wù)邏輯過濾：FILTER FILTERExcel 120 加入條件 限定更嚴格的過濾條件 返回表 可以用

14、作迭代器 X類函數(shù) 的首個參數(shù) 第一個參數(shù)是 表 可以嵌套使用取消已有的過濾：ALL ALL：取消篩選，該效果常常用于Excel 120 返回表的所有行 忽略篩選上下文 返回表 可與迭代器X類函數(shù)配合使用作為第一參數(shù) 需要表作為參數(shù) 可與單個列配合使用 FILTER - ALL 模式FILTER ALL 模式 取消對一個表的已有過濾并施加新的過濾Excel 120 表函數(shù)可混合使用 每個表函數(shù)需要表作為參數(shù) 每個表函數(shù)又返回一個表 FILTER( ALL( T ) , 條件 ) 對 T 整體忽略所處的篩選上下文 同時每一個行，使用條件進行過濾表列去重：DISTINCT 根據(jù)當(dāng)前的篩選上下文，返

15、回列的非重復(fù)值Excel 120SKU數(shù) =COUNTROWS ( DISTINCT ( ''ID ) )客戶表 =( '訂單表'客戶ID )DISTINCT獲得相關(guān)行：RELATEDTABLE 在與之連接的另一個表返回與當(dāng)前行有所有行Excel 120與訂單是一對多表增加計算列：相關(guān)訂單數(shù) =( '訂單' )COUNTROWS(RELATEDTABLE)課程板塊Excel 120計算上下文為 DAX 計算創(chuàng)造環(huán)境計算上下文（Evaluation Context）DAX任何計算都是在 特定環(huán)境 下進行的。篩選上下文形成符合業(yè)務(wù)的含義：2012年5

16、月由地區(qū)經(jīng)理對于30到50歲山東男性客戶利潤率合計負責(zé)的家具類篩選上下文（Filter Context） 來源：Exce 圖形元素產(chǎn)生：行選，列選，報告篩選，切片器。 規(guī)則： 未被篩選到的行不參與計算。 規(guī)律： 視覺元素產(chǎn)生的篩選都可由等效的函數(shù)來實現(xiàn)。l行上下文（Row Context） 來源：Exce 計算列引擎自動隱式創(chuàng)建完成。 迭代器函數(shù) SUMX，AVERAGEX 所有的迭代器X類函數(shù) 其他可以產(chǎn)生行上下文的函數(shù) 取出列值參與計算，反映了“當(dāng)前行”的概念。計算邏輯：從邏輯上，行上下文猶如輪詢整個列的每行，并在行中按DAX表計算結(jié)果。在行上下文中使用 TableColumn 可以獲取當(dāng)

17、前行的值。兩種上下文共同作用 篩選上下文 行上下文Excel 120 形式上著度量值 形式上著計算列 影響到表級別 影響到行級別 篩選上下文可能是空 行上下文也可能是空 決定哪些表行可見（未被篩選掉） 獲取當(dāng)前行的某列值 完全未被篩選的是很少的 可能和篩選上下文交錯影響計算任何計算都是在兩種上下文的同時作用下進行的！DAX 可以設(shè)置篩選 可以在DAX中設(shè)置篩選以覆蓋外部報告的篩選。Excel 120訂單銷售額合計 家具類 =CALCULATE ( 訂單銷售額合計, ''類別 = "家具" )DAX 可以取消篩選 可以在DAX中設(shè)置取消篩選以忽略外部報告的篩選

18、。Excel 120訂單銷售額合計 忽略類別篩選 =CALCULATE ( 訂單銷售額合計, ALL ( ''類別 ) )課程板塊Excel 120CALCULATE實現(xiàn)自定義計算用 CALCULATE 修改外部篩選狀況再計算 使用 CALCULATE 的：Excel 120 修改篩選上下文 自定義篩選條件 CALCULATE 運行在篩選上下文中CALCULATE 語法 CALCULATE分為第一參數(shù)，和后續(xù)參數(shù)，除了第一參數(shù)，后Excel 120續(xù)參數(shù)全部為篩選器參數(shù)。 形如： CALCULATE( 返回值的DAX表, 篩選器1, , 篩選器n ) 度量值內(nèi)部會自動定義CAL

19、CULATE并將計算都至于其中 用 度量值( 篩選器 ) 理解自定義計算的意義CALCULATE 執(zhí)行過程說明1. 它采用當(dāng)前篩選上下文，并后得到一個新的篩選上下文。Excel 1202. 從第二個參數(shù)開始，在 1 中篩選上下文對每個參數(shù)進行計算。3. 如果 2 中對于某列有多個篩選，則取這些篩選的交集。4. 如果 3 中的整體結(jié)果與 1 中篩選上下文有不一致，以 3 為準(zhǔn)。5. 在 4 中篩選上下文繼續(xù)計算 CALCULATE 的第一個參數(shù)。6.注意：如果CALCULATE位于行上下文中，在 1 與 2 之間還存在上下文轉(zhuǎn)換， 將行上下文轉(zhuǎn)為篩選上下文。CALCULATE 計算原理 基于當(dāng)前

20、計算上下文 EC( Evaluation Context ) 計算 CALCULATE：Excel 1其中，篩選上下文，記為 FC( Filter Context )其中，行上下文，記為 RC( Row Context ) 計算CALCULATE( 參數(shù)1 ，參數(shù)2，參數(shù)N )FC 生成同樣的FC，記為FC0將RC轉(zhuǎn)為等效的篩選上下文，記為FC1在 FC中 分別計算 參數(shù)2，參數(shù)N，記為FC2，F(xiàn)CN計算FCNEW =【1】FC1 覆蓋 FC0 ，即相同列篩選以 FC1 為準(zhǔn)【2】計算 FC2 FCN，即第二個參數(shù)到第N個參數(shù)的交集效果【2】覆蓋【1】，即相同列篩選以【2】為準(zhǔn)在FCNEW篩選

21、上下文中計算第一個參數(shù) CALCULATE 返回計算結(jié)果 注意：調(diào)用 CALCULATE 的位置的計算上下文調(diào)用前后是沒有變化的2小提示0不要指望在第一次就能完全理解 CALCULATE 的計算過程，更好的是直接去用。當(dāng)發(fā)現(xiàn)預(yù)期和實際不同時，再多次回來溫習(xí) CALCULATE 的用法說明。相信每次的體會。CALCULATE 計算原理圖釋計算參數(shù)1 FC2 FCN FC2FCN行下文RC轉(zhuǎn)換FC生成篩選上下文FC0源篩選上下文FC篩選上下文FC行上下文RC度量值 = CALCULATE ( 參數(shù)1 , 參數(shù)2 , , 參數(shù)N ) 注：在計算參數(shù)2到N，分別產(chǎn)生篩選上下文FC2到FCNCALCUL

22、ATE 示例CALCULATE ( 訂單銷售額合計, ''類別 = "家具" )Excel 120CALCULATE ( 訂單銷售額合計, FILTER ( ALL ( ''類別 ), ''類別 = "家具" ) )CALCULATE ( 訂單銷售額合計, FILTER ( ALL ( ''子類別 ), 訂單利潤率合計 > 0.2)CALCULATE ( 訂單銷售額合計, ''類別 = "家具", ALL ( '地區(qū) ) , VALUES (

23、 客戶')CALCULATE (訂單銷售額合計,'類別 = "家具",'USERELATIONSHIP ( '日期'日期, '訂單'發(fā)布日期 )CALCULATE ( 訂單銷售額合計, TREATAS ( VALUES ( '日期'日期 ), '訂單'發(fā)布日期 ) )CALCULATE 中的上下文轉(zhuǎn)換 CALCULATE在計算前會進行計算上下文轉(zhuǎn)換Excel 120 如果CALCULATE處于行上下文中 CALCULATE會獲取到當(dāng)前行上下文 轉(zhuǎn)換為等價的篩選上下文 并作用于數(shù)據(jù)模型 然

24、后再進行CALCULATE的計算 該特性非常強大也非常重要 在實踐中體驗的感受課程板塊Excel 120計算上下文與多表模型的計算篩選與 篩選上下文中是否Excel 行上下文 篩選上下文 處于的一端 處于的多端RELATED：從多端按從一端取值 RELATED( tablecolumn )Excel 120 可導(dǎo)航到目標(biāo)表列，并繼續(xù)以行上下文形式。 沿著從多端找到一端的值RELATEDTABLE：從一端按從多端取行集合 RELATEDTABLE(table)Excel 120 用當(dāng)前行篩選作為參數(shù)的表 返回與當(dāng)前行相關(guān)聯(lián)的行 與RELATED函數(shù)類似多表參與的篩選上下文 篩選上下文與的共同作用Excel 120影響篩選上下文 例如：及訂單 沿著傳遞 可以多級傳遞 與行上下文不同篩選的傳遞E0課程板塊Excel 120時間智能基于日期的靈活計算日期表 預(yù)置一個日期表Excel 120 使用DAX 或使用Excel 日期表必須滿足所有連續(xù)日期 不得重復(fù) 中間無空隙 如果包含某年，必須包含該年1月1日到12月31日所有日期用 CALENDAR / CALENDARAUTO 生成日期表生成指定的日期表：Excel 120CALENDAR(DATE(2015,1,1),DATE(2016