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

O ORDERBY, PARTITIONBY, e MATCHBY funções em DAX são funções especiais que só podem ser usadas juntamente com DAX as funções de janela: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

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

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 esta expressão de tabela calculada usa SUMMARIZECOLUMNS para calcular o SUM da coluna SalesAmount na tabela FactInternetSales, utilizando a coluna Color da tabela DimProduct e a 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 subtraí-las das vendas no ano atual, levando em consideração o 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á a acontecer com esta expressão. Usámos ADDCOLUMNS para expandir a tabela anterior com uma coluna chamada PreviousColorSales. O conteúdo dessa coluna é definido como CurrentYearSales, que é SUM(FactInternetSales[SalesAmount]), para a Cor 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 Ano de Calendário 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 no PARTITIONBY. Como PARTITIONBY não foi especificado, ORDERBY está definido como Cor, 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 Cor no ORDERBY, os resultados são os mesmos, uma vez que CalendarYear seria adicionado automaticamente. Isso acontece porque a função irá adicionar tantas colunas quanto 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 que estamos procurando, 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 a especificação ORDERBY é opcional aqui porque ORDERBY contém automaticamente todas as colunas da relação que não estão especificadas em PARTITIONBY. Portanto, a expressão a seguir retorna os mesmos resultados porque ORDERBY é definida 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 forma 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, ordenado por AnoCalendário.

Visualmente, o que está acontecendo é o seguinte:

Primeiro, a chamada para PARTITIONBY faz com que a tabela seja dividida em partes, uma para cada cor. Isso é representado pelas caixas azuis claras na imagem da tabela. Em seguida, ORDERBY assegura-se de que cada parte está ordenada por Ano Calendário, sinalizado 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

Poderá ter notado que não especificámos MATCHBY de todo. 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 OFFSET dar um resultado significativo. Se as colunas em e PARTITIONBY não puderem ORDERBY identificar exclusivamente cada linha, colunas adicionais poderão ser adicionadas à ORDERBY cláusula 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 obrigatório, considere especificar MATCHBY explicitamente 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]
        )
    )

Uma vez que MATCHBY é especificado, tanto as colunas especificadas em MATCHBY como em PARTITIONBY são usadas para identificar exclusivamente linhas. Como Color é especificado em ambos 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 a especificação MATCHBY não é necessária 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 um 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 unicamente as linhas.

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 no 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: "OFFSETO parâmetro Relation pode ter linhas duplicadas, o que não é permitido."

Para que essa expressão funcione, MATCHBY deve ser especificada 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údo relacionado

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER