Dans cet article, nous allons examiner les principes de base de la création de formules de calcul pour les colonnes calculées et les mesures dans Power Pivot. Si vous débutez avec DAX, veillez à case activée démarrage rapide : Découvrir les principes de base de DAX en 30 minutes.
Concepts de base des formules
Power Pivot fournit des expressions DAX (Data Analysis Expressions) pour créer des calculs personnalisés dans des tables Power Pivot et des tableaux croisés dynamiques Excel. DAX inclut certaines des fonctions utilisées dans les formules Excel et des fonctions supplémentaires conçues pour fonctionner avec des 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 |
|
Insère la date du jour dans chaque ligne de la colonne. |
|
Insère la valeur 3 dans chaque ligne de la colonne. |
|
Ajoute les valeurs dans la même ligne de [Column1] et [Column2] et place les résultats dans la même ligne de la colonne calculée. |
Vous pouvez créer Power Pivot formules pour les colonnes calculées tout comme vous créez des formules dans Microsoft Excel.
Lorsque vous créez une formule, procédez comme suit :
-
Chaque formule doit commencer par un signe égal.
-
Vous pouvez taper ou sélectionner un nom de fonction, ou taper une expression.
-
Commencez à taper les premières lettres de la fonction ou du nom souhaité, et la saisie semi-automatique affiche une liste des fonctions, tables et colonnes disponibles. Appuyez sur TAB pour ajouter un élément de la liste Saisie semi-automatique à la formule.
-
Cliquez sur le bouton Fx pour afficher la liste des fonctions disponibles. Pour sélectionner une fonction dans la liste déroulante, utilisez les touches de direction pour mettre l’élément en surbrillance, puis cliquez sur OK pour ajouter la fonction à la formule.
-
Fournissez les arguments à 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.
-
Vérifiez les erreurs de syntaxe : vérifiez que toutes les parenthèses sont fermées et que les colonnes, les tables et les 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 est remplie avec des valeurs. Dans une mesure, appuyez sur ENTRÉE pour enregistrer la définition de la mesure.
Create 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 correcte syntaxiquement ou que vous ne pouvez pas enregistrer ou utiliser la formule. Power Pivot met en évidence les parenthèses, ce qui facilite la case activée si elles sont correctement fermées.
Utilisation de tables et de colonnes
Power Pivot tableaux ressemblent aux tableaux Excel, mais ils sont différents dans la façon dont ils fonctionnent avec les données et les formules :
-
Les formules dans Power Pivot fonctionnent uniquement avec des tables et des colonnes, et non avec des cellules individuelles, des références de plage ou des tableaux.
-
Les formules peuvent utiliser des relations pour obtenir des valeurs à partir de tables associées. Les valeurs récupérées sont toujours liées à la valeur de ligne actuelle.
-
Vous ne pouvez pas coller Power Pivot formules dans une feuille de calcul Excel et vice versa.
-
Vous ne pouvez pas avoir de données irrégulières ou « déséquilibrées », comme vous le faites dans une feuille de calcul Excel. Chaque ligne d’une table doit contenir le même nombre de colonnes. Toutefois, vous pouvez avoir des valeurs vides dans certaines colonnes. Les tables de données Excel et les tables de données Power Pivot ne sont pas interchangeables, mais vous pouvez lier des tableaux Excel à partir de Power Pivot et coller des données Excel dans Power Pivot. Pour plus d’informations, consultez 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 à des tables et des colonnes dans des formules et des expressions
Vous pouvez faire référence à n’importe quelle table et colonne à l’aide de son nom. Par exemple, la formule suivante montre comment faire référence à des colonnes de deux tables à l’aide du nom complet :
=SUM('New Sales'[Amount]) + SUM('Past Sales'[Amount])
Lorsqu’une formule est évaluée, Power Pivot d’abord vérifier la syntaxe générale, puis vérifier les noms des colonnes et des tables que vous fournissez par rapport aux colonnes et tables possibles dans le contexte actuel. Si le nom est ambigu ou si la colonne ou la table est introuvable, vous obtenez une erreur sur votre formule (une chaîne #ERROR au lieu d’une valeur de données dans les cellules où l’erreur se produit). Pour plus d’informations sur les exigences de nommage pour les tables, colonnes et autres objets, consultez « Exigences de nommage dans la spécification de syntaxe DAX pour Power Pivot.
Remarque : Le contexte est une fonctionnalité importante de Power Pivot modèles de données qui vous permet de créer des formules dynamiques. Le contexte est déterminé par les tables du modèle de données, les relations entre les tables et les filtres qui ont été appliqués. Pour plus d’informations, consultez Contexte dans les formules DAX.
Relations de table
Les tables peuvent être liées à d’autres tables. En créant des relations, vous obtenez la possibilité de rechercher des données dans une autre table et d’utiliser des valeurs associées pour effectuer des calculs complexes. Par exemple, vous pouvez utiliser une colonne calculée pour rechercher 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étrable : vous pouvez calculer une somme différente pour chaque ligne de la table actuelle.
De nombreuses fonctions DAX nécessitent l’existence d’une relation entre les tables, ou entre plusieurs tables, afin de localiser les colonnes que vous avez référencées et de retourner des résultats pertinents. D’autres fonctions tenteront d’identifier la relation ; Toutefois, pour obtenir de meilleurs résultats, vous devez toujours créer une relation dans la cas où cela est possible.
Lorsque vous travaillez avec des tableaux croisés dynamiques, il est particulièrement important de connecter toutes les tables utilisées dans le tableau croisé dynamique afin que les données récapitulatives 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 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 de fonctions individuelles, consultez 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 dues à l’un des problèmes suivants :
-
La formule fait référence à une colonne, une table ou une fonction non existante.
-
La formule semble correcte, mais lorsque le Power Pivot extrait les données, il détecte 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, apporté des modifications, puis n’avez 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 dernier cas, DAX grise la colonne pour indiquer que la colonne se trouve dans un état non traité.
