DAX. ¿Todos los caminos conducen a Roma?

DAX. ¿Todos los caminos conducen a Roma?

Ayer retomé la buena costumbre de impartir una formación. Cuando doy una formación ganan las personas a las que va dirigida pero también siempre gano yo. El formador siempre aprende en sus clases y de mis formaciones han surgido muchos posts que luego han ayudado a la comunidad.

Ayer además fue muy especial, una clase particular de manera altruista y desinteresada para una persona que tiene muchísimas ganas de aprender, que se pelea sola contra los problemas que le van surgiendo en DAX como yo cuando empecé en este mundo y que vi que necesitaba que la centrasen un poco explicándole un tema clave en DAX como es el de los contextos.

La clase fue un vis a vis virtual, muy amena y distendida y 100% improvisada a partir de un modelo realizado sobre Adventure Works y en un momento dado me dijo: vengo del mundo de la programación en el que lo normal es que todas las cosas se hagan de la misma forma siempre y sin embargo en DAX puedes conseguir lo mismo de varias maneras y nunca se cual utilizar. Cuando utilizar un CALCULATE con un SUM o un SUMX o un FILTER o KEEPFILTER, o el filtro directamente sin función…

Se nos ocurrió entonces a partir de un ejemplo de “The Definitive Guide to DAX” analizar las diferentes medidas que nos podían llevar a realizar el cálculo de Ventas de la familia “Bikes” y decidir cual es la opción óptima.

ESCENARIO:

Como he comentado utilizamos el modelo de Adventure Works y la primera elección debemos hacerla en el modelo, cuando estamos en fase de creación de la tabla de hechos en Power Query podemos resolver muchos problemas posteriores.

¿Por qué comento esto? Pues porque lo normal es tener un campo de cantidad y otro de precio unitario. Si nosotros en Power Query creamos la columna VentaBruta (Cantidad * Precio) luego simplemente tendremos que hacer un SUM, si no la creamos tendremos que pasar al plan B crear una columna calculada para usar el SUM o utilizar SUMx

¿Cuál es la mejor opción? Todas son absolutamente válidas pero pueden afectar en mayor o menor medida a la performance del modelo. Mi recomendación es por este orden

1º Si se puede crear la columna en power query, hacerlo para utilizar el SUM

2º Si no podemos tener por cualquier motivo la columna en power query (imaginad un modelo que nos viene dado y no podemos tocar) es mejor utilizar SUMX que crear la columna calculada.

Pero para gustos colores, si el modelo tira y la performance es asumible existen esas tres vías totalmente válidas.

MEDIDAS EN DAX. Diferentes formas de conseguir la medida Ventas de Bicicletas.

Vamos a trabajar con el escenario más amplio, tenemos la columna cantidad, tenemos la columna PrecioUnitario y tenemos la columna cantidadxprecio, pero vamos a ver las fórmulas que usaríamos también si no la tuviéramos. Y se nos presenta la segunda elección, como aplicamos el filtro “Bikes”

Caso1 (presuponemos que no tenemos la columna cantidad*Precio) =


SUMX (
    FILTER ( fVentas, RELATED ( dProductos[Familia] ) = «Bikes» ),
    fVentas[Cantidad] * fVentas[PrecioUd]
)

Lo que hacemos en este caso es iterar fila a fila por una tabla de ventas filtrada por la familia “Bikes” y dado que el nombre de la familia lo tenemos en la tabla de productos tenemos que acceder a el mediante la función RELATED

Caso2 (presuponemos que no tenemos la columna cantidad*Precio) =

CALCULATE (
    SUMX ( fVentas, fVentas[Cantidad] * fVentas[PrecioUd] ),
    KEEPFILTERS ( dProductos[Familia] = «Bikes» )
)

En esta ocasión, en lugar de filtrar la tabla sobre la que hacemos el SUMX, filtramos directamente el SUMX con un CALCULATE. En este caso es indiferente utilizar KEEPFILTER o utilizar FILTER(dProductos, dProductos[Familia]=»Bikes») el resultado es el mismo aunque KEEPFILTER como se puede ver, si se conoce este aspecto es mucho más entendible y simple.

Caso3 (presuponemos que no tenemos la columna cantidad*Precio) =

CALCULATE (
    SUMX ( fVentas, fVentas[Cantidad] * fVentas[PrecioUd] ),
    dProductos[Familia] = «Bikes»
)

Hacemos lo mismo que en el caso 2 pero sin el KEEPFILTERS. Esto es equivalente a la fórmula:

CALCULATE (
    SUMX ( fVentas, fVentas[Cantidad] * fVentas[PrecioUd] ),
    FILTER ( ALL ( dProductos ), dProductos[Familia] = «Bikes» )
)

y luego observaremos las implicaciones que tiene.

Caso 4 (utilizamos ya la columna cant*precio) =

