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:

Cor	CalendarYear	CurrentYearSales
"Preto"	2017	393885
"Preto"	2018	1818835
"Preto"	2019	3981638
"Preto"	2020	2644054
"Azul"	2019	994448
"Azul"	2020	1284648
"Multi"	2019	48622
"Multi"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Vermelho"	2017	2961198
"Vermelho"	2018	3686935
"Vermelho"	2019	900175
"Vermelho"	2020	176022
"Prata"	2017	326399
"Prata"	2018	750026
"Prata"	2019	2165176
"Prata"	2020	1871788
"Branco"	2019	2517
"Branco"	2020	2589
"Amarelo"	2018	163071
"Amarelo"	2019	2072083
"Amarelo"	2020	2621602

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 é:

Cor	CalendarYear	CurrentYearSales	AnteriorColorSales
"Preto"	2017	393885
"Preto"	2018	1818835	393885
"Preto"	2019	3981638	1818835
"Preto"	2020	2644054	3981638
"Azul"	2019	994448	2644054
"Azul"	2020	1284648	994448
"Multi"	2019	48622	1284648
"Multi"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Vermelho"	2017	2961198	227295
"Vermelho"	2018	3686935	2961198
"Vermelho"	2019	900175	3686935
"Vermelho"	2020	176022	900175
"Prata"	2017	326399	176022
"Prata"	2018	750026	326399
"Prata"	2019	2165176	750026
"Prata"	2020	1871788	2165176
"Branco"	2019	2517	1871788
"Branco"	2020	2589	2517
"Amarelo"	2018	163071	2589
"Amarelo"	2019	2072083	163071
"Amarelo"	2020	2621602	2072083

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

Nota

Embora ORDERBY esteja definido como CalendarYear e CurrentYearSales automaticamente, nenhuma garantia é dada quanto à ordem em que eles serão adicionados. Se CurrentYearSales for adicionado antes de CalendarYear, a ordem resultante não estará alinhada com o esperado. Seja explícito ao especificar ORDERBY e PARTITIONBY para evitar confusão e resultados inesperados.

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

Cor	CalendarYear	CurrentYearSales	AnteriorAnoVendasParaSameColor
"Preto"	2017	393885
"Preto"	2018	1818835	393885
"Preto"	2019	3981638	1818835
"Preto"	2020	2644054	3981638
"Azul"	2019	994448
"Azul"	2020	1284648	994448
"Multi"	2019	48622
"Multi"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Vermelho"	2017	2961198
"Vermelho"	2018	3686935	2961198
"Vermelho"	2019	900175	3686935
"Vermelho"	2020	176022	900175
"Prata"	2017	326399
"Prata"	2018	750026	326399
"Prata"	2019	2165176	750026
"Prata"	2020	1871788	2165176
"Branco"	2019	2517
"Branco"	2020	2589	2517
"Amarelo"	2018	163071
"Amarelo"	2019	2072083	163071
"Amarelo"	2020	2621602	2072083

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:

Cor	CalendarYear	CurrentYearSales	YoYSalesForSameColor
"Preto"	2017	393885	393885
"Preto"	2018	1818835	1424950
"Preto"	2019	3981638	2162803
"Preto"	2020	2644054	-1337584
"Azul"	2019	994448	994448
"Azul"	2020	1284648	290200
"Multi"	2019	48622	48622
"Multi"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Vermelho"	2017	2961198	2961198
"Vermelho"	2018	3686935	725737
"Vermelho"	2019	900175	-2786760
"Vermelho"	2020	176022	-724153
"Prata"	2017	326399	326399
"Prata"	2018	750026	423627
"Prata"	2019	2165176	1415150
"Prata"	2020	1871788	-293388
"Branco"	2019	2517	2517
"Branco"	2020	2589	72
"Amarelo"	2018	163071	163071
"Amarelo"	2019	2072083	1909012
"Amarelo"	2020	2621602	549519

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:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount
SO51900	5	528	4,99
SO51948	5	528	5,99
SO52043	5	528	4,99
SO52045	5	528	4,99
SO52094	5	528	4,99
SO52175	5	528	4,99
SO52190	5	528	4,99
SO52232	5	528	4,99
SO52234	5	528	4,99
SO52234	2	529	3.99

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:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Valor das vendas anteriores
SO51900	5	528	5,99
SO51948	5	528	4,99	5,99
SO52043	5	528	4,99	4,99
SO52045	5	528	4,99	4,99
SO52094	5	528	4,99	4,99
SO52175	5	528	4,99	4,99
SO52190	5	528	4,99	4,99
SO52232	5	528	4,99	4,99
SO52234	5	528	4,99	4,99
SO52234	2	529	3.99

Conteúdos relacionados

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