Uso de la función USERELATIONSHIP en columnas calculadas

Introducción

En un modelo tabular con relaciones inactivas podemos usar la función USERELATIONSHIP() para activar una de ellas durante el tiempo de ejecución de un determinado cálculo con DAX. Para ello, simplemente tenemos que especificar dicha función (y los parámetros correspondientes) como uno de los argumentos de filtro de la función CALCULATE(). El uso de USERELATIONSHIP() es sencillo cuando queremos crear una medida, pero se complica si lo que deseamos es utilizar dicha función en la definición de una columna calculada. En este artículo vamos a ver qué es lo que ocurre en estos casos, explicando uno de los aspectos más importantes que se produce cuando ejecutamos la función CALCULATE() dentro de una columna calculada, o de forma más general, dentro de un contexto de fila: la transición de contexto.

El contexto de evaluación en DAX

Las expresiones en el lenguaje DAX dependen íntegramente del contexto de evaluación bajo el que se ejecutan, de forma que podemos obtener muchos resultados distintos para una misma fórmula en función del contexto en el cual se evalúa dicha expresión.

Por ejemplo, la siguiente fórmula:

Ventas =
SUMX ( PedidosDetalle; PedidosDetalle[Cantidad] * PedidosDetalle[Precio] )

Aplicada en el campo «Valores» de una matriz muestra el siguiente resultado:

Como podemos observar, en cada celda tenemos un número distinto, el cual depende del conjunto de filtros aplicado en el modelo de datos. En este caso, los campos de las columnas y las filas de la matriz, junto con el filtro procedente del segmentador, forman el contexto de filtro bajo el que la fórmula se ejecuta.

A parte del contexto de filtro, el contexto de evaluación en DAX está también formado por el contexto de fila, el cual contiene una sola fila para la cual se ejecutará la expresión. Existen varias formas de crear un contexto de fila, la más sencilla de ellas consiste en definir una columna calculada, donde automáticamente el motor de DAX generará el contexto e implementará la expresión correspondiente para cada una de las filas de la tabla donde se crea la nueva columna.

La transición de contexto

La función CALCULATE() realiza una importante labor cuando es ejecutada dentro de un contexto de fila, que conviene entender con claridad, ya que aparte de que encontraremos sus efectos en multitud de expresiones, nos permitirá realizar con facilidad cálculos complejos basados en iteraciones de tablas.

Lo que CALCULATE() realiza es la transformación de todos los contextos de fila que se encuentran activos cuando la función es ejecutada en un contexto de filtro equivalente. Como sabemos, los contextos de filtro se propagan a las tablas relacionadas automáticamente, al contrario que los contextos de fila, con los que tenemos que usar las funciones RELATED() y RELATEDTABLE() si queremos acceder a valores relacionados que se encuentran almacenados en otra de las tablas del modelo. Por ejemplo, la siguiente columna calculada en la tabla ‘Productos’:

Productos[CantidadMediaCliente] =
AVERAGEX (
       FILTER ( Clientes; Clientes[Pais] = «Germany» );
       CALCULATE ( SUM ( PedidosDetalle[Cantidad] ) )
)

Calcula la cantidad media del producto actual que ha sido comprada por los clientes alemanes. Existen 3 contextos de fila en la anterior expresión:

1. La tabla ‘Clientes’ es iterada por FILTER(), para mostrar solo los clientes cuyo país es Alemania.
2. La tabla ‘Clientes’ filtrada para los clientes alemanes es iterada por AVERAGEX().
3. La tabla ‘Productos’ es iterada por la propia evaluación de la columna calculada.

De esta forma, la función SUM() dentro de CALCULATE() es ejecutada bajo un contexto de filtro que deja visibles las cantidades de la tabla ‘PedidosDetalle’ (nuestra tabla de hechos) correspondientes al producto actual y al cliente actual (que además tiene que ser Alemán, sino quedará fuera del contexto por la iteración de la función FILTER()). El resultado de la columna calculada es el siguiente (hacer click en la imagen para ampliar):

Comportamiento de USERELATIONSHIP en una columna calculada

