Relaciones y búsquedas en las fórmulas

Una de las características más sólidas de PowerPivot para Excel es la capacidad de crear relaciones entre las tablas y, a continuación, usar las tablas relacionadas para buscar o filtrar los datos relacionados. Puede recuperar valores relacionados de tablas usando el lenguaje de fórmulas Expresiones de análisis de datos (DAX), proporcionado con PowerPivot para Excel. DAX utiliza un modelo relacional y, por consiguiente, puede recuperar con facilidad y precisión los valores relacionados o correspondientes de otra tabla o columna.

Puede crear fórmulas que realicen búsquedas como parte de una columna calculada o como parte de una medida para usar en una tabla dinámica o un gráfico dinámico. Para obtener más información, vea los siguientes temas:

Medidas en PowerPivot

Columnas calculadas

En esta sección se describen las funciones de DAX que se proporcionan para la búsqueda, junto con algunos ejemplos de cómo usarlas.

[!NOTA]

En función del tipo de operación o fórmula de búsqueda que desee usar, podría tener que crear una relación entre las tablas en primer lugar. Para obtener información acerca de cómo crear relaciones, vea Relaciones entre tablas.

Descripción de las funciones de búsqueda

La capacidad de buscar datos coincidentes o relacionados de otra tabla resulta muy útil en situaciones donde la tabla actual solo tiene un identificador de algún tipo, pero los datos que necesita (como el precio del producto, el nombre u otros valores detallados) se almacenan en una tabla relacionada. También es útil cuando hay varias filas en otra tabla que están relacionadas con la fila o el valor actual. Por ejemplo, puede recuperar con facilidad todas las ventas correspondientes a una región, almacén o representante determinado.

A diferencia de las funciones de búsqueda de Excel como VLOOKUP, que se basan en matrices, o LOOKUP, que obtiene el primero de varios valores coincidentes, DAX sigue las relaciones existentes entre las tablas unidas por claves para obtener el valor relacionado único que coincide exactamente. DAX también puede recuperar una tabla de registros relacionados con el registro actual.

[!NOTA]

Si está familiarizado con las bases de datos relacionales, puede considerar que las búsquedas en PowerPivot son similares a la instrucción de subselección en Transact-SQL.

Para obtener más información acerca del modelo relacional usado en PowerPivot, vea Información general de las relaciones.

Recuperar un valor relacionado único

La función RELATED devuelve un único valor de otra tabla que está relacionada con el valor actual en la tabla actual. Debe especificar la columna que contiene los datos que desea y la función sigue las relaciones existentes entre las tablas para capturar el valor de la columna especificada en la tabla relacionada. En algunos casos, la función debe seguir una cadena de relaciones para recuperar los datos.

Por ejemplo, suponga tiene en Excel una lista en de los envíos de hoy. Sin embargo, la lista contiene solo el número de identificador de empleado, el del identificador de pedido y el del identificador de expedidor, por lo que el informe resulta difícil de leer. Para obtener la información adicional que desea, puede convertir esa lista en una tabla vinculada PowerPivot y, a continuación, crear las relaciones a las tablas de Employee y Reseller, haciendo coincidir el campo EmployeeID con el EmployeeKey, y ResellerID con el campo ResellerKey.

Para mostrar la información de búsqueda en su tabla vinculada, agregue dos nuevas columnas calculadas con las siguientes fórmulas:

= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])

Antes de la búsqueda

Después de la búsqueda

Order ID

EmployeeID

ResellerID

100314

230

445

100315

15

445

100316

76

108

EmployeeID

Employee

Reseller

230

Kuppa Vamsi

Modular Cycle Systems

15

Pilar Ackeman

Modular Cycle Systems

76

Kim Ralls

Associated Bikes

Order ID

EmployeeID

ResellerID

Employee

Reseller

100314

230

445

Kuppa Vamsi

Modular Cycle Systems

100315

15

445

Pilar Ackeman

Modular Cycle Systems

100316

76

108

Kim Ralls

Associated Bikes

La función utiliza las relaciones entre la tabla vinculada y las tablas de Employees y Resellers para obtener el nombre correcto de cada fila del informe. También puede usar los valores relacionados para realizar cálculos. Para obtener más información y más ejemplos, vea Función RELATED.

Recuperar una lista de valores relacionados

La función RELATEDTABLE sigue una relación existente y devuelve una tabla que contiene todas las filas coincidentes de la tabla especificada. Por ejemplo, suponga que desea averiguar cuántos pedidos ha hecho este año cada distribuidor. Podría crear una nueva columna calculada en la tabla Resellers que incluya la fórmula siguiente, que busque registros de cada distribuidor en la tabla ResellerSales_USD y cuente el número de pedidos individuales hechos por cada distribuidor. Estas tablas forman parte del libro de ejemplos de DAX. Para obtener más información acerca de los datos de ejemplo, vea Obtener datos de muestra para PowerPivot.

=COUNTROWS(RELATEDTABLE(ResellerSales_USD))

En esta fórmula, la función RELATEDTABLE primero obtiene primero el valor de ResellerKey por cada fila de la tabla actual. (No necesita especificar la columna de identificador en ningún lugar de la fórmula, porque PowerPivot utiliza la relación existente entre las tablas). A continuación, la función RELATEDTABLE obtiene y cuenta todas las filas de la tabla ResellerSales_USD relacionadas con cada revendedor. Tenga en cuenta que si no existe relación (directa o indirecta) entre ambas tablas, obtendrá todas las filas de la tabla ResellerSales_USD.

Para el distribuidor Modular Ciclo Systems de nuestra base de datos de ejemplo, hay cuatro pedidos en la tabla de ventas, por lo que la función devuelve 4. En el caso de Associated Bikes, el revendedor tiene, por lo que la función devuelve un espacio en blanco.

Reseller

Registros en la tabla de ventas para este distribuidor

Modular Cycle Systems

Reseller ID

SalesOrderNumber

445

SO53494

445

SO71872

445

SO65233

445

SO59000

Associated Bikes

  

[!NOTA]

Dado que la función RELATEDTABLE devuelve una tabla y no un valor único, se debe usar como argumento para una función que realiza operaciones en tablas. Para obtener más información, vea Función RELATEDTABLE.

Vea también

Conceptos

Agregar cálculos a los informes, a los gráficos y a las tablas dinámicas

Crear fórmulas para cálculos

Información general sobre expresiones de análisis de datos (DAX)

Agregaciones en fórmulas

Otros recursos

Relaciones entre tablas