La función ALL () en DAX

Share on facebook
Share on twitter
Share on linkedin
Share on telegram
Share on whatsapp

Contenidos

Nivel: intermedio

En este artículo voy a escribir sobre la función ALL (), mostrar cómo funciona y explicar un caso de uso común. También cubriré el comportamiento inesperado que ocurre cuando se usa la función ALL () en Power BI Desktop; más sobre eso más adelante.

Para este artículo usaré mi copia simplificada de la base de datos de Adventure Works como se muestra a continuación.

image_thumb-5220111

Tablas virtuales vs materializadas

Lo primero que quiero cubrir es el concepto de tablas virtuales vs materializadas. Normalmente, en DAX, cuando escribe una fórmula que contiene una función de tabla (una medida o columna calculada), la tabla se crea en la memoria durante la ejecución. La tabla nunca se «materializa», por lo que no se puede ver y no se almacena en ningún lugar. Me refiero a estas tablas como tablas «virtuales». Por otro lado, es posible «materializar» una tabla en DAX de muchas formas, incluyendo

  • Cargar la tabla desde una fuente de datos en Power Pivot / Power BI (materializar en el modelo de datos en la carga de datos)
  • Escribir una consulta DAX y luego usar el truco que cubro aquí para materializar la tabla directamente en Excel.
  • Usar el botón Nueva tabla en Power BI Desktop para materializar la tabla en el modelo de datos después de la carga de datos como cubrí aquí.
  • También puede materializar una tabla usando una tabla dinámica en Excel para construir una tabla que desee ver. Debe tener cuidado con este enfoque si necesita materializar muchas filas de datos, ya que es mejor usar una tabla dinámica como una herramienta de resumen, no como una herramienta de generación de tablas grandes.

Las tablas virtuales son mucho más difíciles de entender, especialmente si viene del mundo de Excel (los profesionales de SQL no suelen tener el mismo problema). Por eso voy a materializar las tablas que produzco en este artículo para que las veas. Pero en todo momento debe recordar que cada tabla que pueda «ver» a continuación podría, en teoría, usarse como una tabla virtual dentro de otra fórmula DAX.

TODOS () Descripción general

La función ALL () parece muy simple en la superficie, sin embargo, tiene capas de complejidad. En su uso más simple, es una función que simplemente devuelve una tabla (virtual o materializada). La sintaxis de ALL () es la siguiente

= TODOS (TableOrColumn,[Column2],[ColumnN]..)

ALL () siempre devolverá una tabla, no un valor. Debido a que es una tabla, no puede poner el resultado directamente en una celda de una tabla dinámica o una matriz.. Piénselo, no puede colocar una tabla con (potencialmente) múltiples columnas y (potencialmente) múltiples filas en una sola celda en un objeto visual, no “encajará”.

A continuación se muestran algunas tablas de ejemplo materializadas de uso simple de ALL () para que pueda «ver» los resultados y tener una idea de qué se trata. (Ves lo que hice ahí)? En los ejemplos a continuación, estoy usando DAX como lenguaje de consulta para materializar las tablas creadas por la función ALL () directamente en Excel usando el truco de materialización que vinculé anteriormente (nuevamente aquí).

super-charge-power-bi-ad_1-1024x128-6595595

ALL () con un parámetro de tabla

Prueba 1 = TODOS (Productos)

La fórmula anterior devuelve una copia completa de la tabla Productos como se muestra (en parte) a continuación.

image_thumb-1-9550150

Es importante destacar que cualquier filtro que exista se QUITARÁ ANTES de producir esta tabla. Entonces la siguiente consulta DAX produce exactamente la misma tabla.

Prueba 2 = CALCULATABLE (TODOS (Productos), Producto[Category]= «Ropa»)

Si no está familiarizado con CALCULATETABLE, es exactamente lo mismo que CALCULATE. se usa cuando el primer parámetro devuelve una tabla en lugar de un valor.

CALCULATE y CALCULATETABLE siempre ejecutan la parte del filtro de la fórmula primero, luego lo último que hace es evaluar el parámetro de expresión (el primer parámetro). Entonces, la Prueba 2 anterior primero aplica un filtro a la tabla Productos (Productos[Category] = “Ropa”) como parámetro de filtro de CALCUALTETABLE y luego lo último que hace es ejecutar el parámetro ALL (). La función ALL () elimina todos los filtros en el contexto de filtro actual, por lo tanto, CALCULATETABLE agrega un filtro y luego ALL () lo elimina. El mismo comportamiento existe dentro de un objeto visual (por ejemplo, en una matriz o una tabla dinámica) donde el objeto visual crea un contexto de filtro; ALL () aún lo eliminará. Más sobre eso a continuación.

TODOS () con un parámetro de columna

Hay una segunda sintaxis para ALL () donde puede usar una o más columnas (no una tabla) como parámetro (s).

Prueba 3 = TODOS (Productos[Category]) devuelve la siguiente tabla.

image_thumb-2-7300751

Es importante tener en cuenta los siguientes puntos clave.

  • Esta no es una representación de la columna de la tabla de productos; ahora es una Mesa por derecho propio.
  • En este caso he materializado esta tabla “Tabla 3” para que la vean, pero no tiene por qué ser así. La función ALL () puede producir esto como una tabla virtual para usar dentro de otras fórmulas DAX. Cuando esto sucede, la mesa nunca se materializa (no se puede ver) pero es real y está ahí.
  • Cuando esta tabla se crea en tiempo de ejecución como una tabla virtual, conserva el linaje de la mesa de donde se originó. Debe pensar en esto como una tabla virtual que se genera dentro del modelo de datos y tiene una relación con la tabla de donde vino. Vea la imagen a continuación.

image_thumb-3-9498227

La imagen de arriba es una ilustración conceptual de lo que sucede bajo el capó cuando usa ALL () dentro de una fórmula (es decir, cuando la tabla no está materializada). Cuando la tabla no se materializa, la tabla ALL () (que se muestra como 1) se crea en la memoria en tiempo de ejecución, la tabla conserva una relación con la tabla desde la que se creó (la tabla Productos en este caso) y la nueva tabla (ilustrado conceptualmente como 1 arriba) también tiene una relación de uno a muchos con la tabla de donde vino (ilustrado conceptualmente como 2 arriba). La implicación es que puede usar esta tabla virtual ALL () en cualquier lugar donde pueda usar una tabla materializada en DAX, y esta tabla virtual se comportará exactamente como si fuera una parte permanente del modelo de datos (durante la vida de la fórmula). Una vez evaluada la fórmula, la tabla virtual desaparece sin dejar rastro * como si nunca hubiera existido.

* De hecho, puede dejar un rastro. En determinadas circunstancias, Power Pivot almacenará en caché los resultados de una fórmula y podrá acceder a ese caché para usarlo nuevamente en otros cálculos, pero los detalles detrás de esto están fuera del alcance de este artículo.

ALL () con varios parámetros de columna

La función TODOS también puede tener múltiples parámetros de columna Como se muestra abajo.

Prueba 4 = TODOS (Productos[Category], Productos[Color])

La fórmula Prueba 4 anterior produce la siguiente tabla.

image_thumb-4-1792600

La tabla anterior contiene todas las combinaciones de Categoría y Color que existen en la tabla Productos. Hay un par de puntos a tener en cuenta

  • Todas las columnas dentro de la función ALL () deben provenir de la misma tabla.
  • Si desea una tabla que contenga 9 de un total de 10 columnas, puede usar la función ALLEXCEPT () en su lugar.
  • También hay una función ALLSELECTED () que tiene un comportamiento ligeramente diferente (no se trata aquí).

Usando TODO () dentro de una medida

Hasta ahora siempre he mostrado copias materializadas de una función ALL () para que pueda «ver» lo que está sucediendo. Sin embargo, el uso más común de la función ALL () (en mi experiencia) es acceder a las filas totales en un objeto visual usando una tabla virtual ALL (). Tome la siguiente tabla dinámica como ejemplo.

image_thumb-5-3738401

Imagínese que quiero saber qué porcentaje de cada una de estas categorías de productos es de las ventas totales. En Excel, esto sería fácil. Escribiría una fórmula que apunte al Gran Total, algo como esto que se muestra a continuación.

image_thumb-6-5189470

Ahora no voy a entrar en los méritos de hacerlo de esta manera (la mayoría son malas), solo para decir que es “fácil” si quieres hacerlo de esta manera, incluso si es una mala práctica (IMO). En Power Pivot / Power BI, necesita un enfoque diferente. El truco para este problema es crear una medida que materialice el valor total general dentro de la tabla dinámica (o matriz) Como se muestra abajo.

image_thumb-7-5830257

Esta tabla es bastante fácil de producir usando la función ALL () como una tabla virtual dentro de CALCULATE de la siguiente manera.

Total de ventas de todos los productos = CALCULAR ([Total Sales],Todos los productos))

La forma en que funciona esta fórmula es la siguiente:

  • CALCULATE siempre ejecuta primero los parámetros de filtro. En este caso, el parámetro de filtro es la función de tabla ALL ()
  • ALL () devuelve una copia de la tabla Productos con los filtros del contexto de filtro actual eliminados. En este caso, las 4 filas de la tabla dinámica se filtran por productos[Category]. Tenga en cuenta que no hay filtrado en los Productos[Category] columna para la fila de Total general, por lo que esa fila siempre devuelve el total general.
  • CALCULAR toma la copia sin filtrar de la tabla Productos (es decir, con el filtro de la Tabla dinámica eliminado), aplica este filtro al modelo de datos (es decir, elimina el filtro) y luego evalúa [Total Sales]. Esta es la razón por la que la cantidad de $ 29 millones aparece en cada fila de la tabla dinámica para esta nueva medida, porque CALCULATE ha utilizado ALL () para eliminar los filtros que provienen de la tabla dinámica.

