ORDERBY, PARTITIONBY및 MATCHBY 함수 이해

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

ORDERBY, PARTITIONBY및 MATCHBY 이해하는 것은 Window 함수를 성공적으로 사용하는 데 중요합니다. 여기에 제공된 예제에서는 OFFSET사용하지만 마찬가지로 다른 Window 함수에도 적용할 수 있습니다.

시나리오

Window 함수를 전혀 사용하지 않는 예제부터 시작해 보겠습니다. 아래는 연간 총 판매액(색상별)을 반환하는 표입니다. 이 테이블을 정의하는 방법에는 여러 가지가 있지만 DAX수행되는 작업을 이해하는 데 관심이 있으므로 계산 테이블을 사용합니다. 여기 테이블 표현식이 있습니다.

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

이 계산 테이블 식은 SUMMARIZECOLUMNS 사용하여 FactInternetSales 테이블의 SalesAmount 열 SUM DimProduct 테이블의 Color 열 및 DimDate 테이블의 CalendarYear 열을 계산하는 것을 볼 수 있습니다. 결과는 다음과 같습니다.

이제 각 색상에 대해 전년 대비 매출 차이를 계산하는 비즈니스 문제를 해결하려고 한다고 상상해 보겠습니다. 전년도에 동일한 색상에 대한 판매를 찾아 현재 연도의 판매에서 효과적으로 빼는 방법이 필요합니다. 예를 들어 [Red, 2019] 조합의 경우, 우리는 [Red, 2018]의 매출을 확인하려고 합니다. 이 작업이 완료되면 현재 판매에서 빼고 필요한 값을 반환할 수 있습니다.

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사용하여 검색됨)에 대해 설정되며, 이는 CurrentYearSales로서 SUM(FactInternetSales[SalesAmount])와 동일합니다.

결과는 다음과 같습니다.

이것은 우리의 목표에 한 걸음 더 가까워졌지만, 자세히 보면 우리가 원하는 것과 정확히 일치하지는 않습니다. 예를 들어 [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 기준으로 오름차순으로 테이블을 정렬하며, 이 정렬 순서가 반환되는 이전 행을 결정합니다.

이러한 두 결과가 동일한 이유는 ORDERBYPARTITIONBY없는 관계의 모든 열을 자동으로 포함하기 때문입니다. 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 추가

이제 다음 계산 테이블 식과 같이 결과를 거의 위해 사용할 수 있습니다.

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]
        )
    )

여기서 PARTITIONBY에 지정되지 않은 모든 열이 ORDERBY에 자동으로 포함되기 때문에, 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]
        )
    )

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

이 표에서 볼 수 있듯이 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]
        )
    )

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

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 모두 행을 고유하게 식별하는 데 사용됩니다. 색은 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가 모두 필요합니다.

각 주문에 대해 SalesAmount에서 내림차순으로 주문한 동일한 제품의 이전 판매액(ProductKey로 표시)을 반환하려고 합니다. vRelation에 여러 행이 포함될 수 있어 OFFSET에 전달되기 때문에 다음 식은 작동하지 않을 것입니다.

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

이 식은 "OFFSET's 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]
            )
    )

그리고 이 식은 실제로 다음과 같은 결과를 반환합니다.

관련 콘텐츠

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER