LuckyTemplates の展開されたテーブル

このチュートリアルでは、DAX を使用しているときに誰もがすでに遭遇したことがある非常に具体的なシナリオ、つまり展開されたテーブルを見ていきます。テーブルが実際にどのように機能するかを理解していないと、テーブルを展開すると計算が混乱する可能性があります。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

この例では、Contoso データベースを使用しています。このデータベースには、sales テーブル、customer テーブル、date テーブル、products テーブル、products category テーブル、products subcategory tableが含まれています。

左端では、製品、カテゴリ、サブカテゴリ、売上テーブルの間に 1 対多の関係があります。また、日付と売上だけでなく、顧客と売上の間にも 1 対多の関係があります。

目次

• 例1
• 例2
• レッドセールスナンバー1
• レッドセールス #2
• レッドセールス #3
• 拡張されたテーブルの検証
• 拡張テーブルの定義
• カテゴリ列をスライスして展開されたテーブルを使用する
• レッドセールス2の解説
• レッドセールス3の解説
• DAX Studio を使用して展開されたテーブルを検証する
• 結論

例1

カテゴリ テーブルのこのカテゴリ列からレポートを作成し、顧客テーブルに存在する顧客の数を計算するメジャーを使用するとします。顧客のテーブルの数値をカテゴリごとにスライスしたいと考えています。

カテゴリ テーブルにフィルタを設定すると、そのフィルタはサブカテゴリ テーブルに移動し、次に製品テーブルに到達し、最後に売上テーブルに到達してフィルタリングされます。ただし、双方向フィルタリングをオンにしない限り、そのフィルタは顧客のテーブルをフィルタリングできません。

レポートに戻ってこの計算を修正しましょう。この計算を CALCULATE 関数内にラップして、Sales を記述することができます。今回は、ビジュアルの各セルで同じ数字を繰り返していないことがわかります。

この計算が正しいかどうかを確認するには、販売テーブルから顧客キーを取得して集計し、それに対してカウント (個別) を実行します。

各行に同じ値が返されていることがわかります。CALCULATE 関数内で sales テーブルを使用することで、計算を修正することができました。

例2

次の例に進みましょう。この例では、2007 年、2008 年、または 2009 年の赤い製品の売上高を特定したいと考えています。スライサーの選択に応じて、1 年前に戻ることもできます。

たとえば、2009 年を選択した場合、2008 年の赤色の売上をレポートしたいと考えます。2008 年を選択した場合、2007 年の赤色の売上もレポートしたいと考えます。

レッドセールスナンバー1

当社の総売上高の計算は、基本的に売上データの合計です。行コンテキストでは、数量に正味価格を掛けます。

新しいメジャーを作成し、「Red Sales」という名前を付けてみましょう。「CALCULATE」、「Total Sales」の順に書きます。Sales テーブルをして、製品の色が赤と等しいことを示します。

次に、日付列の日付にを書き込みます。

その計算をカードビジュアルに持ち込むと、空白が得られます。

レッドセールス #2

ここで何が起きてるの？その計算を書き直して、結果が得られるかどうかを確認してみましょう。新しいメジャーを作成して Red Sales 2 という名前を付け、計算の最初の部分を Red Sales 1 に使用します。

最初の CALCULATE の上に別の CALCULATE を開始します。

このメジャーをマトリックスに取り入れて、結果を見てみましょう。2008 年を選択すると、51,947 になります。2009 を選択すると、24,343 になります。最後に、2010 を選択すると、39,724 になります。

レッドセールス #3

この計算を書く別の方法もあります。新しいメジャーを作成して Red Sales 3 という名前にし、CALCULATE 関数を使用します。

総売上高を計算し、製品の色は赤に等しいと書き込みます。次に、日付に対して SAMEPERIODLASTYEAR 関数を使用します。

メジャー #3 をカード ビジュアルにドラッグすると、これら 2 つのカード値が同じ値を返していることがわかります。これは正しい値です。

しかし、正しい値ではなく空白を返しているため、最初の計算には何か問題があります。

ここで実際に何が起こっているのか見てみましょう。展開されたテーブルに関するいくつかの計算を見てきたので、その背後にある理論を理解しましょう。

拡張テーブルとは何かを理解する前に、ここにあるすべてのテーブルがベース テーブルと呼ばれることを理解する必要があります。

それでは、これらのテーブルはいつ拡張テーブルになるのでしょうか? あるテーブルと別のテーブルの間に多対 1 のリレーションシップを作成すると、ベース テーブルが拡張テーブルになります。

拡張されたテーブルの検証

しかし、テーブル拡張が実際に行われていることをどうやって確認できるのでしょうか? どのテーブルでも関連キーワードを使用できます。片側から列にアクセスできる場合は、テーブルの拡張が発生する可能性があることがわかります。

sales テーブルに移動して、新しい計算列を作成しましょう。

この特定のプロダクト キーの製品テーブルから製品の色を取得するとします。RELATED を使用します。これは、sales テーブルから実際に展開できる IntelliSense 内の列のリストのみを提供します。

customer テーブルと sales テーブルには多対 1 の関係があることがわかります。customer テーブルの列、日付テーブルの列、product 列のリストも確認できます。

Products[Color] を選択すると、RELATED キーワードを使用して sales テーブルに新しい列を作成できます。RELATED では、ベース テーブルが実際に展開できるテーブルの列にのみアクセスできます。

その関係の性質を多対 1 から多対多に変更すると、その計算は機能しなくなります。

この関係の性質を多対多に変更しましょう。下部に警告記号が表示されていることがわかります。

sales テーブルに戻ると、Products[Color] 列が存在しないか、どのテーブルとも関係がないことを示すエラー メッセージが表示されます。

現在の状況では、表示される警告はあまりユーザーフレンドリーではありません。基本的に、これは、特定の行項目の 1 つの値にしかアクセスできないため、sales テーブルを product テーブルに拡張できないことを意味します。

また、多対多のリレーションシップを使用しているため、テーブルの拡張は行われず、RELATED は機能しません。

ダイアグラム ビューに戻って、この計算を修正しましょう。リレーションシップの性質を多対 1 に変更し、そのリレーションシップをアクティブにして計算を機能させます。

拡張テーブルの定義

レポートですでに実行した計算を確認する前に、展開されたテーブルの定義を繰り返してみましょう。

スター スキーマを備えたデータ モデルがある場合、ディメンションとファクト テーブルの間に多対 1 の関係がある場合、ファクト テーブルはデータ モデル内のすべてのテーブルに展開されます。

スノーフレーク スキーマがある場合、製品サブカテゴリ テーブルとカテゴリ テーブルはベース テーブル (この場合は製品テーブル) に展開されます。sales テーブルはベース テーブルであり、他のすべてのテーブルに拡張されます。

舞台裏では、sales テーブルにはすべての列が 1 つのテーブルに含まれます。テーブルの拡張は単なる論理的な概念であるため、データ モデルのサイズが拡張され増加するわけではないことに注意してください。

展開されたテーブルが登場するのは、テーブル (ベース テーブル) を参照する場合、および他のテーブルと多対 1 の関係がある場合のみです。

カテゴリ列をスライスして展開されたテーブルを使用する

レポート ビューですでに行った計算を修正してみましょう。この例では、製品カテゴリ テーブルからカテゴリ列でスライスし、顧客の数を数えようとしています。

それで、私たちはここで実際に何をしているのでしょうか？拡大売上表を参考にさせていただいております。フィルター コンテキストにカテゴリ テーブルの値が含まれると、そのフィルターはサブカテゴリ製品から売上テーブルに到達し、売上に直接到達します。

sales は拡張テーブルであり、CALCULATE 関数内でその参照を使用しているため、sales テーブルには customer テーブルの列も含まれることになります。sales テーブルにフィルタを適用すると、間接的に customer テーブルもフィルタリングされます。

赤の売上計算に戻って、実際に何が起こっているのかを理解してみましょう。空の計算から始めます。そのメジャーを選択すると、ネストされたコードを記述していることがわかります。CALCULATE、次に Total Sales、売上に対する FILTER、および SAMEPERIODLASTYEAR があります。

この計算を段階的に分解してみましょう。まず、計算の対象外に存在する外部フィルター コンテキストを特定する必要があります。

暦年番号スライサーで 2008 を選択しました。

このフィルター コンテキストから、売上テーブルが評価されます。売上テーブルには 2008 年の暦年の行のみが含まれ、製品の色は赤になります。ここには 2 つのフィルターがあります。1 つはフィルター コンテキストによって作成され、もう 1 つはfilter によって作成されます。

SAMEPERIODLASTYEAR は、年が 2008 であるフィルター コンテキストで評価されます。これは 2008 年の日付のリストを受け取り、2008 年の日付を 2007 年にシフトします。これによって返されるテーブルには、次の内容のみが含まれます。 2007 年の日付。

これら 2 つの操作が完了すると、CALCULATE 関数はフィルター コンテキストを準備し、これら 2 つのフィルターをフィルター コンテキストに適用します。これを適用すると、製品の色が赤に等しいフィルター コンテキストが得られ、年の列には 2007 年と 2008 年のフィルターが設定されます。

したがって、このデータ モデルでは、2 つの異なる年に存在する単一のトランザクションは存在しません。CALCULATE がこれら 2 つのフィルターをand条件でマージしようとすると、年は 2008 年と 2007 年である必要があり、製品の色は赤である必要があると表示されます。

2008 年のフィルター コンテキストを使用して sales テーブルをフィルターすると、間接的に日付テーブルもフィルターされます。販売安定と日付安定の関係は多対一の関係にあります。

新しい計算を作成して、日付テーブルに関連する日付の行数を特定してみましょう。CALCULATEを書いてから日付テーブルの次に、sales テーブルのすべてをフィルター処理し、暦年番号の RELATED 日付が 2008 年に等しくなるようにします。

CALCULATE 内では、日付テーブルに対するフィルターを参照していません。暦年番号の日付が 2008 年からのものであることを確認しているだけです。理想的には、このフィルターは日付テーブルをフィルターできません。また、関数も使用しています。これは、スライサーからのフィルター コンテキストを無視します。

この計算用に新しいカードを作成しましょう。348 行を返していることがわかります。

では、2500 行の日付テーブルから 348 行だけが返されるのはなぜでしょうか? 展開されたテーブルを使用する場合は、多対 1 の関係を通じて接続されている他のテーブルも間接的にフィルタリングすることになります。

現在の年にはフィルターはありませんが、片側にあるディメンション テーブルに表示される行数は依然として制限されています。

sales テーブルを返すとき、日付テーブル、customer テーブル、product テーブル、product category テーブル、product subcategory テーブルのフィルタリングされたバージョンも返します。

レッドセールス2の解説

次の計算 (Red Sales 2) に進みましょう。 外側の CALCULATE から始めます。これは、いずれかのシナリオでこの関数をネストする場合、外側の CALCULATE で内側の CALCULATE のフィルター コンテキストを準備する必要があるためです。

スライサーでは、2008 年の暦年を選択します。SAMEPERIODLASTYEAR 関数が 2008 年の日付を取得すると、それらの日付が 2007 年にシフトされ、それが内部計算の FILTER コンテキストになります。この内部の CALCULATE は 2007 年を評価します。

FILTER 関数は売上表をフィルタリングし、売上表は年が 2007 年の行のみに制限されます。2007 年の行を取得したら、関連する製品が赤に等しいことを確認します。

最初の計算とは異なり、2 つの異なる層は返されません。これら 2 つの異なる層は FILTER コンテキストには適用されないため、FILTER 関数によって返されるテーブルには sales テーブルのすべての行が含まれます。

このフィルターを適用すると、2007 年の売上高と赤色に等しい製品が取得されます。一方、最初の計算では 2008 年と 2007 年が返されていました。

今回は、SAMEPERIODLASTYEAR 関数を使用し、CALCULATE 関数を別の CALCULATE 内にネストすることにより、sales テーブルのフィルター コンテキストを準備しました。

レッドセールス3の解説

3 番目の例では、2 つのステートメントを含む非常に単純な CALCULATE 関数があります。1 つは製品の色が赤に等しいというもので、もう 1 つは日付の SAMEPERIODLASTYEAR です。

SAMEPERIODLASTYEAR 関数は、2007 年の日付のみを含むフィルター コンテキストで評価されます。製品の色は、製品テーブルに赤のフィルター コンテキストを適用します。これにより、売上テーブルがフィルターされ、2007 年の日付のみが含まれます。赤い製品。

これら 2 つの値がフィルター コンテキストで適用されると、sales テーブルはフィルターされますが、展開された sales テーブルはフィルターされません。コードのどこにも展開された sales テーブルを参照していないことがわかります。

これは、展開されたテーブルを参照しようとする場合に使用する理想的なシナリオです。展開されたテーブルを使用すると、計算が非常に複雑になる可能性があり、場合によっては、計算が間違った結果を返す理由を特定できないことがあります。

拡張テーブルの概念を理解していない場合は、この特定のコードをモデル メジャーとして開発し、実稼働環境にデプロイする可能性があります。他のユーザーは、このメジャーを超えて別のメジャーの作成を開始する可能性がありますが、DAX 言語についてそのレベルの理解がないため、計算が機能しない理由を理解できない可能性があります。

DAX を使用している場合は、常に単一の列にフィルターを適用するようにしてください。単一の列を使用すると、展開されたテーブルの概念が適用されないためです。たとえば、PRODUCTS [Color] = “Red”を使用すると、フィルターは sales テーブルに到達しますが、拡張された sales テーブルを使用していないため、そのフィルターは顧客のテーブルに到達できません。

単一列を使用する場合、拡張テーブルの概念は参照しません。sales テーブルに基本的なフィルターを適用しているだけであり、他のテーブルには反映されません。

DAX Studio を使用して展開されたテーブルを検証する

最後に、DAX スタジオを使用して Red Sales 3 のコードを検証します。 [表示] タブに移動し、[パフォーマンス アナライザー] をクリックして、記録を開始しましょう。そのビジュアルを更新し、空のカードを選択します。

外部ツールからDAX Studioを起動します

下部の結果画面では、空白が表示されていることがわかります。

サーバー タイミングをオンにして、舞台裏で生成されているクエリを理解します。[サーバー タイミング] タブに移動し、展開してすべてを表示します。

左側の販売テーブルで JOIN を実行しています。また、プロダクト テーブルからプロダクト キーに対して JOIN も実行しています。

WHERE 句では、暦年の数値の日付は 2008 に等しく、製品の色は赤である必要があると指定されています。

次に、Dates[Date] の後に日付の範囲が続く別の条件があります。それで、これらの日付は何ですか? 理想的には、これらの日付は 2008 年のものである必要がありますが、Excel でこれらの数値の書式設定を変更すると、2007 年のものになります。

このため、空白の値が返されます。暦年は 2008 年であるべきだとし、日付は 2007 年であるべきであるという AND 条件を適用します。この 2007 年は、計算で使用した SAMEPERIODLASTYEAR 関数によるものです。

メジャーと参照を Red Sales 2 に変更しましょう。コードを実行すると、日付テーブルから日付を取得し、2008 年の暦年番号で日付列をフィルターしていることがわかります。

今回は、WHERE 句にフィルターを適用しておらず、暦年の数値の日付が 2008 に等しいはずだとしているため、結果を返すことができます。

隠された DAX トラップ
を見つける場所 LuckyTemplates で DISTINCTCOUNT を使用して顧客を長期的にカウントする
高度な DAX を使用した新規顧客分析

結論

このチュートリアルが、拡張テーブルとは何か、そしてそれがどのように計算を混乱させるのかを理解するのに役立つことを願っています。展開されたテーブルは直感的ではなく、理解するのも簡単ではありません。

拡張テーブルの詳細については、LuckyTemplates TV チャンネルに登録することを忘れないでください。私自身やさまざまなコンテンツ作成者からの膨大な量のコンテンツが常に公開されており、すべて LuckyTemplates と Power Platform の使用方法を改善することに専念しています。