Reverse TOPN の DAX 言語 LuckyTemplates チュートリアル

このブログ投稿では、LuckyTemplates フォーラムで寄せられた質問を取り上げ、DAX 言語の LuckyTemplates テクニックを使用して解決します。手順に従ってファイルをダウンロードしたい場合は、LuckyTemplates フォーラムにアクセスし、にアクセスしてください。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

これから説明するシナリオを説明するために、例として簡単な表を示します。最初の列には財務レベル 1があります。2 番目の列には、Completed という名前の日付があります。最後の列には財務レベルと完了日の組み合わせがあり、これをRevenueと名付けます。

次に、 DateとCustomer Nameのスライサーがあります。

特定の期間についてやりたいことは、ゼロではない上位 3 つの日付を特定することです。そして、これらの上位 3 つの日付で財務レベルを特定したいと考えています。

メジャーの最初のバージョンでは、ある程度の収益がある 2021 年 4 月 29 日よりも短い日付をリストしました。

そして、これらの日付で、ある程度の収益を上げた財務レベルを特定したいと考えています。どうすればその計算ができるのかを説明します。

目次

• データモデリング
• バージョン1
• フィルターコンテキストの準備
• DAX 言語 LuckyTemplates 計算の結果の検証
• その他の DAX 言語の LuckyTemplates 計算
• バージョン2
• KEEPFILTERS 関数の使用
• バージョン3
• 結論

データモデリング

実際の DAX を作成する前に、複数のテーブルを含むデータ モデルを簡単に見てみましょう。ここで関心があるのは、Date テーブル、Data テーブル、Job category テーブル、およびCustomer テーブルだけです。

Customer テーブルはスライサー内でのみ使用されるため、それほど重要ではないことに注意してください。

収益メジャーはデータ テーブルで計算されます。職種には、マトリックス上の財務レベルが含まれています。次に、スライサーの Date テーブルの Date 列を使用します。

バージョン1

マトリックスからすべてを削除して、最初から始めましょう。新しいメジャーを作成し、これがバージョン 1 になるので、それを V1 と呼びます。他の 2 つのバリエーションも見ていきます。

フィルターコンテキストの準備

最初に行うことは、フィルター コンテキストから財務レベルのすべての値を取得することです。作成し、 FinancialLevelInFilterContextという名前を付けます。

フィルター コンテキストから値を取得するには、財務レベル列を含むジョブ カテゴリ テーブルでを使用します。次に、別の変数を作成し、 Resultという名前を付ける必要があります。次に、記述し、同様に閉じます。

最初の引数では、日付スライサーを選択します。これらの日付の中で、0 がなく、少なくともある程度の収益がある日付を特定したいと考えています。

そのスライサーで選択されているすべての日付にアクセスしたい場合は、Date テーブルに対して

また、マトリックスに財務レベルがあるため、フィルター コンテキストからその特定の値を確実に削除する必要もあります。Job category に対してを使用して、Financial Level 1 を書き込むことができます。

フィルター コンテキストが準備されたので、CALCULATE の最も内側の計算を作成できます。まず、最初の表で見たように、財務レベルと日付列を組み合わせた変数を宣言します。

を使用し、データ テーブルを使用して、これら 2 つのテーブル (職種テーブルと日付テーブル) を要約します。

これにより、データ テーブル内に存在する財務レベルと日付列の組み合わせが提供されます。次に、作成した最初の変数の収益行の値を保存する別の変数を作成します。

この変数の名前はFinancialLevelAndDatesWithRevenueになります。この変数のコードは、前の変数の次に、コンテキスト遷移を開始するために、収益となる仮想列を作成します。したがって、この変数の各行に収益を割り当てました。

このテーブルには収益が含まれていますが、これもゼロです。これらの行をゼロで除外する必要があります。これを行うには、別の変数を作成し、 RemoveZeroesという名前を付けます。

を使用し、収益がゼロにならないことを確認します。ゼロより大きくてもゼロより小さくてもかまいませんが、厳密にゼロに等しくすることはできません。

次に、 RemoveZeroes 変数から日付を取得します。これを行うには、別の変数を作成し、それにKeepOnlyDatesという名前を付けます。

を使用すると、によって返される重複した日付を取り除くことができます。

次に、上位 3 つの日付を降順で特定します。別の変数を使用してLast3Datesという名前を付け、 を使用します。

次に、日付列の最後の 3 つの日付に対してを使用し、区切り文字を使用します。

新しく作成したメジャーをそのマトリックス内にドラッグ アンド ドロップしてみましょう。得られる結果は、2021/03/31、2021/07/04、2021/04/02 です。これは、私たちの対策が機能していることを意味します。

エディターに戻って RETURN CONCATENATEX 部分を削除しましょう。これらの最後の 3 つの日付を取得するには、 RemoveZeroes変数のどの行が実際にこれらの最後の 3 つの日付の一部であるかを確認する必要があります。

そのために、別の変数DatesInLast3Datesを作成し、RemoveZeroesで FILTER 関数を使用します。これにより、職種と日付で構成されるテーブルが返されます。

そのテーブル内に作成した収益列を合計します。DatesInLast3Dates に対して SUMX を返し、収益列を要約してみましょう。

