Relations et recherches dans les formules
L'une des fonctionnalités les plus puissantes de PowerPivot pour Excel est la possibilité de créer des relations entre les tables, puis d'utiliser les tables associées pour rechercher ou filtrer des données connexes. Vous pouvez récupérer les valeurs associées à partir des tables en utilisant le langage de formule fourni avec PowerPivot pour Excel, Data Analysis Expressions (DAX). Le langage DAX utilise un modèle relationnel et permet, par conséquent, de récupérer facilement et précisément des valeurs associées ou correspondantes dans une autre table ou colonne.
Vous pouvez créer des formules qui effectuent des recherches dans le cadre d'une colonne calculée ou dans le cadre d'une mesure à utiliser dans un tableau croisé dynamique ou un graphique croisé dynamique. Pour plus d'informations, consultez les rubriques suivantes :
Cette section décrit les fonctions DAX fournies pour la recherche, avec quelques exemples sur la façon d'utiliser ces fonctions.
[!REMARQUE]
Selon le type d'opération de recherche ou de formule de recherche que vous souhaitez utiliser, vous devrez peut-être d'abord créer une relation entre les tables. Pour plus d'informations sur la création de relations, consultez Relations entre tables.
Fonctionnement des fonctions de recherche
La possibilité de rechercher des données connexes ou correspondantes dans une autre table est particulièrement utile dans les situations où la table actuelle comporte uniquement un identificateur d'un certain genre, mais que les données dont vous avez besoin (telles que le prix du produit, le nom ou toute autre valeur détaillée) sont stockées dans une table associée. Elle est également utile lorsque plusieurs lignes d'une autre table sont associées à la ligne ou valeur actuelle. Par exemple, vous pouvez facilement récupérer toutes les ventes liées à une région, un magasin ou un vendeur particulier.
Contrairement aux fonctions de recherche Excel telles que VLOOKUP, qui sont basées sur des tableaux, ou LOOKUP qui obtient la première des différentes valeurs correspondantes, DAX suit les relations qui existent entre les tables jointes par clés afin d'obtenir la valeur associée unique qui correspond exactement. DAX peut également récupérer une table d'enregistrements associés à l'enregistrement actif.
[!REMARQUE]
Si vous connaissez les bases de données relationnelles, vous pouvez vous représenter les recherches dans PowerPivot comme une instruction de sous-sélection imbriquée dans Transact-SQL.
Pour plus d'informations sur le modèle relationnel utilisé dans PowerPivot, consultez Vue d'ensemble des relations.
Récupération d'une valeur associée unique
La fonction RELATED retourne une valeur unique à partir d'une autre table qui est associée à la valeur actuelle dans la table actuelle. Vous spécifiez la colonne qui contient les données que vous souhaitez et la fonction suit les relations existantes entre les tables pour récupérer la valeur de la colonne spécifiée dans la table associée. Dans certains cas, la fonction doit suivre une chaîne de relations pour extraire les données.
Supposons par exemple que vous ayez une liste des livraisons du jour dans Excel. Cette liste ne contient toutefois que l'ID d'employé, l'ID de commande et l'ID d'expéditeur, ce qui rend le rapport difficile à lire. Pour obtenir les informations supplémentaires de votre choix, vous pouvez convertir cette liste en table liée PowerPivot, puis créer des relations avec les tables Employee et Reseller, en faisant correspondre EmployeeID au champ EmployeeKey et ResellerID au champ ResellerKey.
Pour afficher les informations de recherche dans votre table liée, vous devez ajouter deux nouvelles colonnes calculées, avec les formules suivantes :
= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])
Avant la recherche |
Après la recherche |
||||||||||||||||||||||||||||||||||||||||||||
|
|
La fonction utilise les relations entre la table liée et les tables Employees et Resellers pour obtenir le nom correct de chaque ligne dans le rapport. Vous pouvez également utiliser les valeurs associées pour des calculs. Pour plus d'informations et d'exemples, consultez Fonction RELATED.
Récupération d'une liste de valeurs associées
La fonction RELATEDTABLE suit une relation existante et retourne une table qui contient toutes les lignes correspondantes de la table spécifiée. Supposons par exemple que vous souhaitiez connaître le nombre de commandes placées cette année par chaque revendeur. Vous pouvez créer une nouvelle colonne calculée dans la table des revendeurs (Resellers) qui inclut la formule suivante, qui recherche des enregistrements pour chaque revendeur dans la table ResellerSales_USD et compte le nombre de commandes individuelles placées par chaque revendeur. Ces tables font partie de l'exemple de classeur DAX. Pour plus d'informations sur les exemples de données, consultez Obtenir des exemples de données pour PowerPivot.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
Dans cette formule, la fonction RELATEDTABLE obtient d'abord la valeur de ResellerKey pour chaque revendeur de la table actuelle. (Vous n'avez pas besoin de spécifier la colonne d'ID dans la formule puisque PowerPivot utilise la relation existante entre les tables). La fonction RELATEDTABLE obtient ensuite toutes les lignes de la table ResellerSales_USD associées à chaque revendeur et compte les lignes. Notez que s'il n'y a aucune relation (directe ou indirecte) entre les deux tables, vous obtenez toutes les lignes de la table ResellerSales_USD.
Pour le revendeur Modular Cycle Systems dans notre exemple de base de données, il y a quatre commandes dans la table des ventes ; la fonction retourne donc 4. Pour Associated Bikes, le revendeur n'a aucune vente ; la fonction retourne donc une valeur vide.
Reseller |
Enregistrements dans la table des ventes pour ce revendeur |
||||||||||
Modular Cycle Systems |
|
||||||||||
Associated Bikes |
|
[!REMARQUE]
Dans la mesure où la fonction RELATEDTABLE retourne une table et non une valeur unique, elle doit être utilisée comme argument pour une fonction qui effectue des opérations sur les tables. Pour plus d'informations, consultez Fonction RELATEDTABLE.
Voir aussi
Concepts
Ajouter des calculs à vos rapports, graphiques et tableaux croisés dynamiques
Générer des formules pour les calculs
Vue d'ensemble du langage DAX (Data Analysis Expressions)