Noções básicas sobre as funções ORDERBY, PARTITIONBY e MATCHBY

As funções ORDERBY, PARTITIONBY e MATCHBY no DAX são funções especiais que só podem ser usadas junto com as funções DAX Window: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Compreender ORDERBY, PARTITIONBY e MATCHBY é fundamental para usar com êxito as funções da janela. Os exemplos fornecidos aqui usam OFFSET, mas são igualmente aplicáveis às outras funções da janela.

Cenário

Vamos começar com um exemplo que não usa as funções do Windows. Abaixo está uma tabela que retorna as vendas totais, por cor, por ano civil. Há várias maneiras de definir essa tabela, mas como estamos interessados em entender o que acontece no DAX, usaremos uma tabela calculada. Aqui está a expressão da tabela:

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

Você verá que essa expressão de tabela calculada usa SUMMARIZECOLUMNS para calcular a SOMA da coluna SalesAmount na tabela FactInternetSales, pela coluna Color da tabela DimProduct e pela coluna CalendarYear da tabela DimDate. Eis o resultado:

Agora, vamos imaginar que estamos tentando resolver a questão comercial de calcular a diferença nas vendas, ano a ano, para cada cor. Efetivamente, precisamos de uma maneira de encontrar vendas para a mesma cor no ano anterior e subtrair isso das vendas no ano atual, no contexto. Por exemplo, para a combinação [Red, 2019] estamos procurando vendas para [Red, 2018]. Uma vez que temos isso, podemos subtraí-lo das vendas atuais e devolver o valor necessário.

Usando OFFSET

OFFSET é perfeito para a comparação típica com tipos anteriores de cálculos necessários para responder à pergunta de negócios descrita acima, pois nos permite fazer um movimento relativo. Nossa primeira tentativa pode ser:

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

Muita coisa está acontecendo com essa expressão. Usamos ADDCOLUMNS para expandir a tabela de antes com uma coluna chamada PreviousColorSales. O conteúdo dessa coluna é definido como CurrentYearSales, que é SUM(FactInternetSales[SalesAmount]), para a Color anterior (recuperada usando OFFSET).

O resultado é:

Este é um passo mais perto do nosso objetivo, mas se olharmos de perto não corresponde exatamente ao que estamos procurando. Por exemplo, para [Silver, 2017] PreviousColorSales está definido como [Red, 2020].

Adicionando ORDERBY

Esta definição é equivalente a:

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

Nesse caso, a chamada para OFFSET usa ORDERBY para ordenar a tabela por Cor e CalendarYear em ordem crescente, o que determina o que é considerado a linha anterior que é retornada.

A razão pela qual esses dois resultados são equivalentes é porque ORDERBY contém automaticamente todas as colunas da relação que não estão em PARTITIONBY. Como PARTITIONBY não foi especificado, ORDERBY é definido como Color, CalendarYear e CurrentYearSales. No entanto, como os pares Color e CalendarYear na relação são exclusivos, adicionar CurrentYearSales não altera o resultado. Na verdade, mesmo se fôssemos especificar apenas Color em ORDERBY, os resultados são os mesmos, já que CalendarYear seria adicionado automaticamente. Isso ocorre porque a função adicionará quantas colunas forem necessárias a ORDERBY para garantir que cada linha possa ser identificada exclusivamente pelas colunas ORDERBY e PARTITIONBY:

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

Adicionando PARTITIONBY

Agora, para quase obter o resultado, podemos usar PARTITIONBY, como mostrado na seguinte expressão de tabela calculada:

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

Observe que especificar ORDERBY é opcional aqui porque ORDERBY contém automaticamente todas as colunas da relação que não são especificadas em PARTITIONBY. Portanto, a expressão a seguir retorna os mesmos resultados porque ORDERBY é definido como CalendarYear e CurrentYearSales automaticamente:

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

Ambas as expressões retornam o resultado que estamos buscando:

Como você vê nesta tabela, a coluna PreviousYearSalesForSameColor mostra as vendas do ano anterior para a mesma cor. Para [Red, 2020], retorna as vendas para [Red, 2019], e assim por diante. Se não houver ano anterior, por exemplo, no caso de [Vermelho, 2017], nenhum valor é devolvido.

Você pode pensar em PARTITIONBY como uma maneira de dividir a tabela em partes nas quais executar o cálculo OFFSET. No exemplo acima, a tabela é dividida em tantas partes quantas forem as cores, uma para cada cor. Em seguida, dentro de cada parte, o OFFSET é calculado, classificado por CalendarYear.

Visualmente, o que está acontecendo é o seguinte:

Primeiro, a chamada para PARTITIONBY resulta na tabela sendo dividida em partes, uma para cada Cor. Isso é representado pelas caixas azuis claras na imagem da tabela. Em seguida, ORDERBY certifica-se de que cada parte é classificada por CalendarYear (representado pelas setas laranja). Finalmente, dentro de cada parte classificada, para cada linha, OFFSET localiza a linha acima dela e retorna esse valor na coluna PreviousYearSalesForSameColor. Como para cada primeira linha em cada parte não há nenhuma linha anterior nessa mesma parte, o resultado nessa linha para a coluna PreviousYearSalesForSameColor está vazio.

Para alcançar o resultado final, basta subtrair CurrentYearSales das vendas do ano anterior para a mesma cor retornada pela chamada para OFFSET. Uma vez que não estamos interessados em mostrar as vendas do ano anterior para a mesma cor, mas apenas nas vendas do ano atual e na diferença ano a ano. Aqui está a expressão final da tabela calculada:

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

E aqui está o resultado dessa expressão:

Usando MATCHBY

Você deve ter notado que não especificamos MATCHBY. Neste caso, não é necessário. As colunas em ORDERBY e PARTITIONBY (na medida em que foram especificadas nos exemplos acima) são suficientes para identificar exclusivamente cada linha. Como não especificamos MATCHBY, as colunas especificadas em ORDERBY e PARTITIONBY são usadas para identificar exclusivamente cada linha para que possam ser comparadas para permitir que OFFSET dê um resultado significativo. Se as colunas em ORDERBY e PARTITIONBY não puderem identificar exclusivamente cada linha, colunas adicionais poderão ser adicionadas à cláusula ORDERBY se essas colunas extras permitirem que cada linha seja identificada exclusivamente. Se isso não for possível, um erro será retornado. Neste último caso, especificar MATCHBY pode ajudar a resolver o erro.

Se MATCHBY for especificado, as colunas em MATCHBY e PARTITIONBY serão usadas para identificar exclusivamente cada linha. Se isso não for possível, um erro será retornado. Mesmo que MATCHBY não seja necessário, considere especificar explicitamente MATCHBY para evitar qualquer confusão.

Continuando a partir dos exemplos acima, aqui está a última expressão:

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

Se quisermos ser explícitos sobre como as linhas devem ser identificadas exclusivamente, podemos especificar MATCHBY como mostrado na seguinte expressão equivalente:

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

Como MATCHBY é especificado, tanto as colunas especificadas em MATCHBY quanto em PARTITIONBY são usadas para identificar linhas exclusivamente. Como Color é especificado em MATCHBY e PARTITIONBY, a expressão a seguir é equivalente à expressão anterior:

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

Como especificar MATCHBY não é necessário nos exemplos que analisamos até agora, vamos ver um exemplo ligeiramente diferente que requer MATCHBY. Neste caso, temos uma lista de linhas de ordem. Cada linha representa uma linha de ordem para uma ordem. Uma ordem pode ter várias linhas de ordem e a linha de ordem 1 aparece em muitas ordens. Além disso, para cada linha de pedido, temos uma ProductKey e um SalesAmount. Um exemplo das colunas relevantes na tabela tem esta aparência:

Observe que SalesOrderNumber e SalesOrderLineNumber são necessários para identificar linhas exclusivamente.

Para cada pedido, queremos devolver o valor de vendas anteriores do mesmo produto (representado pela ProductKey) encomendado pelo SalesAmount em ordem decrescente. A expressão a seguir não funcionará porque há potencialmente várias linhas em vRelation à medida que é passada para OFFSET:

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

Esta expressão retorna um erro: "O parâmetro Relation do OFFSET pode ter linhas duplicadas, o que não é permitido."

Para que essa expressão funcione, MATCHBY deve ser especificado e deve incluir todas as colunas que definem exclusivamente uma linha. MATCHBY é necessário aqui porque a relação, FactInternetSales, não contém chaves explícitas ou colunas exclusivas. No entanto, as colunas SalesOrderNumber e SalesOrderLineNumber juntas formam uma chave composta, onde sua existência junta é exclusiva na relação e, portanto, pode identificar exclusivamente cada linha. Apenas especificar SalesOrderNumber ou SalesOrderLineNumber não é suficiente, pois ambas as colunas contêm valores repetidos. A seguinte expressão resolve o problema:

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

E essa expressão de fato retorna os resultados que buscamos:

Conteúdos relacionados

ENCOMENDADO
PARTIÇÃOPOR
JOGO
ÍNDICE
DESLOCAMENTO
JANELA
POSIÇÃO
NÚMERO DA LINHA