Excel DAX - Formules

DAX est un langage de formule pour créer des calculs personnalisés dans Power PivotTables. Vous pouvez utiliser les fonctions DAX conçues pour fonctionner avec des données relationnelles et effectuer une agrégation dynamique dans des formules DAX.

DAX formulassont très similaires aux formules Excel. Pour créer une formule DAX, vous tapez un signe égal, suivi d'un nom de fonction ou d'une expression et des valeurs ou arguments requis.

Fonctions DAX et formules DAX

Les formules DAX peuvent inclure des fonctions DAX et tirer parti de leur utilisation. C'est là que les formules DAX ont tendance à différer des fonctions DAX de manière importante.

  • Une fonction DAX fait toujours référence à une colonne complète ou à une table. Si vous souhaitez utiliser uniquement des valeurs particulières d'une table ou d'une colonne, vous pouvez ajouter des filtres à la formule.

  • Si vous souhaitez personnaliser les calculs ligne par ligne, Power Pivot 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 renvoie une table comme résultat, plutôt qu'une valeur unique. Ces fonctions peuvent être utilisées pour fournir des entrées à d'autres fonctions, calculant ainsi des valeurs pour des tables ou des colonnes entières.

  • Certaines fonctions DAX fournissent des informations temporelles, ce qui vous permet de créer des calculs à l'aide de plages de dates significatives et de comparer les résultats sur des périodes parallèles.

Comprendre la syntaxe des formules DAX

Chaque formule DAX a la syntaxe suivante -

  • Chaque formule doit commencer par un signe égal.

  • À droite du signe égal, vous pouvez saisir ou sélectionner un nom de fonction ou saisir une expression. L'expression peut contenir des noms de table et des noms de colonne connectés par des opérateurs DAX.

Voici quelques formules DAX valides -

  • [Colonne_Coût] + [Taxe_colonne]
  • = Aujourd'hui ()

Comprendre la fonctionnalité IntelliSense

DAX fournit la fonctionnalité IntelliSense qui vous permettra d'écrire des formules DAX rapidement et correctement. Avec cette fonctionnalité, vous n'avez pas besoin de taper complètement les noms de table, de colonne et de fonction, mais de sélectionner les noms appropriés dans la liste déroulante lors de l'écriture d'une formule DAX.

  • Commencez à taper les premières lettres du nom de la fonction. La saisie semi-automatique affiche une liste des fonctions disponibles avec les noms commençant par ces lettres.

  • Placez le pointeur sur l'un des noms de fonction. L'info-bulle IntelliSense s'affichera pour vous donner l'utilisation de la fonction.

  • Cliquez sur le nom de la fonction. Le nom de la fonction apparaît dans la barre de formule et la syntaxe s'affiche, qui vous guidera lors de la sélection des arguments.

  • Tapez la première lettre du nom de table souhaité. La saisie semi-automatique affiche une liste des tables et des colonnes disponibles avec les noms commençant par cette lettre.

  • Appuyez sur TAB ou cliquez sur le nom pour ajouter un élément de la liste de saisie semi-automatique à la formule.

  • Clique le Fxpour afficher une liste des fonctions disponibles. Pour sélectionner une fonction dans la liste déroulante, utilisez les touches fléchées pour mettre en surbrillance l'élément et 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 saisissant les valeurs requises.

L'utilisation de cette fonctionnalité IntelliSense pratique est fortement recommandée.

Où utiliser les formules DAX?

Vous pouvez utiliser des formules DAX pour créer des colonnes calculées et des champs calculés.

  • Vous pouvez utiliser des formules DAX dans des colonnes calculées, en ajoutant une colonne, puis en tapant une expression dans la barre de formule. Vous créez ces formules dans la fenêtre PowerPivot.

  • Vous pouvez utiliser des formules DAX dans les champs calculés. Vous créez ces formules -

    • Dans la fenêtre Excel de la boîte de dialogue Champ calculé, ou

    • Dans la fenêtre Power Pivot dans la zone de calcul d'un tableau.

La même formule peut se comporter différemment selon que la formule est utilisée dans une colonne calculée ou un champ calculé.

  • Dans une colonne calculée, la formule est toujours appliquée à chaque ligne de la colonne, dans tout le tableau. En fonction du contexte de ligne, la valeur peut changer.

  • Dans un champ calculé, cependant, le calcul des résultats dépend fortement du contexte. Autrement dit, la conception du tableau croisé dynamique et le choix des en-têtes de ligne et de colonne affectent les valeurs utilisées dans les calculs.

Il est important de comprendre le concept de contexte dans DAX pour écrire des formules DAX. Cela peut être un peu difficile au début de votre parcours DAX, mais une fois que vous l'avez compris, vous pouvez écrire des formules DAX efficaces nécessaires à l'analyse de données complexes et dynamiques. Pour plus de détails, reportez-vous au chapitre - Contexte DAX.

Création d'une formule DAX

Vous avez déjà découvert la fonctionnalité IntelliSense dans une section précédente. N'oubliez pas de l'utiliser lors de la création d'une formule DAX.

Pour créer une formule DAX, procédez comme suit:

  • Tapez un signe égal.

  • À droite du signe égal, tapez ce qui suit -

    • Tapez la première lettre d'un nom de fonction ou de table et sélectionnez le nom complet dans la liste déroulante.

    • Si vous avez choisi un nom de fonction, tapez les parenthèses '('.

    • Si vous avez choisi le nom de la table, saisissez le crochet '['. Tapez la première lettre du nom de la colonne et sélectionnez le nom complet dans la liste déroulante.

    • Fermez les noms de colonnes avec ']' et les noms de fonctions avec ')'.

    • Tapez un opérateur DAX entre les expressions ou tapez «,» pour séparer les arguments de fonction.

    • Répétez les étapes 1 à 5 jusqu'à ce que la formule DAX soit terminée.

Par exemple, vous souhaitez trouver le montant total des ventes dans la région Est. Vous pouvez écrire une formule DAX comme indiqué ci-dessous. East_Sales est le nom de la table. Le montant est une colonne du tableau.

SUM ([East_Sales[Amount])

Comme déjà expliqué dans le chapitre - Syntaxe DAX, il est recommandé d'utiliser le nom de la table avec le nom de la colonne dans chaque référence à un nom de colonne. Ceci est appelé - «le nom pleinement qualifié».

La formule DAX peut varier selon qu'il s'agit d'un champ calculé ou d'une colonne calculée. Reportez-vous aux sections ci-dessous pour plus de détails.

Création d'une formule DAX pour une colonne calculée

Vous pouvez créer une formule DAX pour une colonne calculée dans la fenêtre Power Pivot.

  • Cliquez sur l'onglet du tableau dans lequel vous souhaitez ajouter la colonne calculée.
  • Cliquez sur l'onglet Conception sur le ruban.
  • Cliquez sur Ajouter.
  • Tapez la formule DAX pour la colonne calculée dans la barre de formule.
= DIVIDE (East_Sales[Amount], East_Sales[Units])

Cette formule DAX effectue les opérations suivantes pour chaque ligne de la table East_Sales -

  • Divise la valeur de la colonne Montant d'une ligne par la valeur de la colonne Unités de la même ligne.

  • Place le résultat dans la nouvelle colonne ajoutée de la même ligne.

  • Répète les étapes 1 et 2 de manière itérative jusqu'à ce que toutes les lignes du tableau soient terminées.

Vous avez ajouté une colonne pour le prix unitaire auquel ces unités sont vendues avec la formule ci-dessus.

  • Comme vous pouvez le constater, les colonnes calculées nécessitent également un espace de calcul et de stockage. Par conséquent, n'utilisez les colonnes calculées que si nécessaire. Utilisez des champs calculés lorsque cela est possible et suffisant.

Reportez-vous au chapitre - Colonnes calculées pour plus de détails.

Création d'une formule DAX pour un champ calculé

Vous pouvez créer une formule DAX pour un champ calculé dans la fenêtre Excel ou dans la fenêtre Power Pivot. Dans le cas d'un champ calculé, vous devez fournir le nom au préalable.

  • Pour créer une formule DAX pour un champ calculé dans la fenêtre Excel, utilisez la boîte de dialogue Champ calculé.

  • Pour créer une formule DAX pour un champ calculé dans la fenêtre Power Pivot, cliquez sur une cellule dans la zone de calcul du tableau approprié. Démarrez la formule DAX avec CalculatedFieldName: =.

Par exemple, Total East Sales Amount: = SUM ([East_Sales [Amount])

Si vous utilisez la boîte de dialogue Champ calculé dans la fenêtre Excel, vous pouvez vérifier la formule avant de l'enregistrer et en faire une habitude obligatoire pour garantir l'utilisation de formules correctes.

Pour plus de détails sur ces options, reportez-vous au chapitre - Champs calculés.

Création de formules DAX à l'aide de la barre de formule

La fenêtre Power Pivot a également une barre de formule qui ressemble à la barre de formule de la fenêtre Excel. La barre de formule facilite la création et la modification des formules, en utilisant la fonctionnalité de saisie semi-automatique afin de minimiser les erreurs 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 contenant des noms de table valides commençant par ces lettres. Vous pouvez commencer par une lettre et saisir plus de lettres pour réduire la liste si nécessaire.

  • Pour saisir le nom d'une colonne, vous pouvez la sélectionner dans la liste des noms de colonnes du tableau sélectionné. Tapez un crochet «[», à droite du nom de la table, puis choisissez la colonne dans la liste des colonnes de la table sélectionnée.

Conseils d'utilisation de la saisie semi-automatique

Voici quelques conseils sur l'utilisation de la saisie semi-automatique -

  • Vous pouvez imbriquer des fonctions et des formules dans une formule DAX. Dans ce cas, vous pouvez utiliser la saisie semi-automatique de formule au milieu d'une formule existante avec des fonctions imbriquées. Le texte immédiatement avant le point d'insertion est utilisé pour afficher les 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 saisie semi-automatique, mais vous pouvez toujours les saisir.

  • La parenthèse fermante des fonctions n'est pas ajoutée automatiquement. Vous devez le faire vous-même.

  • Vous devez vous assurer que chaque fonction est syntaxiquement correcte.

Comprendre la fonction d'insertion

Vous pouvez trouver le bouton Insérer une fonction étiqueté comme fx, à la fois dans la fenêtre Power Pivot et la fenêtre Excel.

  • Le bouton Insérer une fonction dans la fenêtre Power Pivot se trouve à gauche de la barre de formule.

  • Le bouton Insérer une fonction dans la fenêtre Excel se trouve dans la boîte de dialogue Champ calculé à droite de Formule.

Lorsque vous cliquez sur le fx, la boîte de dialogue Insérer une fonction apparaît. La boîte de dialogue Insérer une fonction est le moyen le plus simple de rechercher une fonction DAX correspondant à votre formule DAX.

La boîte de dialogue Insérer une fonction vous aide à sélectionner des fonctions par catégorie et fournit de brèves descriptions pour chaque fonction.

Utilisation de la fonction d'insertion dans une formule DAX

Supposons que vous souhaitiez créer le champ calculé suivant -

Medal Count: = COUNTA (]Medal])

Vous pouvez utiliser la boîte de dialogue Insérer une fonction en procédant comme suit -

  • Cliquez sur la zone de calcul du tableau Résultats.
  • Tapez ce qui suit dans la barre de formule -
Medal Count: =
  • Cliquez sur le bouton Insérer une fonction (fx).

La boîte de dialogue Insérer une fonction apparaît.

  • Sélectionnez Statistiques dans la zone Sélectionnez une catégorie, comme illustré dans la capture d'écran suivante.

  • Sélectionnez COUNTA dans la zone Sélectionnez une fonction comme indiqué dans la capture d'écran suivante.

Comme vous pouvez le constater, la syntaxe de la fonction DAX sélectionnée et la description de la fonction sont affichées. Cela vous permet de vous assurer qu'il s'agit bien de la fonction que vous souhaitez insérer.

  • Cliquez sur OK. Nombre de médailles: = COUNTA (apparaît dans la barre de formule et une info-bulle affichant la syntaxe de la fonction apparaît également.

  • Tapez [. Cela signifie que vous êtes sur le point de taper un nom de colonne. Les noms de toutes les colonnes et les champs calculés dans la table actuelle seront affichés dans la liste déroulante. Vous pouvez utiliser IntelliSense pour compléter la formule.

  • Tapez M. Les noms affichés dans la liste déroulante seront limités à ceux commençant par «M».

  • Cliquez sur Médaille.

  • Double-cliquez sur Médaille. Nombre de médailles: = COUNTA ([Médaille] sera affiché dans la barre de formule. Fermez la parenthèse.

  • Appuyez sur Entrée. Vous avez terminé. Vous pouvez également utiliser la même procédure pour créer une colonne calculée. Vous pouvez également suivre les mêmes étapes pour insérer une fonction dans la boîte de dialogue Champ calculé de la fenêtre Excel à l'aide de la fonction Insérer une fonction.

  • Cliquez sur la fonction d'insertion (fx) à droite de la formule.

La boîte de dialogue Insérer une fonction apparaît. Les autres étapes sont les mêmes que ci-dessus.

Utilisation de plusieurs fonctions dans une formule DAX

Les formules DAX peuvent contenir jusqu'à 64 fonctions imbriquées. Mais il est peu probable qu'une formule DAX contienne autant de fonctions imbriquées.

Si une formule DAX a de nombreuses fonctions imbriquées, elle présente les inconvénients suivants:

  • La formule serait très difficile à créer.
  • Si la formule contient des erreurs, il serait très difficile de déboguer.
  • L'évaluation de la formule ne serait pas très rapide.

Dans de tels cas, vous pouvez diviser la formule en formules plus petites gérables et créer la grande formule de manière incrémentielle.

Création d'une formule DAX à l'aide d'agrégations standard

Lorsque vous effectuez une analyse de données, vous effectuerez des calculs sur des données agrégées. Il existe plusieurs fonctions d'agrégation DAX, telles que SUM, COUNT, MIN, MAX, DISTINCTCOUNT, etc. que vous pouvez utiliser dans les formules DAX.

Vous pouvez créer automatiquement des formules à l'aide d'agrégations standard à l'aide de la fonctionnalité Somme automatique dans la fenêtre Power Pivot.

  • Cliquez sur l'onglet Résultats dans la fenêtre Power Pivot. Le tableau des résultats sera affiché.
  • Cliquez sur la colonne Médaille. La colonne entière - Médaille sera sélectionnée.
  • Cliquez sur l'onglet Accueil sur le ruban.
  • Cliquez sur la flèche vers le bas en regard de Somme automatique dans le groupe Calculs.
  • Cliquez sur COUNT dans la liste déroulante.

Comme vous pouvez le constater, le champ calculé Nombre de médailles apparaît dans la zone de calcul sous la colonne - Médaille. La formule DAX apparaît également dans la barre de formule -

Count of Medal: = COUNTA([Medal])

La fonction de somme automatique a fait le travail pour vous - a créé le champ calculé pour l'agrégation des données. En outre, AutoSum a pris la variante appropriée de la fonction DAX COUNT, c'est-à-dire COUNTA (DAX a les fonctions COUNT, COUNTA, COUNTAX).

Un mot d'avertissement - Pour utiliser la fonction de Somme automatique, vous devez cliquer sur la flèche vers le bas à côté de Somme automatique sur le ruban. Si vous cliquez sur l'AutoSum lui-même à la place, vous obtiendrez -

Sum of Medal: = SUM([Medal])

Et une erreur est signalée car Medal n'est pas une colonne de données numériques et le texte de la colonne ne peut pas être converti en nombres.

Vous pouvez vous référer au chapitre - DAX Error Reference pour plus de détails sur les erreurs DAX.

Formules DAX et modèle relationnel

Comme vous le savez, dans le modèle de données de Power Pivot, vous pouvez travailler avec plusieurs tables de données et connecter les tables en définissant des relations. Cela vous permettra de créer des formules DAX intéressantes qui utilisent les corrélations des colonnes entre les tables associées pour les calculs.

Lorsque vous créez une relation entre deux tables, vous devez vous assurer que les deux colonnes utilisées comme clés ont des valeurs qui correspondent, au moins pour la plupart des lignes, sinon complètement. Dans le modèle de données Power Pivot, il est possible d'avoir des valeurs non correspondantes dans une colonne de clé et de toujours créer une relation, car Power Pivot n'applique pas l'intégrité référentielle (consultez la section suivante pour plus de détails). Toutefois, la présence de valeurs vides ou non correspondantes dans une colonne clé peut affecter les résultats des formules DAX et l'apparence des tableaux croisés dynamiques.

Intégrité référentielle

L'établissement de l'intégrité référentielle implique la création d'un ensemble de règles pour préserver les relations définies entre les tables lorsque vous entrez ou supprimez des données. Si vous ne vous assurez pas exclusivement de cela, car Power Pivot ne l'applique pas, vous risquez de ne pas obtenir de résultats corrects avec les formules DAX créées avant que les modifications de données ne soient apportées.

Si vous appliquez l'intégrité référentielle, vous pouvez éviter les pièges suivants:

  • Ajout de lignes à une table associée lorsqu'il n'y a pas de ligne associée dans la table primaire (c'est-à-dire avec des valeurs correspondantes dans les colonnes clés).

  • Modification des données dans une table primaire qui entraînerait des lignes orphelines dans une table associée (c'est-à-dire des lignes avec une valeur de données dans la colonne clé qui n'a pas de valeur correspondante dans la colonne clé de table primaire).

  • Suppression de lignes d'une table principale lorsqu'il existe des valeurs de données correspondantes dans les lignes de la table associée.