Relacionar tablas en Power Pivot

Para crear unas relaciones, desde “Inicio” clicamos “Vista de diagrama”. Aquí aparecen todas las tablas de nuestro Power Pivot. Para crear una relación simplemente clicas en la variable de una tabla y arrastras hasta la variable de la otra tabla.

Una alternativa para crear y modificar relaciones es desde la pestaña “Diseñar” las opciones de “Crear relación” y de “Administrar relaciones”. Puedes descargar el archivo Relacionar_tablas_power_pivot.xlsx al final de este post para ver la implementación práctica en Power Pivot.

•  Sólo nos permite crear relaciones 1:1, o 1:N, en nuestro ejemplo creamos una relación de 1:N dado que en la tabla calendario cada fecha se repite una sola vez, mientras que en la tabla de “pax tren” se repite varias veces. Estamos haciendo una relación entre tabla de dimensiones (fechas) y una tabla de hechos (pax).
• En el medio de la línea que junta las dos tablas hay una flecha. Esta indica la dirección de la relación, que en este caso va de “calendario” a “pax tren”. Esto tiene implicaciones cuando hay varias tablas relacionadas y empezamos a filtrar (lo vemos en seguida).

Ahora vamos a relacionar también la tabla “OD” (otra tabla de dimensiones) con “pax tren”. ¿A través de que variable cruzamos las dos tablas? Usamos la variable Origen-Destino dado que en la tabla “OD” cada Origen-Destino se repite una sola vez. Volviendo al tema de la bidireccionalidad, ahora si filtráramos un Origen-Destino concreto, filtraríamos todo lo que está en la tabla “OD” y “pax tren”, pero no la tabla “calendario”. Esto no va a ser un problema si creamos nuestro modelo de datos de manera bien estructurada con la tabla de hechos al centro y varias tablas de dimensiones conectadas.

Bien, ahora tenemos nuestro modelo de pasajeros del tren hecho, pero nos faltan algunas cuestiones de usabilidad para mejorar su explotación a través de una tabla dinámica:

• Crear jerarquías.
• Ocultar variable y tablas que no se utilizan o que son redundantes.

Las jerarquías nos ayudan a navegar entre los datos pudiendo analizar en profundidad las categorías en una tabla dinámica. Para crear una jerarquía clicamos el icono correspondiente en la parte arriba a la izquierda de la tabla. Le damos el nombre que nos guste y arrastramos los campos que queremos meter en la jerarquía. En este caso no voy a meter DoW.

En la lista de campos de la tabla dinámica se ha creado un nuevo conjunto de variables llamado “FECHAS” con la jerarquía. Además, aparece otro grupo de variables llamado “Más campos” donde podemos encontrar todas las variables de la tabla (las incluidas en la jerarquía y DoW). Si metemos FECHAS en Filas, vemos que en la tabla dinámica aparece solo el año. Si clicamos el “+” a la izquierda del año podemos analizar más en profundidad los datos hasta llegar al nivel más detallado. Si quieres un orden diferente puedes volver a Power Pivot cambiar el orden de las variables dentro de la jerarquía simplemente arrastrándolos.

Habrás notado que es un poco engorroso ir a buscar las variables que necesitas por la cantidad de tablas y porque algunas se repiten. Para el usuario de estas tablas, seas tu u otra persona, es importante limpiar todo esto. El primer paso es ocultar todas las tablas no son necesarias, en este caso las de “pax avión”, “PPTO” y “Calendario 1”. Para ocultarlas podemos ir a la vista de datos o la vista de diagrama, clicar con el botón derecho encima de la hoja o de la tabla y dar a “Ocultar en herramienta cliente”. Ahora, de las tablas que quedan, quiero ocultar algunas variables. Por ejemplo, cuando relacionamos una tabla de hechos con una tabla de dimensiones, como regla general voy a usar la variable de la tabla de dimensiones. En este caso vamos a ocultar las variables relacionadas con el OD y las fechas de “pax tren”. Podemos también seleccionar varias columnas a la vez. Para “desocultar” podemos clicar otra vez y seleccionar “Mostrar en herramienta de cliente”.

Antes de pasar a modelos más complejos, quería comentar unas funciones que sirven para buscar valores de otras tablas: RELATED y LOOKUPVALUE.

RELATED(tabla[columna])

RELATED sirve cuando hayamos relacionado dos tablas (como acabamos de ver) y quiero traer la información de una tabla a una columna de la otra. Esto puede ser útil para facilitar unos cálculos o porque quiero que esta columna aparezca en una tabla específica para facilitar el uso del usuario. Se puede usar para traer datos de la tabla con “menos filas” a las tablas con más o iguales filas (es decir siempre de 1 a N, o de 1 a 1, pero no de N a 1). En el ejemplo anterior sería desde la tabla de hechos buscar y traer valore de las tablas de dimensiones. Por ejemplo podrías probar en la tabla “pax tren” a crear una nueva columna od_rt utilizando RELATED(OD[ORIGEN-DESTINO RT] y verás que traerá esta información.

Si las dos tablas no están relacionadas podemos utilizar LOOKUPVALUE, creamos od_rt2:

LOOKUPVALUE(columna_resultado; columna_busqueda; valor_buscado)

=LOOKUPVALUE(OD[ORIGEN-DESTINO RT];OD[ORIGEN-DESTINO];[Origen-Destino])

La columna resultado es la de donde queremos traer los valores, el segundo argumento es la columna en común de la otra tabla, y finalmente la columna en común de la tabla en la que estamos.

La ventaja de LOOKUPVALUE es que a veces no podemos relacionar porque en las dos tablas el valor se repite más veces. En este caso LOOKUPVALUE es la solución, pero, el resultado tiene que ser único para el valor que buscamos. Es decir que un Origen-Destino tiene que corresponder a un único Origen-Destino RT (doble sentido). Si hiciéramos al revés, de Origen-Destino RT a Origen-Destino no funcionaría.

Si, por lo contrario, queremos traer datos de la tabla con más filas a la tabla con menos, necesitamos agregar los datos, por ejemplo, contar el número de filas relacionadas, o sumar alguna métrica. En este caso usamos RELATEDTABLE() en lugar de RELATED(). Para ello, tenemos que utilizar esta función dentro de otra función para especificar el tipo de agregación. Aunque no tenga mucho sentido, vamos a hacer una prueba y agregamos “Prueba_pax” a la abla “OD”. Lo que vamos a hacer es crear una columna con los pax de cada ORIGEN-DESTINO. Explicaré en otro post las funciones “X” como SUMX().

Si las dos tablas no están relacionadas, no podemos usar RELATEDTABLE(). En el caso anterior usábamos LOOKUPVALUE(), pero con una relación N:1 tenemos que usar otras funciones que agregan datos. En este caso usamos funciones como CALCULATE() o las funciones X. Veremos más adelante estas dos funciones y como traer datos de tablas no relacionadas.

Vamos ahora a utilizar el fichero Relacionar_tablas_power_pivot_2.xlsx. El ejemplo anterior es el caso ideal donde vamos relacionando una tabla de hechos con varias tablas de dimensiones. Pero no siempre es así. Imagina que quiero hacer lo siguiente:

Quiero comparar los pasajeros del avión con los pasajeros del tren relacionando las dos tablas. Los datos del avión sólo los tengo por mes, así que la comparativa será por mes, pero quiero también dejar el desglose por fecha para cuando analice lo datos del tren por separado.

Esto implica dos problemáticas adicionales respecto al modelo anterior. Primero, tengo dos tablas de hechos, y, segundo, tienen granularidad diferente (por mes una, y por fecha la otra).

Una opción sería, si no necesitara el dato por fecha del tren, agrupar la tabla “pax tren” en Power Query o SQL eliminando la fecha y sumando los pax por mes. Una alternativa, para mantener el detalle por fecha, sería crear dos tablas diferentes, una con los pax del tren y del avión por mes, y otra tabla con sólo los datos del tren por fecha. Pero supongamos que quiero todo relacionado, es decir que no quiero pasar de una tabla a otra en la tabla dinámica, quiero usar el mismo campo “Pax” para compararlo con los pax del avión y para desglosarlo por fecha.

Primero vamos a replicar las relaciones que vimos antes entre “OD”, “calendario” y “pax tren” (acuérdate de cambiar el formato de Date). Ahora vamos paso por paso con “pax avión”.

Para relacionarlo con la tabla “OD”, tenemos primero que crear la variable Origen-Destino en “pax avión” dado que no es posible hacer la relación con dos variables (ORIGEN y DESTINO). Y en seguida relacionamos las dos tablas a través de Origen-Destino.

Nos vamos a encontrar con el mismo problema para la tabla “calendario”. Debemos crear una variable única que represente el desglose máximo de los pax del avión.

¿Cuál puede ser esta variable? Es mes-año. La tenemos que crear tanto en “calendario” como en “pax avion”, y luego relacionamos las dos tablas. Para crear esta variable uso un pequeño truco, multiplico el año por100 y le sumo el mes, así se ordenan de manera correcta en la tabla dinámica (primero tendrás que transformar en número entero Year y Month de la tabla “pax avion”).

Sin embargo, si intentamos hacer la relación nos da un error. ¿Puedes adivinar por qué?

El problema es que estamos intentado hacer una relación N:N, dado que esta nueva variable se repite más de una vez en ambas tablas. Para resolver este problema tenemos que crear una tabla “puente” entre las dos con valores de Año-Mes (YearMonth) únicos. Lo puedo hacer en una hoja del Excel y la cargo después en el modelo de datos.

Bien, ahora tenemos nuestra tabla puente y tenemos que hacer dos relaciones de N:1 para relacionar “pax avion” con “calendario”. Pero hay un problema. Estos tipos de tablas funcionan cuando filtramos los datos en común desde esta misma tabla puente, pero, como las relaciones son unidireccionales, si filtramos algo en la tabla “calendario” esta no filtrará la tabla “pax avion”, sino que habría que filtrar mes y año desde la misma tabla puente para filtrar tanto “pax avion” como “pax tren”. Para entender mejor esto, puede seguir el flujo de las flechitas en las relaciones. Verás que si empiezas desde “calendario” no puedes llegar a “pax avion” (Si estás interesado en construir modelos de datos más complejos de sugiero Power BI, las funcionalidades son muy similares a Power Query y Power Pivot pero van más allá. Por ejemplo puedes crear relaciones N:N y bidireccionales.). Esto significa que, si por una parte puedo usar la misma variable Pax del tren para compararlo con el avión y para ver el desglose por fecha, por otra parte, me obliga a utilizar dos tablas diferentes para las variables de fecha. Puedes intentar averiguarlo usando la tabla dinámica.

¿Qué podemos hacer?

Si en cuanto a relaciones entre tablas está un poco limitado Power Pivot, ofrece una gran flexibilidad utilizando funciones DAX. Lo que vamos a ver ahora podría considerarse poco “ortodoxo”, pero funciona. Voy a utilizar unas funciones más complejas que no vimos todavía, pero no te desanimes si no las entiendes bien del todo, volveremos sobre el tema, por el momento lo que es importante es entender el concepto de lo que puedes hacer. Cuando veamos las funciones complejas lo entenderás todo perfectamente.

La idea es llevarnos los pax del avión a la tabla “pax tren” con unos ajustes. Primero vamos a la tabla “pax tren” y creamos la nueva columna “Suma_avion”. Usamos CALCULATE() para poder sumar los pax de la otra tabla cuando cumpla las condiciones de filas de la tabla “pax tren”. Es decir que para cada fila de “pax tren” quiero que me sume los pax de “pax avion” con el mismo mes, año y Origen-Destino de esta fila. Fíjate que dentro de FILTER() especifico que la tabla es “pax avion”, dado que es la tabla que quero filtrar para sumar los pax, y después digo que los valores de la columna en “pax avion” tienen que ser iguales a lo que hay en esta fila.

No voy a entrar en el detalle de esta fórmula, pero, por lo general, cuando usamos referencias que no sean un valor fijo (“2017”, “Paris-Barcelona”), como por ejemplo el valor de una fila, es mejor definir a priori este valor “dinámico” utilizando VAR. Como puedes ver en la imagen anterior, definimos unas cuantas variables antes de utilizar CALCULATE.

VAR nombre_variable = tabla[columna] RETURN

En este caso, he creado una variable “y” que utiliza el valor de la fila correspondiente de la columna [Year] de la tabla ‘pax tren’. Después utilizo esta variable en la función CALCULATE cuando defino la condición que se tiene que cumplir. Lo mismo hice con las variables de mes y OD.

El problema es que como en la tabla “pax tren” hay varias fechas para el mismo mes y año, los datos del avión se sumarán varias veces. Deberíamos dividirlos por el número de días del mes, pero cuidado, que solo hay datos cuando hay ventas, es decir que puede que no estén todos los días del mes. Así que lo mejor es crear otra columna donde contamos el número de veces que se repite la combinación de Year, Month, Origen-Destino (Cuenta_filas). En la fórmula uso la expresión COUNTROWS() que cuenta el número de filas, y uso varios FILTER() para decir que cuente las filas que sean iguales en Year, Month y Origen-Destino a la fila actual.

Finalmente, creo la columna Suma_deduplicada donde divido Suma_avion entre Cuenta_filas.

Ahora hemos repartido los pax del avión proporcionalmente para cada fila que aparece para el tren, sin embargo, el reparto es ficticio, y no tiene sentido enseñarlo. Sólo tenemos que enseñar los pax del avión cuando no se quiera ver por fecha o día. Para ello hay una función muy interesante que es ISFILTERED() que identifica si en la tabla dinámica estamos filtrando valores de una dada variable o la estamos usando para desglosar los datos (en filas o columnas). Creamos el campo calculado “Pax_avion” con la condición de que si estamos usando Date, Day o DoW de “calendario” no enseñe nada.

Podemos revisar los datos con la tabla dinámica. De esta manera, puedo utilizar el mismo Pax del tren para ver su desglose por fecha y para compararlo con el avión por mes.

Cuando hacemos estos tipos de cálculos hay que tener cuidado y revisar bien los datos. Si nos fijamos en los pax del avión en su tabla original y en “pax tren” vemos que son menos. Esto es porque estamos trayendo solo los pax que coinciden con las filas de la tabla “pax tren”. En este caso el avión tiene pax en algunos meses donde el tren no tiene. Esto nos puede valer si solo queremos comparar los datos del avión cuando haya datos del tren. De lo contrario tendríamos antes que desglosar la tabla de pax del tren para que incluya también días sin pasajeros en Power Query o SQL.

Este recorrido a través de peticiones cuestionables (ver los pax por fecha y compararlos con el avión), tablas puente que no sirven etc., ha sido para enseñarte el potencial que tiene esta herramienta. Este ejemplo seguramente no requeriría tanto esfuerzo para tanto pocas tablas e indicadores. Pero cuando empiezas a tener decenas de tablas y cientos de variables en un modelo complejo, sí que poder usar el mismo “pax” en lugar de ir cambiándolo en base a lo que quiero ver, puede ser muy útil.

Si quieres profundizar más en el lenguaje DAX, te aconsejo un libro muy completo y con muchísimos ejemplos: The Definitive Guide to DAX