Go to Qiita Advent Calendar 2024 Top
LoginSignup
67
69

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

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関数を使っているうちに、ちょくちょく使う関数が出てくると思いますが、都度更新しようと思います。

67
69
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
67
69

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?