この計算では、フィルター コンテキストからジョブ カテゴリを削除したため、各行で 1 つの値のみが取得されます。

最初に作成した変数を使用して、DatesInLast3Dates 変数の各行がフィルターコンテキストで使用できるかどうかを確認します。別の変数を作成し、IsInFilterContextという名前を付けることができます。

これにより、 DatesInLast3Dates変数内の日付がフィルターされます。次に、IsInFilter コンテキストで SUMX を返し、収益列を要約します。

Enterを押すと、最初と同じ結果が得られることがわかります。

DAX 言語 LuckyTemplates 計算の結果の検証

実際に正しい結果が得られていることを確認するには、これまでに作成したコードが実際に正しい結果を返しているかどうかのデバッグ、識別、検証に役立つ新しい CALCULATE テーブルを作成します。

これまでに書いたコードのコピーを作成してみましょう。以下で強調表示されている部分をコピーし、戻って新しいテーブルを作成し、強調表示されたコードを貼り付けます。このテーブルには興味がないので、わざわざ名前を付けるつもりはありません。

そして、単純に RETURN DatesInLast3Datesと書くことができます。[確認]をクリックするとテーブルが表示されますが、実際にはスライサーの動作を模倣する必要があるため、実際にはまだ正しい結果が返されていないことがわかります。

そのコードを CALCULAETABLE 内にラップし、すべてをインデントできます。最後の部分では、日付は 2021-03-15 以上である必要があり、顧客名はDHL Supply Chainと同じである必要があることを書きます。

そのメジャー内に作成したテーブルは、事実上、正しい結果を返しています。CALCULAETABLE を使用して、計算内で作成している仮想テーブルを検証できます。

その他の DAX 言語の LuckyTemplates 計算

計算が機能していることがわかり、舞台裏で実際に何が起こっているかを理解したので、同じ問題を計算するための他の方法に目を向けることができます。まず、すでに作成したメジャーの複製を作成しましょう。

バージョン2

今回は、VALUES 関数と FILTER 関数に依存しないので、それらを削除します。返された結果と、CALCULATE 内で作成した最後の変数を削除します。次に、CALCULATE を CALCULATETABLE に変換します。

そして、今回はスカラー値を返す代わりに、 DatesInLast3Datesを返します。これにより、前に作成したものと同じ CALCULAETABLE が返されます。CALCULATE 関数を使用し、最初の引数で収益メジャーを計算し、フィルター コンテキストとして CALCULATETABLE を挿入します。

これを行列に取り込むと、各セルに同じ総計値が返されていることがわかります。

KEEPFILTERS 関数の使用

現時点では、舞台裏での計算が正しく機能していることはわかっていますが、各セルについて、その財務レベルの値のみを報告していることをどのようにして確実に知ることができるのでしょうか?

CALCULAETABLE を使用して、財務レベル、日付列、収益列を含むテーブルを作成しました。キー フィルター関数を使用すると、CALCULAETABLE の外部に存在するフィルター コンテキストと CALCULAETABLE によって作成されるフィルター コンテキストとの間の共通部分を作成できます。

[確認] をクリックすると、各セルの正しい値が報告されていること、およびバージョン 1 とバージョン 2 の両方が正しい値を返していることがわかります。

では、これはどのように機能するのでしょうか? CALCULATE テーブルは、メンテナンスレベル、次にレンタル、サービス外部、およびスペアパーツを返します。KEEPFILTERSメンテナンスとそのテーブルの間に交差を作成します。

Rental は初期フィルター コンテキストを作成し、CALCULAETABLE はジョブ カテゴリのすべての値を返します。次に、Rental と CALCULATETABLE によって返されるテーブルの間に共通部分が存在します。そのレンタル部分のみのテーブルまたは行のみを返します。

そして最後に、フィルター コンテキストに SUMMARIZE を挿入すると、CALCULATE 関数はレンタルの収益のみを計算します。同じプロセスが各行で発生します。

バージョン3

DAX 言語 LuckyTemplates を使用して同じ計算を行う別の方法を見てみましょう。バージョン 2 メジャーのコピーを作成し、別のメジャーを作成しましょう。これをバージョン 3 と名付けます。

この計算の背後にある考え方は、CALCULATE 関数を使用して収益を計算しているため、CALCULATE の内部と外部で同じものを複製しているため、ADDCOLUMNS 部分を使用する必要がないということです。代わりに、収益が 0 に等しくないことを単純に書くことができます。

[確認] をクリックしたら、コードが機能していることを確認する必要があるため、マトリックス内にメジャーをドラッグします。

コードが実際に各セルに同じ値を返していることがわかります。

日付スライサーで日付を変更し、顧客名からいくつかの値を選択してフィルター コンテキストを変更しようとすると、実際には 3 つのメジャーすべてが各行に同じ値を返していることがわかります。

結論

このチュートリアルでは、終了日から過去に遡って上位 3 つの日付を特定する複雑なロジックを使用する方法を学びました。この DAX 言語の LuckyTemplates 手法に基づいて、収益を計算し、フィルター コンテキストに存在する値のみを表示することができました。このチュートリアルではこれですべてです。