Power Pivot, Création de Formules DAX
Dans cet article, nous allons aborder les bases de la création de formules de calcul pour les colonnes calculées et les mesures dans Power Pivot. S’il s’agit d’une nouveauté de DAX, n’hésitez pas à consulter l’article démarrage rapide : découvrir les fondamentaux de Dax en 30 minutes.
Notions de base des formules
Power Pivot fournit des expressions DAX (Data Analysis Expressions) pour créer des calculs personnalisés dans Power Pivot tableaux et dans des tableaux croisés dynamiques Excel. DAX inclut certaines fonctions utilisées dans les formules Excel et des fonctions supplémentaires conçues pour fonctionner avec les données relationnelles et effectuer une agrégation dynamique.
Voici quelques formules de base qui peuvent être utilisées dans une colonne calculée :
| Formule | Description |
|---|---|
=TODAY() |
Insère la date du jour dans chaque ligne de la colonne. |
=3 |
Insère la valeur 3 dans chaque ligne de la colonne. |
=[Column1] + [Column2] |
Additionne les valeurs dans la même ligne de [Colonne1] et [Colonne2] et place les résultats dans la même ligne de la colonne calculée. |
Vous pouvez créer des formules Power Pivot pour des colonnes calculées de la même façon que vous créez des formules dans Microsoft Excel.
Utilisez les étapes suivantes lorsque vous créez une formule :
- Chaque formule doit commencer par un signe égal.
- Vous pouvez taper ou sélectionner un nom de fonction ou taper une expression.
- Tapez les premières lettres de la fonction ou du nom de votre choix, et la saisie semi-automatique affiche une liste de fonctions, de tableaux et de colonnes disponibles. Appuyez sur la touche TAB pour ajouter un élément de la liste de saisie semi-automatique à la formule.
- Cliquez sur le bouton FX pour afficher une liste des fonctions disponibles. Pour sélectionner une fonction dans la liste déroulante, utilisez les touches de direction pour mettre en surbrillance l’élément, puis cliquez sur OK pour ajouter la fonction à la formule.
- Fournissez les arguments de la fonction en les sélectionnant dans une liste déroulante de tables et colonnes possibles ou en tapant des valeurs ou une autre fonction.
- Recherchez les erreurs de syntaxe : Vérifiez que toutes les parenthèses sont fermées, et que les colonnes, tables et valeurs sont correctement référencées.
- Appuyez sur Entrée pour accepter la formule.
Remarque : Dans une colonne calculée, dès que vous acceptez la formule, la colonne contient des valeurs. Dans une mesure, appuyer sur entrée enregistre la définition de la mesure.
Créer une formule simple
Pour créer une colonne calculée avec une formule simple
Les valeurs sont ensuite renseignées dans la nouvelle colonne calculée pour toutes les lignes. |
Conseils pour l’utilisation de la saisie semi-automatique
- Vous pouvez utiliser la saisie semi-automatique des formules au milieu d’une formule existante avec les fonctions imbriquées. Le texte immédiatement avant le point d’insertion est utilisé pour afficher des valeurs dans la liste déroulante, et tout le texte après le point d’insertion reste inchangé.
- Power Pivot n’ajoute pas la parenthèse fermante des fonctions, ni ne met automatiquement en correspondance les parenthèses. Vous devez vous assurer que chaque fonction est correctement syntaxique ou que vous ne pouvez pas enregistrer ou utiliser la formule. Power Pivot met en surbrillance les parenthèses, ce qui permet de vérifier plus facilement s’il est correctement fermé.
Utilisation de tables et de colonnes
les tables dePower Pivot ressemblent aux tableaux Excel, mais différentes dans la manière dont elles fonctionnent avec les données et les formules :
- Les formules dans Power Pivot fonctionnent uniquement avec les tables et les colonnes, et non avec les cellules individuelles, les références de plage ou les tableaux.
- Les formules peuvent utiliser des relations pour obtenir les valeurs des tables associées. Les valeurs récupérées sont toujours associées à la valeur de la ligne actuelle.
- Vous ne pouvez pas coller de formules Power Pivot dans une feuille de calcul Excel et vice versa.
- Vous ne pouvez pas avoir de données irrégulières ou irrégulières, comme dans une feuille de calcul Excel. Chaque ligne d’un tableau doit contenir le même nombre de colonnes. Toutefois, certaines colonnes peuvent contenir des valeurs vides. Les tables de données Excel et Power Pivot tables de données ne sont pas interchangeables, mais vous pouvez créer des liens vers des tableaux Excel à partir de Power Pivot et coller des données Excel dans Power Pivot. Pour plus d’informations, reportez-vous à ajouter des données de feuille de calcul à un modèle de données à l’aide d’une table liée et copier et coller des lignes dans un modèle de données dans Power Pivot.
Référence aux tables et aux colonnes dans les formules et les expressions
Vous pouvez faire référence à une table et une colonne en utilisant son nom. Par exemple, la formule suivante montre comment faire référence aux colonnes de deux tables à l’aide du nom complet :
= SOMME (‘nouvelles ventes’ [montant]) + somme (‘ventes passées’ [montant])
Lors de l’évaluation d’une formule, Power Pivot vérifie d’abord la syntaxe générale, puis vérifie les noms de colonnes et de tables que vous fournissez aux colonnes et tables possibles dans le contexte actuel. Si le nom est ambigu ou s’il est introuvable, vous obtiendrez une erreur dans votre formule (une chaîne de #ERROR au lieu de la valeur de données dans les cellules où l’erreur se produit). Pour plus d’informations sur la configuration requise pour les tables, les colonnes et d’autres objets, voir «conventions d’appellation dans la spécification de syntaxe DAX pour Power Pivot.
Remarque : Le contexte est une fonctionnalité importante de Power Pivot de modèles de données qui vous permet de créer des formules dynamiques. Le contexte est déterminé par les tables dans le modèle de données, les relations entre les tables et les filtres appliqués. Pour plus d’informations, consultez Contexte dans les formules DAX.
Relations entre les tables
Les tables peuvent être associées à d’autres tables. En créant des relations, vous pouvez trouver des données dans une autre table et utiliser des valeurs associées pour effectuer des calculs complexes. Par exemple, vous pouvez utiliser une colonne calculée pour chercher tous les enregistrements d’expédition liés au revendeur actuel, puis additionner les frais d’expédition pour chacun d’eux. L’effet est semblable à une requête paramétrée : vous pouvez calculer une somme différente pour chaque ligne de la table actuelle.
De nombreuses fonctions DAX requièrent qu’il existe une relation entre les tables ou entre plusieurs tables, afin de localiser les colonnes que vous avez référencées et de renvoyer les résultats logiquement. D’autres fonctions essaient d’identifier la relation ; Néanmoins, pour de meilleurs résultats, vous devez toujours créer une relation dans la mesure du possible.
Lorsque vous travaillez avec des tableaux croisés dynamiques, il est particulièrement important que vous connectiez toutes les tables utilisées dans le tableau croisé dynamique de sorte que les données de synthèse puissent être calculées correctement. Pour plus d’informations, consultez Utilisation des relations dans les tableaux croisés dynamiques.
Résolution des erreurs dans les formules
Si vous obtenez une erreur lorsque vous définissez une colonne calculée, la formule peut contenir une erreur syntaxique ou une erreur sémantique.
Les erreurs syntaxiques sont les plus faciles à résoudre. Elles impliquent en général une virgule ou une parenthèse manquante. Pour obtenir de l’aide sur la syntaxe des fonctions individuelles, voir référence des fonctions Dax.
L’autre type d’erreur se produit lorsque la syntaxe est correcte, mais que la valeur ou la colonne référencée n’a pas de sens dans le contexte de la formule. Ces erreurs sémantiques peuvent être causées par l’un des problèmes suivants :
- La formule fait référence à une colonne, une table ou une fonction non existante.
- La formule semble être correcte, mais lorsque le Power Pivot extrait les données, il trouve une incompatibilité de type et génère une erreur.
- La formule passe un nombre ou un type de paramètres incorrect à une fonction.
- La formule fait référence à une colonne différente qui comporte une erreur, et par conséquent, ses valeurs ne sont pas valides.
- La formule fait référence à une colonne qui n’a pas été traitée. Cela peut se produire si vous avez changé le classeur en mode manuel, effectué des modifications, puis jamais actualisé les données ou mis à jour les calculs.
Dans les quatre premiers cas, DAX signale la colonne entière qui contient la formule non valide. Dans le
