データ分析式 (DAX) は、最初は少し難しそうに聞こえますが、その名前に騙されないでください。 DAX の基礎は非常に理解しやすいものです。 まず大事なことは、DAX はプログラミング言語ではないということです。 DAX は数式の言語です。 DAX を使用して、 集計列 と メジャー (集計フィールドとも呼ばれます) のユーザー設定の計算を定義できます。 DAX には、Excel の数式で使用できる関数や、リレーショナル データを使って、動的な集計を行うように設計された関数が用意されています。
DAX 数式について
DAX 数式は、Excel の数式とよく似ています。 作成するには、等号、その後に関数名または式、および必要な値または引数を入力します。 Excel と同様に、DAX には、文字列の操作、日付と時刻を使用した計算の実行、条件付き値の作成に使用できるさまざまな関数が用意されています。
ただし、DAX 数式は次の重要な点で異なります。
- 行単位で計算式をカスタマイズする場合、DAX には、現在の行の値または相対値を使って、コンテキストごとに異なる計算を行うための関数が用意されています。
- DAX には、結果として 1 つの値ではなく、テーブルを返すタイプの関数があります。 このような関数を使用して、他の関数に入力値を渡すことができます。
- DAX のタイムインテリジェンス関数では、日付の範囲を使用して計算を行い、並行する同じ期間の結果を比較することができます。
DAX 数式を使うケース
Power Pivot の数式は、集計列または集計フィールドのいずれかで作成できます。
計算列
集計列は、既存の Power Pivot テーブルに追加する列です。 この列に値を貼り付けたり、インポートしたりする代わりに、列の値を定義する DAX 数式を作成します。 ピボットテーブル (またはピボットグラフ) に Power Pivot テーブルを含める場合、集計列は他のデータ列と同様に使用できます。
集計列の数式は、Excel で作成する数式とよく似ています。 ただし、Excel とは異なり、テーブル内の行ごとに異なる数式を作成することはできません。代わりに、列全体に DAX 数式が自動的に適用されます。
列に数式が入力されている場合、値は行ごとに計算されます。 数式を作成すると同時に、その列の結果が計算されます。 列の値の再計算は、その基となるデータが更新された場合または手動再計算を使った場合にのみ行われます。
メジャーやその他の集計列に基づく集計列を作成できます。 ただし、集計列とメジャーに同じ名前を使用すると、結果がわからない場合があります。 列を参照するときは、メジャーを誤って呼び出さないように、完全修飾列参照を使用することをお勧めします。
詳細については、「Power Pivot の計算列」を参照してください。
メジャー
メジャーとは、パワー ピボット データを使用するピボットテーブル (またはピボットグラフ) で使用するために特別に作成される数式です。 メジャーは、COUNT や SUM などの標準の集計関数に基づくことも、DAX を使用して独自の数式を定義することもできます。 メジャーは、ピボットテーブルの 値 の領域で使用されます。 計算結果をピボットテーブルの別の領域に配置する場合は、代わりに集計列を使用します。
明示的なメジャーの数式を定義する場合、ピボットテーブルにメジャーを追加するまでは何も起こりません。 メジャーを追加すると、ピボットテーブルの [ 値 ] 領域の各セルについて数式が評価されます。 結果は行見出しと列見出しの組み合わせごとに作成されるため、メジャーの結果は各セルで異なる場合があります。
作成するメジャーの定義は、そのソース データ テーブルとともに保存されます。 これはピボットテーブルのフィールドの一覧に表示され、ワークブックのすべてのユーザーが使用できます。
詳細については、「Power Pivot のメジャー」をご覧ください。
数式バーを使用した数式の作成
Power Pivot には、Excel と同様に、数式の作成と編集を容易にする数式バーと、入力と構文のエラーを最小限に抑えるオートコンプリート機能が用意されています。
テーブルの名前を入力するには テーブルの名前を入力します。 数式オートコンプリートには、これらの文字で始まる有効な名前を含むドロップダウン リストが用意されています。
列の名前を入力するには 角かっこを入力し、現在のテーブルの列の一覧から列を選びます。 別のテーブルの列については、テーブル名の最初の文字を入力し、オートコンプリートのドロップダウン リストからその列を選択します。
数式の作成方法の詳細とチュートリアルについては、「Power Pivot での計算式の作成」を参照してください。
オートコンプリート使用のヒント
入れ子になった既存の関数の途中で、数式オートコンプリートを使用できます。 挿入ポイントの直前のテキストに基づいてドロップダウン リストの値が表示され、挿入ポイントの後のすべてのテキストは元のまま残ります。
ユーザーが定数に対して作成した定義名はオートコンプリートのドロップダウン リストには表示されませんが、入力することはできます。
Power Pivot は、関数の閉じかっこを追加したり、かっこを自動的に一致させたりしません。 各関数の構文に誤りがないことをユーザー自身が確認する必要があります。
1 つの数式における複数の関数の使用
関数を入れ子にすることができます。つまり、1 つの関数からの結果を別の関数の引数として使用します。 集計列には、最大 64 レベルの関数をネストできます。 ただし、入れ子を使用すると、数式の作成やトラブルシューティングが困難になる場合があります。
多くの DAX 関数は、入れ子になった関数としてのみ使用するように設計されています。 これらの関数はテーブルを返しますが、結果として直接保存することはできません。テーブル関数への入力として指定する必要があります。 たとえば、関数 SUMX、AVERAGEX、MINX はすべて、最初の引数としてテーブルを必要とします。
注
列間の依存関係によって要求される多くの計算によってパフォーマンスが影響を受けないようにするために、メジャー内には関数の入れ子に関するいくつかの制限が存在します。
DAX 関数と Excel 関数の比較
DAX 関数ライブラリは Excel 関数ライブラリをベースにしていますが、両者のライブラリにはさまざまな違いがあります。 このセクションでは、Excel 関数と DAX 関数の相違点の概要を説明します。
- 多くの DAX 関数は、Excel 関数と同じ名前と同じ一般的な動作を持っていますが、異なる種類の入力を受け取るように変更されており、場合によっては異なるデータ型を返す場合があります。 一般に、Excel 数式で DAX 関数を使用したり、Power Pivot の Excel 数式を使用したりするには、変更を加えないでください。
- DAX 関数が参照としてセル参照や範囲を取ることはありませんが、列やテーブルは参照できます。
- たとえば、DAX の日付と時刻の関数は、datetime データ型を返します。 これに対して、Excel の日付と時刻の関数は、日付をシリアル番号として表した整数を返します。
- 新しい DAX 関数の多くは、一連の値から成るテーブルを返すか、入力として与えられた値のテーブルに基づいて計算を行います。 一方、Excel にはテーブルを返す関数はありませんが、配列を処理できる関数はいくつかあります。 完全なテーブルと列を簡単に参照する機能は、Power Pivot の新機能です。
- DAX は、Excel の配列およびベクトル形式の LOOKUP 関数に類似した、新しい LOOKUP 関数を提供します。 ただし、DAX 関数を使用するには、テーブルの間にリレーションシップを確立する必要があります。
- 列内のデータは、常に同じデータ型であることが想定されています。 データが同じ型ではない場合、列全体が、すべての値に最もよく対応するデータ型に変更されます。
DAX のデータ型
Power Pivot データ モデルには、さまざまなデータ型をサポートする可能性のあるさまざまなデータ ソースから、データをインポートすることができます。 データをインポートまたは読み込んで計算またはピボットテーブルで使用すると、データは PowerPivot データ型の 1 つに変換されます。 データ型のリストについては、「データ モデルのデータ型」を参照してください。
table は DAX の新しいデータ型で、さまざまな新しい関数の入力や出力として使われます。 たとえば、FILTER 関数は入力としてテーブルを取り、フィルター条件に一致する行だけを含む新たなテーブルを出力します。 テーブル関数と集計関数を組み合わせることにより、動的に定義されたデータ セットに対して複雑な計算を実行できるようになります。 詳細については、「Power Pivot で使用する集計」を参照してください。
数式とリレーショナル モデル
[Power Pivot] ウィンドウは、複数のデータ テーブルを操作し、リレーショナル モデルのテーブルを接続できる領域です。 このデータ モデル内では、テーブルはリレーションシップによって相互に接続されます。これにより、他のテーブルの列との相関関係を作成し、より興味深い計算を作成できます。 たとえば、関連テーブルの値を合計する数式を作成し、その値を 1 つのセルに保存できます。 または、関連するテーブルの行を制御するために、テーブルと列にフィルターを適用することができます。 詳細については、「 データ モデルのテーブル間のリレーションシップ」を参照してください。
リレーションシップを使用してテーブルをリンクできるため、ピボットテーブルには、さまざまなテーブルにある複数の列からデータを取り込むこともできます。
ただし、数式はテーブルおよび列全体を操作できるため、計算式の組み立ては Excel とは異なります。
- 一般的に、列の DAX 数式は、常にその列にある値セット全体に適用されます。一部の行やセルだけに適用されることはありません。
- Power Pivot のテーブルは、各行の列数が常に同じである必要があり、列のすべての行に同じデータ型が含まれている必要があります。
- テーブルがリレーションシップにより接続されている場合、キーとして使用されている 2 つの列の値の大部分が一致していることを確認する必要があります。 Power Pivot では参照整合性が強制されないため、キー列に一致しない値が含まれていても、リレーションシップが作成される可能性があります。 ただし、空白や一致しない値のプレゼンスが、数式の結果やピボットテーブルの見た目に影響を与える可能性があります。 詳細については、「Power Pivot の数式での参照」を参照してください。
- リレーションシップを使用してテーブルをリンクするときは、範囲、つまり数式が評価されるコンテキストを拡大します。 たとえば、ピボットテーブルの数式は、ピボットテーブルのフィルターまたは列見出しおよび行見出しによる影響を受けます。 コンテキストを操作する数式を書くことはできますが、コンテキストが原因で、想定外の結果となることもあります。 詳細については、「DAX の数式のコンテキスト」を参照してください。
数式の結果の更新
データ更新と再計算は異なる動作ですが、複雑な数式、大量のデータ、または外部データソースから取得されたデータを含むデータ モデルの設計時には理解していなければならない動作であるという点では、関連しています。
データの更新はワークブック内のデータを、外部データ ソースから得られた新しいデータでアップデートするプロセスです。 データは指定した間隔で手動で更新できます。 また、ワークブックを SharePoint サイトに公開している場合は、外部ソースから自動更新をスケジュールすることもできます。
再計算は、数式自体に対する変更、または基になるデータに対する変更が反映されるように数式の結果を更新するプロセスです。 再計算はパフォーマンスに次のような影響を与えます。
- 計算列では、数式が変更されるたびに、必ず列全体の数式の結果が再計算されます。
- メジャーの場合、ピボットテーブルまたはピボットグラフのコンテキストにメジャーが配置されるまで、数式の結果は計算されません。 また、データに適用されたフィルターに影響を与える行見出しまたは列見出しを変更したとき、またはピボットテーブルを手動で更新したときにも、数式は再計算されます。
数式のトラブルシューティング
数式を記述しているときのエラー
数式を定義しているときにエラーが発生した場合、この数式には構文エラー、セマンティックエラー、計算エラーのいずれかが含まれている可能性があります。
一番簡単に解決できるのは構文エラーです。 多くの場合、かっこやカンマが抜けています。 個々の関数の構文については、「DAX 関数リファレンス」を参照してください。
構文エラー以外のエラーは、構文は正しくても、値や参照されている列が、数式のコンテキストで意味をなしていないときに発生します。 このようなセマンティック エラーや計算エラーの原因には、次のような問題が考えられます。
- 数式が、存在しない列、テーブル、または関数を参照しています。
- 数式は正しいように見えますが、データ エンジンがデータをフェッチすると、型の不一致が判明し、エラーとなります。
- 数式から関数に、誤った数値または型のパラメーターが渡されています。
- 数式がエラーを含む別の列を参照しているため、その値が無効になっています。
- 数式はまだ処理されていない列を参照しています。つまり、メタデータはありますが、計算に使用できる実際のデータがありません。
1 つめから 4 つめのケースでは、DAX により、列全体に無効な数式が含まれているというフラグが設定されます。 最後のケースでは、DAX はこの列を灰色で表示し、列が未処理の状態であることを示します。
列の値をランク付けまたは順序付けした場合の間違った結果または例外的な結果
値 NaN (Not a Number: 数字ではありません) を含む列をランク付け、または順序付けすると、間違った結果や例外的な結果となる可能性があります。 たとえば、0 を 0 で割る計算が行われると、NaN という結果が返されます。
これは、数式エンジンは数値を比較して、順序付けとランク付けを行いますが、NaN は列内の他の数字とは比較できないためです。
正しい結果を得るために、IF 関数を使った条件文を使って、NaN 値をテストし、数値 0 を返すことができます。
Analysis Services 表形式モデルと DirectQuery モードの互換性
一般に、Power Pivot で作成する DAX 数式は、Analysis Services の表形式モデルと完全に互換性があります。 ただし、Power Pivot モデルを Analysis Services インスタンスに移行してから、モデルを DirectQuery モードで展開する場合は、いくつかの制限があります。
- DirectQuery モードでモデルを展開した場合、DAX 数式によっては、異なる結果が返されることがあります。
- DirectQuery モードでモデルを展開すると、リレーショナル データ ソースではサポートされていない DAX 関数を使った数式で検証エラーが発生する可能性があります。
詳細については、SQL Server 2012 BooksOnline にある Analysis Services 表形式モデルに関するマニュアルを参照してください。