Compartir a través de

Descripción de las funciones de ORDERBY, PARTITIONBYy MATCHBY

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

Comprender ORDERBY, PARTITIONBYy MATCHBY es fundamental para usar correctamente las funciones window. En los ejemplos que se proporcionan aquí se usan OFFSET, pero se aplican de forma similar a las otras funciones window.

Escenario

Comencemos con un ejemplo que no usa funciones window 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 SUM 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 Año Calendario CurrentYearSales
"Negro" 2017 393885
"Negro" 2018 1818835
"Negro" 2019 3981638
"Negro" 2020 2644054
"Azul" 2019 994448
"Azul" 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. Efectivamente, necesitamos una manera de encontrar las ventas del mismo color en el año anterior y restarlas de las ventas del año actual, teniendo en cuenta el contexto. Por ejemplo, para la combinación de [Red, 2019], estamos buscando las ventas realizadas en [Red, 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 está establecido en el CurrentYearSales, que corresponde a 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
"Azul" 2019 994448 2644054
"Azul" 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].

Agregar ORDERBY

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

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

La razón por la que estos dos resultados son equivalentes es porque ORDERBY contiene automáticamente todas las columnas de la relación que no están en PARTITIONBY. Como PARTITIONBY no se especificó, ORDERBY se establece en Color, CalendarYear y CurrentYearSales. Sin embargo, dado que los pares Color y CalendarYear de 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]
        )
    )

Agregar 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 expresión siguiente 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 en cuanto al orden en el que se agregarán. Si CurrentYearSales se agrega antes de CalendarYear, el orden resultante no está alineado con lo esperado. 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
"Azul" 2019 994448
"Azul" 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 para el 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 [Red, 2017], no se devuelve ningún valor.

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

Visualmente, lo que sucede es esto:

Tabla que muestra OFFSET por año calendario

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 tabla. A continuación, ORDERBY se asegura de que cada elemento está ordenado 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 del 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
"Azul" 2019 994448 994448
"Azul" 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 se especificó 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 con la habilitación OFFSET para dar 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 ORDERBY cláusula si esas columnas adicionales permiten identificar de forma única cada fila. Si no es posible, se devuelve un error. En este último caso, especificar MATCHBY puede ayudar a resolver el error.

Si MATCHBY se especifica, 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 MATCHBY no es necesario, considere la posibilidad de especificar MATCHBY explícitamente 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 MATCHBY que se especifica, se usan tanto las columnas especificadas en MATCHBY como en PARTITIONBY para identificar filas de forma única. Dado que Color se especifica tanto en MATCHBY como en PARTITIONBY, la expresión siguiente 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 no es necesario especificar MATCHBY 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 productKey y SalesAmount. Un ejemplo de las columnas pertinentes de la tabla tiene este aspecto:

Número de Orden de Venta SalesOrderLineNumber Clave de producto ImporteVentas
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 expresión siguiente 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: "OFFSETEl parámetro Relation puede tener filas duplicadas, que no se permiten".

Para que esta expresión funcione, MATCHBY debe especificarse y debe incluir todas las columnas que definen de forma única una fila. MATCHBY se requiere 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. Simplemente especificar SalesOrderNumber o SalesOrderLineNumber no es suficiente, ya que ambas columnas contienen valores repetidos. La expresión siguiente 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 realmente devuelve los resultados que buscamos.

NúmeroDeOrdenDeVenta SalesOrderLineNumber Clave de producto ImporteVentas 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