Mostrar datos de distintas tablas de hechos en la misma tabla

Importante: Este post fue escrito y publicado en 2017, y el contenido a continuación, aunque sea funcional y didácticamente útil, puede que no sea representativo de las posibilidades actuales de Power BI. Por favor, considera este post como un recurso técnico limitado y un registro histórico.

A este respecto, en matrices, activar la opción “mostrar valores como filas” puede que ofrezca el mismo resultado que el descrito aquí.

—–

En este post vamos a ver como mostrar en una misma tabla o matriz de PowerBI valores y cálculos que provienen de distintas tablas de hechos con distinto número de dimensiones. Por el camino podremos ver una aplicación práctica de las funciones DAX: VALUES, HASONEFILTER, FORMAT y SWITCH.

Posibles escenarios de aplicación

Supongamos un proceso productivo en el cual hay varias máquinas con una capacidad máxima o disponibilidad medida en horas con regularidad mensual que se almacena en una tabla, y en otra tabla diferente se almacenan valores de medidas de producción más detallados, como horas de funcionamiento real, paradas, etc… A partir de los mismos se podrían calcular indicadores OEE, de productividad, etc.

Es un planteamiento similar al que podría requerir un hotel que tuviese un calendario de apertura, y un número determinado de habitaciones que proporcionan el dato de disponibilidad. La producción diaria o el calendario de reservas podría dar el dato de porcentaje de ocupación, o gasto medio por habitación… Y conocer indicadores estándar del sector como el RevPar, ADR, etc…

O datos de valoración de servicio que provengan de encuestas de satisfacción de cliente, proporcionados por encuestas de Evalnova, analizar cómo correlacionan un dato con el gasto de personal proveniente de contabilidad. O una empresa de servicios con un calendario laboral y horas de servicio a facturar, o datos de presupuesto contra reales…

La cantidad de escenarios en la que podemos tener la necesidad de mezclar datos de distintas fuentes, con número de dimensiones diferentes, y que sean ofrecidos como una “unidad”, sea por coherencia en su presentación o requerimiento del cliente,  es innumerable.

Veamos un ejemplo de línea productiva

Los datos de origen utilizados en este ejemplo están en un archivo Excel con 5 hojas, 3 con los valores de las dimensiones (dim1-Meses, dim2-Recurso y dim3-Indicadores) y dos tablas de hechos (Disponibilidad y Funcionamiento) que almacenan valores en horas para las 3 dimensiones anteriores. Las relaciones entre las mismas son las siguientes:

Carga de datos y otros preparativos

Vamos a obviar la explicación del proceso de carga de datos en PowerBI desde Excel, pues no plantea nada excepcional que destacar en el mismo, más allá de comprobar que los tipos de datos fechas han sido correctamente detectados:

Una vez cargados los datos en Power BI sí que he decidido reemplazar la tabla de dim1-meses por una tabla de fechas propia. Ha bastado con copiar la DAX de tabla fecha de este artículo. Y después rehacer las relaciones que correspondían a “dim1-meses” que también vamos a ocultar en el modelo.

Además, vamos a añadir manualmente una tabla en PowerBI que vamos a llamar “KPI” con las siguientes líneas:

Tras estos cambios el modelo de datos y las relaciones quedan como sigue:

La fórmula DAX de la measure “ValoresKPI”

El objetivo que se persigue es el siguiente: Una tabla que unifique y/o haga cálculos con los valores que provienen de otras tablas.

La dimensión-tabla “KPI” que hemos creado, como tal, no tiene valores asociados y mediante funciones DAX vamos a hacerlo. A efectos didácticos crearemos 3 medidas, una muy simple y otras dos más complejas.

Para simplificar algo las formulas posteriores añadiremos una Measure llamada “Valores” a la tabla Funcionamiento, que va a ser SUM(value).

La siguiente measure, con nombre “ValoresKPI” la añadiremos a la tabla “KPI”, y esta es la que va a cargar con todo el peso.

Vamos a asignar valores a cada uno de los elementos o líneas de la tabla KPI. Para ello hay que identificar el elemento que corresponde y vía condiciones asignarle la fórmula de cálculo adecuada. Podemos usar un IF, pero cuando hay más de dos condiciones no vemos obligados a anidar y, además de que hay limitación para anidamientos, la legibilidad y depuración de las fórmulas se complica significativamente.

La función SWITCH y VALUES

Así, la función SWITCH, que es un equivalente a Case, Select Case o Switch Case, según lenguaje, permite manejar múltiples condiciones IF más adecuadamente.

Su estructura es:

SWITCH(<expression a evaluar>;
<si value=X>; <then result para X>;
<si value=Y>; <then result para Y>;
...
[, <case else es opcional>]
)

Usando la función VALUES hacemos una especie de SELECT que devuelve una tabla de un columna con valores únicos, en este caso de la tabla KPI. Y es que SWITCH espera una lista de valores y esto es lo que proporciona VALUES, y por cada valor en sus líneas haremos un CASE y asignaremos un valor de test para cada una:

ValoresKPI= SWITCH(VALUES('KPI'[KPI]);
	"Funcionamiento Real"; 1;
	"%Disponibilidad"; 2;
	"Disponibilidad Total"; 3
)

Y aquí puedes ver el resultado de una matriz con esta measure:

La disponibilidad total va a ser la suma de valores de la tabla Disponibilidad: Reemplacemos el 3 por la siguiente función:

ValoresKPI= SWITCH(VALUES('KPI'[KPI]);
	"Funcionamiento Real"; 1;
	"%Disponibilidad"; 2;
	"Disponibilidad Total"; sum(Diponibilidad[value])
)

NOTA: Se podría haber añadido una columna adicional con números índice en la tabla KPI y así evitar el uso de literales en los case del SWITCH, pero se ha hecho de este modo por motivos didácticos.

Valores filtrados con cláusula IN

El “Funcionamiento Real” corresponde a la suma de los valores en la tabla “Funcionamiento” de indicadores con valor “dim3” igual a 3, 8 y 9 (los que tienen la descripción dim3x con un * al final de su descripción). Aquí ya empieza a complicarse el asunto: Hemos de hacer un filtrado de valores de esta tabla y sumarlos. La expresión es:

 calculate(Funcionamiento[Valores]; filter('dim3-Indicadores';'dim3-Indicadores'[dim3] in{3; 8; 9}));

Si reemplazamos el 1 de la fórmula Switch por esta expresión:

Veremos también que en la fila correspondiente se muestra la suma correctamente:

Y es una expresión con CALCULATE y FILTER, una estructura de uso muy habitual y en la que en este artículo no vamos a profundizar. Pero en la misma si quiero destacar la condición utilizada en filter, que es una condición tipo IN. En la misma hay que especificar los valores de la claúsula IN entre llaves:

'dim3-Indicadores'[dim3] in{3; 8; 9}

Formateando como Porcentaje con FORMAT

Finalmente el “%Disponibilidad” va a dividir “Funcionamiento Real/Disponibilidad Total”, cálculos que hemos realizado previamente y que dividiremos usando DIVIDE, así evitamos una condición para contemplar el caso de error por división por cero:

 divide(
	calculate(Funcionamiento[Valores]; filter('dim3-Indicadores';'dim3-Indicadores'[dim3] in{3; 8; 9}))
	;
	sum(Diponibilidad[value])
)

Podemos mejorar el valor devuelto formateándolo como %. Usamos la función FORMAT, que tiene 2 argumentos, la fórmula a formatear y la expresión a utilizar para formatear. En nuestro caso % con dos decimales:

format(
	divide(
		calculate(Funcionamiento[Valores]; filter('dim3-Indicadores';'dim3-Indicadores'[dim3] in{3; 8; 9}))
		;
		sum(Diponibilidad[value]))
;
	"0.00%"
)

Reemplazaremos el 2 del SWITCH por esta fórmula y observaremos que los valores se muestran de acuerdo a lo esperado:

Esto ha permitido también mezclar distintos formatos numéricos en una matriz, lo cual de forma directa no es posible.

HASONEFILTER para terminar

La fórmula es totalmente operativa pero podemos mejorarla añadiendo un control previo de existencia de valores en la tabla que va a retornar los VALUES. En este ejemplo no se va a dar el problema, pero en un proyecto más complejo, con elementos, meses o días eventualmente vacíos, por no tener o por que para los filtros aplicados no haya valores, sí que sería conveniente prevenirlo. Podemos recurrir a las funciones HASONEFILTER o HASONEVALUE, algo similares y en las que no profundizaré ahora, pero que permitirán este control de “errores” vía condición, que en caso de no cumplirse devolverá BLANK().

Así el resultado final de la fórmula es:

ValoresKPI =
IF (HASONEFILTER('KPI'[KPI]);
SWITCH(values('KPI'[KPI]);
	"Funcionamiento Real" ; 
		calculate(Funcionamiento[Valores]; filter('dim3-Indicadores';'dim3-Indicadores'[dim3] in{3; 8; 9}));
	"%Disponibilidad" ; 
		format(divide(calculate(Funcionamiento[Valores]; filter('dim3-Indicadores';'dim3-Indicadores'[dim3] in{3; 8; 9}));sum(Diponibilidad[value]));"0.00%");
	"Disponibilidad Total"; 
		sum(Diponibilidad[value])
)
;
BLANK()
)

Conclusiones

Hay posiblemente modos más eficientes para hacerlo, pero hemos visto un modo muy efectivo y perfectamente válido para presentar datos de diferentes tablas en una y con diferentes formatos según el tipo de valor mostrado. Espero que os haya sido de utilidad.

Si lo deseas puedes descargar los archivos utilizados para este post (XLS+PBIX):

Santiago Tomás

Apasionado de las tecnologías de análisis de datos y Business Intelligence, y con larga experiencia en el uso de las mismas en empresas de todos los sectores, tamaños, y proyectos de diversa complejidad, en los cuales disfruto creando, planteando y desarrollando soluciones.
Tengo una curiosidad insaciable y demasiadas aficiones, entre ellas tocar el bajo con mi grupo.