Compreender 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 de janela DAX: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Entender as funções ORDERBY, PARTITIONBY e MATCHBY é fundamental para usar com êxito as funções Window (funções de janela). Os exemplos fornecidos aqui usam OFFSET, mas são aplicáveis de forma semelhante às outras funções de janela.

Cenário

Vamos começar com um exemplo que não usa funções de janela. Mostrado abaixo está uma tabela que retorna o total de vendas, 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. Esta é a expressão de 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 SUM da coluna SalesAmount na tabela FactInternetSales, pela coluna Color da tabela DimProduct e a coluna CalendarYear da tabela DimDate. Eis o resultado:

Cor	CalendarYear	CurrentYearSales
"Preto"	2017	393885
"Preto"	2018	1818835
"Preto"	2019	3981638
"Preto"	2020	2644054
"Blue"	2019	994448
"Blue"	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 pela mesma cor no ano anterior e subtrair isso das vendas no ano atual, em contexto. Por exemplo, para a combinação [Vermelho, 2019] estamos procurando vendas para [Vermelho, 2018]. Depois disso, podemos subtraí-lo das vendas atuais e retornar o valor necessário.

Usando OFFSET

A função OFFSET é perfeita para os tipos de cálculos típicos de comparar com os anteriores 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 anterior com uma coluna chamada PreviousColorSales. O conteúdo dessa coluna é definido como CurrentYearSales, que é a SUM(FactInternetSales[SalesAmount]), para a Cor anterior (recuperada usando OFFSET).

O resultado é:

Cor	CalendarYear	CurrentYearSales	PreviousColorSales
"Preto"	2017	393885
"Preto"	2018	1818835	393885
"Preto"	2019	3981638	1818835
"Preto"	2020	2644054	3981638
"Blue"	2019	994448	2644054
"Blue"	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 etapa mais perto do nosso objetivo, mas se olharmos de perto não corresponde exatamente ao que estamos procurando. Por exemplo, para [Prata, 2017] o PreviousColorSales está definido como [Vermelho, 2020].

Adicionando ORDERBY

Essa definição acima é 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 Color e CalendarYear em ordem crescente, o que determina o que é considerado a linha anterior retornada.

O motivo pelo 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 que apenas especifiquemos Color em ORDERBY, os resultados serão os mesmos, pois 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 que procuramos podermos usar PARTITIONBY, conforme 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]
        )
    )

Observação

Embora ORDERBY seja definido como CalendarYear e CurrentYearSales automaticamente, nenhuma garantia é dada quanto à ordem na qual elas serão adicionadas. Se CurrentYearSales for adicionado antes de CalendarYear, a ordem resultante não estará em linha 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 procuramos:

Cor	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Preto"	2017	393885
"Preto"	2018	1818835	393885
"Preto"	2019	3981638	1818835
"Preto"	2020	2644054	3981638
"Blue"	2019	994448
"Blue"	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 [Vermelho, 2020], ele retorna as vendas para [Vermelho, 2019], e assim por diante. Se não houver nenhum ano anterior, por exemplo, no caso de [Vermelho, 2017], nenhum valor será retornado.

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 quantas partes houver 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 faz com que a tabela seja dividida em partes, uma para cada Color. Isso é representado pelas caixas azuis claras na imagem da tabela. Em seguida, ORDERBY garante que cada parte seja classificada por CalendarYear (representada pelas setas laranjas). Por fim, 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 dessa linha para a coluna PreviousYearSalesForSameColor está vazio.

Para obter o resultado final, basta subtrair CurrentYearSales das vendas do ano anterior pela mesma cor retornada pela chamada para OFFSET. Isso é o bastante, já que não estamos interessados em mostrar as vendas do ano anterior pela mesma cor, mas apenas nas vendas do ano atual e na diferença ano a ano. Esta é 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
"Blue"	2019	994448	994448
"Blue"	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

Talvez você tenha notado que não especificamos MATCHBY. Nesse caso, não é necessário. As colunas em ORDERBY e PARTITIONBY (até onde 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 habilitar OFFSET para dar 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 resolve 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 com os exemplos acima, esta é 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, conforme 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, as colunas especificadas em MATCHBY e 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 a especificação de MATCHBY não é necessária nos exemplos que examinamos até agora, vamos examinar um exemplo ligeiramente diferente que requer MATCHBY. Nesse caso, temos uma lista de linhas de pedido. Cada linha representa uma linha de pedido para um pedido. Um pedido pode ter várias linhas de pedido e a linha de pedido 1 aparece em muitos pedidos. 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:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount
SO51900	1	528	4,99
SO51948	1	528	5,99
SO52043	1	528	4,99
SO52045	1	528	4,99
SO52094	1	528	4,99
SO52175	1	528	4,99
SO52190	1	528	4,99
SO52232	1	528	4,99
SO52234	1	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 retornar o valor de vendas anterior do mesmo produto (representado pelo ProductKey) ordenado pelo SalesAmount em ordem decrescente. A expressão a seguir não funcionará porque há potencialmente várias linhas em vRelation, pois ela é passada para OFFSET:

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

Essa 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, em que sua existência em conjunto é 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 expressão a seguir 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 realmente retorna os resultados que estamos procurando:

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

Conteúdo relacionado

ORDERBY
PARTITIONBY
MATCHBY
INDEX
DESLOCAMENTO
WINDOW
RANK
ROWNUMBER