ORDERBY, PARTITIONBY, andMATCHBY 함수 이해

DAX ORDERBY, PARTITIONBY, andMATCHBY 함수는 INDEX, OFFSET, WINDOW, RANK, ROWNUMBER등 DAXWindow 함수와 함께 사용할 수 있는 특수 함수입니다.

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

시나리오

all Window 함수를 사용하지 않는 예제부터 살펴보겠습니다. 아래는 calendar및year색상별 총 매출을 반환하는 테이블입니다. 이 테이블을 define할 수 있는 방법은 여러 가지가 있지만, 우리는 DAX에서 어떤 일이 일어나는지를 이해하는 것에 관심이 있어서 계산된 테이블을 사용합니다. 여기 테이블 표현식이 있습니다.

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

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

Now, 각 색상에 대해 year과year의 판매 차이를 계산하는 비즈니스 문제를 해결하려고 한다고 상상해 보겠습니다. 실제로, previousyearand 동일한 색상의 판매를 find 해야 하고, 그 결과를 현재 year의 판매에서 빼는 방법이 필요합니다. 예를 들어 [Red, 2019] 조합의 경우, 우리는 [Red, 2018]의 매출을 확인하려고 합니다. 이 작업이 완료되면 현재 판매액에서 그것을 빼고 필요한 value을 and로 반환할 수 있습니다.

OFFSET 사용하기

OFFSET은 전형적인 및 previous 유형의 계산을 포함한 위에서 설명한 비즈니스 질문에 답하는 데 완벽하며, 상대적 변화를 가능하게 합니다. 우리의 [first] 시도는 이렇습니다.

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

이 표현에는 많은 일이 일어나고 있습니다. ADDCOLUMNS에서 expand로 바꾸며 'PreviousColorSales'라는 열이 포함된 이전의 테이블을 사용했습니다. 해당 열의 내용은 previous 색(OFFSET사용하여 검색됨)에 대해 SUM(FactInternetSales[SalesAmount])인 CurrentYearSales로 설정됩니다.

결과는 다음과 같습니다.

이것은 우리의 목표에 한 걸음 더 다가간 것이지만, if 우리가 자세히 보면 정확히 일치하지 않습니다. 예를 들어 [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 and CalendarYear로 테이블을 오름차순으로 정렬합니다. 그러면 반환되는 previous 행으로 간주되는 항목이 결정됩니다.

이 두 결과가 동일한 이유는 ORDERBY이(가) PARTITIONBY에 없는 관계의 열을 자동으로 containsall하기 때문입니다. PARTITIONBY 지정되지 않았으므로 ORDERBY Color, CalendarYear, and CurrentYearSales로 설정됩니다. 그러나 관계의 Color and CalendarYear 쌍은 고유하므로 CurrentYearSales를 추가해도 결과가 변경되지 않습니다. fact, evenif에서 우리는 ORDERBY에 색상만 지정하도록 되어 있었고, CalendarYear가 자동으로 추가되므로 결과는 동일합니다. 이것은 각 행을 ORDERBY,and,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]
        )
    )

ORDERBY이 PARTITIONBY에서 지정되지 않은 관계의 열을 자동으로 containsall 하기 때문에, 여기서 ORDERBY을 지정하는 것은 선택사항입니다. 따라서 다음 식은 ORDERBY CalendarYear and 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 열은 동일한 색상의 previous과year에 대한 전년도 판매액을 보여줍니다. [Red, 2020]의 경우 [Red, 2019]의 판매량을 반환하며, and. 예를 들어 [Red, 2017]의 경우, previousyear가 없어 If에서 value이 반환되지 않습니다.

PARTITIONBY을 divide하여 테이블을 OFFSET 계산을 실행할 부분으로 나누는 방법으로 생각할 수 있습니다. 위의 예제에서 표는 각 색에 대해 하나씩 색이 있는 만큼의 부분으로 나뉩니다. 그런 다음 각 부분 내에서 OFFSET는 CalendarYear별로 계산된 다음 정렬됩니다.

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

테이블 CalendarYear별 보여주기

First에서 PARTITIONBY을 호출하면 테이블이 각 색상별로 부분으로 나뉩니다. 테이블 이미지의 연한 파란색 상자로 표시됩니다. Next ORDERBY 각 파트가 CalendarYear(주황색 화살표로 표시됨)로 정렬되었는지 확인합니다. 마지막으로 정렬된 각 부분 내에서 각 행에 대해 OFFSET은 위에 있는 행을 찾아 and이 PreviousYearSalesForSameColor 열에서 해당 value를 반환합니다. 각 부분의 모든 first 행에 대해 동일한 부분에 previous 행이 없으므로 PreviousYearSalesForSameColor 열에 대한 해당 행의 결과는 비어 있습니다.

최종 결과를 얻으려면, OFFSET호출에서 반환된 것과 동일한 색의 previousyear 판매에서 CurrentYearSales를 빼기만 하면 됩니다. not 동일한 색에 대한 previousyear 판매를 표시하는 데 관심이 있지만 현재 year 판매에서만 year 차이에 대한 yearand. 최종 계산 테이블 식은 다음과 같습니다.

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

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

MATCHBY 사용하기

all에서 MATCHBY를 지정하지 않았다는 것을 아마도 눈치채셨을 것입니다. 이 경우 필요하지 않습니다. ORDERBY and PARTITIONBY 열(위 예제에서 지정한 한)은 각 행을 고유하게 식별하기에 충분합니다. 우리가 MATCHBY를 지정하지 않았기 때문에, ORDERBYandPARTITIONBY에서 지정된 열은 각 행을 고유하게 식별하여 비교할 수 있게 하여 OFFSET가 의미 있는 결과를 제공할 수 있습니다. ORDERBY and PARTITIONBY 열이 각 행을 고유하게 식별할 수 없는 If 추가 열을 ORDERBY 절에 추가할 수 if 해당 추가 열을 통해 각 행을 고유하게 식별할 수 있습니다. 가능한 notIferror 반환됩니다. 이 last 경우 MATCHBY 지정하는 것이 error해결하는 데 도움이 될 수 있을 것입니다.

If MATCHBY이 지정되었을 경우, MATCHBYandPARTITIONBY의 열은 각 행을 고유하게 식별하는 데 사용됩니다. If 가능한 경우 not, error 반환됩니다. Even if MATCHBY 필요하지는 않지만 혼동을 피하기 위해 MATCHBY 명시적으로 지정하는 것이 좋습니다.

앞서의 예제를 이어서 보면 last 식은 다음과 같습니다.

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

행을 고유하게 식별하려면 If을(를) 명시적으로 지정하고, 다음과 같은 식으로 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 모두 행을 고유하게 식별하는 데 사용됩니다. 색은 두 MATCHBYandPARTITIONBY모두 지정되므로 다음 식은 previous 식과 동일합니다.

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필요한 약간 다른 예제를 살펴보겠습니다. 이 경우 주문 줄 목록이 있습니다. 각 행은 주문에 대한 주문 줄을 나타냅니다. 주문에는 여러 개의 주문 내역이 있을 수 있습니다. and 주문 내역 1이 여러 주문에 나타날 수 있습니다. 또한 각 주문 라인에는 ProductKey and와 SalesAmount가 있습니다. 테이블의 관련 열 sample 다음과 같습니다.

SalesOrderNumber and 및 SalesOrderLineNumber은(는) 행을 고유하게 식별하기 위해 모두 필요합니다.

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

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

이 식은 error반환합니다. "OFFSET'의 Relation 매개 변수에는 허용되지 않는 중복 행이 있을 수 있습니다."

이 식을 작동하려면 행을 고유하게 defineall 열을 포함해야 andMATCHBY 지정해야 합니다. 관계인 FactInternetSales에는 고유한 열에 or 명시적 키가 포함되어 있지 않으므로 여기서 MATCHBY 필요합니다. 그러나 SalesOrderNumber and SalesOrderLineNumber 열은 복합 키형성합니다. 여기서 이러한 열은 관계 and 고유하므로 각 행을 고유하게 식별할 수 있습니다. SalesOrderLineNumber or SalesOrderNumber를 지정하는 것만으로는 두 열에 반복 values포함하기 때문에 충분하지 않습니다. 다음 식은 문제를 해결합니다.

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

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

콘텐츠 Related

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER