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):

XLS y PBIX Horas Funcionamiento Maquina

Te puede interesar...

10 comentarios en «Mostrar datos de distintas tablas de hechos en la misma tabla»

  1. Buenas noches, primero agradecerle por su muy buena explicación, quisiera consultarle que variaciones se deberían realizar si se tuviera mas columnas de detalle como el grupo de equipos al que pertenecen las horas y las actividades aplicables de dichos equipos

    1. Hola Leonardo,
      en principio podrían agregarse más filtros sin mayor problema. Por ejemplo, y siguiendo el ejemplo del post, “Grupos de recursos” que permitirían agrupar los recursos. La vista siguiente ilustra el caso:
      Post Horas Grupos
      La precaución a tomar es en el cálculo de los porcentajes, en los totales de cada grupo, pues por defecto hará un sum de la medida calculada y habría que hacer algo similar a un “divide” entre el número de hijos o crear un “calendario” de disponibilidad de recurso o, incluso pensar en un rediseño (el ejemplo está planteado para la selección de un recurso) o crear medidas adicionales para estos casos.

      1. Estimado Tomás, tengo un problema parecido, pero más complejo, en cada fila necesito el nombre del indicador y como columnas necesito numerador y denominador, pero cada numerador y denominador son diferentes para cada indicador (fila). Cómo podría resolverlo?

  2. Buenos días Santiago:
    En primer lugar felicitarte por tu Blog, es difícil encontrar un sitio donde se explique tan bien y de manera estructurada las materias que tratas. Me gustaría hacerte la siguiente pregunta : ¿ es posible resaltar en negrita alguna de las filas (KPIs) que se obtienen como resultado como por ejemplo la fila “%Disponibilidad”?, esto es, que en la fila donde se encuentra el KPI “%Disponibilidad” que en negrita tanto sus valores mensuales como la etiqueta de fila (%Disponibilidad).
    Muchas gracias por anticipado:
    José Luis

    1. Hola José Luis, muchas gracias!. Respecto a tu consulta, me temo que concretamente poner en negrita, no. Pero si que es posible jugar con “conditional formatting” para aplicar fondos de color o Font color específicos. Estos pueden aplicarse dinámicamente basándose en los propios valores representados o en otros que no lo están… Siguiendo el ejemplo del post, vamos a añadir una medida que nos “proporcione” el formato.
      Nota: Esto, lógicamente, tiene sentido hacerlo cuando los valores originales son demasiado heterogéneos como para encontrar un patrón aplicable, como eran los originales del ejemplo del post.
      Duplicamos la medida “ValoresKPI”, la renombramos como “ColoresKPI” y en la fórmula del switch nos devolverá un 1, 2 o 3 en lugar del cálculo anterior:

      ColoresKPI =
      IF (HASONEVALUE('KPI'[KPI]);
      SWITCH(values('KPI'[KPI]);
      "Funcionamiento Real" ; 1;
      "%Disponibilidad" ; 2;
      "Disponibilidad Total"; 3
      )
      ;BLANK())

      Ahora, en la tabla original, sobre el valor podemos añadir un formato condcional, en este caso, Background color:
      Añadir formato condicional en power bi

      Y este va a cambiar de acuerdo a una serie de “rules” o reglas que definen la nueva medida añadida:
      Formato condicional basado en otras medidas power bi

      1. Hay alguna forma de que el formato condicional funcione sobre cada valor y no por filas, me explico, en la parte de porcentajes los que sean menor de 50% en rojo y mayor en verde, pero en las demás lineas, valide de otra forma?

        1. Hola Tomás, sí, puedes usar una medida dax que devuelva como resultado un código de color Hex resultante de comprobar el valor y con ISINSCOPE cuál es el valor de cada elemento del encabezado de fila.
          Ojo, que en este caso el % se saca usando Format, que convierte a texto, por lo que tendrás que hacer una conversión explicita para pasar a valor y después validar condición.
          Info de la función https://docs.microsoft.com/es-es/dax/isinscope-function-dax
          Ejemplo de medida color con DAX: https://www.biti.es/pareto-o-diagrama-abc-dinamico-con-power-bi/
          Saludos

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *