はじめに
Power BIでレポートを作成するために、DAXを学ぶ方は多いと思います。
DAXを勉強しようと思って、ググって、公式ドキュメントを読んだり、QiitaやSQLBIの記事を読むと、なにやら見慣れぬ構文で書かれているDAXっぽいもの…これをみて頭が真っ白になった方も多いのでは。私もそうでした😭
DAXクエリ？🤔
EVALUATE
SUMMARIZE (
Sales,
'Product'[Brand],
'Product'[Color],
"Amount", [Sales Amount]
)
ORDER BY
[Amount] DESC
DAXクエリとDAXは似ています。
関数などはほとんど一緒です。
しかし、イコールではありません。
DAXクエリのことを少しでも知っていると、勉強が捗ります。もちろん、開発も捗ります。
DAXの基礎がわかっていれば怖くありません！今日、DAXクエリに入門しましょう～🎉
DAX.do
まずは便利サイトのご紹介。
あらかじめ、データモデルが用意してあり、そのデータモデルに対してDAXクエリを記述・実行し、結果を見ることができるサイトです。DAXの練習には持って来い！DAXのインテリセンスも表示されるし、関数をクリックすると右側にDAXガイドと連携して文法も表示してくれます。本記事で書いているDAXクエリはDAX.doで記述可能なものなので、ぜひコピペ→実行し、カスタマイズなどしてみてどうなるか試してみてください。
DAXクエリの使い所
SQLBIのホワイトペーパーには以下のように書かれています。
DAX 言語は、Power BI、Analysis Services、または Power Pivot for Excel のいずれかの表形式モデルでメジャーと計算列を作成するためによく使用されます。ただし、DAXは、モデルのクエリとレポートの表示のためにPower BIで使用される言語でもあります。ユーザーとモデル作成者は、複数の目的でDAXクエリを使用することもできます。
- ページ分割されたレポート: Power BI サービスで発行された既存のデータセットからデータを取得します。
- Power BI または分析サービスからデータをエクスポートする: DAX Studioを使用して DAXクエリを実行し、結果をCSVまたは Excel ファイルに保存します。
- Power BI または Analysis Services を新しい Power BI モデルのデータ ソースとして使用する: 複合モデルがオプションではない場合は、XMLA エンドポイントを介して接続して、既存の表形式モデルからデータを抽出します。
- Excel テーブルにデータをインポートする: Analysis ServicesデータベースまたはPower BIデータセットに接続されたExcelテーブルにカスタムDAXクエリを作成します。
また最近だとPower AutomateのアクションにもDAXクエリを書くことができるようになったので、DAXクエリを使いこなせば自由自在にデータセットからデータを取り出して活用できます。
Power BI Desktopで確認
Power BI Desktopでパフォーマンスアナライザーを開くとDAXクエリを見ることができます。
ビジュアルの中で数値を計算するために使用されるのがDAXで、ビジュアル全体を出力するために使われているのがDAXクエリです。
画像では、SalesAmount列を作成するために定義しているのがDAXです。見慣れた書き方ですよね。
カラー列、プロダクト列をグループ化し、SalesAmount列を集計し、そしてテーブル形式で出力するために、データセットに対して問い合わせているのがDAXクエリです。
DAXクエリの構文
[DEFINE
(
(MEASURE <table name>[<measure name>] = <scalar expression>) |
(VAR <var name> = <table or scalar expression>) |
(TABLE <table name> = <table expression>) |
(COLUMN <table name>[<column name>] = <scalar expression>) |
) +
]
EVALUATE <table>
[ORDER BY {<expression> [{ASC | DESC}]}[, …]]
[START AT {<value>|<parameter>} [, …]]]
必須 EVALUATE
DAXクエリを構成するために最低限必要なのがEVALUATEです。これだけあればDAXクエリは実行できます。
最小単位はEVALUATEのあとにテーブル式を記入することです。
EVALUATE
Sales
Salesテーブルの全件を出力されます。
以下リンククリックをクリックするとDAX.doでクエリ結果を見られます。
オプション ORDER BY
EVALUATE の後ろで宣言します。
[ORDER BY {<expression> [{ASC | DESC}]}[, …]]
プロダクトカラー別の売上をみたいときには、こんな感じで書いてあげます。
EVALUATE
ADDCOLUMNS (
SUMMARIZE ( 'Product', 'Product'[Color] ),
"SalesAmt", CALCULATE ( SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ) )
)
ORDER BY [SalesAmt] DESC
すると結果はこんな感じ。
| Color | 売上 |
|---|---|
| Silver | 6,798,560.86 |
| Blue | 2,435,444.62 |
| White | 5,829,599.91 |
| Red | 1,110,102.10 |
| Black | 5,860,066.14 |
| Green | 1,403,184.38 |
| Orange | 857,320.28 |
| Pink | 828,638.54 |
| Yellow | 89,715.56 |
| Purple | 5,973.84 |
| Brown | 1,029,508.95 |
| Grey | 3,509,138.09 |
| Gold | 361,496.01 |
| Azure | 97,389.89 |
| Silver Grey | 371,908.92 |
| Transparent | 3,295.89 |
ORDER BYをつけることによって、任意の列の降順・昇順を決めることができることがわかります。
オプション START AT
EVALUATE → ORDER BY → START AT の順番で並べます。クエリ結果を開始する値を定義します。
START ATはORDER BYと1対1で対応しています。
構文は下記。
[START AT {<value>|<parameter>} [, …]]]
実際に先程のDAXクエリにSTART ATを付け足してみます。
EVALUATE
ADDCOLUMNS (
SUMMARIZE ( 'Product', 'Product'[Color] ),
"SalesAmt", CALCULATE ( SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ) )
)
ORDER BY [SalesAmt] DESC
START AT 5860066.14
結果はこうなり、Black以下しか出力されていないことが確認できます。
| Color | SalesAmt |
|---|---|
| Black | 5,860,066.14 |
| White | 5,829,599.91 |
| Grey | 3,509,138.09 |
| Blue | 2,435,444.62 |
| Green | 1,403,184.38 |
| Red | 1,110,102.10 |
| Brown | 1,029,508.95 |
| Orange | 857,320.28 |
| Pink | 828,638.54 |
| Silver Grey | 371,908.92 |
| Gold | 361,496.01 |
| Azure | 97,389.89 |
| Yellow | 89,715.56 |
| Purple | 5,973.84 |
| Transparent | 3,295.89 |
オプション DEFINE
DEFINEはEVALUATEの前に書きます。
またその中でDEFINE（定義）できるものが4つあります。
- MEASURE
- VAR
- TABLE
- COLUMN
それぞれ、Power BI Desktopでレポートを開発する際に作成をしている、
- メジャー
- 変数
- 計算テーブル
- 計算列
になります。
[DEFINE
(
(MEASURE <table name>[<measure name>] = <scalar expression>) |
(VAR <var name> = <table or scalar expression>) |
(TABLE <table name> = <table expression>) |
(COLUMN <table name>[<column name>] = <scalar expression>) |
) +
]
例えばさきほどから実行しているDAXクエリの、EVALUATE以下を変数に置き換えてみます。Power BI Desktopで記述するメジャーと同様にVAR（変数）を使用して可読性の高いコードを書くことができます。
DEFINE
VAR Producttbl =
ADDCOLUMNS (
SUMMARIZE ( 'Product', 'Product'[Color] ),
"SalesAmt", CALCULATE ( SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ) )
)
EVALUATE
Producttbl
ORDER BY [SalesAmt] DESC
START AT 5860066.14
またこのように記述すると、Salesテーブルに新しい計算列が追加されていることが確認できます。
DEFINE
COLUMN Sales[SalesAmount] = Sales[Net Price] * Sales[Quantity]
EVALUATE
Sales
さいごに
DAXクエリを理解するためにはまずDAXを勉強すること。Docsにも書いてありますよね。DAXを一周したあとにDAXクエリの構文を理解してテーブルを生成するためによく使われるSUMMARIZE、ADDCOLUMNS、SUMMARIZECOLUMNSなどの関数をおさらい。そうすると理解が深まっていくと思います。
