Business Intelligence Controlling

La nueva generación del reporting económico-financiero y el control de gestión empresarial

Las funciones ALL* como modificadores de CALCULATE

INTRODUCCIÓN

La función ALL (y sus compañeras de familia: ALLEXCEPT, ALLNOBLANKROW, ALLCROSSFILTERED y ALLSELECTED) son unas de las funciones tabulares de DAX más utilizadas, principalmente con el propósito de expandir el número de registros a considerar en la realización de un determinado cálculo, eliminando filtros que se encuentran activos en el contexto actual.

Como función tabular, ALL devuelve todas las filas de una tabla o todos los valores únicos de una o más columnas dependiendo del parámetro utilizado.

Cuando usamos una de estas funciones como la función de primer nivel en un argumento de filtro de CALCULATE, su comportamiento cambia y en lugar de funcionar como una función de tabla actúa como un modificador de CALCULATE, eliminando un filtro existente en el contexto en lugar de crear uno nuevo.

Es por ello que, en el lenguaje DAX existe actualmente otra función denominada REMOVEFILTERS, que es simplemente un alias para ALL, pero que solo puede usarse como modificador de CALCULATE y no como función tabular.

LOS MODIFICADORES DE CALCULATE

A parte de entender como CALCULATE crea un nuevo contexto a partir de sus argumentos de filtro y de la transición de contexto que se produce cuando existen uno o varios contextos de fila activos en el momento de invocar dicha función, es importante entender también el concepto de modificador de CALCULATE, para tener claro el conjunto de reglas que rigen el comportamiento y el orden de ejecución del algoritmo que CALCULATE realiza.

Estos modificadores corresponden a funciones que, utilizadas como parámetros de filtro de CALCULATE, no van a introducir filtros en el nuevo contexto, sino que van a determinar cómo los argumentos de filtro se van a fusionar con el contexto original, es decir, influyen en la preparación del nuevo contexto de filtro bajo el cual se va a evaluar el parámetro de expresión de CALCULATE.

Algunos de estos modificadores son USERELATIONSHIPCROSSFILTER y KEEPFILTERS, siendo esta última función en realidad un modificador de un argumento de filtro y no un modificador de CALCULATE como tal, ya que KEEPFILTERS solo cambia la forma en la que un filtro se fusiona con el contexto original.

Como venimos diciendo, las funciones ALL*, cuando se usan como argumentos de filtro de CALCULATE, actúan como modificadores de CALCULATE. Por lo tanto, estas funciones eliminan un filtro existente en lugar de crear uno nuevo en el contexto.

La diferencia entre los dos comportamientos, añadir o quitar filtros, no es relevante en muchos escenarios. Sin embargo, hay situaciones en las que esta diferencia tiene un impacto importante y puede llevarnos a resultados erróneos.

USANDO LAS FUNCIONES ALL* COMO MODIFICADORES DE CALCULATE

Para ver con claridad la diferencia que existe entre usar las funciones de la familia ALL* como funciones tabulares y usarlas como modificadores de CALCULATE, vamos a utilizar varios ejemplos.

Ejemplo con la función ALL

Comencemos creando dos medidas, una de ellas contará el número de clientes con distinto nombre en nuestra dimensión de Clientes, y la otra hará lo mismo, pero solamente teniendo en cuenta aquellos clientes que efectivamente hayan realizado alguna compra y, por tanto, que tengan registros asociados en la tabla de hechos de Ventas.

# Clientes =
DISTINCTCOUNT ( Customer[Name] )

----

# ClientesVendidos =
CALCULATE (
    [# Clientes],
    Sales
)
DAX

Para entender esta segunda medida hay que considerar que la tabla que estamos usando como argumento de filtro de CALCULATE, la tabla Ventas, se refiere en realidad a la versión expandida de la misma y, por tanto, contiene todas las columnas de Ventas, pero también todas las columnas de las tablas que se encuentran en el lado 1 de una relación con esta. Como nuestro modelo solo contiene una tabla de hechos, la tabla Ventas, su versión expandida contiene en realidad todo el modelo de datos.

Es por esto que, aunque estemos contando los valores de una columna de la dimensión Cliente, el resultado corresponde al número de clientes que han realizado alguna compra. Dado que utilizamos la tabla de hechos como argumento de filtro de CALCULATE, el contexto de filtro final sobre el que se evaluará el parámetro de expresión contiene todas las columnas de la dimensión Cliente, ya que dicha dimensión se encuentra en el lado 1 de una relación con Ventas.

Podemos ver el resultado de ambas medidas en la siguiente consulta:

Ahora vamos a introducir en el contexto de filtro algún atributo de la dimensión Cliente, por ejemplo el País, y nuestras dos medidas se representarán de la siguiente manera:

Supongamos que existe el requisito de calcular el % del valor en el contexto sobre el total para ambas medidas, es decir, el % de clientes de cada país frente al total de todos los países. Podemos pensar en crear estas dos medidas de la siguiente manera:

% Clientes =
VAR _NumClientes = [# Clientes]
VAR _TodosLosClientes =
    CALCULATE (
        [# Clientes],
        ALL ( Sales )
    )
VAR _Rdo =
    DIVIDE (
        _NumClientes,
        _TodosLosClientes
    )
RETURN
    _Rdo
    
----

% ClientesVendidos =
VAR _NumClientesVendidos =
    CALCULATE (
        [# Clientes],
        Sales
    )
VAR _TodosLosClientesVendidos =
    CALCULATE (
        [# Clientes],
        ALL ( Sales )
    )
VAR _Rdo =
    DIVIDE (
        _NumClientesVendidos,
        _TodosLosClientesVendidos
    )
RETURN
    _Rdo
DAX

Si observamos el resultado y realizamos los cálculos, veremos que la medida % ClientesVendidos nos devuelve un resultado erróneo, que podemos identificar fácilmente al ver que el total no corresponde al 100%:

El motivo de este comportamiento lo podemos encontrar en el cálculo del denominador de la medida incorrecta:

VAR _TodosLosClientesVendidos =
CALCULATE(
        [# Clientes],
        ALL(Sales)
)
DAX

Estamos utilizando ALL como la función de primer nivel en un parámetro de filtro de CALCULATE y, por lo tanto, ALL no se comporta como función tabular sino como modificador de CALCULATE, y su propósito es el de eliminar filtros del contexto, no el de devolver una tabla como resultado. En el caso de este ejemplo, ALL está eliminando cualquier filtro que provenga de la versión expandida de Sales que incluye la columna presente en el contexto original, Customer[Country].

Por esta razón, en el denominador estamos calculando el número total de clientes, que es de 16.258, no el de los clientes que han efectuado alguna compra, que es de 13.992.

Llegados a este punto, entendemos porque es preferible, en el caso de usar ALL como modificador de CALCULATE, utilizar la función REMOVEFILTERS, de manera que identifiquemos rápidamente el papel que juega un parámetro de filtro en una fórmula.

¿Y cómo arreglamos el denominador de la medida % ClientesVendidos para obtener el resultado correcto? La respuesta consiste en no utilizar ALL como función de primer nivel en un parámetro de filtro. Existen varias maneras de cambiar la semántica de la fórmula y forzar, de forma explícita, la devolución por parte de ALL(Sales) de una tabla. Una de ellas consiste en utilizar CALCULATETABLE en el denominador de nuestra fórmula de la siguiente forma:

% ClientesVendidos =
VAR _NumClientesVendidos =
    CALCULATE (
        [# Clientes],
        Sales
    )
VAR _TodosLosClientesVendidos =
    CALCULATE (
        [# Clientes],
        CALCULATETABLE (
            ALL ( Sales )
        )
    )
VAR _Rdo =
    DIVIDE (
        _NumClientesVendidos,
        _TodosLosClientesVendidos
    )
RETURN
    _Rdo
DAX

De esta forma, estaremos calculando en el denominador de la métrica el número de clientes que efectivamente han realizado compras, y lograremos el resultado correcto:

Cabe resaltar, antes de dar por terminado este ejemplo, que en este último caso ALL se comporta como función tabular y no como modificador, al no ser la función de primer nivel del parámetro de filtro. De hecho, si intentáramos sustituir ALL por REMOVEFILTERS, quedando la variable del denominador de la siguiente forma:

VAR _TodosLosClientesVendidos =
CALCULATE (
    [# Clientes],
    CALCULATETABLE (
        REMOVEFILTERS ( Sales )
    )
)
DAX

Obtendríamos el siguiente error:

Ejemplo con la función ALLEXCEPT

Veamos otro ejemplo en el que podemos observar nuevamente el comportamiento de las funciones de la familia ALL* descrito a lo largo del artículo. Esta vez con una de las hermanas de ALL, la función ALLEXCEPT que, como venimos comentando, cuando se usa como función de primer nivel en un argumento de filtro de CALCULATE también actúa como un modificador de dicha función y no como función tabular.

Imaginemos que disponemos del siguiente informe en el que podemos ver las Ventas segmentadas por categoría y subcategoría de producto:

Y lo que buscamos es crear una medida que nos indique el % de ventas de cada subcategoría sobre el total de su categoría correspondiente. Para ello, vamos a crear una medida que, en el denominador, elimine el filtro sobre la Subcategoría:

PctOverCategory =
VAR _Ventas = [Ventas]
VAR _VentasCategoria =
    CALCULATE (
        [Ventas],
        REMOVEFILTERS ( 'Product'[Subcategory] )
    )
VAR _Rdo =
    DIVIDE (
        _Ventas,
        _VentasCategoria
    )
RETURN
    _Rdo
DAX

El resultado obtenido es el esperado:

Aunque la medida propuesta funciona en el escenario actual, dejaría de hacerlo si, por ejemplo, el usuario sustituye en las filas de la matriz la subcategoría por otro atributo dimensional, pongamos, por ejemplo, el color del producto:

La razón por la que nuestra medida no funciona es porque estamos eliminando el filtro de la Subcategoría, pero no el del Color, por lo que estaríamos dividiendo el valor de las Ventas de cada Subcategoría y Color por el mismo importe, obteniendo siempre como resultado un 100%.

Llegados a este punto, podemos pensar que, con el objetivo de proporcionar una solución más robusta resultaría mejor opción eliminar el filtro de cualquier columna de la tabla de Producto, excepto el de la Categoría para que, independientemente de la columna que el usuario introduzca como segundo nivel de segmentación en las filas de la matriz, su filtro se elimine en el denominador de nuestro cálculo. Para llevar esto a cabo podemos probar con la función ALLEXCEPT, que como se ejecuta dentro de CALCULATE, actuaría como un modificador y no como una función tabular. El código sería el siguiente:

PctOverCategory =
VAR _Ventas = [Ventas]
VAR _VentasCategoria =
    CALCULATE (
        [Ventas],
        ALLEXCEPT (
            'Product',
            'Product'[Category]
        )
    )
VAR _Rdo =
    DIVIDE (
        _Ventas,
        _VentasCategoria
    )
RETURN
    _Rdo
DAX

Veamos el resultado obtenido con esta última formula:

Como podemos observar, el resultado vuelve a ser el correcto, y lo seguiría siendo, aunque sustituyéramos la columna Color por otro atributo de la dimensión producto que no fuera la Categoría, cuyo filtro es el único que mantenemos en el denominador del cálculo.

Para probar el nivel de robustez de nuestra métrica, volvamos a introducir en el contexto la Subcategoría y eliminemos de la visualización la Categoría, y veamos si el resultado sigue siendo correcto, es decir, nos sigue devolviendo el % de las Ventas de la Subcategoría presente en el contexto sobre el total de la Categoría a la que pertenece:

Si nos detenemos a observar el resultado, veremos que la medida no está dividiendo las ventas de cada Subcategoría por las del total de su Categoría correspondiente, sino que está dividiendo sobre el total de las Ventas de todas las Categorías de producto presentes en nuestro modelo.

La razón por la cual nuestra fórmula no se comporta de la manera esperada es la misma que la del ejemplo anterior y la razón de este artículo. Cuando utilizamos CALCULATE en DAX, estamos creando un nuevo contexto de filtro basado en el contexto original. En nuestro denominador:

CALCULATE (
        [Ventas],
        ALLEXCEPT (
            'Product',
            'Product'[Category]
        )
    )
DAX

Inicialmente CALCULATE hace una copia del contexto de filtro original y, posteriormente, ALLEXCEPT elimina cualquier filtro que provenga de la tabla de producto, excepto el de la columna Categoría. Como en este caso, ALLEXCEPT actúa como un modificador de CALCULATE y no como una función tabular, la única acción que puede realizar es la de eliminar filtros del contexto original, pero no puede introducir ni mantener ningún filtro activo, porque no devuelve ninguna tabla, al contrario que cuando usamos las funciones ALL* como funciones tabulares. Es por esto que nuestra fórmula solo funciona en el caso de que en el contexto original exista un filtro en la columna Categoría, ya que ALLEXCEPT no puede, de ninguna manera, introducir un filtro en el contexto.

Para que nuestra fórmula funcione de la manera que estamos buscando, debemos asegurarnos de introducir un filtro (o mantenerlo, en el caso de que ya exista) en el contexto, y debemos hacerlo de forma proactiva, ya que, como hemos visto, no podemos fiarnos de ALLEXCEPT para esta finalidad. Lo que necesitamos es utilizar como parámetro de filtro de CALCULATE una función tabular que nos devuelva los valores de la columna Categoría que se encuentren visibles en el contexto de filtro actual. La función que puede ayudarnos para tal fin es VALUES. En el caso de que quisiéramos introducir (o mantener) el filtro en más de una columna de la dimensión producto utilizaríamos la función SUMMARIZE. Por consiguiente, nuestra fórmula quedaría de la siguiente manera:

PctOverCategory =
VAR _Ventas = [Ventas]
VAR _VentasCategoria =
    CALCULATE (
        [Ventas],
        ALL ( 'Product' ),
        VALUES ( Product[Category] )
    )
VAR _Rdo =
    DIVIDE (
        _Ventas,
        _VentasCategoria
    )
RETURN
    _Rdo
DAX

Es por este motivo por lo que, cuando queremos eliminar todos los filtros de una tabla salvo el de alguna/s columna/s, se considera buena práctica y se recomienda siempre utilizar la técnica de ALL (o REMOVEFILTERS) y VALUES y no la de ALLEXCEPT, que la dejaremos para escenarios más complejos, como el de evitar referencias circulares en columnas calculadas mediante la eliminación de la dependencia de ciertas columnas en la transición de contexto producida cuando utilizamos CALCULATE en un contexto de fila.

Volviendo a nuestro ejemplo, podemos verificar que esta última fórmula se comporta de manera correcta independientemente de que dispongamos o no en el contexto original, de un filtro en la Categoría:

Ejemplo con ALL y KEEPFILTERS

Por último, planteemos otro ejemplo en el que podremos ver la principal característica de los modificadores de CALCULATE en acción, que corresponde al hecho de que siempre se van a aplicar antes que los argumentos de filtro explícitos.

Tenemos dos medidas que en principio deberían darnos resultados distintos. La primera sería la siguiente:

VentasFrance =
CALCULATE (
    [Ventas],
    Customer[Country] = "France"
)
DAX

Que, como sabemos, es lo mismo que:

VentasFrance =
CALCULATE (
    [Ventas],
    FILTER (
        ALL ( Customer[Country] ),
        Customer[Country] = "France"
    )
)
DAX

Esta medida elimina cualquier filtro del contexto original aplicado en la columna Customer[Country], y lo sustituye por uno en dicha columna con el valor «France». Por lo tanto, siempre nos daría el mismo resultado, independientemente del País seleccionado en el contexto original:

La segunda de las medidas es esta:

VentasFranciaKF =
CALCULATE (
    [Ventas],
    KEEPFILTERS ( Customer[Country] = "France" ),
    ALL ( Customer[Country] )
)
DAX

Parece intuitivo pensar que, por la presencia de KEEPFILTERS, esta medida nos devuelva el valor de las Ventas de Francia solamente en el caso de que dicho valor se encuentre en el contexto original. Por el contrario, esta medida arroja el mismo resultado que la anterior. La razón es que ALL actúa como un modificador de CALCULATE y, por lo tanto, se aplica antes que el filtro explicito rodeado por KEEPFILTERS. Una vez ALL elimina el filtro en la columna Customer[Country] del contexto original, KEEPFILTERS no dispone de la posibilidad de mantener ningún filtro en dicha columna, ya que no existe ninguno. Como podemos observar, ambas medidas producen el mismo resultado:

CONCLUSIÓN

Las funciones de la familia ALL* pueden actuar como funciones tabulares o como modificadores de CALCULATE. La diferencia semántica entre ambos comportamientos es sutil. En la mayoría de casos esta diferencia pasará inadvertida. Sin embargo, existen ocasiones en las que esta distinción en el comportamiento de ALL cambiará por completo el resultado de una fórmula. Conviene por tanto comprender profundamente las implicaciones de ambos comportamientos para evitar resultados en nuestros informes que puedan llevar al usuario a conclusiones erróneas.

 

Todo el código DAX de este artículo ha sido formateado con «DAX Formatter»
DAX Formatter by SQLBI

 
 

Anterior

Workshop sobre DAX en Power BI Days Madrid

Siguiente

Integridad referencial y miembros desconocidos en Power BI

1 comentario

  1. Miguel Lopez

    Estaria fenomenal poder descargar el archivo de ejemplo….. para poder practicar un rato. En cualquier caso, enhorabuen por el articulo y a ver si soy capaz de asimilarlo.

Deja un comentario

BI CONTROLLING 2023 © TODOS LOS DERECHOS RESERVADOS

A %d blogueros les gusta esto: