Existen una serie de funciones en DAX que se utilizan para retornar una tabla en lugar de una expresión escalar. Este tipo de funciones son muy útiles si se quiere iterar primero por una tabla definida, filtrada o toda la tabla. Por ejemplo:

Ventas := SUMX( Ventas, Ventas[Quantity] * Ventas[Net Price]

En este ejemplo se puede observar que la función SUMX itera sobre la tabla Ventas. Aunque el resultado es un escalar, para realizar el cálculo la función recorre toda la tabla Ventas durante la computación del cálculo. Código parecido se puede utilizar pero en este caso filtrando la tabla para iterar sobre un volumen de datos más pequeño.

Ventas Items Multiples := 
     SUMX (
          FILTER (
              Ventas,
              Ventas[Quantity] > 1
          ),
       Ventas[Quantity] * Ventas[Net Price]
) 

En este caso FILTER retorna una tabla filtrada cuya cantidad es Mayor que 1. Basada en esa condición se realiza el cálculo y se retorna un resultado.

Es importante decir que el código anterior no es el más optimo para obtener el resultado. Seguiremos introduciendo apuntes para mejorar y optimizar código DAX de tal forma que no tengamos problemas de rendimiento.

Si has utilizado DAX sabrás que se pueden usar variables tanto para guardar valores simples como para guardar tablas, el código anterior se podría escribir de la siguiente forma:

Ventas Items Multiples := 
   VAR VentasItemsMultiples = FILTER ( Ventas, Ventas[Quantity] > 1 )
     SUMX (
          VentasItemsMultiples,
          Ventas[Quantity] * Ventas[Net Price]
     ) 

La variable VentasItemsMultiples es una variable que guarda la tabla entera retornada por la función FILTER, recomiendo usar variables en todo momento para hacer el código más simple, es una forma fácil de documentar tu código.

Dentro de una columna calculada o en una iteración, también se puede utilizar la función RELATEDTABLE para obtener una tabla con todas las filas relacionadas a esta tabla. Por ejemplo, dentro de la tabla Producto, podemos utilizar el siguiente código para computar las ventas de todos los productos correspondientes:

'Product[Product Sales Amount] = 
SUMX (
      RELATEDTABLE ( Sales ),
      Sales[Quantity] * Sales[Unit Price]
)

Asimismo, las funciones de tabla se pueden anidar. El siguiente código calcula las ventas de los productos relacionados con la tabla Sales cuya cantidad es mayor que 1.

'Product[Product Sales Amount Multiple Items] = 
SUMX (
  FILTER (
      RELATEDTABLE ( Sales ),
      Quantity > 1
       ),
      Sales[Quantity] * Sales[Unit Price]
)

En este caso RELATEDTABLE está dentro de FILTER, como regla DAX evalua primero las funciones más interiores y después las más exteriores.

Este comportamiento no es el mismo que con las funciones CALCULATE y CALCULATETABLE como veremos en próximos artículos que voy a publicar.

Función FILTER DAX a fondo

Para empezar a comprender las funciones de tabla vamos a utilizar la función FILTER.

FILTER ( < table >, < condition > )

FILTER recibe una tabla y una condición lógica como parámetros. El resultado que retorna es una tabla con todas las filas que evalúan a TRUE la condición.

FILTER recibe una tabla y una condición lógica como parámetros. El resultado que retorna es una tabla con todas las filas que evalúan a TRUE la condición. Recorre la tabla completa fila a fila evaluando cada fila con la condición. Es decir, itera toda la tabla.

La función FILTER se suele utilizar para reducir el número de filas de las iteraciones. Por ejemplo, las ventas de productos de color rojo se podría expresar de la siguiente forma:

RedSales := 
SUMX (
     FILTER (
            Sales,
            RELATED ( 'Product'[Color] ) = "Red"
            ),
     Sales[Quantity] * Sales[Net Price]
  ) 

Asímismo, la función FILTER DAX, se puede anidar dentro de otras funciones DAX o combinar este anidamineto con una función AND. Por ejemplo, las siguientes dos medidas producen el mismo resultado.

FabrikamHighMarginProducts = 
  FILTER (
    FILTER (
         'Product',
         'Product'[Brand] = "Fabrikam"
        ),
      'Product'[Unit Price] > 'Product'[Unit Cost] * 3
 )
FabrikamHighMarginProducts = 
  FILTER (
    FILTER (
         'Product',
         AND('Product'[Brand] = "Fabrikam",'Product'[Unit Price] > 'Product'[Unit Cost] * 3)
        )
 )

Aunque el resultado es el mismo, cuando utilicemos tablas grandes el rendimiento de la opción con primera con FILTER anidados es peor que la opción segunda ya que se realizará primero la iteración más interna y después la iteración externa. Si se opta por la opción anidada, habrá que tener en cuenta la selectividad del filtro más interno, cuanto más selectivo mejor para el rendimiento de la consulta.

Por ejemplo, si hay pocos productos Fabrikam sería recomendable poner el filtro interno con Brand = «Fabrikam».

FabrikamHighMarginProducts = 
  FILTER (
    FILTER (
         'Product',
         'Product'[Brand] = "Fabrikam"
        ),
      'Product'[Unit Price] > 'Product'[Unit Cost] * 3
 )

Usar la función FILTER, provoca que los desarrolladores puedan tener código mucho m´ás fácil de leer y mantener. Por ejemplo, supongamos que quieres sumar el número de clientes de color rojo, si no usamos la función FILTER DAX sería de la siguiente forma.

NumOfRedProducts := 
   SUMX ( 
       'Product',
       IF ( 'Product'[Color] = "Red",1,0 )
     ) 

Aunque el código funciona, es un poco tricky. Una implementación mucho más optima y que produce que el optimizador genere un plan de ejecución mejor ya que el optimizador puede detectar lo que quiere el desarrollador, es la siguiente:

NumOfRedProducts := 
   COUNTROWS ( 
       FILTER ('Products', 'Product'[color] = "Red")
     )