Power Pivot, Expressions DAX

by ·

Les expressions DAX (Data Analysis Expressions) peuvent intimider au début, mais ne vous laissez pas bloquer par ce nom. Les aspects fondamentaux des expressions DAX sont très faciles à comprendre. Premièrement, DAX n’est PAS un langage de programmation. DAX est un langage de formule. Vous pouvez utiliser DAX pour définir des calculs personnalisés pour des colonnes calculées et des mesures (également appelées champs calculés). DAX inclut quelques-unes des fonctions utilisées dans les formules Excel, ainsi que des fonctions supplémentaires conçues pour l’utilisation de données relationnelles et l’agrégation dynamique.

Présentation des formules DAX

Les formules DAX sont très semblables aux formules Excel. Pour en créer une, tapez un signe égal, suivi d’un nom de fonction ou d’une expression, et de toutes les valeurs ou arguments requis. Comme Excel, DAX fournit diverses fonctions que vous pouvez utiliser pour utiliser des chaînes, effectuer des calculs à l’aide de dates et d’heures ou créer des valeurs conditionnelles.

Toutefois, les formules DAX diffèrent des manières importantes suivantes :

Si vous voulez personnaliser des calculs en fonction de chaque ligne, DAX fournit des fonctions qui vous permettent d’utiliser la valeur de ligne actuelle ou une valeur associée pour effectuer des calculs qui varient selon le contexte.
DAX inclut un type de fonction qui retourne une table comme résultat, plutôt qu’une valeur unique. Ces fonctions peuvent être utilisées pour fournir des valeurs d’entrée à d’autres fonctions.
Les fonction TIme Intelligence dans le langage Dax autorisent les calculs à l’aide de plages de dates et de comparer les résultats entre des périodes parallèles.

Où utiliser des formules DAX

Vous pouvez créer des formules dans Power Pivot soit dans des colonnes calculées, soit dans des champs calculés.

Colonnes calculées

Une colonne calculée est une colonne que vous ajoutez à une table Power Pivot existante. Au lieu de coller ou importer des valeurs dans la colonne, vous créez une formule DAX qui définit les valeurs de colonne. Si vous incluez la table Power Pivot dans un tableau croisé dynamique (ou un graphique croisé dynamique), la colonne calculée peut être utilisée comme toute autre colonne de données.

Les formules dans les colonnes calculées sont très semblables aux formules que vous créez dans Excel. Toutefois, contrairement à Excel, vous ne pouvez pas créer une formule différente pour des lignes différentes dans une table ; à la place, la formule DAX est appliquée automatiquement à la colonne entière.

Lorsqu’une colonne contient une formule, la valeur est calculée pour chaque ligne. Les résultats sont calculés pour la colonne dès que vous créez la formule. Les valeurs de colonnes sont recalculées uniquement si les données sous-jacentes sont actualisées ou en cas de recalcul manuel.

Vous pouvez créer des colonnes calculées basées sur des mesures et autres colonnes calculées. Néanmoins, évitez d’utiliser le même nom pour une colonne calculée et une mesure, car cela peut entraîner des résultats confus. Lorsque vous faites référence à une colonne, il est préférable d’utiliser une référence de colonne complète pour éviter d’appeler accidentellement une mesure.

Pour des informations plus détaillées, voir Colonnes calculées dans Power Pivot.

Mesures

Une mesure est une formule créée spécifiquement pour une utilisation dans un tableau croisé dynamique (ou graphique croisé dynamique) qui utilise des données Power Pivot. Les mesures peuvent être basées sur des fonctions d’agrégation standard, telles que NB ou somme, ou vous pouvez définir votre propre formule à l’aide de DAX. Une mesure est utilisée dans la zone valeurs d’un tableau croisé dynamique. Si vous souhaitez placer des résultats calculés dans une zone différente d’un tableau croisé dynamique, utilisez plutôt une colonne calculée.

Lorsque vous définissez une formule pour une mesure explicite, rien ne se produit tant que vous n’avez pas ajouté la mesure dans un tableau croisé dynamique. Lorsque vous ajoutez la mesure, la formule est évaluée pour chaque cellule dans la zone valeurs du tableau croisé dynamique. Dans la mesure où un résultat est créé pour chaque combinaison d’en-têtes de ligne et de colonne, le résultat de la mesure peut être différent dans chaque cellule.

La définition de la mesure que vous créez est enregistrée avec sa table de données source. Elle apparaît dans la liste des champs PivotTable et est disponible pour tous les utilisateurs du classeur.

Pour obtenir des informations plus détaillées, consultez mesures dans Power Pivot.

Création de formules à l’aide de la barre de formule

Power Pivot, comme Excel, comprend une barre de formule qui simplifie la création et la modification de formules, ainsi qu’une fonctionnalité de saisie semi-automatique pour réduire les erreurs de frappe et de syntaxe.

Pour entrer le nom d’une table Commencez à taper le nom de la table. La saisie semi-automatique de formule fournit une liste déroulante qui contient des noms valides commençant par ces lettres.

