La función VALUES y la creación de medidas calculadas. Profundizando en el uso de DAX como lenguaje de consulta (2)

En la primera parte de esta serie comenzamos abordando la creación de columnas calculadas, que uníamos al resto de columnas de la tabla mediante la función ADDCOLUMNS. En esta segunda entrega veremos cómo construir consultas basadas en los valores de una única columna.

VALUES. Valores distintos de una columna

Aunque la función ADDCOLUMNS es adecuada para dotar de valores adicionales a una tabla, puede suceder que en determinados momentos estemos más interesados en obtener el contenido de una sola de sus columnas, y más concretamente, de los distintos valores que la componen, bien para visualizarlos o como parte de una expresión más compleja.

Para tal cometido disponemos de la función VALUES, que recibe como parámetro una columna de la que extrae sus distintos valores, retornándolos en forma de tabla de una sola columna, como vemos en el siguiente ejemplo, donde se muestran los nombres de las marcas de productos de la tabla DimProduct.

EVALUATE
VALUES('DimProduct'[BrandName])
ORDER BY [BrandName]

En una consulta SQL disponemos de la cláusula DISTINCT, con la que obtendremos los distintos valores de uno de los campos de la tabla.

SELECT DISTINCT BrandName
FROM DimProduct
ORDER BY BrandName

Puesto que el tipo de dato devuelto por VALUES es una tabla, y ADDCOLUMNS recibe un valor de este tipo en su primer parámetro, podemos combinar ambas funciones para crear una consulta en la que mostremos las marcas de productos de la tabla DimProduct, y una columna calculada que sume las ventas de la tabla FactSales, obteniendo como resultado las cifras de ventas por cada marca. Recordemos que para que la operación de suma se lleve a cabo correctamente emplearemos las funciones CALCULATE y SUM, o la combinación de SUMX y RELATEDTABLE.

EVALUATE
ADDCOLUMNS(
VALUES('DimProduct'[BrandName]),
"Ventas por marca", CALCULATE(SUM('FactSales'[SalesAmount]))
)
ORDER BY [BrandName]

La función DISTINCT también devuelve, al igual que VALUES, los valores únicos de una columna, por lo que en la mayoría de las situaciones los resultados serán iguales utilizando indistintamente una u otra. No obstante, en algunos casos, VALUES devolverá un valor vacío o elemento desconocido, cuando al combinar dos tablas no exista correspondencia entre alguno de sus miembros.

EVALUATE
ADDCOLUMNS(
DISTINCT('DimProduct'[BrandName]),
"Ventas por marca", CALCULATE(SUM('FactSales'[SalesAmount]))
)
ORDER BY [BrandName]

Desde Transact-SQL necesitamos en este caso emplear la cláusula GROUP BY, puesto que junto al campo BrandName vamos a usar la función de agregado SUM. Para la combinación entre las tablas DimProduct y FactSales definiremos sobre esta última una expresión común de tabla o CTE (Common Table Expression).

WITH tblFactSales AS
(
	SELECT ProductKey, SalesAmount
	FROM FactSales
)
SELECT BrandName, SUM(tblFactSales.SalesAmount) AS [Ventas por marca]
FROM DimProduct
INNER JOIN tblFactSales
ON DimProduct.ProductKey = tblFactSales.ProductKey
GROUP BY BrandName
ORDER BY BrandName

DEFINE y MEASURE. Creación y reutilización de medidas calculadas

Si en la anterior consulta con VALUES quisiéramos además ordenar el resultado por la columna «Ventas por marca», tendríamos que repetir la expresión que contiene la operación de suma en la cláusula de ordenación.

EVALUATE
ADDCOLUMNS(
VALUES('DimProduct'[BrandName]),
"Ventas por marca", CALCULATE(SUM('FactSales'[SalesAmount]))
)
ORDER BY CALCULATE(SUM('FactSales'[SalesAmount]))

Esta situación puede resultar bastante incómoda en el caso de consultas extensas, provocando además, que nuestro código se vuelva más complicado de leer y mantener.

Una forma de evitar este tipo de reiteraciones pasa por utilizar las instrucciones DEFINE y MEASURE al comienzo de la consulta, las cuales, tal y como sus nombres indican, nos permiten definir una medida calculada que podemos reutilizar tantas veces como queramos a lo largo de la consulta. Como ventaja adicional, en el ejemplo que nos ocupa, al crear la medida con DEFINE ya no será necesario emplear la función CALCULATE.

DEFINE
MEASURE FactSales[Ventas] = SUM('FactSales'[SalesAmount])

EVALUATE
ADDCOLUMNS(
VALUES('DimProduct'[BrandName]),
"Ventas por marca", FORMAT(FactSales[Ventas], "Currency")
)
ORDER BY FactSales[Ventas] DESC

Para mejorar la visualización de los datos al crear esta nueva versión de la consulta incluimos la función FORMAT, aplicando un formato de moneda a la medida/columna calculada.

El siguiente paso consistirá en construir una consulta compuesta por columnas provenientes de varias tablas, cuestión esta que abordaremos en las próximas entregas.