Siempre utilizamos filtros cuando desarrollamos expresiones DAX, como medidas DAX, o cuando escribimos consultas DAX.
¿Pero qué pasa exactamente cuando aplicamos filtros?
Este artículo trata exactamente sobre esta pregunta.
Comenzaré con consultas simples y agregaré variantes para explorar lo que sucede bajo el capó.
Utilizo DAX Studio y la opción de mostrar los tiempos del servidor para cada consulta.
En caso de que desee obtener más información sobre esta función y cómo interpretar los resultados, lea el primer artículo en la sección Referencias al final de este artículo.
Comencemos con la consulta base:
EVALUAR CALCULATETABLE( SUMMARIZECOLUMNS('Producto'[Marca] ,"Ventas en línea", [Suma de ventas en línea] ) )
Cuando activamos los tiempos del servidor y ejecutamos la consulta, obtenemos las estadísticas de ejecución y la consulta/consultas de los motores de almacenamiento (SE) necesarias para obtener los datos:
Como puede ver, solo necesitamos una consulta del motor de almacenamiento (SE) para recuperar los resultados.
La consulta se completa en sólo 47 ms y es atendida casi en su totalidad por SE (95,7%).
Cuanto más tiempo pueda dedicar el SE a una consulta, mejor, porque es el componente que recupera datos de los almacenes y tablas de datos.
Además, el SE puede utilizar varios núcleos de CPU, mientras que el Formula Engine (FE) puede utilizar sólo uno. No podemos examinar exactamente lo que sucede en FE tan fácilmente como podemos hacerlo con las consultas SE.
Puede obtener más información sobre la diferencia entre estos dos motores en el artículo mencionado anteriormente.
Una breve nota:
Hace unos meses escribí un artículo aquí con un título muy similar. Pero, si bien ese se trataba solo de filtros de fecha con funciones de Time Intelligence, este va un paso más allá en la madriguera del conejo.
Este es mucho más genérico que aquel.
Si se lo perdió, agregué el enlace del artículo y recursos adicionales sobre el tema actual a la sección Referencias a continuación.
Agregar filtros simples
A continuación, agregamos un filtro simple para el color rojo del producto a la consulta:
EVALUAR CALCULATETABLE( SUMMARIZECOLUMNS('Producto'[Nombre de marca] ,"Ventas en línea", [Suma de ventas en línea] ), 'Producto'[Nombre de color] = "Rojo" )
Aquí está la consulta y los resultados restringidos al color rojo del producto:
Cuando miramos las estadísticas de la consulta, vemos esto:
Como puede ver, toda la consulta se ejecuta en una única consulta SE.
El filtro está en la cláusula WHERE de la consulta. Por lo tanto, sólo se recuperan los datos restringidos.
Esto es visible en la columna "Filas", ya que esta consulta solo devuelve 14 filas.
Pero, ¿qué sucede cuando usamos la función FILTER() para filtrar los productos?
EVALUAR CALCULATETABLE( SUMMARIZECOLUMNS('Producto'[Nombre de marca] ,"Ventas en línea", [Suma de ventas en línea] ), FILTRO('Producto' ,'Producto'[Nombre de color] = "Rojo") )
Como sabrá, no se recomienda utilizar la función FILTER() debido a su funcionamiento.
Puede obtener más información sobre este tema en el segundo artículo vinculado en la sección Referencias a continuación.
El resultado no cambia:
¿Pero cómo afecta el plan de ejecución y las consultas SE?
Como puede ver, en este caso, el SE optimiza la consulta, generando el mismo plan de ejecución que antes.
Pero, a medida que cambiemos nuestro código, veremos que usar FILTER() no siempre es una buena idea.
Agregar múltiples filtros
Ahora bien, ¿qué sucede cuando agregamos múltiples filtros a una consulta?
EVALUAR CALCULATETABLE( SUMMARIZECOLUMNS('Producto'[Nombre de marca] ,"Ventas en línea", [Suma de ventas en línea] ), 'Producto'[Nombre de color] = "Rojo", 'Geografía'[Nombre de continente] = "Europa" )
Si bien el resultado no nos resulta tan interesante, veamos las estadísticas de la consulta:
Nuevamente, la consulta puede ser atendida por una única consulta SE que contenga ambos filtros.
La consulta se ejecuta tan rápido que el porcentaje de tiempo FE es relativamente alto, pero aún así solo tarda 6 ms.
Al cambiar la consulta para usar la función FILTER(), la consulta SE tampoco cambia:
Esto muestra que, con este tipo de consulta, el motor puede optimizar la ejecución para encontrar la forma más eficiente de cumplir con la consulta DAX.
De todos modos, el resultado no cambia. En ambos casos es idéntico, como debe ser, porque no cambiamos el filtro propiamente dicho. Pero por favor ten paciencia conmigo; Vuelvo a la función FILTER() y a por qué es importante comprender sus efectos en un momento.
Mover filtros a medidas
A continuación, veamos qué sucede cuando el filtro se mueve hacia la medida.
Hasta ahora, la consulta se construía para que la medida [Suma Ventas Online] recibiera su filtro desde afuera.
Probemos esto:
DEFINE MEASURE 'Todas las medidas'[Ventas en línea A. Datum] = CALCULATE( SUMX('Ventas en línea', ( 'Ventas en línea'[Precio unitario] * 'Ventas en línea'[Cantidad de ventas]) – 'Ventas en línea'[Montodedescuento] ), 'Producto'[Marca] = "A. Datum" ) EVALUAR CALCULATETABLE( SUMMARIZECOLUMNS('Producto'[Marca] ,"Dato A. De Ventas En Línea", [Dato A. De Ventas En Línea] ) )
Como puedes ver, el filtro se aplica dentro de la medida [Dato A. Ventas Online].
Por supuesto, el número resultante es el mismo en cada fila del resultado, ya que la Marca se establece como "A. Datum":
Pero la ejecución es ligeramente diferente:
Esta vez, tenemos dos consultas SE.
La consulta para obtener las ventas de la Marca “A.Datum”. Esta consulta contiene el filtro para esa marca. La segunda consulta se utiliza para obtener la lista de todas las marcas en el conjunto de resultados.
La primera consulta es la más importante para nosotros, porque todavía muestra el filtro para la marca establecida dentro de la medida.
El SE puede atender completamente esta consulta con un filtro simple de una manera muy eficiente.
Pero, en la mayoría de los casos, queremos agregar varias medidas a una consulta (o un objeto visual en un informe).
¿Qué sucede cuando agregamos la medida [Suma de ventas en línea] a la consulta?
El resultado no es especialmente importante, ya que muestra una columna con las ventas de cada marca y otra con las ventas de la marca filtrada.
Pero las estadísticas de consultas son interesantes:
Como puede ver en la línea marcada en rojo en la consulta SE, el filtro Marca ya no está presente.
Debido a que el motor reconoce que el filtro de la medida se aplica a la misma columna que la de la consulta, mueve el filtro al FE y devuelve el resultado.
Ahora bien, ¿qué pasa cuando filtramos otra columna de la medida, por ejemplo, el color?
DEFINE MEASURE 'Todas las medidas'[Ventas en línea Rojo] = CALCULATE( SUMX('Ventas en línea', ( 'Ventas en línea'[Precio unitario] * 'Ventas en línea'[Cantidad de ventas]) – 'Ventas en línea'[Montodedescuento] ), 'Producto'[NombreColor] = "Rojo" ) EVALUAR CALCULATETABLE( SUMMARIZECOLUMNS('Producto'[Nombre de marca] ,"Ventas en línea", [Suma de ventas en línea], "Ventas en línea rojo", [Ventas en línea rojo]))
Una vez más, el resultado no es particularmente interesante. Nos interesan las estadísticas de la consulta:
Como puedes ver, esta vez tenemos dos consultas por BrandName. Uno sin y otro con el filtro para el color.
Ambas consultas devuelven el mismo número de filas (14), una para cada marca.
El FE se encarga de combinar los dos resultados en una sola tabla.
Toda la consulta todavía es atendida principalmente por SE, lo cual es excelente.
Pero ahora, agreguemos la función FILTER() al Filtro:
Para este ejemplo, cambio la medida para filtrar dos valores con el operador IN:
,'Producto'[Nombre de la marca] IN { "A. Datum", "Adventure Works" }
En esta variante, la consulta SE es como las anteriores.
El filtro se pasa directamente a la cláusula WHERE de la consulta.
Pero, ¿qué pasa cuando lo cambio a esto?
,FILTER('Producto','Producto'[Nombre de marca] IN { "A. Datum", "Adventure Works" } )
En primer lugar, el resultado cambia:
La razón es que FILTER() funciona de manera completamente diferente.
Conserva el contexto de filtro existente y agrega uno nuevo.
Expliqué este comportamiento en otro artículo que agregué como segundo enlace en la sección Referencias a continuación.
Además, el SE ya no puede manejar esto en una sola consulta:
Las dos primeras consultas recuperan los valores de la marca a filtrar (consulte las consultas marcadas en rosa).
Observe la gran cantidad de filas (324 y 2560) devueltas por las dos primeras consultas. Esta es la materialización de resultados intermedios necesarios para realizar el cálculo.
La tercera consulta utiliza estos resultados intermedios para filtrar los datos (marcados en rojo).
El resultado de la tercera consulta son solo dos filas: las dos filas que vemos en el resultado general.
Como se describe en mi otro artículo, FILTER() debe usarse con cuidado.
No sólo es considerablemente más lento, sino que además funciona de forma completamente diferente a un simple filtro.
De todos modos, puedo restaurar el comportamiento anterior agregando ALL() en la llamada FILTER():
No quiero ocultar que este ejemplo es especial, ya que el filtro aplicado afecta a la misma columna que se utiliza en la consulta.
Al cambiar la consulta para filtrar el país, el motor puede optimizar la ejecución y volver a utilizar el formulario simple:
Como puede ver, el motor optimiza la ejecución de la consulta y recurre a un filtro simple al filtrar columnas que difieren de las utilizadas en la consulta DAX. En el recuadro azul, ves los resultados.
Veo esta forma de filtrado muy a menudo cuando los desarrolladores que no son tan competentes escriben medidas DAX.
El uso de la función FILTER() parece intuitivo, pero puede producir resultados incorrectos o confusos y es más lento que un simple filtro. Recomiendo encarecidamente leer mi artículo vinculado a continuación sobre esta función, así como la documentación de dax.guide y los artículos vinculados en SQLBI.com.
Además, tengo que escribir mucho más que cuando uso un filtro simple.
Como persona perezosa, esta es una razón importante para no usar FILTER() cuando no es necesario.
Agregar un filtro complejo
Finalmente, quiero mostrar lo que sucede al aplicar un filtro usando una función DAX, como CONTAINSSTRING().
EVALUAR CALCULATETABLE( SUMMARIZECOLUMNS('Producto'[Nombre de marca], "Ventas en línea", [Suma de ventas en línea]), CONTAINSSTRING('Ventas en línea'[Número de pedido de ventas], "202402252C"))
Esta consulta se ejecuta cuando utiliza una segmentación de datos en su informe para filtrar un pedido específico y recuperar las marcas de los productos comprados.
Como el resultado no es importante en este punto, veamos directamente las estadísticas de la consulta:
Si bien la consulta tardó más de 6 segundos en completarse, el FE dedicó el 99,6% del tiempo a ejecutar la función CONTAINSSTRING() para encontrar filas coincidentes en los datos. Esta operación consume mucha CPU, ya que el FE sólo puede utilizar un núcleo. Cuando ejecuto esta consulta en mi computadora portátil, tarda más de 2 segundos más.
Elegí deliberadamente una función lenta para demostrar sus efectos.
Pero el SE aún pudo ejecutar la consulta con una sola consulta. Sin embargo, el efecto positivo de este hecho es insignificante en este caso.
Conclusión
Si bien no es mi intención darte consejos sobre qué hacer y qué no hacer, quería mostrarte las consecuencias de las diferentes formas de escribir código DAX y aplicar filtros en tus medidas o consultas.
Los motores DAX son muy eficientes para optimizar las consultas, pero tienen limitaciones.
Por lo tanto, siempre debemos tener cuidado al escribir nuestro código DAX.
Si el rendimiento es deficiente o el código escrito por otra persona parece extraño, debemos analizarlo para determinar cómo mejorarlo.
Quería mostrarte cómo hacerlo y qué buscar al analizar tu código DAX.
Recordar:
El motor de almacenamiento (SE) puede utilizar varios núcleos de CPU. Cuanto más trabajo haga la SE, mejor. El SE solo puede ejecutar agregaciones simples y funciones matemáticas simples (como +, -, x y /). Intente reducir la carga de trabajo en el motor de fórmulas (FE). El FE puede usar solo un núcleo de CPU. Intente reducir la materialización de datos (la columna Filas en las estadísticas de la consulta). Intente reducir la cantidad de consultas SE.
Sé que los requisitos nos obligarán a escribir código DAX, que no es óptimo.
Peor aún, los diseñadores del informe podrían agregar lógica al informe que provoque un rendimiento deficiente.
En tales casos, elimine esa lógica y verifique nuevamente el tiempo de respuesta. Podría valer la pena explorar la posibilidad de crear una medida específica para estos casos. Recuerde que es posible crear medidas locales en un informe que esté conectado a un modelo semántico a través de una conexión de vida.
Pero lo más importante: tómate tu tiempo al escribir código DAX. Puede ahorrar tiempo al evitar la necesidad de optimizar su código DAX, que se escribió rápidamente. Hablo por experiencia. Este es un sentimiento muy malo.
Espero que hayas aprendido algo nuevo.
Referencias
Para conocer los detalles sobre cómo interpretar los resultados de Server Timings en DAX Studio, lea este artículo:
¿Tiene curiosidad sobre cómo utilizar correctamente la función FILTER()? Lee esto:
Otra función DAX que puede perjudicar el rendimiento es KEEPFILTERS(). Para obtener más información sobre la función KEEPFILTERS(), lea este artículo:
Aquí, el artículo mencionado sobre filtros de fechas:
Una interesante publicación de blog de Data Mozart sobre el motor de almacenamiento:
Como en mis artículos anteriores, utilizo el conjunto de datos de muestra de Contoso. Puede descargar el conjunto de datos ContosoRetailDW de forma gratuita desde Microsoft aquí.
Los datos de Contoso se pueden utilizar libremente bajo la licencia MIT, como se describe en este documento. Cambié el conjunto de datos para cambiar los datos a fechas contemporáneas.