Con suerte, ahora puede ver por qué a veces llamamos a la función ALL () la función «Eliminar filtros» (cuando se usa con CALCULATE).

Ahora que puede «ver» las ventas totales y las ventas totales de todos los productos en la tabla dinámica anterior, es de esperar que ahora sea obvio cómo crear el% de las ventas totales para cada categoría de producto.

image_thumb-8-6269852

% de todas las ventas de productos = DIVIDIR ([Total Sales],[Total All Product Sales])

Espera, ¿qué pasa con el% del total general?

Ok, en este punto escuché a algunos de ustedes decir «¿qué tal si usas la función% del total general?» De las tablas dinámicas. Bueno, por supuesto que tiene razón, pero hay algunas razones por las que esta puede no ser la mejor opción.

  1. El% del gran total es un truco de visualización, no es parte del modelo de datos. Si crea otra tabla dinámica y necesita el número nuevamente, debe aplicar manualmente el truco de visualización nuevamente.
  2. No puede usar los resultados del truco de visualización del% del total general dentro de otra medida. Por ejemplo, imagínese si desea pagar una bonificación del 10% a todos los representantes de ventas que vendan el 5% de las ventas totales de Accesorios. Deberá poder acceder al% de accesorios como un número dentro de otra medida de DAX si desea hacer esto, y no puede hacerlo usando el truco de visualización del% del total general.

Cuidado con Power BI

Bien, ahora para el comportamiento inesperado de Power BI. Tome la siguiente tabla dinámica y fórmulas como ejemplo.

image_thumb-9-3320656

Month % of Full Year =
DIVIDE(
       [Total Sales],
       CALCULATE([Total Sales], ALL('Calendar'[MonthName]))
)

Algunas cosas a tener en cuenta sobre la tabla dinámica y la medida anteriores.

  1. Deliberadamente he escrito una medida más compleja agregando CALCUALTE (línea 4 arriba) en una sola función.
  2. La función TODOS en la línea 4 anterior elimina los filtros de la columna MonthName solamente, no de toda la tabla. Por esta razón, la medida se denomina “% mensual del año completo” y no “% del año completo”.
  3. La medida anterior y la tabla dinámica funcionan como esperaba.

Pero, ¿qué pasa con Power BI Desktop?

Bien, ahora, cuando replico exactamente la misma medida y visualización en Power BI Desktop, obtengo lo siguiente (en una matriz).

image_thumb-11-4499965

Imagínese mi consternación cuando vi esto por primera vez. Sabía que mi fórmula era correcta (es la misma que usé en Excel y publiqué arriba). Por una corazonada, decidí probar la siguiente medida en su lugar.

Month % of Full Year  =
DIVIDE(
       [Total Sales],
       CALCULATE(
                 [Total Sales],
                 ALL('Calendar'[MonthName], 'Calendar'[MonthNumberOfYear])
       )
)

Tenga en cuenta la adición de la columna MonthNumberOfYear dentro de mi función ALL () anterior. Esta nueva fórmula funciona en Power BI Desktop.

Entonces, como probablemente ya pueda adivinar, he usado la función «Ordenar columnas» de Power Pivot y Power BI Desktop para ordenar la columna Nombre del mes en el orden lógico del mes. Al utilizar Excel, no es necesario eliminar el filtro de esta «columna de clasificación» pero es necesario dentro de Power BI Desktop.

Seguramente esto es un error que dije

Estaba bastante seguro de que se trataba de un error, así que lo registré con el equipo de Power BI. Me sorprendió la respuesta que obtuve: «funciona según lo diseñado». Bueno, no estaba contento con esa respuesta, así que envié una llamada de socorro a Marco Russo y le pedí su opinión (Marco es el benchmark de lo que está bien y lo que está mal en el mundo de DAX, en mi opinión). Marco me explicó que «Ordenar por columna» no es en realidad parte del motor Power Pivot, sino que en realidad es parte de la capa de visualización. Dicho de otra manera, Excel administra «Ordenar por columna» dentro de Excel (no Power Pivot) y el motor de visualización hace lo mismo en Power BI Desktop (no el motor de modelado de datos). Cualquiera sea el motivo, no es fácil que la capa de visualización de Power BI funcione de la misma manera que en Excel y no hay ningún plan para cambiarla. Por supuesto, esto no es un problema mientras lo sepa y sepa cómo manejarlo, ahora lo sabe.

!function(f,b,e,v,n,t,s)
{if(f.fbq)return;n=f.fbq=function(){n.callMethod?
n.callMethod.apply(n,arguments):n.queue.push(arguments)};
if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version=’2.0′;
n.queue=[];t=b.createElement(e);t.async=!0;
t.src=v;s=b.getElementsByTagName(e)[0];
s.parentNode.insertBefore(t,s)}(window,document,’script’,
‘https://connect.facebook.net/en_US/fbevents.js’);
fbq(‘init’, ‘639916389503636’);
fbq(‘track’, ‘PageView’);

Suscribite a nuestro Newsletter

No te enviaremos correo SPAM. Lo odiamos tanto como tú.