CALCULATE (
    SUM ( fVentas[CantxPre] ),
    KEEPFILTERS ( dProductos[Familia] = «Bikes» )
)


Como estamos utilizando ya la columna calculada, no necesitamos iterar por cada línea con el SUMX para realizar la multiplicación en la iteración. Es matemática simple, no podemos hacer sum(cantidad)* Sum(ventas) el resultado no sería el esperado ya que hay que hacer un SUM(cantidad*venta) y esto no se puede hacer con la función SUM que solo admite una columna (no una expresión) como argumento.

Esta medida con KEEPFILTERS igual que comentamos anteriormente es análoga a la medida

CALCULATE (
    SUM ( fVentas[CantxPre] ),
    FILTER ( dProductos, dProductos[Familia] = «Bikes» )
)

ANÁLISIS y ELECCIÓN

Ya tenemos todas las posible métricas planteadas. Sabiendo que KEEPFILTERS equivale a FILTER(Table,expresión) y el filtro directo equivale a FILTER(ALL(table),expresión) toca elegir y para ello debemos de analizar que ocurre en diferentes contextos.

Pero tenemos claro lo que es el CONTEXTO en DAX, hay amigos, ese es el tema.

Mirad esta imagen:

Son las 5 métricas que hemos utilizado en el desarrollo en un mismo contexto de evaluación, a nivel de producto. Las 5 devuelven el mismo resultado y en un modelo muy pequeño como es este caso del ejemplo con apenas 120.000 filas en la tabla de hechos la diferencia de performance es inapreciable. Habría que irse a un modelo más complejo con millones de filas para apreciar realmente cuál es la óptima y decantarnos por ella. Evidentemente va a depender también como comentábamos de si tenemos o no la columna (cantidad*precio). En todo caso, a lo que quiero llegar en este punto es en cómo afecta el contexto de evaluación a éstas métricas. En este caso en el contexto de producto no afecta para nada.

Si introducimos en la visual la familia, el contexto de evaluación permanece inalterable. Seguimos evaluando la métrica al nivel de producto y por tanto dado los filtros, no nos aparecen datos de otras familias:

¿Pero qué creéis que pasa si eliminamos el producto de la visual y cambiamos el contexto de evaluación al nivel familia?

OMG! Qué pasó!!!Aparecieron el resto de familias para las metricas 3 y 5 en las que utilizamos el filtro directo que equivale a FILTER(ALL(Tabla),Expresión)

¿A qué se debe esto? Esto se debe a la primera y más importante regla de la aplicación de los contextos en las medidas DAX.

1º se evalúa el contexto de consulta. ¿Cuál es el contexto de consulta? Pues el contexto que se introduce mediante las dimensiones en la visual o mediante filtros de slicer. En este caso nuestro contexto de consulta es el nivel familia

2º se evalúa el contexto de filtro que contiene la medida. En los casos 1,2 y 4 el contexto del filtro es muy concreto, evalúame la medida para el contexto Familia=”Bikes” Si sacamos de la visual las medidas 3 y 5 nos devuelve sólo las ventas para Bikes

Y otra regla primordial, en caso de conflicto entre los contextos de consulta y de filtro, prevalece el contexto de filtro que aparece en el calculate, por tanto aunque el contexto de consulta es familia, prevalece el contexto de filtro del calculate para solo mostrar Familia=Bikes.

En los casos 3 y 5 sin embargo el contexto de filtro que se establece es el resultado la evaluación del Resultado Familia=”Bikes” pero en el contexto de Familia ya que con el ALL(Tabla) estamos estableciendo como contexto la Familia, no la Familia=”Bikes” Por eso al evaluar la métrica y para cualquier Familia de productos el resultado que nos devuelve es el de Ventas de Bicicletas. En el contexto de Familia=”Accesories” se vendieron 95.145.657,13 $ en bicicletas.

Esta particularidad es la que necesitaremos conocer a la hora de decantarnos por una medida u otra En la mayoría de los contextos las 5 nos arrojarán el mismo resultado, pero cuando estemos en el contexto de Familia, ojo aquí es cuando debemos pararnos a valorar cual necesitamos, si la que nos devuelve datos de ventas de bicicletas para todas las familias o las que nos devuelve datos de ventas de bicicletas sólo para la familia Bicicletas.

CONCLUSIONES:

En DAX todos los caminos llevan a Roma, pero se hace necesario conocer bien los contextos en los que queremos trabajar para elegir la mejor opción.

FranM

4 comentarios

Abel Publicado el11:55 am - 21/09/2020

Muy buena explicación !

Francisco Mullor Publicado el1:56 pm - 21/09/2020

Gracias

OscarK Publicado el3:58 am - 23/09/2020

Muy buen ejemplo. Gracias.

Tomás Coleto Publicado el4:10 pm - 03/04/2021

Buen ejemplo del contexto filtro. Gracias.

Deja una respuesta