Introducción
Puede escribir una fórmula de Expresiones de análisis de datos (DAX) para agregar una tabla calculada a un modelo. La fórmula puede duplicar o transformar los datos del modelo para generar una tabla.
Nota
Una tabla calculada no se puede conectar a datos externos; hay que utilizar Power Query para realizar esa tarea.
Una fórmula de tabla calculada debe devolver un objeto de tabla. La fórmula más sencilla puede duplicar una tabla de modelo.
Sin embargo, las tablas calculadas tienen una desventaja: aumentan el tamaño de almacenamiento del modelo y pueden prolongar el tiempo de actualización de los datos. La razón es que las tablas calculadas vuelven a calcularse cuando tienen dependencias de fórmulas en tablas actualizadas.
Duplicado de una tabla
En la siguiente sección se describe un desafío de diseño común que puede resolverse creando una tabla calculada. En primer lugar, debe descargar y abrir el archivo Adventure Works DW 2020 M03.pbix y, a continuación, cambiar al diagrama del modelo.
En el diagrama del modelo, observe que la tabla Sales tiene tres relaciones con la tabla Date.
El diagrama del modelo muestra tres relaciones porque la tabla Sales almacena los datos de ventas por fecha de pedido, fecha de envío y fecha de vencimiento. Si examina las columnas OrderDateKey, ShipDateKey y DueDateKey, observe que hay una relación representada mediante una línea sólida, que es la relación activa. Las demás relaciones, que se representan con líneas discontinuas, son relaciones inactivas.
Nota
Solo puede haber una relación activa entre dos tablas de modelo cualquiera.
En el diagrama, mantenga el cursor sobre la relación activa para resaltar las columnas relacionadas, que es cómo interactuaría el usuario con el diagrama del modelo para obtener información sobre las columnas relacionadas. En este caso, la relación activa filtra la columna OrderDateKey de la tabla Sales. Por lo tanto, los filtros que se aplican a la tabla Date se propagarán a la tabla Sales para filtrar por fecha de pedido; nunca filtrarán por fecha de envío o por fecha de vencimiento.
El siguiente paso consiste en eliminar las dos relaciones inactivas entre la tabla Date y la tabla Sales. Para eliminar una relación, haga clic con el botón derecho en ella y, a continuación, seleccione Eliminar en el menú contextual. Asegúrese de eliminar ambas relaciones inactivas.
A continuación, agregue una nueva tabla para permitir que los usuarios de informes filtren las ventas por fecha de envío. Cambie a la vista de informe y, a continuación, en la ficha de cinta Modelado, en el grupo Cálculos, seleccione Nueva tabla.
En la barra de fórmulas (situada debajo de la cinta de opciones), escriba la siguiente definición de tabla calculada y, a continuación, presione ENTRAR.
Ship Date = 'Date'
La definición de la tabla calculada duplica los datos de la tabla Date para generar una nueva tabla denominada Ship Date. La tabla Ship Date tiene exactamente las mismas columnas y filas que la tabla Date. Cuando se actualizan los datos de la tabla Date, se vuelve a calcular la tabla Ship Date, por lo que siempre estarán sincronizados.
Cambie al diagrama del modelo y, a continuación, observe que se ha agregado la tabla Ship Date.
A continuación, cree una relación entre la columna DateKey de la tabla Ship Date y la columna ShipDateKey de la tabla Sales. Puede crear la relación arrastrando la columna DateKey de la tabla Ship Date hasta la columna ShipDateKey de la tabla Sales.
Una tabla calculada solo duplica los datos, no las propiedades del modelo u objetos como la visibilidad de la columna o las jerarquías. Tendrá que configurarlos para la nueva tabla, si es necesario.
Sugerencia
Es posible cambiar el nombre de las columnas de una tabla calculada. En este ejemplo, es buena idea cambiar el nombre de las columnas para que describan mejor su finalidad. Por ejemplo, se puede cambiar el nombre de la columna Fiscal Year de la tabla Ship Date a Ship Fiscal Year. En consecuencia, cuando los campos de la tabla Ship Date se usan en objetos visuales, sus nombres se incluirán automáticamente en subtítulos como el título del objeto visual o las etiquetas del eje.
Para completar el diseño de la tabla Ship Date, puede hacer lo siguiente:
- Cambiar el nombre de las columnas siguientes:
- Date como Ship Date
- Fiscal Year como Ship Fiscal Year
- Fiscal Quarter como Ship Fiscal Quarter
- Month como Ship Month
- Full Date como Ship Full Date
- Ordenar la columna Ship Full Date por la columna Ship Date
- Ordenar la columna Ship Month por la columna MonthKey
- Ocultar la columna MonthKey
- Crear una jerarquía denominada Fiscal con los siguientes niveles:
- Ship Fiscal Year
- Ship Fiscal Quarter
- Ship Month
- Ship Full Date
- Marcar la tabla Ship Date como una tabla de fechas usando la columna Ship Date
Las tablas calculadas son útiles para trabajar en escenarios en los que hay varias relaciones entre dos tablas, como se ha descrito anteriormente. También se pueden usar para agregar una tabla de fechas al modelo. Las tablas de fechas son necesarias para aplicar filtros de hora especiales conocidos como inteligencia de tiempo.
Creación de una tabla de fechas
En el ejemplo siguiente, se creará una segunda tabla calculada, esta vez mediante la función DAX CALENDARAUTO.
Cree la tabla calculada Due Date usando la siguiente definición.
Due Date = CALENDARAUTO(6)
La función CALENDARAUTO de DAX toma un solo argumento opcional, que es el último número de mes del año, y devuelve una tabla de una sola columna. Si no pasa un número de mes, se da por sentado que es 12 (correspondiente a diciembre). Por ejemplo, en Adventure Works, su año financiero finaliza el 30 de junio de cada año, por lo que se pasa el valor 6 (correspondiente a junio).
La función examina todas las columnas de fecha y hora del modelo para determinar los valores de fecha almacenados más antiguos y más recientes. A continuación, genera un conjunto completo de fechas que abarcan todas las fechas del modelo, garantizando que se cargan años completos de fechas. Por ejemplo, si la fecha más antigua almacenada en el modelo es el 15 de octubre de 2021, la primera fecha que devuelve la función CALENDARAUTO sería el 1 de julio de 2021. Si la fecha más reciente almacenada en el modelo es el 15 de junio de 2022, la última fecha que devuelve la función CALENDARAUTO sería el 30 de junio de 2022.
De hecho, la función CALENDARAUTO garantiza que se cumplan los siguientes requisitos para marcar una tabla de fechas:
- La tabla debe incluir una columna de tipo de datos Date.
- La columna debe contener años completos.
- La columna no debe tener fechas que falten.
Sugerencia
También puede crear una tabla de fechas usando la función DAX CALENDAR y pasar dos valores de fecha, que representan el intervalo de fechas. La función genera una fila por cada fecha dentro del intervalo. Puede pasar valores de fecha estáticos o expresiones que recuperen las fechas más antiguas y más recientes de columnas específicas del modelo.
A continuación, cambie a la vista de datos y, a continuación, en el panel Campos, seleccione la tabla Due Date. Ahora, revise la columna de fechas. Recomendamos ordenarlas para ver la fecha más antigua en la primera fila seleccionando la flecha dentro del encabezado de columna Date y, a continuación, ordenando en orden ascendente.
Nota
Ordenar o filtrar columnas no cambia la forma en que se almacenan los valores. Estas funciones ayudan a explorar y comprender los datos.
Ahora que está seleccionada la columna Date, revise el mensaje en la barra de estado (que se encuentra en la esquina inferior izquierda). Describe el número de filas que almacena la tabla y el número de valores distintos que hay en la columna seleccionada.
Cuando las filas de la tabla y los valores distintos son iguales, significa que la columna contiene valores únicos. Esto es importante por dos motivos: cumple los requisitos para marcar una tabla de fechas y permite usar esta columna en una relación de modelo como el lado "uno".
La tabla calculada Due Date se volverá a calcular cada vez que se actualice una tabla que contenga una columna de fechas. En otras palabras, cuando una fila se carga en la tabla Sales con una fecha de pedido del 1 de julio de 2022, la tabla Due Date se extenderá automáticamente para incluir fechas hasta el final del año siguiente: 30 de junio de 2023.
La tabla Due Date requiere columnas adicionales para admitir requisitos de filtrado y agrupación conocidos, específicamente por año, trimestre y mes.