Pour entrer le nom d’une colonne Tapez un crochet, puis sélectionnez la colonne dans la liste de colonnes dans la table actuelle. S’il s’agit d’une colonne d’une autre table, commencez à taper les premières lettres du nom de la table, puis sélectionnez la colonne dans la liste déroulante de saisie semi-automatique.

Pour des informations plus détaillées et la procédure pas à pas de création de formules, voir Créer des formules pour les calculs dans Power Pivot.

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é.

Les noms définis que vous créez pour les constantes ne s’affichent pas dans la liste déroulante de la saisie semi-automatique, mais vous pouvez toujours les taper.

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 vous ne pouvez pas enregistrer ni utiliser la formule.

Utilisation de plusieurs fonctions dans une formule

Vous pouvez imbriquer des fonctions, ce qui signifie que vous utilisez les résultats d’une fonction comme un argument d’une autre fonction. Vous pouvez imbriquer jusqu’à 64 niveaux de fonctions dans les colonnes calculées. Toutefois, l’imbrication peut rendre la création ou le dépannage de formules difficile.

De nombreuses fonctions DAX sont conçues pour être utilisées uniquement comme fonctions imbriquées. Ces fonctions retournent une table, qui ne peut pas être enregistrée directement comme résultat ; elle doit être fournie comme entrée à une fonction de table. Par exemple, les fonctions SUMX, AVERAGEX et MINX requièrent toutes une table comme premier argument.

Remarque : Il existe des limites relatives à l’imbrication de fonctions dans les mesures, qui permettent de s’assurer que les performances ne sont pas affectées par les nombreux calculs requis par les dépendances entre les colonnes.

Comparaison entre les fonctions DAX et Excel

La bibliothèque de fonctions DAX est basée sur la bibliothèque de fonctions Excel, mais il existe de nombreuses différences entre ces bibliothèques. Cette section résume les différences et les ressemblances entre les fonctions Excel et DAX.

De nombreuses fonctions DAX ont le même nom et le même comportement général que les fonctions Excel, mais ont été modifiées pour accepter différents types d’entrées et, dans certains cas, peuvent retourner un type de données différent. En général, vous ne pouvez pas utiliser de fonctions DAX dans une formule Excel ni utiliser des formules Excel dans Power Pivot sans effectuer quelques modifications.
Les fonctions DAX ne prennent jamais une cellule ou une plage comme référence, mais plutôt une colonne ou une table.
Les fonctions DAX de date et d’heure retournent un type de données datetime. Par opposition, les fonctions de date et d’heure Excel retournent un entier qui représente une date sous la forme d’un numéro de série.
Un grand nombre des nouvelles fonctions DAX retournent une table de valeurs ou effectuent des calculs basés sur une table de valeurs comme entrée. Par opposition, Excel n’a aucune fonction qui retourne une table, mais certaines fonctions peuvent utiliser des tableaux. La capacité de référencer facilement des tables et des colonnes complètes est une nouvelle fonctionnalité dans Power Pivot.
DAX fournit de nouvelles fonctions de recherche, semblables aux fonctions de recherche de tableau et de vecteur dans Excel. Toutefois, les fonctions DAX requièrent l’établissement d’une relation entre les tables.
Les données d’une colonne sont supposées être toujours du même type. Si les données ne sont pas du même type, DAX modifie la colonne entière en type de données convenant le mieux à l’ensemble des valeurs.

Types de données DAX

Vous pouvez importer des données dans un modèle de données Power Pivot à partir de nombreuses sources de données différentes, qui peuvent prendre en charge des types de données différents. Lorsque vous importez ou chargez les données, puis utilisez les données dans des calculs ou dans des tableaux croisés dynamiques, les données sont converties dans l’un des types de données Power Pivot. Pour obtenir la liste des types de données, consultez Types de données dans les modèles de données.

Le type de données de table est un nouveau type de données dans DAX utilisé comme entrée ou sortie dans de nombreuses nouvelles fonctions. Par exemple, la fonction FILTER prend une table en entrée et génère en sortie une autre table qui contient uniquement les lignes qui répondent aux conditions de filtre. En associant des fonctions de table à des fonctions d’agrégation, vous pouvez effectuer des calculs complexes sur des jeux de données définis de façon dynamique. Pour plus d’informations, consultez Agrégations dans Power Pivot.

Formules et modèle relationnel

La fenêtre Power Pivot est une zone où vous pouvez travailler avec plusieurs tables de données et connecter les tables au sein d’un modèle relationnel. Dans ce modèle de données, les tables sont connectées entre elles par des relations, ce qui vous permet de créer des corrélations avec des colonnes d’autres tables ainsi que des calculs plus intéressants. Par exemple, vous pouvez créer des formules qui additionnent des valeurs pour une table associée, puis enregistrer la valeur obtenue dans une cellule unique. Sinon, pour contrôler les lignes de la table associée, vous pouvez appliquer des filtres aux tables et aux colonnes. Pour plus d’informations, voir Relations entre les tables dans un modèle de données.

Comme vous pouvez lier des tables à l’aide de relations, vos tableaux croisés dynamiques peuvent également inclure des données de plusieurs colonnes issues de tables différentes.

Toutefois, étant donné que les formules peuvent fonctionner avec des colonnes et des tables entières, vous devez concevoir des calculs d’une autre manière que dans Excel.

En général, une formule DAX dans une colonne est toujours appliquée au jeu entier de valeurs dans la colonne (elle ne s’applique jamais à quelques lignes ou cellules uniquement).
Les tables dans Power Pivot doivent toujours avoir le même nombre de colonnes dans chaque ligne, et toutes les lignes d’une colonne doivent contenir le même type de données.
Lorsque les tables sont connectées par une relation, vous devez vous assurer que les deux colonnes utilisées comme clés ont des valeurs qui correspondent, pour la plupart. Étant donné que Power Pivot n’applique pas l’intégrité référentielle, il est possible d’avoir des valeurs sans correspondance dans une colonne clé et, néanmoins, de créer une relation. Toutefois, la présence de valeurs vides ou sans correspondance peut affecter les résultats des formules et l’apparence des tableaux croisés dynamiques. Pour plus d’informations, consultez Recherches dans les formules Power Pivot.
Lorsque vous liez des tables à l’aide de relations, vous agrandissez l’étendue, ou le contexte, dans lequel vos formules sont évaluées. Par exemple, les formules incluses dans un tableau croisé dynamique peuvent être affectées par des filtres ou des en-têtes de colonnes et de lignes quelconques dans le tableau croisé dynamique. Vous pouvez écrire des formules qui manipulent le contexte, mais ce dernier peut également provoquer des changements inattendus au niveau des résultats. Pour plus d’informations, voir Contexte dans les formules DAX.

Mise à jour des résultats de formules

L’actualisation des données et le recalcul sont deux opérations distinctes, mais connexes, que vous devez comprendre lorsque vous concevez un modèle de données qui contient des formules complexes, de grandes quantités de données ou des données obtenues de sources de données externes.

L’actualisation des données est le processus de mise à jour des données dans votre classeur avec de nouvelles données issues d’une source de données externe. Vous pouvez actualiser des données manuellement aux intervalles que vous spécifiez. Sinon, si vous avez publié le classeur sur un site SharePoint, vous pouvez planifier une actualisation automatique depuis des sources externes.

Le recalcul est le processus de mise à jour des résultats des formules afin de refléter toutes les modifications apportées aux formules, ainsi que toutes les modifications des données sous-jacentes. Le recalcul peut affecter les performances des façons suivantes :

Pour une colonne calculée, le résultat de la formule doit toujours être recalculé, pour la colonne entière, chaque fois que vous modifiez la formule.
Pour une mesure, les résultats d’une formule ne sont pas calculés tant que la mesure n’a pas été placée dans le contexte du tableau croisé dynamique ou du graphique croisé dynamique. La formule est également recalculée lorsque vous modifiez tout en-tête de ligne ou de colonne qui affecte des filtres sur les données, ou lorsque vous actualisez manuellement le tableau croisé dynamique.

Dépannage des formules

Erreurs lors de la rédaction de formules

Si vous obtenez une erreur lorsque vous définissez une formule, la formule peut contenir une erreur syntaxique, une erreur sémantique ou une erreur de calcul.

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, consultez la 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. De telles erreurs sémantiques et de calcul peuvent être provoqué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 moteur de données 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, c’est-à-dire qu’elle contient des métadonnées, mais aucune donnée réelle à utiliser pour 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é.

Résultats incorrects ou inhabituels lors du classement ou du tri de valeurs de colonnes

Lors du classement ou du tri d’une colonne contenant la valeur NaN (Not a Number, non numérique), il se peut que vous obteniez des résultats incorrects ou inattendus. Par exemple, lorsqu’un calcul divise 0 par 0, le résultat NaN est renvoyé.

La raison en est que le moteur de formule effectue le tri et le classement en comparant les valeurs numériques ; toutefois, NaN ne peut pas être comparé aux autres nombres dans la colonne.

Pour être sûr d’avoir des résultats corrects, vous pouvez utiliser des instructions conditionnelles en utilisant la fonction SI pour tester la présence de valeurs NaN et renvoyer une valeur 0 numérique.

Compatibilité avec les modèles tabulaires Analysis Services et le mode DirectQuery

En général, les formules DAX que vous construisez dans Power Pivot sont totalement compatibles avec les modèles tabulaires Analysis Services. Cependant, si vous migrez votre modèle Power Pivot vers une instance Analysis Services et si vous déployez le modèle en mode DirectQuery, il existe un certain nombre de limitations.

Certaines formules DAX peuvent retourner des résultats différents si vous déployez le modèle en mode DirectQuery.
Certaines formules peuvent provoquer des erreurs de validation lorsque vous déployez le modèle en mode DirectQuery, car la formule contient une fonction DAX qui n’est pas prise en charge sur une source de données relationnelle.

Pour plus d’informations, consultez la documentation relative à la modélisation tabulaire Analysis Services dans la documentation en ligne de SQL Server 2012.