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

DAX の ORDERBY、PARTITIONBY、および MATCHBY 関数は、DAX ウィンドウ関数 (INDEX、OFFSET、WINDOW、RANK、ROWNUMBER) と一緒の場合のみ使用できる特別な関数です。

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

シナリオ

ウィンドウ関数をまったく使用しない例から説明します。 次に示すのは、色ごとに 1 年あたりの合計売上を返す表です。 このテーブルを定義する方法は複数ありますが、DAX で起こることを理解したいため、計算テーブルを使用します。 テーブル式を次に示します。

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

この計算テーブル式では SUMMARIZECOLUMNS を使用して、FactInternetSales テーブルの SalesAmount 列の SUM、DimProduct テーブルの Color 列、DimDate テーブルの CalendarYear 列を計算します。 結果は次のようになります。

Color	CalendarYear	CurrentYearSales
"黒"	2017	393885
"黒"	2018	1818835
"黒"	2019	3981638
"黒"	2020	2644054
"Blue"	2019	994448
"Blue"	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

ここで、色ごとに売上高の差を前年比で計算するビジネス上の問題を解決しようとしていると想像してみてください。 実質的には、前年の同じ色の売上を見つけ、コンテキストで現在の年の売上からその売上を減算する方法が必要です。 たとえば、[赤、2019] の組み合わせについては、[赤、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 (SUM(FactInternetSales[SalesAmount]) に設定されます。

結果は次のとおりです。

Color	CalendarYear	CurrentYearSales	PreviousColorSales
"黒"	2017	393885
"黒"	2018	1818835	393885
"黒"	2019	3981638	1818835
"黒"	2020	2644054	3981638
"Blue"	2019	994448	2644054
"Blue"	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

これは目標に一歩近づきますが、よく見ると、目指している内容と正確には一致しません。 たとえば、[シルバー、2017] の場合、PreviousColorSales は [赤、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 で Color のみを指定した場合でも、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 を指定する場合は明示的に指定してください。

どちらの式も、期待される結果を返します。

Color	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"黒"	2017	393885
"黒"	2018	1818835	393885
"黒"	2019	3981638	1818835
"黒"	2020	2644054	3981638
"Blue"	2019	994448
"Blue"	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 列は同じ色の前年の売上を示します。 [赤、2020] の場合、[赤、2019] などの売上が返されます。 [赤、2017] の場合など、前の年がない場合、値は返されません。

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

視覚的に起こる内容は次のとおりです。

まず、PARTITIONBY を呼び出すと、テーブルは各 Color に対して 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]
        )
    )

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

Color	CalendarYear	CurrentYearSales	YoYSalesForSameColor
"黒"	2017	393885	393885
"黒"	2018	1818835	1424950
"黒"	2019	3981638	2162803
"黒"	2020	2644054	-1337584
"Blue"	2019	994448	994448
"Blue"	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	ProductKey	SalesAmount
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 を指定し、行を一意に定義するすべての列を含める必要があります。 リレーションシップ FactInternetSales には明示的なキーまたは一意の列が含まれていないため、ここでは MATCHBY が必要になります。 ただし、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	ProductKey	SalesAmount	以前の売上金額
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