Introducción a Funciones de Tablas en DAX
DAX es un lenguaje con una gama de posibilidades y tonalidades nada desdeñables, además, sabemos que su utilización recae en los tres cálculos DAX. Dos de los cuales están disponibles en Power Pivot para Excel, a saber:
- Medidas
- Columnas Calculadas
.
El tercer cálculo DAX, tablas calculadas, no está disponible en Power Pivot; esperemos que en una actualización futura poder contar con el.
DAX es un lenguaje de funciones destinado a resumir datos, por lo tanto, destinado al análisis de datos; para los tres cálculos DAX disponibles podemos escribir fórmulas o expresiones para conseguir un objetivo determinado, un resumen de los datos de acuerdo un cálculo algorítmico deseado.
Aunque el objetivo es resumir los datos en un valor para cierto contexto en la base o bases de datos, lo cierto es que también podemos crear expresiones que devuelvan varios elementos; esta es la razón por la cual el lenguaje DAX puede ser utilizado como lenguaje de consulta.
… En cualquier caso …
Podemos crear expresiones DAX que den como resultado una tabla. ¡Ese es el punto!
lenguaje DAX tenemos dos tipos de funciones de acuerdo al valore que arrojan:
- Funciones Escalares
- Funciones de Tabla
Funciones Escalares y Funciones de Tabla
Decimos entonces que: En DAX podemos crear dos tipos de fórmulas o expresiones, Expresiones Escalares y Expresiones de Tabla. Ejemplo:
Expresión Escalar: Función o fórmula que arroja como resultado final una escalar, es decir un valor único, solo un elemento
=
SUM ( TABLA_Pedidos[Precio de Venta] )
Expresión de Tabla: Función o fórmula que retornar como resultado final una tabla o un conjutno de resultados, varios elementos. Ejemplo:
=
FILTER ( TABLA_Pedidos; TABLA_Pedidos[Precio de Venta] >= 40 )
La expresión anterior devuelve un subconjunto de tabla Pedidos de tal manera que únicamente retorna los registros que son igual o mayores a cuarenta en el campo Precio de Venta.
NOTA IMPORTATE: Aunque podemos utilizar expresiones DAX que retornen una tabla como resultado, lo cierto es que: No podemos asignar expresiones de tabla directamente a una Medida o Columna Calculada.
Inferir el porqué de la nota no es muy complejo si miramos una tabla dinámica:
Vemos que los datos se deben resumir en único valor para mostrarlo en una celda determinada del reporte de tabla dinámica.
Una Medida se calcula de manera independiente para cada una de las celdas de la tabla dinámica, esto de acuerdo a su contexto, es decir, de acuerdo a los filtros que la rodean. Por lo anterior la expresión de la medida debe arrojar un escalar para poder presentarle en dicha celda.
A pesar de lo previo, hay funciones de tipo escalar que aceptan tablas en sus argumentos, por consiguiente, podemos utilizar expresiones de tabla dentro de funciones escalares. De aquí es donde nace el principio de encapsulación o Take Away de encapsulación como lo llamamos en el libro El ADN de Power Pivot.
– Ejemplo: Función COUNTROWS
Por ejemplo, la función COUNTROWS es una función que retornar un escalar, por ello de agregación, además en su único argumento acepta una tabla. Como su nombre lo indica es una función que cuenta el número de filas en uan tabla o, en una tabla definida por una expresión.
Sintaxis: COUNTROWS(<tabla>)
Según la definición podemos utilizar COUNTROWS así:
=
COUNTROWS ( TABLA_Pedidos )
Lo cual cuenta el número de filas en la tabla Pedidos , sin embargo, también nos dice que en dicho argumento podemos utilizar expresiones que devuelva una tabla, por lo que podemos crear la siguiente medidad:
[Ventas de Alta Rentabilidad]:=
COUNTROWS ( FILTER ( TABLA_Pedidos; TABLA_Pedidos[Precio de Venta] >= 40 ) )
En la expresión DAX de la medida anterior, sabemos que la función FILTER retorna una tabla con solamente aquellas filas que en la columna Precio de Venta tienen un valor mayor o igual a 40 y, como esta encapsulada en la función COUNTROWS, entonces la medida retorna el número de ventas con un precio mayor o igual a 40 para el contexto actual.
Existen una infinidad de funciones de tabla, alguns de uso básico, otras de alta complejidad.
Aquí en este artículo les quiero hablar de la función VALUES y la combinación IF(VALUES()).
– Función VALUES
La función VALUES retorna una tabla de una columna la cual contiene los distintos valores de una tabla especificada o una expresión de tabla en el contexto actual, es decir, es una función de tabla que remueve los valores duplicados y retorna los valores únicos.
EJEMPLO: si creamos una tabla dinámica con el campo Fecha de Envio (Año) en el área de filas y la siguiente Medida:
[Ítem]:=
COUNTROWS ( VALUES ( TABLA_Pedidos[SKU] ) )
Obtenemos:
Todos los años tiene el mismo número como resultado, ya que está contando el número de SKU diferentes (Productos) para el contexto, por lo que se infiere que en cada año hubo por lo menos una venta de cada uno de los productos disponibles en el catálogo.
La excepción radica en el año 1999, pues arroja 12, de ello se puede deducir que para 1999 la lista de elementos únicos del total disponible era 12, queriendo decir que 7 de los productos en el catálogo no tuvieron ventas en dicho año.
Nota como la función VALUES se encapsulo en la función COUNTROWS, no obstante, ¿Qué sucede si la tabla es de solo una fila y una columna?
– Función VALUES como Escalar
Aunque la función VALUES es una función de tabla, También La Podemos Utilizar Como Escalar, esto cuando el resultado de la tabla es de una fila por una columna, un único elemento (Esto es una característica del Lenguaje DAX en general)
Para tratar de dilucidar mejor, supongamos que queremos crear una tabla dinámica con la siguiente configuración:
.
- Tipo de Compra de la TABLA_Pedidos al área de filtros (Filtrado por «Devolución»)
- Categoría de Descuento de la tabla Descuentos al área de filtros (Filtrado por «Black Fraday»)
- Fecha de Envio (Año) al área de filas
- Un Slicer (Segmentación de Datos) del Campo Ciudad (Filtrado por «Caracas»)
- La Medida: [Sum Of Precio de Venta] en el área de valores
.
La imagen siguiente ilustra la tabla dinámica:
Lo que queremos hacer es ver el Nombre del SKU en caso de que cierta selección tenga el mismo SKU, la Medida sería:
[Nombre SKU]:=
IF (
COUNTROWS ( VALUES ( TABLA_Pedidos[SKU] ) ) = 1;
VALUES ( TABLA_Pedidos[SKU] )
)
El resultado:
Podemos apreciar en la tabla dinámica anterior, que las columnas que tienen las celdas en blanco significa que hay más de un SKU para el contexto, mientras que los que tiene el valor solamente tiene dicho elemento único.
Un punto interesante aquí es que estamos utilizando el resultado de VALUES como un valor escalar, a pesar de que por definición VALUES retorna una tabla, por lo tanto, se debe convertir de «formato» Tabla a «formato» Escalar; en esta situación el DAX engine lo hace automáticamente. Para resumirlo:
Regla de Conversión Implícita (Tabla a Escalar): Sí una o función de tabla arroja una tabla con solo una fila y una sola columna, entonces, la conversión de tabla a escalar se hace automáticamente, si es requerido.
Si miramos el contexto para el año 2003, tenemos:
Vemos que hay más de un valor diferente para la columna SKU, estos son: L01, L02, C14 y CB01.
Sí no utilizáramos el condicional, una vez llegue aquí la ejecución arrojará un error, pues la conversión no se puede hacer, no se cumple la regla de conversión implícita.
Si miramos el contexto para el año 2002:
Vemos para este caso que sólo hay un elemento: L04, por lo que VALUES retorna una tabla de un único elemento, por esto aparece en la tabla dinámica.
¡La conversión sucede naturalmente!
Siempre, SIEMPRE, se debe proteger la conversión (Regla De Conversión Implícita) cuando se utiliza una expresión o función de tabla que devuelve un elemento único, esta protección se hace con un condicional, lo cual permite que el flujo siga si solo hay un valor y la conversión implícita se lleve a cabo, o que salte sino se cumple.
Combinación IF(VALUES()) Caso Duplicar Valor
** Las Línea a continuación son un extracto del libro El ADN de Power Pivot.
Miremos el siguiente caso, supogamos que tenemos la siguiente tabla dinámica:
Digamos, por ejemplo, que el valor del producto C10 en la tabla dinámica lo queremos multiplicar por 2 (Los demás no hacerlas nada). La medida que formularíamos sería la siguiente:
[C10 Doble Suma Precio de Venta]:=
IF (
VALUES ( TABLA_Pedidos[SKU] ) = «C10»;
[Suma of Precio de Venta] * 2;
[Suma of Precio de Venta]
)
No obstante, la medida anterior arroja un error inevitable, esto debido a que no se está protegiendo la conversión como dijimos previamente, en otras palabras, la expresión DAX puede arrojar más de un valor, el DAX engine lo sabe, nos advierte y nos detiene con un error.
La medida válida sería:
[C10 Doble Suma Precio de Venta]:=
IF (
COUNTROWS ( VALUES ( TABLA_Pedidos[SKU] ) ) = 1;
IF (
VALUES ( TABLA_Pedidos[SKU] ) = «C10»;
[Suma of Precio de Venta] * 2;
[Suma of Precio de Venta]
);
[Suma of Precio de Venta]
)
– ¿En Qué Momento se Presentaría más de un Valor?
Puede llegar a ser engañoso el caso donde se presente más de un valor en la función VALUES para la tabla dinámica anterior, esto si nos fijamos únicamente en la configuración de la tabla dinámica previa; puesto que para cada celda en la tabla dinámica siempre habría un sólo SKU; por ejemplo, la primera celda: C10, la segunda C11, etc.
Es decir, sólo uno en un momento determinado, con lo cual la parte de la medida: COUNTROWS ( VALUES ( TABLA_Pedidos[SKU] ) ) = 1 no parecería tener un caso en el que sea falso.
–> He aquí la «trampa» …
¿Qué si pasa si reordenamos los campos de la tabla dinámica de tal manera que el campo SKU no quede en ninguna de las áreas de colocación?
UN EJEMPLO:
¿Lo ves ahora?
Ahora es algo más claro donde puede suceder y el porqué de la protección, pues se debe garantizar para cualquier configuración (arreglo de campos en las áreas de colocación)
De hecho, esta maleabilidad en la cual se puede utilizar una medida en cualquier configuración de una tabla dinámica y en cualquier tabla dinámica del mismo origen se denomina: PORTABILIDAD DE FUNCIONES. ¡Claro, se debe garantizar!
– Función HASONEVALUE
Una última cosa antes de cerrar este artículo
En lugar de usar: COUNTROWS ( VALUES ( TABLA_Pedidos[SKU] ) ) = 1
Podemos acortar de la siguiente manera: HASONEVALUE ( TABLA_Pedidos[SKU] )
¿Entiendes lo que hace la función HASONEVALUE?
Al final del día podríamos acortar un poco la expresión de la medida así:
[C10 Doble Suma Precio de Venta]:=
IF (
HASONEVALUE ( TABLA_Pedidos[SKU] );
IF (
VALUES ( TABLA_Pedidos[SKU] ) = «C10»;
[Suma of Precio de Venta] * 2;
[Suma of Precio de Venta]
);
[Suma of Precio de Venta]
)
.
Bueno mis amigos, esto ha sido todo de mi parte el día,
Cuéntame ¿Qué te ha parecido este primer acercamiento a las funciones de tabla con VALUES? –
No dejes de comentar.
¡Hasta la Próxima oportunidad!