Power BI-DAX公式常用函式套路
一.篩選類函式
1.ALL, ALLEXCEPT, ALLSELECTED
ALL(table/column) --ALL用來清除整個表或某個欄位的篩選條件,通常配合CALCULATE使用
ALLEXCEPT(table/column) --刪除表中除已應用於指定列的篩選器之外的所有上下文篩選器
ALLSELECTED(table/column) --從當前查詢的列和行中刪除上下文篩選器,同時保留所有其他上下文篩選器或顯式篩選器
2.CALCULATE(<expression>,<filter1>,<filter2>...)
--Calculate可以應用在多個表
Calculate使用AND關係時可直接在條件間加',',但表達“或”時,必須用'||',而且連線的兩個條件
必須引用同一列。
Calculate中當“[列] = 固定值”這種篩選搞不定時,可用filter/all/values篩選器搭配使用
3.FILTER(table,<filter>) --返回值:只包含篩選行資料的表
--篩選時對錶進行逐行掃描,計算量很大,所以選擇篩選的表儘量為lookup表
4.HASONEVALUE(<columnName>) --將 columnName 的上下文篩選為只剩下一個非重複值時,
將返回 TRUE。 否則為 FALSE。
5.RELATED(<column>) --將“一”端的列值新增到“多”端的表中
RELATEDTABLE(<tableName>) --將“多”端關聯的表資料新增到“一”端的列上,
返回的是一張表
6.VALUES(<TableNameOrColumnName>) --返回由一列構成的一個表,該表包含來自指定表或列的
非重複值。 換言之,重複值將被刪除,僅返回唯一值。
7.EARLIER函式:
例如我們有“產品名稱”和“銷售金額”兩列資料。基於此,我們希望計算出每個產品的銷售金額排名。
COUNTROWS(
FILTER('產品銷售表',
EARLIER('產品銷售表'[銷售金額])<'產品銷售表'[銷售金額])
)+1
--EARLIER=當前行,按照當前行進行逐行掃描,SUMX+FILTER+EARLIER
是Power Pivot中比較常用的函式組合,由於Earlier針對每一行資料都進行計算,
所以理論上計算量相當於資料行數的平方。如果有10行資料,則需要計算100次,
所以資料量大的時候大家使用要小心,有可能會造成模型計算緩慢。
二.邏輯與邏輯判斷函式
1.IF(logical_test>,<value_if_true>, value_if_false)
--檢查是否滿足作為第一個引數提供的條件。 如果該條件為 TRUE,則返回一個值;
如果該條件為 FALSE,則返回另一個值,用法與excel類似
2.SWITCH(<expression>,條件1,值1,條件2,值2) --根據值列表計算表示式,
並返回多個可能的結果表示式之一。
3.CONTAINS(InternetSales, [ProductKey], 214, [CustomerKey], 11185)
--是否同時存在銷售給客戶 11185 的產品 214 的任何 Internet 銷售額。
4.ISBLANK(column) --判斷該列中某個值是否為空
5.ISNONTEXT(column) --檢查某個值是否不是文字
6.ISNUMBER(column) --檢查某個值是否為數字
7.ISTEXT(column) --檢查某個值是否為文字
8.ISLOGICAL(column) --檢查某個值是否是邏輯值
三.時間日期類函式
1.計算年初至今累計--TOTALYTD函式
例如計算年初至今累計的銷售金額:
[年累計金額]:=
TOTALYTD([銷售金額],
'日曆年'[日期])
# 如果要加上一個時間截止點,則寫為:
[年累計截止金額]:=
TOTALYTD([銷售金額],
'日曆年'[日期],
'日曆年'[日期]<DATE(2016,6,1))
# 同理計算季度初至今累計--TOTALQTD, 計算月初至今累計--TOTALMTD
2.計算上一年/季度/月的銷售額--DATEADD函式
[上一年銷售額]:=
CALCULATE([銷售金額],
DATEADD('日曆年'[日期],-1,YEAR)) --YEAR可改為QUARTER,MONTH,DAY
--將-1改為+1則時間後移
3.計算同比/環比
# 月同比--今年每月的銷售金額相比去年同月份增長的比率
[上一年銷售額]:=
CALCULATE([銷售金額],
DATEADD('日曆年'[日期],-1,YEAR))
[同比]:=
DIVIDE([銷售金額]-[上一年銷售額],
[上一年銷售額])
# 月環比--這月與上月相比銷售額增長的比率
[上一月銷售額]:=
CALCULATE([銷售金額],
DATEADD('日曆年'[日期],-1,MONTH))
[環比]:=
DIVIDE([銷售金額]-[上一月銷售額],
[上一月銷售額])
4. 計算兩個日期之間間隔--DATEDIFF
[時間間隔]:=
DATEDIFF([開始日期],
[結束日期],
'Y') --引數"Y"換成"M"或"D",則分別對應月和日
--也可以改成HOUR,MINUTE,SECOND計算小時,分,秒
5.其他日期時間函式:
DATE(2009,7,8) --結果返回'2009/7/8 0:00:00'
DATEVALUE("8/1/2009") --將文字形式的日期轉換為日期時間格式的日期,結果返回
'2009/8/1 0:00:00'
DATEADD([日期],3,DAY) --日期加減函式
EOMONTH([日期],3) --返回指定月份數之前或之後的月份的最後一天的日期
EDATE([日期],3) --返回在開始日期之前或之後指示的月數的日期
YEAR(),MONTH(),DAY(),HOUR(),MINUTE(),SECOND()
NOW() --返回當前的日期時間 TODAY()--返回當前的日期
WEEKDAY([日期],1) --一週中的第幾天,引數1代表從星期日開始計數,引數2代表從星期一開始計數
引數3代表從星期一開始計數,但到星期六結束
WEEKNUM([日期],1) --一年中的第幾周,1代表從週日開始計數,2代表從週一開始計數
統計聚合函式
1. SUMX(table,<expression>) --SUMX與CALCULATE功能類似,但SUMX屬於迭代函式,計算量大
--同理AVERAGEX,MINX.MAXX用法相同
2. SUM(),MAX(),MIN(),SQRT()
DISTINCTCOUNT()--計算去重後列所包含的元素個數
COUNTROWS() --計算表的行數
COUNT() --計算列中包含數字的單元的數目
COUNTA() --計算列中不為空的單元的數目
COUNTBLANK() --計算列中空白單元的數目
3.DIVIDE(分子,分母) --在DAX中涉及到除法必須用DIVIDE
4.CROSSJOIN(<table1>, <table2>) --生成笛卡爾積表
SUMMARIZE(<table>, <groupBy_columnName>,<name>,<expression>)
--table,任何返回資料表的 DAX 表示式。
groupBy_columnName將使用該列中找到的值建立摘要組,此引數不能是表示式。
name,給予總計或彙總列的名稱,包含在雙引號內。
expression,任何返回單個標量值的 DAX 表示式,其中,表示式將計算多次(針對每行/上下文)。
GENERATE(<table1>, <table2>)--生成笛卡爾積表
四.文字函式
1. LEFT(),RIGHT(),MID(),FIND(),SEARCH(),LEN() ---字串提取函式
2. REPLACE(),SUBSTITUTE() --字串替換函式
3. TRIM(),LOWER(),UPPER()
4. FORMAT函式,對於日期格式的自定義設定如下圖:
文章轉自https://zhuanlan.zhihu.com/p/35982521,來到簡書只為學習記錄,侵刪。
相關文章
- 15個常用excel函式公式 excel函式辦公常用公式Excel函式公式
- 常用的10個 Excel 萬能公式套路Excel公式
- 學Excel函式公式,怎能不會這個組合套路?Excel函式公式
- Excel函式公式大全,辦公文員必備Excel函式公式Excel函式公式
- 三角函式公式函式公式
- Mysql 常用函式(1)- 常用函式彙總MySql函式
- 常用函式函式
- 大資料開發-Hive-常用日期函式&&日期連續題sql套路大資料Hive函式SQL
- Power Apps SortByColumns 函式使用注意事項APP函式
- Mysql 常用函式(20)- ceiling 函式MySql函式
- Mysql 常用函式(15)- upper 函式MySql函式
- MySQL 常用函式MySql函式
- kotlin常用函式Kotlin函式
- js 常用函式JS函式
- Oracle常用函式Oracle函式
- 常用函式整理函式
- TensorFlow常用函式函式
- PHP 常用函式PHP函式
- QT常用函式QT函式
- 常用助手函式函式
- loadrunner常用函式函式
- PHP常用函式PHP函式
- MySQL 常用函式。MySql函式
- 常用函式集合函式
- 三角函式公式推導函式公式
- Hive常用函式及自定義函式Hive函式
- 核函式 多項式核函式 高斯核函式(常用)函式
- excel根號公式怎麼打 excel開根號函式公式Excel公式函式
- 三角函式之和差角公式函式公式
- Excel vba自定義函式公式智慧提示Excel函式公式
- miRNA常用的套路
- javascript常用函式大全JavaScript函式
- PHP常用函式篇PHP函式
- Js常用的函式JS函式
- Carbon 類常用函式函式
- Hyperf 常用助手函式函式
- 常用啟用函式函式
- 常用函式彙總函式