Descripción de las funciones ORDERBY, PARTITIONBY y MATCHBY

Artículo
07/28/2023

Las funciones ORDERBY, PARTITIONBY y MATCHBY de DAX son funciones especiales que solo se pueden usar junto con funciones de ventana DAX: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Comprender ORDERBY, PARTITIONBY y MATCHBY es fundamental para usar correctamente las funciones de ventana. En los ejemplos que se proporcionan aquí se usa OFFSET, pero se aplican de forma similar en las demás funciones de ventana.

Escenario

Comencemos con un ejemplo que no usa funciones de ventana en absoluto. A continuación se muestra una tabla que devuelve las ventas totales por color, por año natural. Hay varias maneras de definir esta tabla, pero dado que estamos interesados en comprender lo que sucede en DAX, usaremos una tabla calculada. Esta es la expresión de tabla:

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

Verá que esta expresión de la tabla calculada usa SUMMARIZECOLUMNS para calcular la SUMA de la columna SalesAmount en la tabla FactInternetSales, por la columna Color de la tabla DimProduct y la columna CalendarYear de la tabla DimDate. Este es el resultado:

Color	CalendarYear	CurrentYearSales
«Negro»	2017	393885
«Negro»	2018	1818835
«Negro»	2019	3981638
«Negro»	2020	2644054
"Blue"	2019	994448
"Blue"	2020	1284648
«Multi»	2019	48622
«Multi»	2020	57849
«N/D»	2019	207822
«N/D»	2020	227295
«Rojo»	2017	2961198
«Rojo»	2018	3686935
«Rojo»	2019	900175
«Rojo»	2020	176022
«Plata»	2017	326399
«Plata»	2018	750026
«Plata»	2019	2165176
«Plata»	2020	1871788
«Blanco»	2019	2517
«Blanco»	2020	2589
«Amarillo»	2018	163071
«Amarillo»	2019	2072083
«Amarillo»	2020	2621602

Ahora, imaginemos que estamos tratando de resolver la cuestión empresarial de calcular la diferencia en ventas, año a año para cada color. De hecho, necesitamos una manera de encontrar ventas para el mismo color en el año anterior y restar eso de las ventas en el año actual, en contexto. Por ejemplo, para la combinación [Rojo, 2019] estamos buscando ventas para [Rojo, 2018]. Una vez que lo tengamos, podemos restarlo de las ventas actuales y devolver el valor necesario.

Uso de OFFSET

OFFSET es perfecto para la comparación típica con los tipos anteriores de cálculos necesarios para responder a la pregunta empresarial descrita anteriormente, ya que nos permite hacer un movimiento relativo. Nuestro primer intento podría ser:

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

Muchas cosas suceden con esta expresión. Hemos usado ADDCOLUMNS para expandir la tabla de antes con una columna denominada PreviousColorSales. El contenido de esa columna se establece en CurrentYearSales, que es SUM(FactInternetSales[SalesAmount]), para el color anterior (recuperado mediante OFFSET).

El resultado es el siguiente:

Color	CalendarYear	CurrentYearSales	PreviousColorSales
«Negro»	2017	393885
«Negro»	2018	1818835	393885
«Negro»	2019	3981638	1818835
«Negro»	2020	2644054	3981638
"Blue"	2019	994448	2644054
"Blue"	2020	1284648	994448
«Multi»	2019	48622	1284648
«Multi»	2020	57849	48622
«N/D»	2019	207822	57849
«N/D»	2020	227295	207822
«Rojo»	2017	2961198	227295
«Rojo»	2018	3686935	2961198
«Rojo»	2019	900175	3686935
«Rojo»	2020	176022	900175
«Plata»	2017	326399	176022
«Plata»	2018	750026	326399
«Plata»	2019	2165176	750026
«Plata»	2020	1871788	2165176
«Blanco»	2019	2517	1871788
«Blanco»	2020	2589	2517
«Amarillo»	2018	163071	2589
«Amarillo»	2019	2072083	163071
«Amarillo»	2020	2621602	2072083

Estamos un paso más cerca de nuestro objetivo, pero si miramos bien, no coincide exactamente con lo de después. Por ejemplo, para [Plata, 2017], PreviousColorSales se establece en [Rojo, 2020].

Adición de ORDERBY

Esa definición anterior equivale 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]
        )
    )

En este caso, la llamada a OFFSET usa ORDERBY para ordenar la tabla por Color y CalendarYear en orden ascendente, que determina lo que se considera la fila anterior que se devuelve.

La razón por la que estos dos resultados son equivalentes es que ORDERBY contiene automáticamente todas las columnas de la relación que no están en PARTITIONBY. Dado que no se especificó PARTITIONBY, ORDERBY se establece en Color, CalendarYear y CurrentYearSales. Sin embargo, dado que los pares Color y CalendarYear en la relación son únicos, agregar CurrentYearSales no cambia el resultado. De hecho, incluso si solo se especificara Color en ORDERBY, los resultados son los mismos, ya que CalendarYear se agregaría automáticamente. Esto se debe a que la función agregará tantas columnas como sea necesario a ORDERBY para asegurarse de que cada fila se pueda identificar de forma única mediante las columnas ORDERBY y 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]
        )
    )

Adición de PARTITIONBY

Ahora, para obtener casi el resultado que buscamos podemos usar PARTITIONBY como se muestra en la siguiente expresión de tabla 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 es opcional aquí porque ORDERBY contiene automáticamente todas las columnas de la relación que no se especifican en PARTITIONBY. Por lo tanto, la siguiente expresión devuelve los mismos resultados porque ORDERBY se establece en CalendarYear y CurrentYearSales automáticamente:

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

Aunque ORDERBY se establece en CalendarYear y CurrentYearSales automáticamente, no se proporciona ninguna garantía sobre el orden en el que se agregarán. Si CurrentYearSales se agrega antes de CalendarYear, el orden resultante no está alineado como se espera. Sea explícito al especificar ORDERBY y PARTITIONBY para evitar confusiones y resultados inesperados.

Ambas expresiones devuelven el resultado que buscamos:

Color	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
«Negro»	2017	393885
«Negro»	2018	1818835	393885
«Negro»	2019	3981638	1818835
«Negro»	2020	2644054	3981638
"Blue"	2019	994448
"Blue"	2020	1284648	994448
«Multi»	2019	48622
«Multi»	2020	57849	48622
«N/D»	2019	207822
«N/D»	2020	227295	207822
«Rojo»	2017	2961198
«Rojo»	2018	3686935	2961198
«Rojo»	2019	900175	3686935
«Rojo»	2020	176022	900175
«Plata»	2017	326399
«Plata»	2018	750026	326399
«Plata»	2019	2165176	750026
«Plata»	2020	1871788	2165176
«Blanco»	2019	2517
«Blanco»	2020	2589	2517
«Amarillo»	2018	163071
«Amarillo»	2019	2072083	163071
«Amarillo»	2020	2621602	2072083

Como ve en esta tabla, la columna PreviousYearSalesForSameColor muestra las ventas del año anterior del mismo color. Para [Rojo, 2020], devuelve las ventas de [Rojo, 2019], etc. Si no hay ningún año anterior, por ejemplo, en el caso de [Rojo, 2017], no se devuelve ningún valor.

Puede considerar PARTITIONBY como una manera de dividir la tabla en partes en las que ejecutar el cálculo OFFSET. En el ejemplo anterior, la tabla se divide en tantas partes como colores, una por cada color. A continuación, dentro de cada parte, se calcula el OFFSET, ordenado por CalendarYear.

Visualmente, lo que sucede es esto:

En primer lugar, la llamada a PARTITIONBY da como resultado que la tabla se divida en partes, una para cada color. Esto se representa mediante los cuadros azules claros de la imagen de la tabla. A continuación, ORDERBY se asegura de que cada parte está ordenada por CalendarYear (representado por las flechas naranjas). Por último, dentro de cada parte ordenada, para cada fila, OFFSET busca la fila encima de ella y devuelve ese valor en la columna PreviousYearSalesForSameColor. Puesto que para cada primera fila de cada parte no hay ninguna fila anterior en esa misma parte, el resultado de esa fila para la columna PreviousYearSalesForSameColor está vacía.

Para lograr el resultado final, simplemente tenemos que restar CurrentYearSales de las ventas del año anterior para el mismo color devuelto por la llamada a OFFSET. Puesto que no estamos interesados en mostrar las ventas del año anterior para el mismo color, sino solo en las ventas del año actual y en la diferencia año a año. Esta es la expresión de tabla final 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]
        )
    )

Y este es el resultado de esa expresión:

Color	CalendarYear	CurrentYearSales	YoySalesForSameColor
«Negro»	2017	393885	393885
«Negro»	2018	1818835	1424950
«Negro»	2019	3981638	2162803
«Negro»	2020	2644054	-1337584
"Blue"	2019	994448	994448
"Blue"	2020	1284648	290200
«Multi»	2019	48622	48622
«Multi»	2020	57849	9227
«N/D»	2019	207822	207822
«N/D»	2020	227295	19473
«Rojo»	2017	2961198	2961198
«Rojo»	2018	3686935	725737
«Rojo»	2019	900175	-2786760
«Rojo»	2020	176022	-724153
«Plata»	2017	326399	326399
«Plata»	2018	750026	423627
«Plata»	2019	2165176	1415150
«Plata»	2020	1871788	-293388
«Blanco»	2019	2517	2517
«Blanco»	2020	2589	72
«Amarillo»	2018	163071	163071
«Amarillo»	2019	2072083	1909012
«Amarillo»	2020	2621602	549519

Uso de MATCHBY

Es posible que haya observado que no hemos especificado MATCHBY en absoluto. En este caso, no es necesario. Las columnas de ORDERBY y PARTITIONBY (en la medida en que se especificaron en los ejemplos anteriores) son suficientes para identificar de forma única cada fila. Dado que no se ha especificado MATCHBY, las columnas especificadas en ORDERBY y PARTITIONBY se usan para identificar de forma única cada fila para que se puedan comparar para permitir que OFFSET proporcione un resultado significativo. Si las columnas de ORDERBY y PARTITIONBY no pueden identificar de forma única cada fila, se pueden agregar columnas adicionales a la cláusula ORDERBY si esas columnas adicionales permiten identificar cada fila de forma única. Si no es posible, se devuelve un error. En este último caso, especificar MATCHBY puede ayudar a resolver el error.

Si se especifica MATCHBY, las columnas de MATCHBY y PARTITIONBY se usan para identificar de forma única cada fila. Si no es posible, se devuelve un error. Incluso si NO se requiere MATCHBY, considere la posibilidad de especificar explícitamente MATCHBY para evitar cualquier confusión.

Siguiendo con los ejemplos anteriores, esta es la última expresión:

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

Si queremos ser explícitos sobre cómo se deben identificar las filas de forma única, podemos especificar MATCHBY como se muestra en la siguiente expresión 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]
        )
    )

Dado que se especifica MATCHBY, tanto las columnas especificadas en MATCHBY como en PARTITIONBY se usan para identificar filas de forma única. Dado que Color se especifica en MATCHBY y en PARTITIONBY, la siguiente expresión es equivalente a la expresión 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]
        )
    )

Dado que la especificación de MATCHBY no es necesaria en los ejemplos que hemos examinado hasta ahora, echemos un vistazo a un ejemplo ligeramente diferente que requiere MATCHBY. En este caso, tenemos una lista de líneas de pedido. Cada fila representa una línea de pedido para un pedido. Un pedido puede tener varias líneas de pedido y la línea de pedido 1 aparece en muchos pedidos. Además, para cada línea de pedido tenemos un ProductKey y un SalesAmount. Un ejemplo de las columnas pertinentes de la tabla tiene este aspecto:

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 y SalesOrderLineNumber son necesarios para identificar filas de forma única.

Para cada pedido, queremos devolver el importe de ventas anterior del mismo producto (representado por ProductKey) ordenado por SalesAmount en orden descendente. La siguiente expresión no funcionará porque hay potencialmente varias filas en vRelation, ya que se pasa a OFFSET:

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

Esta expresión devuelve un error: "El parámetro Relation de OFFSET puede tener filas duplicadas, lo que no es viable".

Para que esta expresión funcione, debe especificarse MATCHBY y debe incluir todas las columnas que definan una fila de forma única. MATCHBY es necesario aquí porque la relación, FactInternetSales, no contiene ninguna clave explícita ni columnas únicas. Sin embargo, las columnas SalesOrderNumber y SalesOrderLineNumber forman una clave compuesta, donde su existencia conjunta es única en la relación y, por tanto, pueden identificar de forma única cada fila. Solo especificar SalesOrderNumber o SalesOrderLineNumber no es suficiente, ya que ambas columnas contienen valores repetidos. La siguiente expresión resuelve el 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]
            )
    )

Y esta expresión ya sí devuelve los resultados que buscamos:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Importe de ventas anterior
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

ORDERBY
PARTITIONBY
MATCHBY
INDEX
OFFSET
WINDOW
RANK
ROWNUMBER

Compartir a través de

Descripción de las funciones ORDERBY, PARTITIONBY y MATCHBY

Escenario

Uso de OFFSET

Adición de ORDERBY

Adición de PARTITIONBY

Uso de MATCHBY

Comentarios

Recursos adicionales

Compartir a través de

Descripción de las funciones ORDERBY, PARTITIONBY y MATCHBY

Escenario

Uso de OFFSET

Adición de ORDERBY

Adición de PARTITIONBY

Uso de MATCHBY

Contenido relacionado

Comentarios

Recursos adicionales