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

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

Compreender ORDERBY, PARTITIONBY e MATCHBY é crucial para usar com êxito as 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 em DAX, usaremos uma tabela calculada. Esta é a expressão da tabela:

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

Você verá que essa expressão da tabela calculada usa SUMMARIZECOLUMNS para calcular a SUM da coluna SalesAmount na tabela FactInternetSales, pela coluna Cor da tabela DimProduct e a coluna CalendarYear da tabela DimDate. Aqui está o resultado:

Cor	Ano do Calendário	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
"ND"	2019	207822
"ND"	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 de vendas, ano a ano para cada cor. Na prática, precisamos de uma forma de encontrar as vendas com a mesma cor no ano anterior e subtrair isso das vendas no ano atual, em contexto. Por exemplo, para a combinação [Vermelho, 2019] procuramos as vendas para [Vermelho, 2018]. Depois disso, podemos subtraí-lo das vendas atuais e retornar o valor necessário.

Usando OFFSET

OFFSET é perfeita para os tipos de cálculos típicos 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 para CurrentYearSales, que é 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
"Azul"	2019	994448	2644054
"Azul"	2020	1284648	994448
"Multi"	2019	48622	1284648
"Multi"	2020	57849	48622
"ND"	2019	207822	57849
"ND"	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, ele não corresponde exatamente ao que estamos atrás. Por exemplo, para [Silver, 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 Cor 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.PARTITIONBY Como PARTITIONBY não foi especificado, ORDERBY está 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 especifiquemos apenas Cor 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 para garantir que ORDERBY 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 obtermos o resultado desejado, podemos 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 a especificação 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 é 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]
        )
    )

Observação

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

Ambas as expressões retornam o resultado que buscamos.

Cor	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"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
"ND"	2019	207822
"ND"	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], retorna as vendas para [Vermelho, 2019], e assim por diante. Se não houver nenhum ano anterior, por exemplo, no caso de [Red, 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, OFFSET é calculado, classificado por CalendarYear.

Visualmente, o que está acontecendo é o seguinte:

Primeiro, a chamada para PARTITIONBY resulta na divisão da tabela em partes, uma para cada cor. Isso é representado pelas caixas azuis claras na imagem da tabela. Em seguida, ORDERBY verifique se cada parte é 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 com a mesma cor retornada pela chamada para OFFSET. Como 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. 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
"Azul"	2019	994448	994448
"Azul"	2020	1284648	290200
"Multi"	2019	48622	48622
"Multi"	2020	57849	9227
"ND"	2019	207822	207822
"ND"	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 de forma alguma. Nesse caso, não é necessário. As colunas dentro 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 ORDERBY e PARTITIONBY são usadas para identificar exclusivamente cada linha para que possam ser comparadas para permitir OFFSET um resultado significativo. Se as colunas dentro ORDERBY e PARTITIONBY não puderem 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 é especificado, as colunas em MATCHBY e PARTITIONBY sã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 MATCHBY explicitamente para evitar qualquer confusão.

Continuando com os 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 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 foi especificado, as colunas especificadas tanto em MATCHBY quanto em PARTITIONBY são usadas para identificar linhas de forma exclusiva. 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 examinar um exemplo ligeiramente diferente que requer MATCHBY. Nesse caso, temos uma lista de linhas de pedidos. 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 é exibida 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	Chave do Produto	Valor de Vendas
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 no vRelation conforme 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: "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 nenhuma chave explícita 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 queremos:

Número do Pedido de Venda	SalesOrderLineNumber	Chave do Produto	Valor de Vendas	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 OFFSET WINDOW RANK ROWNUMBER