ORDERBY、PARTITIONBY、および MATCHBY 関数について

DAXのORDERBY、PARTITIONBY、およびMATCHBY関数は、INDEX、OFFSET、WINDOW、RANK、ROWNUMBERなどのDAXウィンドウ関数と共にのみ使用できる特殊な関数です。

Window 関数を正常に使用するには、 ORDERBY、 PARTITIONBY、および MATCHBY について理解することが重要です。 ここで示す例では OFFSETを使用していますが、他の Window 関数にも同様に適用できます。

シナリオ

まず、Window 関数をまったく使用しない例から始めましょう。 次に示すのは、カレンダー年ごとの合計売上 (色ごと) を返す表です。 このテーブルを定義する方法は複数ありますが、 DAXで何が起こるかを理解することに関心があるため、計算テーブルを使用します。 テーブル式を次に示します。

BasicTable = 
    SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
    )

この計算テーブル式では、SUMMARIZECOLUMNS を使用して、FactInternetSales テーブルの SalesAmount 列の SUM を計算します。この計算では、DimProduct テーブルの Color 列と DimDate テーブルの CalendarYear 列が利用されます。 結果を次に示します。

色	‘Date’[CalendarYear],	当年度の売上高
"黒"	2017	393885
"黒"	2018	1818835
"黒"	2019	3981638
"黒"	2020	2644054
ブルー	2019	994448
"青"	2020	1284648
"多色"	2019	48622
"多色"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"赤"	2017	2961198
"赤"	2018	3686935
"赤"	2019	900175
"赤"	2020	176022
"シルバー"	2017	326399
"シルバー"	2018	750026
"シルバー"	2019	2165176
"シルバー"	2020	1871788
"白"	2019	2517
"白"	2020	2589
"黄"	2018	163071
"黄"	2019	2072083
"黄"	2020	2621602

それでは、色ごとに売上の差を前年比で計算するビジネス上の問題を解決しようとしているとします。 実質的に、前年の同じ色の売上を検索し、コンテキストで現在の年の売上からその売上を減算する方法が必要です。 たとえば、[Red, 2019] の組み合わせについては、[Red, 2018] の売上を探しています。 それを取得したら、現在の売上からそれを減算し、必要な値を返すことができます。

OFFSET の使用

OFFSET は、上記のビジネス上の質問に答えるために必要な以前の種類の計算との一般的な 比較 に最適です。相対移動を行うことができます。 最初の試みは次のようになります。

1stAttempt = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation
            ),
            [CurrentYearSales]
        )
    )

この式では多くのことが起こっています。 ADDCOLUMNSを使用して、PreviousColorSales という名前の列を使用して以前のテーブルを展開しました。 その列の内容は、前の Color (OFFSET を使用して取得) の CurrentYearSales (SUMFactInternetSales[SalesAmount]) に設定されます。

結果は次のとおりです。

色	‘Date’[CalendarYear],	CurrentYearSales	PreviousColorSales
"黒"	2017	393885
"黒"	2018	1818835	393885
"黒"	2019	3981638	1818835
"黒"	2020	2644054	3981638
"青"	2019	994448	2644054
"青"	2020	1284648	994448
"多色"	2019	48622	1284648
"多色"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"赤"	2017	2961198	227295
"赤"	2018	3686935	2961198
"赤"	2019	900175	3686935
"赤"	2020	176022	900175
"シルバー"	2017	326399	176022
"シルバー"	2018	750026	326399
"シルバー"	2019	2165176	750026
"シルバー"	2020	1871788	2165176
"白"	2019	2517	1871788
"白"	2020	2589	2517
"黄"	2018	163071	2589
"黄"	2019	2072083	163071
"黄"	2020	2621602	2072083

これは目標に一歩近づいていますが、よく見ると私たちが求めているものと完全に一致しているわけではありません。 たとえば、[Silver, 2017] の場合、PreviousColorSales は [Red, 2020] に設定されます。

ORDERBY の追加

上記の定義は次のようになります。

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color], ASC, [CalendarYear], ASC, [CurrentYearSales], ASC)
            ),
            [CurrentYearSales]
        )
    )

この場合、 OFFSET の呼び出しでは、 ORDERBY を使用してテーブルを Color と CalendarYear で昇順に並べ替えます。これにより、返される前の行と見なされる内容が決まります。

これら 2 つの結果が同等である理由は、 ORDERBY には、 PARTITIONBYに含まれていないリレーションシップのすべての列が自動的に含まれているためです。 PARTITIONBYが指定されていないため、ORDERBYは Color、CalendarYear、CurrentYearSales に設定されます。 ただし、リレーションシップ内の Color と CalendarYear のペアは一意であるため、CurrentYearSales を追加しても結果は変更されません。 実際、 ORDERBYで色のみを指定した場合でも、CalendarYear が自動的に追加されるため、結果は同じです。 これは、ORDERBY列とPARTITIONBY列で各行を一意に識別できるように、関数が必要な数の列をORDERBYに追加するためです。

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS(
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

PARTITIONBY の追加

さて、次の計算テーブル式に示すように、PARTITIONBYを使用することで、私たちが目指す結果にほぼ到達することができます。

UsingPARTITIONBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]), 
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

ORDERBYには、PARTITIONBYで指定されていないリレーションシップのすべての列が自動的に含まれるため、ORDERBYの指定は省略可能であることに注意してください。 そのため、次の式は、 ORDERBY が CalendarYear と CurrentYearSales に自動的に設定されているため、同じ結果を返します。

UsingPARTITIONBYWithoutORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

注

ORDERBYは CalendarYear と CurrentYearSales に自動的に設定されますが、追加される順序については保証されません。 CurrentYearSales が CalendarYear の前に追加された場合、結果の順序は想定どおりのインラインではありません。 混乱や予期しない結果を避けるために、 ORDERBY と PARTITIONBY を指定するときは明示的に指定してください。

どちらの式も、目的の結果を返します。

色	‘Date’[CalendarYear],	本年度の売上	PreviousYearSalesForSameColor
"黒"	2017	393885
"黒"	2018	1818835	393885
"黒"	2019	3981638	1818835
"黒"	2020	2644054	3981638
ブルー	2019	994448
"青"	2020	1284648	994448
"多色"	2019	48622
"多色"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"赤"	2017	2961198
"赤"	2018	3686935	2961198
"赤"	2019	900175	3686935
"赤"	2020	176022	900175
"シルバー"	2017	326399
"シルバー"	2018	750026	326399
"シルバー"	2019	2165176	750026
"シルバー"	2020	1871788	2165176
"白"	2019	2517
"白"	2020	2589	2517
"黄"	2018	163071
"黄"	2019	2072083	163071
"黄"	2020	2621602	2072083

この表に示すように、PreviousYearSalesForSameColor 列には、同じ色の前年の売上が表示されます。 [Red, 2020] の場合、[Red, 2019]などの売上が返されます。 前年がない場合 (たとえば、[Red, 2017]の場合)、値は返されません。

PARTITIONBYは、テーブルをOFFSET計算を実行する部分に分割する方法と考えることができます。 上の例では、テーブルは色がある限り多くの部分に分割され、色ごとに 1 つずつです。 次に、各パーツ内で、 OFFSET が CalendarYear で並べ替えられて計算されます。

視覚的には、何が起こっているかは次のとおりです。

まず、 PARTITIONBY 呼び出すと、テーブルは色ごとに 1 つずつ、パーツに分割されます。 これは、表の画像の水色のボックスで表されます。 次に、 ORDERBY では、各パーツが CalendarYear (オレンジ色の矢印で表される) で並べ替えられていることを確認します。 最後に、並べ替えられた各部分内で、各行について、 OFFSET その上の行を検索し、PreviousYearSalesForSameColor 列でその値を返します。 各パーツの最初の行ごとに、その同じ部分に前の行がないため、PreviousYearSalesForSameColor 列のその行の結果は空になります。

最終的な結果を得るには、 OFFSETの呼び出しによって返されたのと同じ色に対して、前年の売上から CurrentYearSales を減算するだけです。 前年の売上を同じ色で表示することには関心がないため、現在の年の売上と年度の差のみを表示します。 最終的な計算テーブル式を次に示します。

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

その式の結果を次に示します。

色	‘Date’[CalendarYear],	CurrentYearSales	YoYSalesForSameColor
"黒"	2017	393885	393885
"黒"	2018	1818835	1424950
"黒"	2019	3981638	2162803
"黒"	2020	2644054	-1337584
"青"	2019	994448	994448
"青"	2020	1284648	290200
"多色"	2019	48622	48622
"多色"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"赤"	2017	2961198	2961198
"赤"	2018	3686935	725737
"赤"	2019	900175	-2786760
"赤"	2020	176022	-724153
"シルバー"	2017	326399	326399
"シルバー"	2018	750026	423627
"シルバー"	2019	2165176	1415150
"シルバー"	2020	1871788	-293388
"白"	2019	2517	2517
"白"	2020	2589	72
"黄"	2018	163071	163071
"黄"	2019	2072083	1909012
"黄"	2020	2621602	549519

MATCHBY の使用

MATCHBYをまったく指定しなかったことに気付いたかもしれません。 この場合、これは必要ありません。 ORDERBYとPARTITIONBYの列 (上記の例で指定した場合) は、各行を一意に識別するのに十分です。 MATCHBYを指定していないため、ORDERBYとPARTITIONBYで指定された列を使用して各行を一意に識別し、OFFSETを比較して意味のある結果を得ることができます。 ORDERBYおよびPARTITIONBYの列が各行を一意に識別できない場合は、追加の列で各行を一意に識別できる場合は、ORDERBY句に追加の列を追加できます。 それが不可能な場合は、エラーが返されます。 この最後のケースでは、 MATCHBY を指定すると、エラーの解決に役立つ場合があります。

MATCHBYを指定すると、MATCHBYとPARTITIONBYの列を使用して各行が一意に識別されます。 それが不可能な場合は、エラーが返されます。 MATCHBY必要ない場合でも、混乱を避けるためにMATCHBYを明示的に指定することを検討してください。

上記の例に引き続き、最後の式を次に示します。

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

行を一意に識別する方法を明示的に指定する場合は、次の同等の式に示すように MATCHBY を指定できます。

FinalResultWithExplicitMATCHBYOnColorAndCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([Color], [CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

MATCHBYが指定されているため、MATCHBYで指定された列とPARTITIONBY内の列の両方が、行を一意に識別するために使用されます。 Color は MATCHBY と PARTITIONBYの両方で指定されているため、次の式は前の式と同じです。

FinalResultWithExplicitMATCHBYOnCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

これまでに説明した例では MATCHBY を指定する必要がないため、 MATCHBYを必要とする少し異なる例を見てみましょう。 この場合、注文明細行の一覧が表示されます。 各行は、注文の注文明細行を表します。 注文には複数の注文明細行が含まれることができ、注文明細行 1 は多くの注文に含まれることがあります。 さらに、注文明細行ごとに ProductKey と SalesAmount があります。 テーブルの関連する列のサンプルは次のようになります。

SalesOrderNumber	SalesOrderLineNumber	プロダクトキー	売上金額
SO51900	1	528	4.99
SO51948	1	528	5.99
SO52043	1	528	4.99
SO52045	1	528	4.99
SO52094	1	528	4.99
SO52175	1	528	4.99
SO52190	1	528	4.99
SO52232	1	528	4.99
SO52234	1	528	4.99
SO52234	2	529	3.99

行を一意に識別するには、SalesOrderNumber と SalesOrderLineNumber の両方が必要であることに注意してください。

各注文について、SalesAmount によって降順に並べ替えられた同じ製品 (ProductKey で表される) の前の売上金額を返します。 次の式は、 OFFSETに渡されるため、vRelation に複数の行が存在する可能性があるため、機能しません。

ThisExpressionFailsBecauseMATCHBYIsMissing = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

この式は、"OFFSETの Relation パラメーターに重複する行が含まれている可能性があり、これは許可されていません" というエラーを返します。

この式を機能させるには、 MATCHBY を指定し、行を一意に定義するすべての列を含める必要があります。 MATCHBY リレーションシップ FactInternetSales には明示的なキーまたは一意の列が含まれていないため、ここで必要です。 ただし、SalesOrderNumber 列と SalesOrderLineNumber 列は 複合キーを形成します。このキーの存在はリレーションシップ内で一意であるため、各行を一意に識別できます。 両方の列に繰り返し値が含まれるので、SalesOrderNumber または SalesOrderLineNumber を指定するだけでは不十分です。 次の式で問題が解決されます。

ThisExpressionWorksBecauseOfMATCHBY = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] ),
                    MATCHBY ( FactInternetSales[SalesOrderNumber], 
                                FactInternetSales[SalesOrderLineNumber] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

そして、この式は確かに私たちが求めている結果を返します。

SalesOrderNumber	SalesOrderLineNumber	プロダクトキー	売上額	以前の売上金額
SO51900	1	528	5.99
SO51948	1	528	4.99	5.99
SO52043	1	528	4.99	4.99
SO52045	1	528	4.99	4.99
SO52094	1	528	4.99	4.99
SO52175	1	528	4.99	4.99
SO52190	1	528	4.99	4.99
SO52232	1	528	4.99	4.99
SO52234	1	528	4.99	4.99
SO52234	2	529	3.99

関連コンテンツ

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER