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
  1. Leonardo Escobedo Zenteno

    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

    • 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.

  2. José Luis González

    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

    • 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

Deja un comentario

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