Grupos de cálculo
Se aplica a: 
SQL Server 2019 y versiones posteriores Analysis Services
Azure Analysis Services
Power BI Premium
Los grupos de cálculo pueden reducir significativamente el número de medidas redundantes mediante la agrupación de expresiones de medida comunes como elementos de cálculo. Los grupos de cálculo se admiten en modelos tabulares en el nivel de compatibilidad 1500 y superior.
En este artículo se describe:
- Ventajas
- Cómo funcionan los grupos de cálculo
- Cadenas de formato dinámico
- Prioridad
- Recursividad lateral
- Ordenación
- Cómo crear
- Limitaciones
Ventajas
Los grupos de cálculo abordan un problema en modelos complejos en los que puede haber una proliferación de medidas redundantes con los mismos cálculos, lo más común con los cálculos de inteligencia de tiempo. Por ejemplo, un analista de ventas quiere ver los totales de ventas y los pedidos por mes hasta la fecha (MTD), trimestre hasta la fecha (QTD), año hasta la fecha (YTD), pedidos hasta la fecha del año anterior (PY), y así sucesivamente. El modelador de datos tiene que crear medidas independientes para cada cálculo, lo que puede dar lugar a docenas de medidas. Para el usuario, esto puede significar tener que ordenar el mismo número de medidas y aplicarlas individualmente a su informe.
En primer lugar, echemos un vistazo a cómo se muestran los grupos de cálculo a los usuarios en una herramienta de informes como Power BI. A continuación, echaremos un vistazo a lo que forma un grupo de cálculo y cómo se crean en un modelo.
Los grupos de cálculo se muestran en los clientes de informes como una tabla con una sola columna. La columna no es como una columna o dimensión típica, sino que representa uno o varios cálculos reutilizables o elementos de cálculo que se pueden aplicar a cualquier medida ya agregada al filtro Valores para una visualización.
En la animación siguiente, un usuario está analizando los datos de ventas de los años 2012 y 2013. Antes de aplicar un grupo de cálculo, la medida base común Ventas calcula una suma de las ventas totales de cada mes. A continuación, el usuario quiere aplicar cálculos de inteligencia de tiempo para obtener los totales de ventas del mes hasta la fecha, trimestre hasta la fecha, año hasta la fecha, y así sucesivamente. Sin los grupos de cálculo, el usuario tendría que seleccionar medidas de inteligencia de tiempo individuales.
Con un grupo de cálculo, en este ejemplo denominado Inteligencia de tiempo , cuando el usuario arrastra el elemento Cálculo de tiempo al área de filtro Columnas, cada elemento de cálculo aparece como una columna independiente. Los valores de cada fila se calculan a partir de la medida base, Sales.
Los grupos de cálculo funcionan con medidas DAX explícitas. En este ejemplo, Sales es una medida explícita ya creada en el modelo. Los grupos de cálculo no funcionan con medidas DAX implícitas. Por ejemplo, en Power BI se crean medidas implícitas cuando un usuario arrastra columnas a objetos visuales para ver los valores agregados, sin crear una medida explícita. En este momento, Power BI DAX para las medidas implícitas escritas como cálculos DAX en línea, lo que significa que las medidas implícitas no pueden funcionar con grupos de cálculo. Se ha introducido una nueva propiedad de modelo visible en el modelo de objetos tabulares (TOM), DiscourageImplicitMeasures. Actualmente, para crear grupos de cálculo, esta propiedad debe establecerse en true. Cuando se establece en true, Power BI Desktop modo Live Connect deshabilita la creación de medidas implícitas.
Los grupos de cálculo también admiten consultas MDX (Expresiones de datos multidimensionales). Esto significa que Microsoft Excel, que consultan modelos de datos tabulares mediante MDX, pueden aprovechar al máximo los grupos de cálculo en tablas dinámicas y gráficos de hojas de cálculo.
Cómo funcionan
Ahora que ha visto cómo los grupos de cálculo benefician a los usuarios, echemos un vistazo a cómo se crea el ejemplo de grupo de cálculo de Time Intelligence mostrado.
Antes de entrar en los detalles, vamos a presentar algunas funciones DAX nuevas específicamente para los grupos de cálculo:
SELECTEDMEASURE: se usa en expresiones para elementos de cálculo que hacen referencia a la medida que está actualmente en contexto. En este ejemplo, la medida Sales.
SELECTEDMEASURENAME: se usa en expresiones para los elementos de cálculo con el fin de determinar la medida que está en contexto por nombre.
ISSELECTEDMEASURE: las expresiones usadas por los elementos de cálculo para determinar la medida que está en contexto se especifican en una lista de medidas.
SELECTEDMEASUREFORMATSTRING: se usa en expresiones para los elementos de cálculo para recuperar la cadena de formato de la medida que está en contexto.
Ejemplo de Inteligencia de tiempo
Nombre de tabla: inteligencia de tiempo
Nombre de columna: cálculo de tiempo
Prioridad: 20
Elementos de cálculo de inteligencia de tiempo
Current
SELECTEDMEASURE()
MTD
CALCULATE(SELECTEDMEASURE(), DATESMTD(DimDate[Date]))
QTD
CALCULATE(SELECTEDMEASURE(), DATESQTD(DimDate[Date]))
YTD
CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))
PY
CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(DimDate[Date]))
PY MTD
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR(DimDate[Date]),
'Time Intelligence'[Time Calculation] = "MTD"
)
PY QTD
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR(DimDate[Date]),
'Time Intelligence'[Time Calculation] = "QTD"
)
PY YTD
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR(DimDate[Date]),
'Time Intelligence'[Time Calculation] = "YTD"
)
YOY
SELECTEDMEASURE() -
CALCULATE(
SELECTEDMEASURE(),
'Time Intelligence'[Time Calculation] = "PY"
)
% DEL YOY
DIVIDE(
CALCULATE(
SELECTEDMEASURE(),
'Time Intelligence'[Time Calculation]="YOY"
),
CALCULATE(
SELECTEDMEASURE(),
'Time Intelligence'[Time Calculation]="PY"
)
)
Para probar este grupo de cálculo, ejecute una consulta DAX en SSMS o en DAX Studio de código abierto. Nota: Los porcentajes YOY y YOY se omiten en este ejemplo de consulta.
Consulta de inteligencia de tiempo
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
DimDate[CalendarYear],
DimDate[EnglishMonthName],
"Current", CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "Current" ),
"QTD", CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "QTD" ),
"YTD", CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "YTD" ),
"PY", CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY" ),
"PY QTD", CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY QTD" ),
"PY YTD", CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY YTD" )
),
DimDate[CalendarYear] IN { 2012, 2013 }
)
Devolución de consulta de Inteligencia de tiempo
La tabla de valor devuelto muestra los cálculos de cada elemento de cálculo aplicado. Por ejemplo, vea QTD para marzo de 2012 es la suma de enero, febrero y marzo de 2012.
Cadenas de formato dinámico
Las cadenas de formato dinámico con grupos de cálculo permiten la aplicación condicional de cadenas de formato a medidas sin forzarlas a devolver cadenas.
Los modelos tabulares admiten el formato dinámico de las medidas mediante la función FORMAT de DAX. Sin embargo, la función FORMAT tiene la desventaja de devolver una cadena, lo que obliga a que las medidas que de otro modo serían numéricas también se devuelvan como una cadena. Esto puede tener algunas limitaciones, como no trabajar con la mayoría de los objetos Power BI en función de los valores numéricos, como los gráficos.
Cadenas de formato dinámico para la inteligencia de tiempo
Si observamos el ejemplo de Inteligencia de tiempo mostrado anteriormente, todos los elementos de cálculo excepto YOY% deben usar el formato de la medida actual en contexto. Por ejemplo, el YTD calculado en la medida base Sales debe ser moneda. Si se tratase de un grupo de cálculo para algo como una medida base Orders, el formato sería numérico. No obstante, el porcentaje de YOY% debe ser un porcentaje independientemente del formato de la medida base.
Para YOY%, podemos invalidar la cadena de formato estableciendo la propiedad de expresión de cadena de formato en 0,00%;-0,00%;0,00%. Para obtener más información sobre las propiedades de expresión de cadena de formato, vea Propiedades de celda MDX- Format STRING Contents.
En este objeto visual de matriz Power BI, verá que Sales Current/YOY y Orders Current/YOY conservan sus respectivas cadenas de formato de medida base. Sin embargo, Sales YOY% y Orders YOY% invalidan la cadena de formato para usar el formato de porcentaje.
Cadenas de formato dinámico para la conversión de moneda
Las cadenas de formato dinámico proporcionan una conversión de moneda sencilla. Considere el siguiente modelo de datos de Adventure Works. Se modela para la conversión de moneda de uno a varios, tal como se define en Tipos de conversión.
Se agrega una columna FormatString a la tabla DimCurrency y se rellena con cadenas de formato para las monedas correspondientes.
En este ejemplo, el siguiente grupo de cálculo se define como:
Ejemplo de conversión de moneda
Nombre de tabla: conversión de moneda
Nombre de columna: cálculo de conversión
Prioridad: 5
Elementos de cálculo para la conversión de moneda
Sin conversión
SELECTEDMEASURE()
Moneda convertida
IF(
//Check one currency in context & not US Dollar, which is the pivot currency:
SELECTEDVALUE( DimCurrency[CurrencyName], "US Dollar" ) = "US Dollar",
SELECTEDMEASURE(),
SUMX(
VALUES(DimDate[Date]),
CALCULATE( DIVIDE( SELECTEDMEASURE(), MAX(FactCurrencyRate[EndOfDayRate]) ) )
)
)
Expresión de cadena de formato
SELECTEDVALUE(
DimCurrency[FormatString],
SELECTEDMEASUREFORMATSTRING()
)
La expresión de cadena de formato debe devolver una cadena escalar. Usa la nueva función SELECTEDMEASUREFORMATSTRING para revertir a la cadena de formato de medida base si hay varias monedas en el contexto de filtro.
La animación siguiente muestra la conversión de moneda de formato dinámico de la medida Sales en un informe.
Prioridad
Precedencia es una propiedad definida para un grupo de cálculo. Especifica el orden de evaluación cuando hay más de un grupo de cálculo. Un número mayor indica una mayor prioridad, lo que significa que se evaluará antes que los grupos de cálculo con menor prioridad.
En este ejemplo, usaremos el mismo modelo que el ejemplo de inteligencia de tiempo anterior, pero también agregaremos un grupo de cálculo Averages. El grupo de cálculo Promedios contiene cálculos promedio que son independientes de la inteligencia de tiempo tradicional, ya que no cambian el contexto del filtro de fecha; simplemente aplican cálculos promedio dentro de él.
En este ejemplo, se define un cálculo de promedio diario. Los cálculos, como los promedios de petróleo al día, son comunes en las aplicaciones de petróleo y gas. Otros ejemplos empresariales comunes incluyen el promedio de ventas de tiendas en el comercio minorista.
Aunque estos cálculos se calculan independientemente de los cálculos de inteligencia de tiempo, puede haber un requisito para combinarlos. Por ejemplo, es posible que un usuario quiera ver los toneles de petróleo al día YTD para ver la tasa diaria de petróleo desde el principio del año hasta la fecha actual. En este escenario, se debe establecer la prioridad para los elementos de cálculo.
Ejemplo de promedios
El nombre de la tabla es Averages.
El nombre de columna es Cálculo medio.
La precedencia es 10.
Elementos de cálculo para promedios
Sin promedio
SELECTEDMEASURE()
Promedio diario
DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate))
Este es un ejemplo de una consulta DAX y una tabla de devolución:
Consulta averages
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
DimDate[CalendarYear],
DimDate[EnglishMonthName],
"Sales", CALCULATE (
[Sales],
'Time Intelligence'[Time Calculation] = "Current",
'Averages'[Average Calculation] = "No Average"
),
"YTD", CALCULATE (
[Sales],
'Time Intelligence'[Time Calculation] = "YTD",
'Averages'[Average Calculation] = "No Average"
),
"Daily Average", CALCULATE (
[Sales],
'Time Intelligence'[Time Calculation] = "Current",
'Averages'[Average Calculation] = "Daily Average"
),
"YTD Daily Average", CALCULATE (
[Sales],
'Time Intelligence'[Time Calculation] = "YTD",
'Averages'[Average Calculation] = "Daily Average"
)
),
DimDate[CalendarYear] = 2012
)
Promedios de devolución de consulta
En la tabla siguiente se muestra cómo se calculan los valores de marzo de 2012.
| Nombre de la columna | Cálculo |
|---|---|
| YTD | Suma de ventas de enero, febrero de 2012 = 495 364 + 506 994 + 373 483 |
| Promedio diario | Ventas de marzo de 2012 divididas por el número de días de marzo = 373 483 / 31 |
| Media diaria de YTD | YTD de marzo de 2012 dividido por el número de días de enero, febrero y marzo = 1 375 841 / (31 + 29 + 31) |
Esta es la definición del elemento de cálculo YTD, aplicado con precedencia de 20.
CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))
Este es el promedio diario, aplicado con una precedencia de 10.
DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate))
Puesto que la precedencia del grupo de cálculo Inteligencia de tiempo es mayor que la del grupo de cálculo Promedios, se aplica de la forma más amplia posible. El cálculo de promedio diario de YTD aplica el YTD tanto al numerador como al denominador (recuento de días) del cálculo medio diario.
Esto equivale a la expresión siguiente:
CALCULATE(DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate)), DATESYTD(DimDate[Date]))
No es esta expresión:
DIVIDE(CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date])), COUNTROWS(DimDate)))
Recursividad lateral
En el ejemplo de Inteligencia de tiempo anterior, algunos de los elementos de cálculo hacen referencia a otros del mismo grupo de cálculo. Esto se denomina recursividad lateral. Por ejemplo, YOY% hace referencia a YOY y PY.
DIVIDE(
CALCULATE(
SELECTEDMEASURE(),
'Time Intelligence'[Time Calculation]="YOY"
),
CALCULATE(
SELECTEDMEASURE(),
'Time Intelligence'[Time Calculation]="PY"
)
)
En este caso, ambas expresiones se evalúan por separado porque usan instrucciones calculate diferentes. No se admiten otros tipos de recursividad.
Elemento de cálculo único en el contexto de filtro
En nuestro ejemplo de Inteligencia de tiempo, el elemento de cálculo PY YTD tiene una expresión de cálculo única:
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR(DimDate[Date]),
'Time Intelligence'[Time Calculation] = "YTD"
)
El argumento YTD de la función CALCULATE() invalida el contexto de filtro para reutilizar la lógica ya definida en el elemento de cálculo YTD. No es posible aplicar PY e YTD en una sola evaluación. Los grupos de cálculo solo se aplican si un único elemento de cálculo del grupo de cálculo está en contexto de filtro.
Ordenación
De forma predeterminada, cuando una columna de un grupo de cálculo se coloca en un informe, los elementos de cálculo se ordenan alfabéticamente por nombre en el informe. El orden en que los elementos de cálculo aparecen en un informe se puede cambiar especificando la propiedad Ordinal. Especificar el orden de los elementos de cálculo con la propiedad Ordinal no cambia la prioridad ,el orden en que se evalúan los elementos de cálculo. Tampoco cambia el orden en que aparecen los elementos de cálculo en el Explorador de modelos tabulares.
Para especificar la propiedad ordinal para los elementos de cálculo, debe agregar una segunda columna al grupo de cálculo. A diferencia de la columna predeterminada donde Tipo de datos es Texto, una segunda columna usada para ordenar los elementos de cálculo tiene un tipo de datos Número entero. El único propósito de esta columna es especificar el orden numérico en el que aparecen los elementos de cálculo del grupo de cálculo. Dado que esta columna no proporciona ningún valor en un informe, es mejor establecer la propiedad Hidden en True.
Después de agregar una segunda columna al grupo de cálculo, puede especificar el valor de la propiedad Ordinal para los elementos de cálculo que desea ordenar.
Para obtener más información, vea Para ordenar elementos de cálculo.
Creación de un grupo de cálculo
Los grupos de cálculo se admiten Visual Studio con Analysis Services Projects VSIX update 2.9.2 y versiones posteriores. Los grupos de cálculo también se pueden crear mediante tabular model scripting language (TMSL) o el editor tabular de código abierto.
Para crear un grupo de cálculo mediante Visual Studio
En el Explorador de modelos tabulares, haga clic con el botón derecho en Grupos de cálculo y, a continuación, haga clic en Nuevo grupo de cálculo. De forma predeterminada, un nuevo grupo de cálculo tendrá una sola columna y un único elemento de cálculo.
Use Propiedades para cambiar el nombre y escriba una descripción para el grupo de cálculo, la columna y el elemento de cálculo predeterminado.
Para escribir una expresión de fórmula DAX para el elemento de cálculo predeterminado, haga clic con el botón derecho y, a continuación, haga clic en Editar fórmula para abrir el Editor DAX. Escriba una expresión válida.
Para agregar elementos de cálculo adicionales, haga clic con el botón derecho en Elementos de cálculo y, a continuación, haga clic en Nuevo elemento de cálculo.
Para ordenar elementos de cálculo
En el Explorador de modelos tabulares, haga clic con el botón derecho en un grupo de cálculo y, a continuación, haga clic en Agregar columna.
Asigne a la columna el nombre Ordinal (o algo similar), escriba una descripción y establezca la propiedad Hidden en True.
Para cada elemento de cálculo que desee ordenar, establezca la propiedad Ordinal en un número positivo. Cada número es secuencial, por ejemplo, aparecerá primero un elemento de cálculo con una propiedad Ordinal de 1, una propiedad de 2 aparecerá en segundo lugar, y así sucesivamente. Los elementos de cálculo con el valor predeterminado -1 no se incluyen en la ordenación, pero aparecerán antes que los elementos ordenados en un informe.
Limitaciones
No se admite la seguridad de nivel de objeto (OLS) definida en tablas de grupo de cálculo. Sin embargo, OLS se puede definir en otras tablas del mismo modelo. Si un elemento de cálculo hace referencia a un objeto protegido de OLS, se devuelve un error genérico.
No se admite la seguridad de nivel de fila (RLS). Defina RLS en tablas del mismo modelo, pero no en los propios grupos de cálculo (directa o indirectamente).
Las expresiones de filas de detalles no se admiten con los grupos de cálculo.
Narración inteligente objetos visuales de Power BI no se admiten con grupos de cálculo.
Las agregaciones de columnas implícitas Power BI no se admiten para los modelos con grupos de cálculo. Actualmente, si la propiedad DiscourageImplicitMeasures está establecida en false (valor predeterminado), aparecen opciones de agregación, pero no se pueden aplicar. Si DiscourageImplicitMeasures está establecido en true, las opciones de agregación no aparecen.
Al crear Power BI informes mediante LiveConnection, las cadenas de formato dinámico no se aplican a las medidas de nivel de informe.