Función DAX ALL y ALLEXCEPT

En otros artículos hemos visto la utilidad de la función DAX FILTER, la cual es útil para limitar el número de registros que se quieren consultar en la fórmula. Sin embargo, a veces queremos hacer lo contrario, es decir, queremos aumentar o extender el número de registros a considerar en la consulta o cálculo. DAX ofrece un conjunto de funciones DAX especialistas en este propósito: ALL, ALLEXCEPT, ALLCROSSFILTER, ALLNOBLANKROW, y ALLSELECTED.

Para empezar, vamos a analizar con ejemplos las funciones ALL y ALLEXCEPT.

ALL retorno todas las filas de una tabla o todos los valores de una columna o varias columnas, dependiendo del parámetro pasado. Por ejemplo, la siguiente expresión DAX retorna una tabla calculada ProductCopy con una copia de todas las filas de la tabla Product:

ProductCopy = ALL ( 'Product' )

La función ALL es muy útil sobre todo para realizar porcentajes y ratios porque automáticamente ignora los filtros introducidos en el informe. Por ejemplo, supongamos que queremos calcular el porcentaje respecto del total de cada una de las filas del siguiente report:

La medida Cantidad de Ventas se calcula con un iterador de la «Cantidad Ventas» por el «Precio Neto» de cada una de las categorías:

Cantidad de Ventas = SUMX
                        ( Sales, 
                            Sales[Cantidad] * Sales[Precio Neto] 
                        )

Para calcular el porcentaje, dividimos la medida Cantidad Ventas por el Total de Ventas, esta medida contiene la función ALL, la cual realiza el cálculo sobre toda la tabla.

Cantidad Total de Ventas = SUMX( 
                                ALL(Sales), 
                                Sales[Cantidad] * Sales[Precio Neto] 
                            ) 

La única diferencia entre las dos medidas es que en lugar de utilizar la tabla Sales como parámetro utilizamos ALL ( Sales ). Haciendo esto estamos en disposición de poder calcular el porcentaje por cada categoría, con una simple división, función DIVIDE, aunque también se podría utilizar el operador «/».

Pct Ventas = DIVIDE([Cantidad de Ventas],[Cantidad Total de Ventas]) 

Como se puede ver utilizando ALL ( Sales ) como tabla dentro de SUMX estamos diciendo a la medida que realice el cálculo total de las categorías y lo compute para cada una de las categorías, esto hace se repita el valor «584.399,59» para cada fila. Por último, sólo tenemos que dividir ambas medidas y obtenemos el porcentaje por cada fila o cada categoría.

Se pueden añadir múltiples columnas como parámetro de la función ALL. En este caso, la función retorna las combinaciones existentes posibles de los valores de las columnas.

Siempre hay que tener en cuenta que la función ALL ignora todos los filtros introducidos en el informe para producir el resultado.

Si en lugar de incluir todas las columnas de la tabla, necesitas excluir alguna de ellas debes utilizar ALLEXCEPT. Esta función recibe como parámetros una tabla y las columnas a excluir. Como resultado la función retorna la combinación existente de los valores de las otras columnas de la tabla.

Por ejemplo, estas dos expresiones retornan la misma tabla:

ALL(Producto[Name],Producto[Class],Producto[Brand])
ALLEXCEPT(Producto,Producto[Categoria],Producto[Subcategoria],Producto[Color])

Aunque el comportamiento parece idéntico son totalmente diferentes, mientras con ALL mostramos las columnas que queremos ver, con ALLEXCEPT declaramos las columnas que queremos borrar del resultado.

Como ejemplo voy a crear una tabla con el resultado de las categorías y subcategorías que venden más de dos veces la media de las ventas de las subcategorías. Para realizar esto primero tenemos que determinar la media de ventas por subcategoría y después listar las subcategorías que tienen ventas dos veces por encima de la media.

Este es el código que he utilizado realizar el cálculo que estoy interesado:

BestCategories = 
       VAR Subcategories = 
            ALL( Producto[Categoria],Producto[Subcategoria] )
       VAR AverageSales = 
            AVERAGEX(  
                Subcategories,
                SUMX ( RELATEDTABLE ( Sales ), Sales[Cantidad de Ventas] * Sales[Precio Neto] ) 
            )
        VAR TopCategories = 
            FILTER ( 
                    Subcategories,
                    VAR SalesOfCategory = 
                        SUMX ( RELATEDTABLE ( Sales ), Sales[Cantidad de Ventas] * Sales[Precio Neto] ) 
                    RETURN SalesOfCategory >= AverageSales * 2
            ) 
  RETURN TopCategories

La primera variable Subcategories , guarda una lista de todas las categorías y las subcategorías. La variable AverageSales realiza el computo de la media de ventas para cada subcategoría y finalmente TopCategories borra de Subcategories las subcategorías que no superan en ventas, dos veces el valor de la media de AverageSales.

En mi caso el resultado es este: