PowerPivot における Data Analysis Expressions (DAX)

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 その他…表示数を減らす

Data Analysis Expressions (DAX) と聞くと難しく感じるかもしれませんが、心配しないでください。 DAX の基礎は非常に理解しやすいものです。まず大事なことは、DAX はプログラミング言語ではないということです。 DAX は数式の言語です。 DAX を使用して target=”_blank” title=”、計算列” rel=”noopener”>、計算列と target=”_blank” title=”メジャー” rel=”noopener”>メジャー (計算フィールドとも呼ばれます) のカスタム計算を定義できます。 DAX には、Excel の数式で使用できる関数や、リレーショナルデータを使って、動的な集計を行うように設計された関数が用意されています。

DAX 数式について

DAX 数式は Excel 数式とよく似ています。作成するには、等号を入力し、その後に関数名または式、および必要な値または引数を入力します。 Excel と同様に、DAX には、文字列の操作、日付と時刻を使用した計算、条件付き値の作成に使用できるさまざまな関数が用意されています。

ただし、DAX 数式は次の重要な点で異なります。

行単位で計算式をカスタマイズする場合、DAX には、現在の行の値または相対値を使って、コンテキストごとに異なる計算を行うための関数が用意されています。
DAX には、結果として 1 つの値ではなく、テーブルを返すタイプの関数があります。このような関数を使用して、他の関数に入力値を渡すことができます。
タイムインテリジェンス関数 DAX では、日付の範囲を使用して計算を行い、並列期間にわたって結果を比較できます。

DAX 数式を使うケース

DAX 数式は、PowerPivot の計算列または計算 フィールド で作成できます。

計算列

計算列は、ユーザーが既存の PowerPivot テーブルに追加する列です。この列に値を貼り付けたり、インポートしたりする代わりに、列の値を定義する DAX 数式を作成します。ピボットテーブル (またはピボットグラフ) にこの PowerPivot テーブルを入れた場合、他のデータ列と同じようにこの計算列を使用できます。

計算列の数式は、Excel で作成した数式とよく似ています。ただし、Excel とは異なり、テーブル内の行ごとに異なる数式を作成することはできません。代わりに、DAX 数式が列全体に自動的に適用されます。

列に数式が入力されている場合、値は行ごとに計算されます。数式を作成すると同時に、その列の結果が計算されます。列の値の再計算は、その基となるデータが更新された場合または手動再計算を使った場合にのみ行われます。

メジャーやその他の計算列に基づく計算列を作成できます。ただし、計算列とメジャーには同じ名前を使用しないでください。これにより、結果がわかりにくい場合があります。列を参照する場合は、メジャーを誤って呼び出さないように、完全修飾列参照を使用することをお勧めします。

詳細については、「 target=”_blank” title=”Power Pivot の計算列” rel=”noopener”>Power Pivot の計算列」を参照してください。

メジャー

メジャーは、 PowerPivot データを使用するピボットテーブル (またはピボットグラフ) で使用するために特別に作成される数式です。メジャーは、COUNT や SUM などの標準の集計関数に基づくか、DAX を使用して独自の数式を定義できます。メジャーは、ピボットテーブルの [値] 領域で使用されます。計算結果をピボットテーブルの別の領域に配置する場合は、代わりに計算列を使用します。

明示的なメジャーの数式を定義する場合、ピボットテーブルにメジャーを追加するまでは何も発生しません。メジャーを追加すると、ピボットテーブルの [値] 領域の各セルに対して数式が評価されます。行ヘッダーと列ヘッダーの組み合わせごとに結果が作成されるため、メジャーの結果はセルごとに異なる場合があります。

作成したメジャーの定義は、ソースデータテーブルと共に保存されます。これはピボットテーブルのフィールドの一覧に表示され、ワークブックのすべてのユーザーが使用できます。

詳細については、「 target=”_blank” title=”Power Pivot のメジャー” rel=”noopener”>Power Pivot のメジャー」をご覧ください。

数式バーを使用した数式の作成

Excel PowerPivotと同じく、にも、数式の作成や編集を簡単に行うための数式バーと、入力や構文エラーを最小限に抑えるためのオートコンプリート機能があります。

テーブルの名前を入力するには テーブルの名前の入力を開始します。数式オートコンプリートには、これらの文字で始まる有効な名前を含むドロップダウンリストが用意されています。

列の名前を入力するには 角かっこを入力し、現在のテーブルの列の一覧から列を選択します。別のテーブルの列の場合は、テーブル名の最初の文字の入力を開始し、[オートコンプリート] ドロップダウンリストから列を選択します。

数式の作成方法の詳細とチュートリアルについては、「 target=”_blank” title=”Power Pivot での計算式の作成” rel=”noopener”>Power Pivot での計算式の作成」を参照してください。

オートコンプリート使用のヒント

入れ子になった既存の関数の途中で、数式オートコンプリートを使用できます。挿入ポイントの直前のテキストに基づいてドロップダウンリストの値が表示され、挿入ポイントの後のすべてのテキストは元のまま残ります。

ユーザーが定数に対して作成した定義名はオートコンプリートのドロップダウンリストには表示されませんが、入力することはできます。

PowerPivot には、関数の閉じかっこを追加する機能や、かっこを自動的に照合する機能はありません。各関数の構文に誤りがないことをユーザー自身が確認する必要があります。

1 つの数式における複数の関数の使用

関数を入れ子にできます。つまり、ある関数の結果を別の関数の引数として使用します。計算列には、最大 64 レベルの関数を入れ子にすることができます。ただし、入れ子にすると、数式の作成やトラブルシューティングが困難になる可能性があります。

多くの DAX 関数は、入れ子になった関数としてのみ使用するように設計されています。これらの関数はテーブルを返します。これは結果として直接保存することはできません。テーブル関数への入力として指定する必要があります。たとえば、関数 SUMX、AVERAGEX、MINX はすべて、最初の引数としてテーブルを必要とします。

注: 関数の入れ子に関するいくつかの制限は、列間の依存関係に必要な多くの計算によってパフォーマンスが影響を受けないように、メジャー内に存在します。

DAX 関数と Excel 関数の比較

DAX 関数ライブラリは Excel 関数ライブラリをベースにしていますが、両者のライブラリにはさまざまな違いがあります。このセクションでは、Excel 関数と DAX 関数の相違点の概要を説明します。

多くの DAX 関数は、Excel 関数と同じ名前と同じ一般的な動作を持ちますが、さまざまな種類の入力を受け取るように変更されており、場合によっては異なるデータ型を返す場合があります。一般に、Excel 数式で DAX 関数を使用したり、 PowerPivot で Excel 数式を使用したりすることはできません。
DAX 関数が参照としてセル参照や範囲を取ることはありませんが、列やテーブルは参照できます。
たとえば、DAX の日付と時刻の関数は、datetime データ型を返します。これに対して、Excel の日付と時刻の関数は、日付をシリアル番号として表した整数を返します。
新しい DAX 関数の多くは、一連の値から成るテーブルを返すか、入力として与えられた値のテーブルに基づいて計算を行います。一方、Excel にはテーブルを返す関数はありませんが、配列を処理できる関数はいくつかあります。テーブル全体や列を簡単に参照できる動作は、PowerPivot の新しい機能です。
DAX は、Excel の配列およびベクトル形式の LOOKUP 関数に類似した、新しい LOOKUP 関数を提供します。ただし、DAX 関数を使用するには、テーブルの間にリレーションシップを確立する必要があります。
列内のデータは、常に同じデータ型であることが想定されています。データが同じ型ではない場合、列全体が、すべての値に最もよく対応するデータ型に変更されます。

DAX のデータ型

PowerPivot データモデルにはさまざまなデータソースからデータをインポートできますが、これらのデータソースでサポートされているデータ型が異なることがあります。データをインポートまたはロードし、そのデータを計算またはピボットテーブルで使用する場合、このデータは PowerPivot データ型のいずれかに変換されます。データ型のリストについては、「 target=”_blank” title=”データモデルのデータ型” rel=”noopener”>データモデルのデータ型」を参照してください。

table は DAX の新しいデータ型で、さまざまな新しい関数の入力や出力として使われます。たとえば、FILTER 関数は入力としてテーブルを取り、フィルター条件に一致する行だけを含む新たなテーブルを出力します。テーブル関数と集計関数を組み合わせることにより、動的に定義されたデータセットに対して複雑な計算を実行できるようになります。詳細については、「 target=”_blank” title=”Power Pivot で使用する集計” rel=”noopener”>Power Pivot で使用する集計」を参照してください。

数式とリレーショナルモデル

PowerPivotウィンドウは、複数のデータテーブルを操作し、リレーショナルモデル内のテーブルを接続できる領域です。このデータモデル内では、テーブルはリレーションシップによって相互に接続されるため、他のテーブルの列との相関関係を作成し、より興味深い計算を作成できます。たとえば、関連するテーブルの値を合計し、その値を 1 つのセルに保存する数式を作成できます。または、関連するテーブルの行を制御するために、テーブルと列にフィルターを適用できます。詳細については、「 target=”_blank” title=”データモデル内のテーブル間のリレーションシップ” rel=”noopener”>データモデル内のテーブル間のリレーションシップ」を参照してください。

リレーションシップを使用してテーブルをリンクできるため、ピボットテーブルには、さまざまなテーブルにある複数の列からデータを取り込むこともできます。

ただし、数式はテーブルおよび列全体を操作できるため、計算式の組み立ては Excel とは異なります。

一般的に、列の DAX 数式は、常にその列にある値セット全体に適用されます。一部の行やセルだけに適用されることはありません。
PowerPivot のテーブルにある各行には必ず同じ数の列があります。また、1 つの列にあるすべての行には同じ型のデータが含まれます。
テーブルがリレーションシップにより接続されている場合、キーとして使用されている 2 つの列の値の大部分が一致していることを確認する必要があります。 PowerPivot では、参照の整合性は強制されていないため、キー列の値が一致していなくてもかまいませんし、リレーションシップを作成することもできます。ただし、空白や一致しない値のプレゼンスが、数式の結果やピボットテーブルの見た目に影響を与える可能性があります。詳細については、「 target=”_blank” title=”Power Pivot の数式での参照” rel=”noopener”>Power Pivot の数式での参照」を参照してください。
リレーションシップを使用してテーブルをリンクするときは、範囲、つまり数式が評価されるコン テキスト を拡大します。たとえば、ピボットテーブルの数式は、ピボットテーブルのフィルターまたは列見出しおよび行見出しによる影響を受けます。コンテキストを操作する数式を書くことはできますが、コンテキストが原因で、想定外の結果となることもあります。詳細については、「 target=”_blank” title=”DAX の数式のコンテキスト” rel=”noopener”>DAX の数式のコンテキスト」を参照してください。

数式の結果の更新

データ 更新と再計算は異なる動作ですが、複雑な数式、大量のデータ、または外部データソースから取得されたデータを含むデータモデルの設計時には理解していなければならない動作であるという点では、関連しています。

データの更新はワークブック内のデータを、外部データソースから得られた新しいデータでアップデートするプロセスです。データは指定した間隔で手動で更新できます。また、ワークブックを SharePoint サイトに公開している場合は、外部ソースから自動更新をスケジュールすることもできます。

target=”_blank” title=”再計算” rel=”noopener”>再計算は、数式自体に対する変更、または基になるデータに対する変更が反映されるように数式の結果を更新するプロセスです。再計算はパフォーマンスに次のような影響を与えます。

計算列では、数式が変更されるたびに、必ず列全体の数式の結果が再計算されます。
メジャーの場合、メジャーがピボットテーブルまたはピボットグラフのコンテキストに配置されるまで、数式の結果は計算されません。また、データに適用されたフィルターに影響を与える行見出しまたは列見出しを変更したとき、またはピボットテーブルを手動で更新したときにも、数式は再計算されます。

数式のトラブルシューティング

**数式を記述しているときのエラー **

数式を定義しているときにエラーが発生した場合、この数式には 構文エラー 、 セマンティックエラー 、 計算エラー のいずれかが含まれている可能性があります。

一番簡単に解決できるのは構文エラーです。多くの場合、かっこやカンマが抜けています。個々の関数の構文については、「DAX 関数リファレンス」を参照してください。

構文エラー以外のエラーは、構文は正しくても、値や参照されている列が、数式のコンテキストで意味をなしていないときに発生します。このようなセマンティックエラーや計算エラーの原因には、次のような問題が考えられます。

数式が、存在しない列、テーブル、または関数を参照しています。
数式は正しいように見えますが、データエンジンがデータをフェッチすると、型の不一致が判明し、エラーとなります。
数式から関数に、誤った数値または型のパラメーターが渡されています。
数式がエラーを含む別の列を参照しているため、その値が無効になっています。
数式はまだ処理されていない列を参照しています。つまり、メタデータはありますが、計算に使用できる実際のデータがありません。

1 つめから 4 つめのケースでは、DAX により、列全体に無効な数式が含まれているというフラグが設定されます。最後のケースでは、DAX はこの列を灰色で表示し、列が未処理の状態であることを示します。

列の値をランク付けまたは順序付けした場合の間違った結果または例外的な結果

値 NaN (Not a Number: 数字ではありません) を含む列をランク付け、または順序付けすると、間違った結果や例外的な結果となる可能性があります。たとえば、0 を 0 で割る計算が行われると、NaN という結果が返されます。

これは、数式エンジンは数値を比較して、順序付けとランク付けを行いますが、NaN は列内の他の数字とは比較できないためです。

正しい結果を得るために、IF 関数を使った条件文を使って、NaN 値をテストし、数値 0 を返すことができます。

Analysis Services 表形式モデルと DirectQuery モードの互換性

基本的に、PowerPivot で構築した DAX 数式は、Analysis Services 表形式モデルと完全な互換性があります。ただし、PowerPivot モデルを Analysis Services インスタンスに移行し、DirectQuery モードでモデルを展開する場合には、多少の制約があります。

DirectQuery モードでモデルを展開した場合、DAX 数式によっては、異なる結果が返されることがあります。
DirectQuery モードでモデルを展開すると、リレーショナルデータソースではサポートされていない DAX 関数を使った数式で検証エラーが発生する可能性があります。

詳細については、SQL Server 2012 BooksOnline にある Analysis Services 表形式モデルに関するマニュアルを参照してください。