ORDERBY, PARTITIONBY 및 MATCHBY 함수 이해

DAX의 ORDERBY, PARTITIONBY 및 MATCHBY 함수는 DAX 창 함수(INDEX, OFFSET, WINDOW, RANK, ROWNUMBER)와 함께 사용할 수 있는 특수 함수입니다.

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 열을 계산하는 것을 볼 수 있습니다. 결과는 다음과 같습니다.

색상	CalendarYear	CurrentYearSales
"Black"	2017	393885
"Black"	2018	1818835
"Black"	2019	3981638
"Black"	2020	2644054
"파랑"	2019	994448
"파랑"	2020	1284648
"Multi"	2019	48622
"Multi"	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 라는 열을 사용하여 이전부터 테이블을 확장했습니다. 해당 열의 내용은 이전 색(OFFSET을 사용하여 검색됨)에 대해 SUM(FactInternetSales[SalesAmount])인 CurrentYearSales로 설정됩니다.

결과는 다음과 같습니다.

색상	CalendarYear	CurrentYearSales	PreviousColorSales
"Black"	2017	393885
"Black"	2018	1818835	393885
"Black"	2019	3981638	1818835
"Black"	2020	2644054	3981638
"파랑"	2019	994448	2644054
"파랑"	2020	1284648	994448
"Multi"	2019	48622	1284648
"Multi"	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별로 테이블을 정렬합니다. 그러면 반환된 이전 행으로 간주되는 항목이 결정됩니다.

이러한 두 결과가 동일한 이유는 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로 자동으로 설정되지만 추가될 순서에 대한 보장은 없습니다. CalendarYear 앞에 CurrentYearSales가 추가되면 결과 순서가 예상과 인라인되지 않습니다. ORDERBY 및 PARTITIONBY를 지정할 때 혼동과 예기치 않은 결과를 방지하려면 명시적이어야 합니다.

두 식 모두 다음과 같은 결과를 반환합니다.

색상	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Black"	2017	393885
"Black"	2018	1818835	393885
"Black"	2019	3981638	1818835
"Black"	2020	2644054	3981638
"파랑"	2019	994448
"파랑"	2020	1284648	994448
"Multi"	2019	48622
"Multi"	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 계산을 실행할 부분으로 나누는 방법으로 생각할 수 있습니다. 위의 예제에서 표는 각 색에 대해 하나씩 색이 있는 만큼의 부분으로 나뉩니다. 그런 다음 각 파트 내에서 OFFSET이 CalendarYear별로 정렬되어 계산됩니다.

시각적으로, 무슨 일이 일어나고 있는지는 다음과 같습니다.

먼저 PARTITIONBY를 호출하면 테이블이 각 색에 대해 하나씩 파트로 나뉩니다. 테이블 이미지의 연한 파란색 상자로 표시됩니다. 다음으로 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]
        )
    )

해당 식의 결과는 다음과 같습니다.

색상	CalendarYear	CurrentYearSales	YoYSalesForSameColor
"Black"	2017	393885	393885
"Black"	2018	1818835	1424950
"Black"	2019	3981638	2162803
"Black"	2020	2644054	-1337584
"파랑"	2019	994448	994448
"파랑"	2020	1284648	290200
"Multi"	2019	48622	48622
"Multi"	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