LoginSignup
66
68

More than 3 years have passed since last update.

@pytomo

DAX関数で最低限覚えておいたほうが良いかも一覧｜Power BI

Last updated at Posted at 2020-07-30

まずはざっくり

DAXは、Data Analysis Expressionsの略で、リレーショナルデータベースを操作するための数式言語。
ExcelやSQL Server Analysis Service、PowerBIで主に利用され、式を記述するための部品（関数や演算子）が多く準備されています。

本稿の目的

多くの関数があるわけですが、下記のリファレンスを見ればすべて網羅されています。

参考
https://docs.microsoft.com/ja-jp/dax/dax-function-reference

そこで、本稿では結構な頻度で使用されるであろう関数に焦点を当てて記載します。
きっと覚えていて損はないかと。

こんがらない為に

・末尾X付きの関数について

よく「SUMとSUMX」「COUNTとCOUNTX」「MINとMINX」など、末尾に「X」のつく関数があります。
違いとしては、フィルタリングしたテーブルで使いたい時に「X」付き関数を使います。

・計算列とメジャーの違い

DAXの使いどころとして計算列やメジャーがありますが、それぞれの違いは明確にしておかないとこんがらがります。

  • 計算列は、テーブル内の各行に対して値が作成される
  • メジャーは、全体（フィルタリング後など）の集計など必要に応じて都度計算される

日付と時刻の関数

何かと使う機会は多いかと思います。

・CALENDAR

連続する日付セットが欲しいとき

・TODAY／UTCTODAY

現在日／UTC現在日が欲しいとき

パラメータ1 戻り値
なし datetime型日付

・DATE

指定した日付を取得したいとき

パラメータ1 パラメータ2 パラメータ3 戻り値
年 月 日 datetime型日付
サンプル
テーブル = CALENDAR(DATE(2020,4,1), TODAY())

2020/4/1から現在日までのカレンダデータセットで[DATE]カラムのみのデータセットが出来上がります。

・YEAR

指定日時から年（year）だけ欲しい

パラメータ1 戻り値
指定日時 年
サンプル
YEAR("2020/01/03 21:01:33")
// 2020

・MONTH

指定日時から月（month）だけ欲しい

パラメータ1 戻り値
指定日時 分（1~59）
サンプル
MONTH("2020/01/03 21:01:33")
// 1

・DAY

指定日時から日だけが欲しい

パラメータ1 戻り値
テキスト日付 datetime型の日
サンプル
DAY("2020/01/03")
// 3

・WEEKDAY

指定日時の曜日を数字で欲しい

パラメータ1 パラメータ2 戻り値
指定日時 戻り値タイプ（1:日曜1スタート、2:月曜1スタート、3:月曜0スタート） 整数値
サンプル
WEEKDAY("2020/07/27 9:21:53",3)
// 0

・DATEDIFF

指定した日付（時刻）間の境界数を取得したい

パラメータ1 パラメータ2 戻り値
開始日付（時刻） 終了日付（時刻） インターバル（SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR）
サンプル
DATEDIFF(DATE(2020,1,1),"2020-01/01 00:00:03",SECOND)
// 3

・NOW／UTCNOW

現在日時／UTC現在日時が欲しい

パラメータ1 戻り値
なし datetime型日付
サンプル
NOW()
// 2020/07/27 18:00:01

タイムインテリジェンス関数

・DATEADD

日付テーブルの中から、日付から指定された間隔だけ前後にシフトした日付が欲しい

パラメータ１ パラメータ２ パラメータ３ 戻り値
日付列 加算減算する整数値 間隔タイプ（year,quarter,month,day） 日付値のテーブル
サンプル
DATEADD(Datetime[DateKey],-1,year)
// DateTimeテーブルのDateKey列の日付の、1年前の日付テーブル

・DATESBETWEEN

指定範囲期間の日付が欲しい

パラメータ１ パラメータ２ パラメータ３ 戻り値
日付列 開始日 終了日 日付値のテーブル
サンプル
DATESBETWEEN(DATE(2020,7,1),DATE(2020,7,31))
// 2020/07/01~2020/07/31までの日付テーブル

・TOTALYTD

年度累計値を出したい

パラメータ1 パラメータ2 パラメータ3 パラメータ4 戻り値
スカラー値を返す式 日付列 フィルター式 年度末日付（既定は12\31） スカラー値
サンプル
TOTALYTD(SUM(Sales[Sales]), 'Date'[Date], "6-30")

統計関数

・AVERAGEX

テーブルに対して評価される式のセットの平均が欲しい

パラメータ1 パラメータ2 戻り値
テーブル スカラー値の結果をもつ式 10進数の値
サンプル
AVERAGEX('テーブル'[列],CALCULATE[列])

・COUNTROWS

指定したテーブルもしくは式で定義したテーブルの行数が欲しい

パラメータ1 戻り値
式を含むテーブル 整数値
サンプル
COUNTROWS('テーブル')
　とか
COUNTROWS(RELATEDTABLE(ResellerSales))
// リセラー毎の注文数をカウント　※別途リセラーテーブルあり、リレーションが貼られている

・ROW

フィルタリングされたテーブルに対しての最小値が欲しい

パラメータ1 パラメータ2 戻り値
列名 単一のスカラー地を返すDAX式 1行のテーブル
サンプル
ROW("Internet Total Sales (USD)", SUM(InternetSales_USD[SalesAmount_USD]),  
         "Resellers Total Sales (USD)", SUM(ResellerSales_USD[SalesAmount_USD]))

・SELECTCOLUMNS

指定した名前で参照列などを追加したい

パラメータ1 パラメータ2 パラメータ3 戻り値
テーブル 列名 参照列 テーブル
サンプル
SELECTCOLUMNS('Info', “StateCountry”, [State]&”, ”&[Country])

・MINX

フィルタリングされたテーブルに対しての最小値が欲しい

パラメータ1 パラメータ2 戻り値
テーブル 式 最小値
サンプル
MINX(FILTER(InternetSales,[SalesTerritoryCode]="5"), InternetSales[TaxAmt]+ InternetSales[Freight])

・MAXX

フィルタリングされたテーブルに対しての最大値が欲しい

パラメータ1 パラメータ2 戻り値
テーブル 式 最大値
サンプル
MAXX(FILTER(InternetSales,[SalesTerritoryCode]="5"), InternetSales[TaxAmt]+ InternetSales[Freight])

・SUMMARIZE

指定したベーステーブルに集計列などをつけたサマリーテーブルが欲しい

パラメータ1 パラメータ2 パラメータ3 パラメータ4 戻り値
テーブル 既存列でグループ化に使う列 集計に使う列名 単一の値を返すDAX式 テーブル
サンプル
SUMMARIZE(ResellerSales_USD  
      , DateTime[CalendarYear]  
      , ProductCategory[ProductCategoryName]  
      , "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD])  
      , "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])  
      )

・ADDCOLUMNS

指定したテーブルに計算列を追加したい

パラメータ1 パラメータ2 パラメータ3 戻り値
テーブル 列名 式 テーブル
サンプル
ADDCOLUMNS(ProductCategory,
               , "Internet Sales", SUMX(RELATEDTABLE(InternetSales_USD), InternetSales_USD[SalesAmount_USD])  
               , "Reseller Sales", SUMX(RELATEDTABLE(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD]))

フィルター関数

・ALL

フィルターをクリアしたテーブルや列が欲しい

パラメータ１ パラメータ２ 戻り値
テーブル 列 テーブル
サンプル
ALL('ProductCategory'[ProductCategoryName])

・CALCULATE

インタラクティブに影響されない計算結果が欲しい

パラメータ１ パラメータ２ 戻り値
式 ブール式、フィルター定義したリスト 式の結果
サンプル
CALCULATE(SUM('Sales OrderDetails'[Total Price]), YEAR('Sales OrderDetails'[orderdate]) = 2015)

・USERRELATIONSHIP

既存のリレーションシップは上書きせずに計算時だけ別のリレーションシップを指定したい

パラメータ１ パラメータ２ 戻り値
列1 列2 値なし
サンプル
CALCULATE(Sales[TotalPrice], USERELATIONSHIP('Calendar'[Date], Sales[ShipDate]))

・RELATED

別のテーブルから関連する値が欲しい

パラメータ1 戻り値
列 現在の行に関連する単一の値

・FILTER

フィルター処理した行を含むテーブルが欲しい

パラメータ1 パラメータ2 戻り値
テーブル ブール式 テーブル
サンプル
FILTER('InternetSales_USD', RELATED('SalesTerritory'[SalesTerritoryCountry])<>"United States")

・ISFILTERD

フィルタリングしているかの判定がしたい

パラメータ1 戻り値
列 指定列がフィルタリングされているかのブール値
サンプル
IF(
    ISFILTERD('テーブル'[列]),
    ERRPR("エラーメッセージ"),
    //Falseの場合の処理を記載
)

・REMOVEFILTERS

フィルターをクリアして計算したい

パラメータ1 パラメータ2 戻り値
テーブル or 列 列 書式設定された文字列
サンプル
REMOVEFILTERS('Region'[Region])

論理関数

・SWITCH

式の結果に応じて何かしたい

パラメータ1 パラメータ2 パラメータ3 パラメータ4 戻り値
式 値 値と一致した場合の式 それ以外の場合の式 式から取得されるスカラー値
サンプル
=SWITCH([Month], 1, "January", 2, "February", 3, "March", 4, "April"  
               , 5, "May", 6, "June", 7, "July", 8, "August"  
               , 9, "September", 10, "October", 11, "November", 12, "December"  
               , "Unknown month number" )

・IF

条件分岐で値を指定したい

パラメータ1 パラメータ2 パラメータ3 戻り値
booleanで判定できる式 TRUEの場合の値 FALSEの場合の値（省略可） 書式設定された文字列
サンプル
Year =
"FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1)

数学関数、三角関数

・SUMX

フィルタリングされたテーブルに対しての合計値が欲しい

パラメータ1 パラメータ2 戻り値
テーブル 式 10進数
サンプル
=SUMX(FILTER(InternetSales, InternetSales[SalesTerritoryID]=5),[Freight])

・DIVIDE

割り算と除算エラー時の代替結果を指定したい

パラメータ1 パラメータ2 パラメータ3 戻り値
割られる数 割る数 除算エラーになった場合に返す値（省略可） 10進数
サンプル
DIVIDE(
   SUM(Sales[Sales]),
   CALCULATE(
        SUM(Sales[Sales]),
        REMOVEFILTERS(Region)
   )

テキスト関数

・FORMAT

こっちが指定した書式で変換してほしい

パラメータ1 パラメータ2 戻り値
値 書式テンプレート 書式設定された文字列
サンプル
FORMAT([Date],"yyyy/mm/dd")

そのほか

・SUMMARIZECOLUMNS

指定したグループをベースに、指定した列の組み合わせを含むテーブルが欲しい

パラメータ1 パラメータ2 パラメータ3 パラメータ4 戻り値
ベーステーブルの列 フィルタ 後続の列名 単一の値を返すDAX式 テーブル
サンプル
SUMMARIZECOLUMNS
(
    DimDate[CalendarYear],TREATAS({2007, 2008}, DimDate[CalendarYear]),
    "Sales", [Sales],
    "Visual Total Sales", CALCULATE([Sales], ALLSELECTED(DimDate[CalendarYear]))
)

・ERROR

エラーを発生させてメッセージを表示したい

パラメータ1 戻り値
エラーテキスト テーブル
サンプル
IF(
    SELECTEDVALUE(DimProduct[Color]) = "Magenta",
    ERROR("エラーです"),
    SELECTEDVALUE(DimProduct[Color])
  )

DAX関数を使っているうちに、ちょくちょく使う関数が出てくると思いますが、都度更新しようと思います。

66
68
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
66
68