4.2Power Pivot, modelo de datos e introducción a DAX
Lo esencial en 30 segundos
- Relacionar en lugar de aplanar: el modelo de datos (Power Pivot) relaciona varias tablas mediante relaciones sobre una clave común — una tabla dinámica bebe entonces de varias tablas como si fueran una sola.
- DAX = cálculos centralizados: una medida (ej. SUMA(Ventas[Montant])) vive en el modelo y se recalcula según los filtros de la tabla dinámica — un solo cálculo de «facturación» para todas las tablas.
- Reflejo de Wassim: en cuanto los BUSCARX se multiplican, es hora de modelar — y es la puerta hacia Power BI (mismos conceptos).
Mini-caso hilado — Wassim cruza ventas y productos sin un solo BUSCARX
Wassim, contable en Argel, analiza la cifra de negocio por categoría: su tabla Ventas (CodigoProducto, Montant) y su tabla Productos (CodigoProducto, Categoria) le obligaban a BUSCARX frágiles sobre miles de filas. Carga las dos tablas en el modelo, crea una relación sobre CodigoProducto y escribe una medida Total Ventas. Su tabla dinámica cruza la categoría (de Productos) y la medida — sin BUSCARX. Segundo hito del hilo conductor: la tabla Power Query de 4.1 alimenta ahora un modelo real.
Continuación del caso 4.1 — la tabla limpiada por Wassim entra en el modelo de datos.
1. Relacionar tablas en lugar de aplanarlo todo
En lugar de escribir BUSCARX para reunirlo todo en una sola tabla gigante, el modelo de datos (Power Pivot) relaciona varias tablas mediante relaciones (una clave común, ej. CodigoProducto). Una tabla dinámica puede entonces beber de varias tablas relacionadas como si fueran una sola. Es el principio de las bases de datos dentro de Excel — y Wassim gana con ello libros ligeros y fiables.
La relación exige claves del mismo tipo (texto con texto, número con número) y valores únicos en el lado principal: si no, la unión falla o produce duplicados. Se verifican los tipos desde Power Query, antes de modelar.
2. La medida DAX
DAX (Data Analysis eXpressions) es el lenguaje de cálculo del modelo. Una medida es un cálculo reutilizable y contextual: Total Ventas := SUMA(Ventas[Montant]) se recalcula según lo que se filtre en la tabla dinámica (por región, por mes…). Una medida vive en el modelo, no en una celda — todas las tablas dinámicas comparten el mismo cálculo.
No confundas medida (contextual, recalculada por filtro) con fórmula de celda (fija, local): es la contextualidad la que da la potencia al modelo de Wassim.
3. Por qué es potente
El modelo gestiona grandes volúmenes (muy por encima de una hoja), evita la duplicación (nada de BUSCARX por todas partes) y centraliza los cálculos (una medida «facturación» usada por todas las tablas dinámicas). Es el paso hacia Power BI, que se apoya en los mismos conceptos (modelo + DAX).
En la práctica: se cargan las tablas limpiadas (idealmente mediante Power Query) en el modelo con Agregar al modelo de datos, se declaran las relaciones, se escriben las medidas básicas y luego se construyen las tablas dinámicas.
¿Lo sabías?
La tabla dinámica es solo el escaparate; el modelo es la tienda.
🔎 Ejemplo concreto — 🔎 Ejemplo concreto — Una tabla dinámica sobre dos tablas relacionadas
Tabla Ventas (CodigoProducto, Montant) relacionada con Productos (CodigoProducto, Categoria):
Relación: Ventas[CodigoProducto] → Productos[CodigoProducto]
Medida : Total Ventas := SUM(Ventas[Montant])
Tabla dinámica: Categoria (de Productos) en Filas, [Total Ventas] en Valores
→ Facturación por categoría SIN ningún BUSCARX
// Variante: facturación de Argel únicamente
CA Argel := CALCULATE(SUM(Ventas[Montant]), Ventas[Ciudad] = "Argel")Lectura: la tabla dinámica cruza dos tablas gracias a la relación, y la medida sigue los filtros.
Lección: se modela (relaciones) en lugar de aplanar a base de BUSCARX.
Vocabulario de la sección
- Modelo de datos
- Conjunto de tablas relacionadas por relaciones, almacenadas en el libro.
Ejemplo sencillo: Wassim carga en él Ventas y Productos para cruzarlas en la tabla dinámica.
Lo que no es: una sola tabla gigante aplanada a base de BUSCARX.
Reflejo: varias tablas que cruzar de forma duradera: cargarlas en el modelo.
- Relación
- Vínculo entre dos tablas mediante una clave común (ej. CodigoProducto), de tipos idénticos.
Ejemplo sencillo: Wassim relaciona Ventas y Productos por CodigoProducto.
Lo que no es: una fórmula que copia un valor línea a línea.
Reflejo: cruzar dos tablas: declarar la relación sobre la clave.
- Medida DAX
- Cálculo reutilizable y contextual que se adapta a los filtros de la tabla dinámica.
Ejemplo sencillo: El Total Ventas de Wassim se recalcula por mes y por ciudad.
Lo que no es: una fórmula de celda fija y local.
Reflejo: cálculo compartido por varias tablas dinámicas: escribir una medida.
- Contexto de filtro
- Lo que está filtrado en la tabla dinámica y condiciona el resultado de una medida.
Ejemplo sencillo: Filtrar por Argel recalcula automáticamente la facturación de Wassim.
Lo que no es: un valor fijo independiente de los filtros.
Reflejo: medida sorpresa: verificar primero los filtros activos.
En la práctica — En práctica — Relacionar dos tablas
- Carga dos tablas (Ventas, Productos) en el modelo de datos.
- Crea una relación sobre la clave común (CodigoProducto).
- Crea una medida simple: Total Ventas := SUM(Ventas[Montant]).
- Haz una tabla dinámica que cruce una columna de Productos y la medida, sin BUSCARX.
Guion listo para decir — explicar el modelo a un compañero (30 s)
«Soy Wassim: en lugar de copiar las categorías con BUSCARX, relacioné las dos tablas por el código de producto. La tabla dinámica cruza directamente las tablas, y mi medida de la cifra de negocio se recalcula según los filtros. Un solo cálculo para todas las tablas — y prepara Power BI.»
Decirlo cuando alguien proponga «otra columna más de BUSCARX»: muestra la tabla dinámica multi-tabla.
Plantilla para copiar y pegar — Medidas DAX básicas
Total Ventas := SUM(Ventas[Montant])
Nb Ventas := COUNTROWS(Ventas)
Ticket Medio := DIVIDE([Total Ventas], [Nb Ventas])
CA Argel := CALCULATE(SUM(Ventas[Montant]), Ventas[Ciudad] = "Argel")Facturación por categoría
Como Wassim: relación Ventas-Productos por CodigoProducto, tabla dinámica con categoría en filas y medida en valores.
Seguimiento por ciudad
Filtrar la tabla dinámica por Argel: la misma medida se recalcula sin nueva fórmula.
Grandes volúmenes
Cientos de miles de filas: el modelo las absorbe donde la hoja se satura.
Claves de tipos distintos
Fallo de relación: realinear los tipos en Power Query (texto/texto) y luego recrear la relación.
✓ Buenas prácticas
- Modelar por relaciones en lugar de duplicar.
- Centralizar los cálculos en medidas.
- Ver el modelo como la puerta hacia Power BI.
✗ Errores frecuentes
- Aplanarlo todo con BUSCARX (pesado, frágil).
- Relaciones sobre claves de tipos distintos (fallo).
- Confundir una medida (contextual) y una fórmula de celda (fija).
Puntos clave
- El modelo de datos relaciona varias tablas mediante relaciones.
- Una tabla dinámica puede beber de varias tablas relacionadas.
- DAX: una medida = cálculo reutilizable y contextual.
- Mismos conceptos que Power BI (modelo + DAX).
Reto 48h — aplícalo mañana
Sobre tus tablas reales: carga dos en el modelo, crea la relación sobre la clave común, escribe una medida SUMA y crúzala en tabla dinámica sin BUSCARX.
- Sé cargar tablas y crear una relación.
- Sé escribir una medida DAX simple y contextual.
- Sé cruzar varias tablas en tabla dinámica sin BUSCARX.
Preguntas frecuentes
¿Modelo de datos o BUSCARX?
BUSCARX para traer un valor puntual; el modelo para analizar de forma duradera varias tablas relacionadas (grandes volúmenes, cálculos centralizados). En cuanto los BUSCARX se multiplican, es hora de modelar.
¿Es difícil DAX?
Las medidas simples (SUMA, PROMEDIO dentro del modelo) son accesibles; la potencia (contextos, CALCULATE) requiere práctica. Se empieza por medidas básicas, exactamente como se empezó por =SUMA.
¿Para qué sirve una relación en el modelo de datos?