Le contexte vous permet d’effectuer une analyse dynamique dans laquelle les résultats d’une formule peuvent changer pour refléter la sélection de ligne ou de cellule actuelle, ainsi que toutes les données associées. Il est très important de comprendre ce qu’est un contexte et de savoir l’utiliser à bon escient pour créer des formules et des analyses dynamiques performantes et pour résoudre les problèmes dans les formules.
Cette section définit les différents types de contextes : contexte de ligne, contexte de requête et contexte de filtre. Elle explique comment un contexte est évalué pour des formules dans des colonnes calculées et dans des tableaux croisés dynamiques.
La dernière partie de cet article fournit des liens vers des exemples détaillés qui illustrent comment les résultats des formules changent en fonction du contexte.
Présentation du contexte
Dans Power Pivot, les formules peuvent être affectées par les filtres appliqués dans un tableau croisé dynamique, par les relations entre les tables et par les filtres utilisés dans les formules. Le contexte est ce qui permet d’effectuer une analyse dynamique. Il est important de comprendre le contexte pour générer et résoudre les problèmes liés aux formules.
Il existe des types différents de contexte : contexte de ligne, contexte de requête et contexte de filtre.
Le contexte de ligne peut être considéré comme la « ligne actuelle ». Si vous avez créé une colonne calculée, le contexte de ligne représente les valeurs dans chaque ligne individuelle et celles dans les colonnes associées à la ligne actuelle. Certaines fonctions (EARLIER et EARLIEST) obtiennent également une valeur à partir de la ligne actuelle, puis utilisent cette valeur lors de l’exécution d’une opération sur une table entière.
Le contexte de requête fait référence au sous-ensemble de données créé implicitement pour chaque cellule dans un tableau croisé dynamique, selon les en-têtes de colonnes et de lignes.
Le contexte de filtre correspond au jeu de valeurs autorisées dans chaque colonne, selon les contraintes de filtre appliquées à la ligne ou définies par les expressions de filtre dans la formule.
Contexte de ligne
Si vous créez une formule dans une colonne calculée, le contexte de ligne correspondant à cette formule inclut les valeurs de toutes les colonnes dans la ligne actuelle. Si la table est associée à une autre table, le contenu inclut également toutes les valeurs de cette autre table qui sont mises en relation avec la ligne actuelle.
Par exemple, supposons que vous créez une colonne calculée, =[Freight] + [Tax],
qui additionne deux colonnes de la même table. Cette formule se comporte comme des formules dans un tableau Excel qui font automatiquement référence à des valeurs de la même ligne. Notez que les tables diffèrent des plages : vous ne pouvez pas référencer une valeur de la ligne avant la ligne actuelle à l’aide de la notation de plage, et vous ne pouvez pas référencer de valeur unique arbitraire dans une table ou cellule. Vous devez toujours travailler avec les tables et les colonnes.
Le contexte de ligne suit automatiquement les relations entre les tables pour déterminer les lignes des tables associées qui sont liées à la ligne actuelle.
Par exemple, la formule suivante utilise la fonction RELATED pour extraire une valeur de taxe d’une table associée, selon la région vers laquelle la commande a été expédiée. La valeur de taxe est déterminée par l’utilisation de la valeur de la région dans la table actuelle, la recherche de la région dans la table associée, puis l’obtention du taux d’imposition de cette région à partir de la table associée.
= [Freight] + RELATED('Region'[TaxRate])
Cette formule obtient simplement le taux d’imposition de la région actuelle à partir de la table Region. Vous n’avez pas besoin de connaître ou de spécifier la clé qui connecte les tables.
Contextes de ligne multiples
En outre, le langage DAX (Data Analysis Expressions) inclut des fonctions qui itèrent des calculs sur une table. Ces fonctions peuvent avoir plusieurs lignes actuelles et contextes de ligne actuelle. En termes de programmation, vous pouvez créer des formules qui parcourent de manière récursive une boucle interne et externe.
Par exemple, supposons que votre classeur contienne une table Products et une table Sales. Vous pouvez vouloir parcourir la totalité de la table Sales, remplie de transactions impliquant plusieurs produits, et rechercher la quantité maximale commandée pour chaque produit dans l’ensemble de ces transactions.
Dans Excel, ce calcul requiert une série de résumés intermédiaires, qui devraient être régénérés si les données changeaient. Si vous êtes un utilisateur chevronné d’Excel, vous pouvez être en mesure de générer des formules de tableau qui effectueraient cette opération. Une autre solution consisterait à écrire des sous-sélections imbriquées dans une base de données relationnelle.
Toutefois, avec DAX, vous pouvez générer une formule unique qui retourne la valeur correcte, et les résultats sont automatiquement mis à jour chaque fois que vous ajoutez des données aux tables.
=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
Pour obtenir une procédure pas à pas détaillée de cette formule, consultez l’article EARLIER.
En bref, la fonction EARLIER stocke le contexte de ligne de l’opération qui a précédé l’opération actuelle. Cette fonction stocke systématiquement en mémoire deux ensembles de contexte : le premier ensemble représente la ligne actuelle pour la boucle interne de la formule, et le second ensemble représente la ligne actuelle pour la boucle externe de la formule. DAX fournit automatiquement les valeurs entre les deux boucles pour vous permettre de créer des agrégats complexes.
Contexte de requête
Un contexte de requête fait référence au sous-ensemble de données qui est implicitement récupéré pour une formule. Lorsque vous déposez une mesure ou un autre champ de valeur dans une cellule d’un tableau croisé dynamique, le moteur de Power Pivot examine les en-têtes de ligne et de colonne, les segments et les filtres de rapport pour déterminer le contexte. Power Pivot effectue ensuite les calculs nécessaires pour remplir chaque cellule du tableau croisé dynamique. L’ensemble de données récupéré constitue le contexte de requête de chaque cellule.
Étant donné que le contexte peut changer en fonction de l’endroit où vous placez la formule, les résultats de cette dernière varient également selon que vous utilisez la formule dans un tableau croisé dynamique avec de nombreux regroupements et filtres, ou dans une colonne calculée dépourvue de filtres et présentant un contexte minimal.
Par exemple, supposons que vous créiez la formule simple ci-après qui additionne les valeurs de la colonne Profit de la table Sales : =SUM('Sales'[Profit]).
Si vous utilisez cette formule dans une colonne calculée de la table Sales, les résultats de la formule seront identiques pour la totalité de la table, car le contexte de requête de la formule correspond toujours à l’ensemble de données complet de la table Sales. Vos résultats présenteront les bénéfices de la totalité des régions, des produits, des années, etc.
Toutefois, plutôt que d’obtenir le même résultat des centaines de fois, vous préférez généralement obtenir le bénéfice d’une année, d’un pays, d’une région ou d’un produit spécifique, ou le bénéfice d’une combinaison quelconque de ces éléments, puis obtenir un total général.
Dans un tableau croisé dynamique, il est facile de modifier le contexte en ajoutant ou en supprimant des en-têtes de colonne et de ligne, et en ajoutant ou en supprimant des segments. Vous pouvez créer une formule comme celle ci-dessus, dans une mesure, puis la déposer dans un tableau croisé dynamique. Chaque fois que vous ajoutez des en-têtes de colonne ou de ligne au tableau croisé dynamique, vous modifiez le contexte de requête dans lequel la mesure est évaluée. Les opérations de segmentation et de filtrage affectent également le contexte. Par conséquent, la même formule, utilisée dans un tableau croisé dynamique, est évaluée dans un contexte de requête différent pour chaque cellule.
Contexte de filtre
Un contexte de filtre est ajouté lorsque vous spécifiez des contraintes de filtre sur l’ensemble des valeurs autorisées dans une colonne ou une table, en utilisant les arguments d’une formule. Un contexte de filtre s’applique au-dessus des autres contextes, tels qu’un contexte de ligne ou un contexte de requête.
Par exemple, un tableau croisé dynamique calcule les valeurs de chacune de ses cellules en fonction des en-têtes de ligne et de colonne, comme décrit dans la section précédente relative au contexte de requête. Toutefois, dans les mesures ou colonnes calculées que vous ajoutez au tableau croisé dynamique, vous pouvez spécifier des expressions de filtre pour contrôler les valeurs utilisées par la formule. Vous pouvez également supprimer les filtres de manière sélective sur des colonnes spécifiques.
Pour plus d’informations sur la création de filtres dans des formules, consultez fonctions de filtre.
Pour obtenir un exemple de la façon dont les filtres peuvent être effacés pour créer des totaux généraux, consultez l’article ALL.
Pour obtenir des exemples d’effacement sélectif et d’application de filtres dans des formules, consultez la fonction ALLEXCEPT.
Par conséquent, vous devez examiner la définition des mesures ou des formules utilisées dans un tableau croisé dynamique afin de connaître le contexte de filtre lors de l’interprétation des résultats des formules.
Détermination du contexte dans des formules
Lorsque vous créez une formule, Power Pivot pour Excel commence par vérifier la syntaxe générale, puis compare les noms des colonnes et des tables que vous spécifiez aux colonnes et aux tables possibles dans le contexte actuel. Si Power Pivot ne peut pas trouver les colonnes et les tables spécifiées par la formule, vous obtiendrez une erreur.
Le contexte est déterminé comme décrit dans les sections précédentes, à l’aide des tables disponibles dans le classeur, des relations entre les tables et des filtres appliqués.
Par exemple, si vous venez d’importer des données dans une nouvelle table et que vous n’avez pas appliqué de filtres, l’ensemble entier de colonnes dans la table fait partie du contexte actuel. Si vous disposez de plusieurs tables liées par des relations et que vous travaillez dans un tableau croisé dynamique qui a été filtré par l’ajout d’en-têtes de colonne et l’utilisation de segments, le contexte inclut les tables associées et tous les filtres sur les données.
Le contexte est un concept puissant qui peut également compliquer la résolution des problèmes liés aux formules. Nous vous recommandons de commencer par des formules et relations simples de façon à comprendre comment fonctionne le contexte, puis d’expérimenter ces formules simples dans des tableaux croisés dynamiques. La section suivante fournit également quelques exemples d’utilisation de différents types de contextes par les formules pour un retour dynamique des résultats.
Exemples de contexte dans les formules
-
La fonction RELATED développe le contexte de la ligne actuelle pour inclure des valeurs dans une colonne associée. Cela vous permet d’effectuer des recherches. L’exemple utilisé dans cette rubrique illustre l’interaction du filtrage et du contexte de ligne.
-
La fonction FILTER vous permet de spécifier les lignes à inclure dans le contexte actuel. Les exemples utilisés dans cette rubrique illustrent également la façon d’incorporer des filtres dans d’autres fonctions qui effectuent des agrégations.
-
La fonction ALL définit le contexte dans une formule. Vous pouvez l’utiliser pour remplacer les filtres qui sont appliqués comme résultat du contexte de requête.
-
La fonction ALLEXCEPT vous permet de supprimer tous les filtres à l’exception d’un filtre que vous spécifiez. Les deux rubriques incluent des exemples qui vous guident pas à pas dans la création de formules et l’analyse de contextes complexes.
-
Les fonctions EARLIER et EARLIEST vous permettent d’effectuer une boucle sur des tables en effectuant des calculs, tout en référençant une valeur issue d’une boucle interne. Si vous êtes familier des concepts de récursivité et de boucles internes et externes, vous apprécierez la puissance des fonctions EARLIER et EARLIEST. Si ces concepts vous sont étrangers, suivez scrupuleusement les différentes étapes de l’exemple pour comprendre la façon dont les contextes internes et externes sont utilisés dans les calculs.
Intégrité référentielle
Cette section aborde des concepts avancés relatifs aux valeurs manquantes dans des tables Power Pivot connectées par des relations. Cette section peut vous être utile si vous avez créé des classeurs avec plusieurs tables et des formules complexes, et souhaitez de l’aide pour mieux comprendre les résultats.
Si les concepts relatifs aux données relationnelles ne vous sont pas familiers, nous vous recommandons de lire la rubrique d’introduction Vue d’ensemble des relations.
Intégrité référentielle et relations Power Pivot
Power Pivot ne requiert pas l’application stricte d’une intégrité référentielle entre deux tables pour définir une relation valide. Au lieu de cela, une ligne vide est créée sur l’extrémité « un » de chaque relation un-à-plusieurs et est utilisée pour gérer toutes les lignes sans correspondance de la table associée. Elle se comporte en réalité comme une jointure externe SQL.
Dans les tableaux croisés dynamiques, si vous regroupez les données selon le côté « un » de la relation, toutes les données non appariées sur le côté « plusieurs » de la relation sont regroupées et seront incluses dans les totaux avec un en-tête de ligne vide. Le titre vierge est à peu près équivalent au « membre inconnu ».
Présentation du membre inconnu
Vous connaissez peut-être le concept du membre inconnu si vous avez travaillé avec des systèmes de bases de données multidimensionnelles, tels que SQL Server Analysis Services. Si ce terme ne vous est pas familier, l’exemple suivant explique la définition du membre inconnu, ainsi que la manière dont il affecte les calculs.
Supposons que vous créez un calcul qui additionne les ventes mensuelles pour chaque magasin, mais qu’une colonne de la table Sales ne contient pas de valeur pour le nom du magasin. Étant donné que les tables pour Store et Sales sont connectées par le nom du magasin, qu’attendez-vous dans la formule ? Comment le tableau croisé dynamique doit-il regrouper ou afficher les chiffres de vente qui ne sont pas associés à un magasin existant ?
Ce problème est courant dans les entrepôts de données, dans lesquels les grandes tables de données de faits doivent être mises logiquement en rapport avec les tables de dimension qui contiennent des informations concernant des magasins, des régions et d’autres attributs utilisés pour la catégorisation et le calcul de faits. Pour résoudre le problème, tous les nouveaux faits qui ne sont pas liés à une entité existante sont affectés temporairement au membre inconnu. C’est pourquoi les faits non liés apparaîtront groupés dans un tableau croisé dynamique sous un titre vierge.
Traitement de valeurs vides par rapport à celui des lignes vides
Les valeurs vides sont différentes des lignes vides ajoutées pour tenir compte du membre inconnu. La valeur vide est une valeur spéciale utilisée pour représenter les valeurs Null, les chaînes vides et d’autres valeurs manquantes. Pour plus d’informations sur la valeur vide, ainsi que sur d’autres types de données DAX, consultez Types de données dans les modèles de données.
