Filtrar datos en las fórmulas
Puede crear filtros en las fórmulas para restringir los valores de los datos de origen que se usan en los cálculos. Para ello, se especifica una tabla como una entrada de la fórmula y se define una expresión de filtro. La expresión de filtro que proporcione se usa para consultar los datos y devuelve solo un subconjunto de los datos de origen. El filtro se aplica dinámicamente cada vez que actualiza los resultados de la fórmula, en función del contexto actual de sus datos. En esta sección se describe cómo crear filtros en las fórmulas DAX (Expresiones de análisis de datos).
Crear un filtro en una tabla usada en una fórmula
Puede aplicar filtros en fórmulas que usan una tabla como entrada. En lugar de escribir un nombre de tabla, use la función FILTER para definir un subconjunto de filas de la tabla especificada. Esa subconjunto se pasa a otra función para efectuar operaciones como, por ejemplo, agregaciones personalizadas.
Por ejemplo, suponga que tiene una tabla de datos que contiene información de pedidos de los distribuidores y desea calcular cuánto vendió cada uno. Sin embargo, desea mostrar la cantidad de ventas solo para los distribuidores que vendieron varias unidades de sus productos mayor valor. La fórmula siguiente, basada en un libro de ejemplo de DAX, muestra un ejemplo de cómo puede crear este cálculo con un filtro:
=SUMX(
FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Quantity] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
La primera parte de la fórmula especifica unas de las funciones de agregación de PowerPivot, que tiene una tabla como argumento. SUMX calcula una suma de una tabla.
La segunda parte de la fórmula, FILTER(table, expression), le indica a SUMX los datos que debe usar. SUMX requiere una tabla o una expresión que dé una tabla como resultado. Aquí, en lugar de utilizar todos los datos de una tabla, se usa la función FILTER para especificar las filas de la tabla que se usan.
La expresión de filtro tiene dos partes: la primera indica el nombre de la tabla a la que se aplicará el filtro. La segunda parte define una expresión que se usará como condición de filtro. En este caso, va a filtrar los distribuidores que vendieron más de cinco unidades y los productos que costaron más de 100 $. El operador, &&, es un operador lógico AND, que indica que ambas partes de la condición deben cumplirse para que la fila pertenezca al subconjunto filtrado.
La tercera parte de la fórmula indica a la función SUMX que se deben sumar los valores. En este caso solo va a usar la cantidad de ventas.
Tenga en cuenta que funciones como FILTER, que devuelven una tabla, nunca devuelve directamente la tabla o filas al libro de PowerPivot, pero están siempre incrustadas en otra función. Para obtener más información acerca de FILTer y de otras funciones que se usan para filtrar, incluidos más ejemplos, vea Funciones de filtro (DAX).
[!NOTA]
El contexto en el que se utiliza la expresión de filtro le afecta también. Por ejemplo, si usa un filtro en una medida y la medida se usa en una tabla dinámica o un gráfico dinámico, el subconjunto de datos que se devuelve se puede ver afectado por los filtros o segmentaciones adicionales que el usuario haya aplicado a la tabla dinámica. Para obtener más información acerca del contexto, vea Contexto de las fórmulas DAX.
Filtros que quitan duplicados
Además de filtrar por valores específicos, puede devolver un conjunto único de valores de otra tabla o columna. Esto puede resultar útil si desea contar el número de valores únicos de una columna o usar una lista de valores único para otras operaciones. DAX proporciona dos funciones para devolver valores distintivos: Función DISTINCT y Función VALUES.
La función DISTINCT examina una única columna que especifique como argumento para la función y devuelve una nueva columna que solo contiene los valores distintivos.
La función VALUES también devuelve una lista de valores únicos, así como el miembro Unknown. Esto resulta útil cuando se usan valores de dos tablas que están unidas por una relación y falta un valor en una tabla que se encuentra en la otra. Para obtener más información acerca de la administración del miembro Unknown, vea Contexto de las fórmulas DAX.
Ambas funciones devuelven una columna completa de valores; por consiguiente, las funciones se usan para obtener una lista de valores que se pasa a continuación a otra función. Por ejemplo, podría utilizar la siguiente fórmula para obtener una lista de los distintos productos que ha vendido un distribuidor determinado, utilizando la clave del producto única y, a continuación, contabilizar los productos de esa lista utilizando la función COUNTROWS:
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
Cómo afecta el contexto a los filtros
Al agregar una fórmula DAX a una tabla dinámica o gráfico dinámico, el contexto puede afectar a los resultados de la fórmula. Si está trabajando en una tabla de PowerPivot, el contexto es la fila actual y sus valores. Si está trabajando en una tabla dinámica o un gráfico dinámico, el contexto es el conjunto o subconjunto de datos que se definen mediante operaciones como segmentación o filtrado. El diseño de la tabla dinámica o del gráfico dinámico también impone su propio contexto. Por ejemplo, si crea una tabla dinámica que agrupe ventas por región y año, en la tabla dinámica solo aparecen los datos que se aplican a esas regiones y años. Por consiguiente, cualquier medida que agregue a la tabla dinámica se calcula en el contexto de los encabezados de fila y columna además de los filtros en la fórmula de la medida.
Para obtener más información, vea Contexto de las fórmulas DAX.
Quitar filtros
Al trabajar con fórmulas complejas, es recomendable saber exactamente cuáles son los filtros actuales o modificar el filtro que forma parte de la fórmula. DAX proporciona varias funciones que permiten quitar filtros y controlar las columnas que se conservan como parte del contexto de filtros actual. En esta sección se proporciona información general del modo en que estas funciones afectan a los resultados de una fórmula.
Invalidar todos los filtros con la función ALL
Puede usar la función ALL para invalidar los filtros aplicados anteriormente y devolver todas las filas de la tabla a la función que realiza la agregación u otra operación. Si utiliza una o más columnas, en lugar de una tabla, como argumentos a ALL, la función ALL devuelve a todas las filas y omite cualquier filtro de contexto.
[!NOTA]
Si conoce la terminología de bases de datos relacionales, puede considerar ALL como la función que genera la combinación externa izquierda natural de todas las tablas.
Por ejemplo, suponga que tiene las tablas Sales y Products, y desea crear una fórmula que calcule la suma de ventas del producto actual dividida entre las ventas de todos los productos. Debe tener en cuenta el hecho de que, si la fórmula se usa en una medida, el usuario de la tabla dinámica puede estar usando una segmentación para filtrar un determinado producto, con el nombre de producto de las filas. Por lo tanto, para obtener el verdadero valor del denominador independientemente de filtros o segmentaciones, debe agregar la función ALL para invalidar los filtros. La fórmula siguiente es un ejemplo de cómo usar ALL para invalidar los efectos de filtros anteriores:
=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))
La primera parte de la fórmula, SUM (Sales[Amount]), calcula el numerador.
La suma tiene en cuenta el contexto actual, lo que significa que si agrega la fórmula a una columna calculada, se aplica el contexto de la fila, pero si agrega la fórmula a una tabla dinámica como una medida, se aplican los filtros que están aplicados en la tabla dinámica (el contexto de filtros).
La segunda parte de la fórmula calcula el denominador. La función ALL invalida cualquier filtro que pueda haberse aplicado a la tabla Products.
Para obtener más información, incluidos ejemplos detallados, vea Función ALL.
Invalidar filtros específicos con la función ALLEXCEPT
La función ALLEXCEPT también invalida los filtros existentes, pero puede especificar que se conserven algunos de los filtros existentes. Las columnas que señale como argumentos para la función ALLEXCEPT especifican las columnas que continuarán filtradas. Si desea invalidar los filtros en la mayoría de las columnas pero no todos, ALLEXCEPT es más conveniente que ALL. La función ALLEXCEPT resulta muy útil cuando se crean tablas dinámicas que pueden estar filtradas por muchas columnas diferentes y desea controlar los valores que se usan en la fórmula. Para obtener más información, incluido un ejemplo detallado de cómo usar ALLEXCEPT en una tabla dinámica, vea Función ALLEXCEPT.