Compartir a través de

Descripción de las funciones ORDERBY, PARTITIONBY y MATCHBY

  • Artículo

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:

Table showing OFFSET by Calendar Year

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

Contenido relacionado

ORDERBY
PARTITIONBY
MATCHBY
INDEX
OFFSET
WINDOW
RANK
ROWNUMBER