Relaciones entre tablas en PowerBI y funciones para manejarlas

¿Relaciones con claves compuestas?

Uno de mis primeros encontronazos con PowerBI vino motivado por el tema de las relaciones entre tablas. Estaba acostumbrado a crear modelos con relaciones compuestas entre tablas y, cuando me puse a intentar reproducir un proyecto reciente desarrollado con Analysis Services con PowerBI e ir probando su potencial, vi que no había manera. Al menos no como pretendía.

Consistía en algo muy simple, 2 tablas, una con un diario contable multiempresa, y otra con el cuadro de cuentas de cada empresa, en el cual había cuentas que pueden repetirse, por lo que había una relación múltiple entre ambas. Cuando trataba de relacionar alguno de los campos aparecía un mensaje informando de que no era posible por haber campos duplicados:

Sólo una relación activa

Y es que en PowerBI (o Power Pivot) sólo puede haber una relación activa entre 2 tablas y en una de ellas los valores han de ser únicos.

Teniendo esto claro se pueden efectuar modificaciones en las tablas para crear campos únicos, por ejemplo, concatenando los campos empresaid & “-” & codcuenta en ambas tablas y a continuación relacionarlas de nuevo. Con esto solventaríamos el problema.

Cuando necesitamos que hayan varias relaciones

Otro caso surgió cuando estaba desarrollando un proyecto de análisis de facturación. Una tabla con facturas emitidas, con fecha de emisión y fecha de vencimiento y una tabla de fechas que se relacionaba con ambas fechas.

Cuando se crean las relaciones entre las mismas una queda activa (línea continua) y otras desactivadas (línea discontinua).

Cualquier cálculo o filtro aplicado seguirá el camino de la relación activa.

Podríamos crear otra tabla de fechas y relacionarla, pero a efectos de extraer informes nos complica el desarrollo y nos interesa conservar una traza única para simplificar los filtros de los informes, eventuales medidas DAX…

La relación activa es, por ejemplo, la correspondiente a la fecha de emisión con Date, y si deseamos crear la suma de importes de las facturas emitidas por mes lo podemos hacer instantáneamente:

¿Pero si queremos, además, ver el importe total por fecha de vencimiento, que NO es la relación activa?

Ya no podremos hacerlo directamente y tendremos que crear una medida con DAX.

USERELATIONSHIP al rescate

Existe una función DAX, USERELATIONSHIP, que permite efectuar cálculos que requieran el uso de relaciones no activas. No devuelve ningún resultado y sólo podrá usarse en funciones que usan filtros como argumento, no funciona por si sola.

Requiere que la relación, aunque no esté activa, se realice previamente, y no es posible crear relaciones al vuelo.

Su sintaxis es:

USERELATIONSHIP(<columnName1>;<columnName2>)

Y para aplicarla a nuestro ejemplo requiere la creación de una nueva medida que permitirá calcular la suma de facturación por fecha de vencimiento:

Total Vencimiento=
 CALCULATE (
 SUM ( FACTURAS[IMPORTE] );
 USERELATIONSHIP ( FACTURAS[FECHA VENCIMIENTO]; Fechas[Date] )
 )

Como hemos mencionado, USERELATIONSHIP trabaja en combinación con otras funciones, CALCULATE en este caso, está actuando como “filtro” para la misma.

Esta función también puede anidarse, siendo la más interior la que prevalecerá en caso de conflicto.

En un próximo artículo trataremos otras funciones que utilizan las relaciones, como RELATED y RELATEDTABLE.

NOTA: Si deseas ver un caso en el que jugamos con 2 fechas diferentes sin usar USERELATIONSHIP te sugiero que leas este post:

Te puede interesar...

28 comentarios en «Relaciones entre tablas en PowerBI y funciones para manejarlas»

  1. Hola tengo estas tablas triage, HC, Calendar y totaten estas se relacionen por hc y fecha de atencion.
    Necesito obtener en cuantos dias retorno un paciente al servicio para ello elabore una tabla con las hc y conlas formulas de max y min (min2 = min(Triage[FECHA MEDICO])) y de esto obtuve los dias en que reingreso sumax1 = SUMX(hc,IF([dias2]<1,BLANK(),1)) de aqui deseo determinar cuantos reingresaron dentro de 1 y dos dias

  2. hola quiero poder exportar de power bi el resultado de la union de dos tablas relacionadas entre si por el campo id_cliente, necesito que la tabla resultado contenga todos los registros de la tabla 1 y solo 2 columnas de la tabla 2 donde el id cliente coincida. al intentar exportar una tabla visualizada no me deja porque excede resultados, por eso intentaba crear una tabla a parte

    1. Hola Carlos,
      las tablas de powerbi tienen límite de tamaño. No es su finalidad mostrar listados grandes, para eso hay otras herramientas. No sé si es opción, pero trataría de crear una vista en el propio origen y exportar desde ahí. Y si no es opción, conectaría desde excel al dataset de power bi y reproduciría la tabla en cuestión: En Excel no tendrás esa limitación

      1. el problema de exceder resultados viene unicamente al exportar una tabla visualizada. es decir, que ambas tablas se han cargado bien en power bi. pero ahora necesito crear una tercera tabla resultado del cruce de la tabla 1 (40k registros) y tabla 2 (4m registros): todos los registros y columnas de la t1 + 3 columnas de la t2 con los registros que coincidan por el campo comun relacionado id_cliente. para luego poder exportarla o explotarla directamente en power bi

        1. Si, el problema es que los visuales tiene límite (30k registros). Este articulo detallas algunos:
          https://go.christiansteven.com/knowledge/pbrs-for-power-bi/pbrs-technical-faqs/what-are-the-limitations-when-exporting-power-bi-to-excel-or-csv-using-pbrs

          ¿En el modelo datos power bi las tablas que indicas están relacionadas? Si es así puedes construir la tabla como pivot table conectada al dataset de power bi en excel y exportar o “guardar cómo”, pues no tiene ese límite que tiene el visual table de power bi. Por que power bi no tiene una funcionalidad exportar como tal: Puedes exportar el contenido de un visual.

          Insisto: Power BI no es adecuado para estos menesteres
          Por cierto ¿4m son miles o millones? Si son millones olvídate también de excel…

  3. Hola, lo que sucede es que tengo 2 tablas , en una de ellas esta “Fecha de Recepción” y “Fecha de Calibración”, y en la otra tabla tengo el calendario; necesito calcular el tiempo que tarda los productos en salir del laboratorio, pero sin contar los fines de semana y días festivos.
    Agradezco cualquier ayuda que me pueda brindar. Gracias

  4. Hola, tengo dos tablas, una es el maestro de clientes con llave única el ID_CLIENTE y la otra tabla es de facturas la cual se relaciona con el ID_CLIENTE del cliente, Power BI me hace la relación de muchos a muchos cuando debería ser de UNO(clientes) a muchos(facturas), he revisado y garantizado que el ID del cliente sea único, si borro la relación y la hago manualmente pasa igual, que me puede estar afectando esta relación?
    De antemano, Gracias.

    1. Hola Diego, lo único que se me ocurre es, y por dar ideas , si son campos tipo texto, es que una de las columnas temas espacios y la otra no… Puede pasar cuando usan campos Char de largo fijo y cruzas con varchar. Se resuelve facil en powerquery haciendo un trim o recortar espacios. O eso o que no sean mismo tipo dato… Saludos

  5. Me sale el error de “La funcion USERRELATIONSHIP solo puede usar las dos referencias de columna que participan en la relacion”

    1. Hola Sergio, las columnas que vas a usar para relacionar en USERELATIONSHIP deben estar previamente relacionadas en el diagrama de relaciones entre tablas. Seguramente , si antes las has relacionado por otro campo, esta segunda relación se verá con una línea discontinua. Con USERELATIONSHIP indicas que use esa otra relación, en lugar de la principal para realizar los cálculos

  6. Santiago,,
    Me parece muy interesante tu contenido y creo que puedes ayudarme con un lío que tengo semanas intentando resolver sin éxito.

    Necesito crear 2 relaciones entre 2 tablas, lo requiero de esta manera ya que al momento de aplicar filtros solo filtra en base a la columna relacionada.

    1. Hola Carlos, hay algunas posibilidades: Si son pocos datos combinar ambas tablas en una sola tabla en power query. O tratar de convertir a modelo de relaciones es estrella creando una tabla intermedia que haga de puente entre ambas. Este artículo dew Christina Blake explica la segunda posibilidad.

  7. Estimado, estoy intentando hacer una relacion uno a varios, tabla 1 contiene id cliente y nombre, tabla 2 contiene id cliente y fechas de visitas. Sin embargo, despues de crear la relacion power bi me la cambia a varios a uno.
    No me funciona esa opcion porque quiero crear una tabla con todo el listado de clientes, y si fue visitado que me señale en que fecha, si no que las fechas queden todas en blanco. Lo que hace power bi es solo mostrarme los clientes que al menos tienen una fecha de visita, los que no simplemente no los muestra.
    ¿Sabras por qué lo hace?
    Muchas gracias.

  8. Estimado
    Una pregunta.
    Al tener las dos tablas siguientes:
    Tabla 1 (Sin duplicados) con dos variables
    Codigo item
    descripción
    Tabla 2
    Codigo de item
    Cantidad despachada

    Que relación recomiendas realizar Uno a Varios o Varios a Varios y porque por fav

    1. Hola Franklin, la relación debería ser 1 (desde tabla1) a muchas (hasta tabla2). La tabla 2 se podría considerar la tabla de hechos y la 1 una dimensión. Si la tabla2 tuviese fecha y agregases una tabla 3 con fechas únicas harías lo mismo de la 3 a la 2… así vas construyendo un modelo de estrella, que es lo recomendable

  9. Buenas atrdes.

    Si tengo una llave compuesta, por ejemplo: enc_Factura(Ciudad,Fecha,numero) se relaciona con det_Factura(Ciudad,Fecha,numero), como se logra realizar esta relación en powerBI?

    1. Hola Diego, tienes varias opciones. A mi la que más me gusta es actuar en el origen de datos, o sea, en la consulta de origen, en la que puedes crear un campo calculado. Supongamos el siguiente caso en Sql Server (puedes extrapolar el procedimiento a tu caso fácilmente concatenando los campos que indicas) de una vista de familias de productos:
      SELECT
      CodigoEmpresa AS EmpresaID,
      CodigoFamilia AS FamId,
      CAST(CodigoEmpresa AS VARCHAR(10)) + '-' + CAST(CodigoFamilia AS VARCHAR(10)) AS FamiliaUID,
      Descripcion AS Familia
      FROM dbo.Familias

      Puedes observar que la tercera columna es el resultado de concatenar las 2 primeras (requieren ademñás de una conversión de int a char).

      Si no tienes acceso al origen, otra posibilidad es hacer lo mismo en Query Editor de Power BI, en el cual tendrás que añadir una “Custom Column” que haga exactamente lo mismo que en ejemplo anterior.
      En la siguiente imagen podrás ver que el resultado en ambos casos es el mismo:

      Custom Column
      Esto tendrás que hacerlo con todos los campos que deban relacionarse, tanto en maestros como tablas de hechos, y a continuación relacionar del meestro a tabla de hechos por estos campos.
      También podrías añadir una columna calculada como Dax fórmula, pero no aconsejo con tablas grandes como pueden ser las de facturación.

    2. Buenas tardes.
      Por favor, a ver si puede ayudarme con la siguiente duda: ¿Cómo consigo dividir los campos cantidades de todos los productos entre uno de ellos? Cuando la tabla es del tipo siguiente y por ejemplo habría que dividir entre el prod_01 (y posteriormente poder filtrar):

      Mes Estructura Producto Cantidad
      Ene Estruc_01 Prod_01 Número
      Feb Estruc_02 Prod_02 Número
      Feb Estruc_03 Prod_03 Número

      Siento el asalto. Muchas gracias.

      1. Me temo que no entiendo… ¿Se trata de sacar el % de cada producto respecto a uno concreto?¿Cuál es la finalidad?

  10. Hola de nuevo,

    No me deja compartir foto….a ver si puedo hacerlo aquí más o menos:

    La tabla principal que rige se llama CheckoutbehaviourCy, está tiene una relación activa */1 con otra tabla con fechas (es una tabla creada con fechas ISO) y con otras tres tablas (CheckoutbehaviourLy, CheckoutbehaviourFunnel y CheckoutbehaviourTransactions) activas con una relación */* por el campo “Country”.

    También tiene una relación no activa con estas tres tablas con el campo “PlatformWebsiteVersion”. He creado una tabla en la que las filas son los valores de “PlatformWebsiteVersion” y me obliga a activar la relación con un USERELATIONSHIP, el problema es que tengo que hacerlo con las métricas de las tres tablas. Entonces cuando intento ahora filtrar por un país, ya no lo hace ya que la relación de “Country” se ha desactivado por la otra. A la vez activo otra relación entre las fechas y las otras tres tablas para que me coja el filtro de fecha.

    *BD_Fechas* *CheckoutbehaviourCy* *Otras 3 Tablas* *BD_Fechas*
    Date —————— Date Date (———–) Date
    Country ———– Country
    PlatformWebVersion (———-) PlatformWebVersion

    Si no me he explicado bien, si tiene otra canal con el que comunicarnos, coméntemelo y te mando una imagen con la relación de tablas.

    Muchas gracias de nuevo.
    Alfonso Ramos

    1. Ok, más claro. La primera solución que me ha venido a la cabeza es la de una unión entre tablas CheckoutbehaviourCy y CheckoutbehaviourLy, pero por tamaño no es opción…

      Pero ¿es necesario?
      Al margen de que seguramente habrá más motivos para que lo tengas así: No entiendo por que las relacionas (ni que campo te permite hacerlo). Pero a falta de más detalles me arriesgo a sugerirte que puedes tenerlas por separado y relacionar cada una de ellas por separado con Maestro Country y Maestro fechas u otras que puedas necesitar relacionar, sin necesidad de relacionar las de checkout entre ellas.
      Es como si tuvieras una tabla con ventas diarias y otra con ventas presupuestadas: Ambas puedes relacionarlas por separado con fechas y otros maestros y analizarlas conjuntamente, aunque tendrás que crear medidas que via dateadd o similares para buscar los valores equivalentes para Ly.

      No sé, dale una vuelta, quizá no sea la solución que buscas, pero te dé ideas…

      Propuesta relaciones powerbi

      Si no dispones de maestros puedes crear maestros al “vuelo”. Mira este post, hacia el final, igual te ayuda:

      https://www.biti.es/consultar-datos-que-no-existen-ejemplo-en-powerbi/

      Saludos!

      En respuesta a:

      La tabla azul es la principal, la de la izquierda es la misma pero con la información del año anterior, las dos de abajo son dos tablas que son el resultado de la union de otras 4 (hay dos por año y las he juntado para minimizar las relaciones) y por último la de rojo es la de las fechas. Las dos tablas de Checkoutbehaviour no me deja unirlas por memoria, ya que cada una tiene unos 70 millones de registros y da error.

      Entonces cuando intento generar tablas con campos dentro de CheckoutbehaviourCy y meto métricas que se calculan con otra de las tablas, me hace activar la relación con la variable que tenga en las columnas, así que siempre falla y tengo que crearme la misma métrica pero varias veces dependiendo del filtro que le meta. Entonces se carga el filtro de país y el resto de filtros.

      ¿Ves una solución al respecto?

  11. Buenas,

    Tengo un problema con la relación de tablas. Si yo tengo una tabla con una relación directa por ejemplo por país, y tengo una tabla en la que la variable para las filas es la web de acceso, me da fallo en los datos de las tablas relacionadas por país y me muestra el valor total para cada fila. Uso entonces el USERELATIONSHIP para que me conecte las tablas por este valor y lo hace bien.

    Ahora viene el problema, cuando hago un filtro general por país o por otra variable, los valores de esa métrica en la que he aplicado la nueva relación no se inmutan ya que ya no se relacionan por el país si no por la web…

    ¿Qué solución ve mejor para esta problemática?

    Muchas gracias.
    Un saludo.

    1. Hola Alfonso, creo que no entiendo el planteamiento. Un diagrama con las tablas y sus relaciones ayudaría a comprenderlo, puedes remitirlo?

Responder a franklin Chiluisa Cancelar la respuesta

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