Como hemos indicado a lo largo del artículo, cuando definimos una columna calculada estamos escribiendo una expresión DAX que será ejecutada dentro de un contexto de fila creado en la tabla donde almacenaremos la nueva columna. Como la función USERELATIONSHIP() la usamos como argumento de filtro de la función CALULATE(), y esta última realiza una transición de contexto cuando es ejecutada en un contexto de fila, el uso de relaciones inactivas en columnas calculadas se vuelve una operación compleja, donde obtener el comportamiento deseado no es tan intuitivo como en las medidas.

Si queremos definir una columna calculada en la tabla ‘Pedidos’ que nos devuelva, para cada fila, la fecha de envío de la dimensión temporal utilizando una de las relaciones inactivas entre las 2 tablas, por ejemplo la fecha de envío, podemos probar con el siguiente código:

Pedidos[FechaEnvioUR] =
CALCULATE (
       VALUES ( Calendario[Fecha] );
       USERELATIONSHIP ( Calendario[Fecha]; Pedidos[FechaEnvio] )
)

Atendiendo al comportamiento habitual de la función USERELATIONSHIP() en una medida podemos esperar que la anterior expresión devuelva, para cada fila de la tabla ‘Pedidos’, la fecha de envío correspondiente, pero como podemos observar en la siguiente imagen, DAX ignora por completo la relación especificada con la función USERELATIONSHIP(), devolviendo el valor basado en la relación activa en el modelo de datos, que corresponde a la fecha de pedido:

Este resultado se produce porque, en los modelos tabulares, el contexto de filtro se propaga automáticamente desde las tablas de consulta hacia las tablas de datos; es decir, cuando asignamos un filtro a una dimensión, este se propaga de forma que solo las filas de la tabla de datos que cumplan las condiciones del filtro serán tenidas en cuenta por DAX a la hora de evaluar la expresión.

Si damos la vuelta a la situación anterior e intentamos usar USERELATIONSHIP() desde la dimensión para acceder a los valores correspondientes almacenados en la tabla de hechos mediante una relación que se encuentra inactiva en el modelo de datos veremos que en este caso los valores se filtran correctamente, obteniendo valores distintos a los obtenidos si filtramos la tabla ‘Pedidos’ a través de la relación activa por defecto en nuestro modelo. Por ejemplo, podemos definir las siguientes columnas calculadas en la dimensión temporal:

Calendario[VentasPedido] =
CALCULATE (
    SUMX ( PedidosDetalle; PedidosDetalle[Cantidad] * PedidosDetalle[Precio] )
)

Calendario[VentasEnvio] =
CALCULATE (
    SUMX ( PedidosDetalle; PedidosDetalle[Cantidad] * PedidosDetalle[Precio] );
    USERELATIONSHIP ( Pedidos[FechaEnvio]; Calendario[Fecha] )
)

Si visualizamos una tabla con la fecha de la dimensión temporal y las 2 columnas calculadas anteriores observaremos que el filtro de contexto correspondiente a cada día se propaga correctamente a las tablas relacionadas, obteniendo el comportamiento deseado:

Podemos entonces afirmar que la función USERELATIONSHIP(), usada en la expresión de una columna calculada, nos permitirá «viajar» a través de las relaciones inactivas en un modelo de datos, solo si los filtros son aplicados en las tablas de consulta (o dimensiones) para acceder a los valores almacenados en las tablas de hechos. Si, por el contrario, invocamos la función desde una tabla de hechos, para acceder a valores almacenados en una dimensión, la dirección de la propagación automática del filtro de contexto impedirá el correcto funcionamiento de la misma.

Existen distintas maneras de resolver este problema. Una de ellas se basa en la creación de relaciones calculadas puras, que no dependen de la activación de relaciones presentes en el modelo, sino que generan la búsqueda de los valores correspondientes desde el propio código. Por ejemplo, si queremos desnormalizar, mediante la creación de una columna calculada en la tabla ‘Pedidos’ la fecha de la dimensión temporal correspondiente a la fecha den envío, tal y como intentábamos al principio de este apartado, podemos usar el siguiente código:

Pedidos[FechaEnvioFILTER] =
CALCULATE (
       VALUES ( Calendario[Fecha] );
       FILTER ( Calendario; Calendario[Fecha] = Pedidos[FechaEnvio] )
)

Y obtendríamos el resultado deseado: