Démarrage rapide : découvrir les fondamentaux de DAX en 30 minutes

Tâche : créer une colonne simple pour une colonne calculée

1. Si vous n’êtes pas déjà dans la fenêtre Power Pivot, dans Excel, dans le ruban Power Pivot, cliquez sur Power Pivot Fenêtre.

2. Dans la fenêtre Power Pivot, cliquez sur la table FactSales (onglet).

3. Faites défiler jusqu’à la colonne la plus à droite, puis dans l’en-tête de colonne, cliquez sur Ajouter une colonne.

4. Cliquez dans la barre de formule en haut de la fenêtre du générateur de modèles.

   

   Votre curseur apparaît maintenant dans la barre de formule. La barre de formule est l’emplacement où vous pouvez entrer une formule pour une colonne calculée ou un champ calculé.

   Prenons un moment pour observer trois boutons situés à gauche de la barre de formule.

   

   Lorsque le curseur est actif dans la barre de formule, ces trois boutons sont actifs. Le bouton situé à l’extrême gauche, X, correspond tout simplement à un bouton Annuler. Allez-y, cliquez dessus. Le curseur ne s’affiche plus dans la barre de formule ; par ailleurs, le bouton Annuler et le bouton de coche n’apparaissent plus. Allez-y, cliquez à nouveau dans la barre de formule. Le bouton Annuler et le bouton de coche réapparaissent maintenant. Cela signifie que vous êtes prêt pour l’écriture d’une formule.

   Le bouton de coche correspond au bouton de vérification de la formule. Il n’a pas une grande utilité tant que vous n’avez pas écrit de formule. Nous y reviendrons un peu plus tard.

   Cliquez sur le bouton Fx. Vous remarquez qu’une nouvelle boîte de dialogue s’affiche, à savoir la boîte de dialogue Insérer une fonction. La boîte de dialogue Insérer une fonction représente la méthode la plus facile pour commencer l’écriture d’une formule DAX. Nous ajouterons une fonction à une formule lorsque nous créerons une mesure un peu plus tard, mais pour l’instant, vous n’avez pas besoin d’ajouter une fonction à votre formule de colonne calculée. Fermez donc la boîte de dialogue Insérer une fonction.

5. Dans la barre de formule, tapez un signe égal =, puis entrez un crochet ouvrant [. Vous verrez une petite fenêtre s’afficher avec toutes les colonnes de la table FactSales. Cela signifie que la fonctionnalité IntelliSense est active.

   Les colonnes calculées étant toujours créées dans la table active, il est inutile de faire précéder le nom de la colonne par le nom de la table. Faites maintenant défiler la liste vers le bas et double-cliquez sur [SalesQuantity]. Vous pouvez également faire défiler jusqu’au nom de colonne souhaité, puis appuyer sur la touche Tab.

   Le curseur est maintenant actif à droite de [SalesQuantity].

6. Tapez un espace, puis entrez un opérateur de soustraction - (signe moins), puis tapez un autre espace.

7. Maintenant, tapez un autre crochet ouvrant [. Cette fois, sélectionnez la colonne [ReturnQuantity], puis appuyez sur Entrée.

   Si vous obtenez une erreur, examinez soigneusement la syntaxe. Si nécessaire, comparez-la à la formule de la colonne calculée Margin décrite précédemment.

   Une fois que vous avez appuyé sur Entrée pour terminer la formule, le message de calcul en cours s’affiche dans la barre d’état au bas de la fenêtre Power Pivot. La procédure est rapide, même si vous venez de calculer de nouvelles valeurs pour plus de trois millions de lignes.

8. Cliquez avec le bouton droit sur l’en-tête de colonne et renommez la colonne, NetSales.

Voilà ! Vous venez de créer une formule DAX, certes simple, mais très puissante. Pour chaque ligne de la table FactSales, la formule NetSales calcule une valeur en soustrayant la valeur figurant dans la colonne [ReturnQuantity] de la valeur de la colonne [SalesQuantity]. Notez l’importance de la mention « Pour chaque ligne ». Ceci est un aperçu d’un autre concept très important dans DAX, le contexte de ligne. Vous en apprendrez plus sur le contexte de ligne dans la suite de ce didacticiel.

Lorsque vous tapez un opérateur dans une formule DAX, il est vraiment important de comprendre à quoi correspond le type de données dans les arguments que vous utilisez. Par exemple, si vous entrez la formule suivante, = 1 & 2, la valeur retournée correspond à la valeur textuelle « 12 ». Cela est dû au fait que l’opérateur « et commercial » (&) correspond à une concaténation de texte. DAX interprète cette formule de la manière suivante : calculez un résultat en prenant la valeur 1 en tant que texte et ajoutez la valeur 2 en tant que texte. Maintenant, si vous tapez = 1 + 2, DAX interprète cette formule comme suit : calculez un résultat en prenant la valeur numérique 1 et en y ajoutant la valeur numérique 2. Le résultat est évidemment « 3 », c’est-à-dire une valeur numérique. DAX calcule les valeurs résultantes en fonction de l’opérateur dans la formule, et non en fonction du type de données des colonnes utilisées dans l’argument. Les types de données dans DAX sont très importants, mais n’entrent pas dans le cadre de ce guide de démarrage rapide. Pour en savoir plus sur les types de données et les opérateurs dans les formules DAX, consultez la référence DAX (http://go.microsoft.com/fwlink/?LinkId=239769&clcid=0x409) dans la documentation en ligne.

Essayons une autre opération. Cette fois, vous allez créer une mesure en tapant la formule et en utilisant IntelliSense. Ne vous inquiétez pas si vous ne comprenez pas la formule dans son intégralité. Ici, l’important est de savoir comment créer une formule comportant plusieurs éléments en utilisant la syntaxe correcte.

Tâche : Créer une formule de mesure

1. Dans la table FactSales, cliquez dans n’importe quelle cellule vide au sein de la Zone de calcul. Il s’agit de la zone de cellules vides juste au-dessous d’une table dans la fenêtre Power Pivot.

1. Dans la barre de formule, tapez le nom « Previous Quarter Sales: ».

2. Tapez un signe égal = pour démarrer la formule de calcul.

3. Tapez les premières lettres, CAL, puis double-cliquez sur la fonction à utiliser. Dans cette formule, vous souhaitez utiliser la fonction CALCULATE.

4. Tapez une parenthèse ouvrante (pour commencer la saisie des arguments à transmettre à la fonction CALCULATE).

   Notez qu’après avoir tapé la parenthèse ouvrante, IntelliSense vous présente les arguments requis pour la fonction CALCULATE. Vous en apprendrez un peu plus sur les arguments dans un instant.

5. Tapez les premières lettres de la table FactSales , puis dans la liste déroulante, double-cliquez sur FactSales[Sales].

6. Tapez une virgule (,) pour spécifier le premier filtre, puis tapez PRE et double-cliquez sur la fonction PREVIOUSQUARTER.

   Après avoir sélectionné la fonction PREVIOUSQUARTER, une autre parenthèse ouvrante apparaît, ce qui indique qu’un autre argument est requis ; cette fois, c’est pour la fonction PREVIOUSQUARTER.

7. Tapez les premières lettres, Dim, puis double-cliquez sur DimDate[DateKey].

8. Fermez à la fois l’argument passé à la fonction PREVIOUSQUARTER et celui transmis à la fonction CALCULATE en tapant deux parenthèses fermantes )).

   Votre formule doit maintenant ressembler à ceci :

   Previous Quarter Sales:=CALCULATE(FactSales[Sales], PREVIOUSQUARTER(DimDate[DateKey]))

9. Cliquez sur le bouton de vérification de la formule sur la barre de formule afin de valider la formule. Si vous obtenez une erreur, vérifiez chaque élément de la syntaxe.

Et voilà ! Vous venez de créer une mesure à l’aide de DAX, ce qui n’est pas facile. Cette formule permettra de calculer les ventes totales du trimestre précédent, selon les filtres appliqués dans un tableau croisé dynamique ou un graphique croisé dynamique.

Vous venez de découvrir plusieurs aspects importants des formules DAX. D’abord, cette formule intégrait deux fonctions. Notez que la fonction PREVIOUSQUARTER est imbriquée en tant qu’argument passé à la fonction CALCULATE . Les formules DAX peuvent compter jusqu’à 64 fonctions imbriquées. Il est peu probable qu’une formule contienne un aussi grand nombre de fonctions imbriquées. En fait, il serait très difficile de créer et de déboguer une telle formule, et cette procédure serait probablement très fastidieuse.

Dans cette formule, vous avez également utilisé des filtres. Les filtres restreignent les éléments à calculer. Dans ce cas, vous avez sélectionné un filtre en tant qu’argument, qui est en réalité une autre fonction. Vous en apprendrez plus sur les filtres ultérieurement.

Enfin, vous avez utilisé la fonction CALCULATE. Il s’agit de l’une des fonctions les plus puissantes de DAX. À mesure que vous allez créer des modèles de données et des formules plus complexes, vous serez probablement amené à utiliser cette fonction à de nombreuses reprises. Une description complète de la fonction CALCULATE dépasse le cadre de ce guide de démarrage rapide, mais à mesure que vos connaissances de DAX s’étofferont, accordez une attention toute particulière à cette fonction.

Tâche de crédit supplémentaire : créer une formule de mesure à l’aide de la fonctionnalité Somme automatique

1. Dans la table FactSales, faites défiler jusqu’à la colonne ReturnQuantity, puis cliquez sur l’en-tête de colonne pour sélectionner la colonne dans son intégralité.

2. Sous l’onglet Accueil , dans le ruban, dans le groupe Calculs , cliquez sur le bouton Somme automatique.

Cliquez sur la flèche vers le bas en regard de Somme automatique, puis cliquez sur Moyenne (notez les autres fonctions d’agrégation standard que vous pouvez également utiliser).

Immédiatement, une nouvelle mesure est créée avec le nom Average of ReturnQuantity : suivi de la formule =AVERAGE([ReturnQuantity]).

Ce n’était pas bien compliqué, n’est-ce pas ? Bien entendu, toutes les formules que vous allez créer ne seront pas aussi simples. Cependant, grâce à la fonction de somme automatique, vous pouvez créer des formules simplement et rapidement à l’aide de calculs d’agrégation standard.

Ceci devrait vous donner une assez bonne compréhension de la syntaxe utilisée dans les formules DAX. Nous vous avons également présenté certaines fonctionnalités vraiment intéressantes, comme IntelliSense et la somme automatique, pour vous aider à créer des formules simples, rapides et précises. Bien sûr, il existe encore de nombreuses autres informations concernant la syntaxe que vous avez intérêt à connaître. Pour en savoir plus, consultez la référence DAX ou la documentation en ligne de SQL.

Test QuickQuiz sur la syntaxe

1. Que fait ce bouton de la barre de formule ?
   

2. Par quoi un nom de colonne est-il toujours entouré dans une formule DAX ?

3. Comment écrire une formule pour le suivant :
   Dans la table DimProduct, pour chaque ligne de la colonne calculée UnitMargin, calculez une valeur en soustrayant les valeurs de la colonne UnitCost des valeurs de la colonneUnitPrice ?

Les réponses sont fournies à la fin de cette rubrique.

Tâche : ajouter une fonction dans une formule à l’aide de la fonctionnalité Insérer une fonction

1. Dans la table FactSales, faites défiler jusqu’à la colonne la plus à droite, puis dans l’en-tête de colonne, cliquez sur Ajouter une colonne.

2. Dans la barre de formule, tapez un signe égal, =.

3. Cliquez sur le bouton Insérer une fonction . La boîte de dialogue Insérer une fonction s’ouvre.

4. Dans la boîte de dialogue Insérer une fonction , cliquez sur la zone de liste Sélectionner une catégorie . Par défaut, l’option Tout est sélectionnée et toutes les fonctions de la catégorie Tout sont répertoriées ci-dessous. Le nombre de fonctions étant très important, vous souhaiterez les filtrer afin de faciliter la recherche du type de fonction que vous recherchez.

5. Pour cette formule, vous voulez retourner des données qui existent déjà dans une autre table. Pour ce faire, vous allez utiliser une fonction dans la catégorie Filtre. Cliquez sur la catégorie Filtre , puis dans Sélectionner une fonction, faites défiler vers le bas et double-cliquez sur la fonction RELATED. Cliquez sur OK pour fermer la boîte de dialogue Insérer une fonction .

6. Utilisez IntelliSense pour vous aider à trouver et à sélectionner la colonne DimChannel[ChannelName].

7. Fermez la formule, puis appuyez sur Entrée.

8. Une fois que vous avez appuyé sur Entrée pour terminer la formule, le message de calcul en cours s’affiche dans la barre d’état au bas de la fenêtre Power Pivot. Vous pouvez à présent constater que vous venez de créer une nouvelle colonne dans la table FactSales avec les informations de canal issues de la table DimChannel.

9. Renommez la colonne Channel.

   Votre formule doit ressembler à ceci : =RELATED(DimChannel[ChannelName])

Vous venez de découvrir une autre fonction très importante dans DAX, la fonction RELATED . La fonction RELATED retourne des valeurs à partir d’une autre table. Vous pouvez utiliser RELATED dans la mesure où il existe une relation entre la table dans laquelle vous travaillez actuellement et la table qui contient les valeurs que vous souhaitez obtenir. Naturellement, la fonction RELATED présente d’immenses possibilités. Dans ce cas, vous pouvez désormais inclure le canal de vente pour chaque vente réalisée dans la table FactSales. Vous pouvez maintenant masquer la table DimChannel dans la liste de champs de tableau croisé dynamique, ce qui facilite la navigation et permet d’afficher uniquement les informations les plus importantes dont vous avez vraiment besoin. À l’instar de la fonction CALCULATE décrite précédemment, la fonction RELATED est très importante et vous l’utiliserez probablement à de nombreuses reprises.

Comme vous pouvez le voir, les fonctions DAX peuvent vous aider à créer des formules très puissantes. Nous avons seulement abordé les aspects fondamentaux des fonctions. À mesure que vos connaissances DAX vont s’améliorer, vous allez être amené à créer des formules à l’aide de nombreuses fonctions différentes. L’un des meilleurs emplacements pour en savoir plus sur toutes les fonctions DAX est la référence DAX (Data Analysis Expressions).

Test QuickQuiz sur les fonctions

1. À quoi une fonction fait-elle toujours référence ?

2. Une formule peut-elle contenir plusieurs fonctions ?

3. Quelle catégorie de fonctions utiliseriez-vous pour concaténer deux chaînes de texte dans une seule chaîne ?

Les réponses sont fournies à la fin de cette rubrique.

Contexte de ligne

Pour des raisons de simplicité, le contexte de ligne peut être considéré comme correspondant à la ligne actuelle. Par exemple, vous vous souvenez de la colonne calculée Margin que vous avez vue précédemment lors de la leçon sur la syntaxe ? La formule =[SalesAmount] - [TotalCost] calcule une valeur dans la colonne Margin pour chaque ligne de la table. Les valeurs de chaque ligne sont calculées à partir des valeurs de deux autres colonnes, [SalesAmount] et [TotalCost] dans la même ligne. DAX peut calculer les valeurs de chaque ligne dans la colonne Margin car il existe un contexte : pour chaque ligne, les valeurs de la colonne [TotalCost] sont prises en compte et soustraites des valeurs de la colonne [SalesAmount].

Dans la cellule sélectionnée indiquée ci-dessous, la valeur 49,54 $ dans la ligne actuelle a été calculée en soustrayant la valeur 51,54 $ dans la colonne [TotalCost] de la valeur 101,08 $ dans la colonne [SalesAmount].

Le contexte de ligne ne s’applique pas seulement aux colonnes calculées. Le contexte de ligne s’applique également chaque fois qu’une formule présente une fonction qui applique des filtres afin d’identifier une ligne unique dans une table. La fonction applique intrinsèquement un contexte de ligne pour chaque ligne de la table sur laquelle un filtrage a lieu. Ce type de contexte de ligne s’applique le plus souvent aux mesures.

Contexte de filtre

Le contexte de filtre est un peu plus difficile à comprendre que le contexte de ligne. Pour simplifier, essayez de vous représenter le contexte de filtre comme suit : un ou plusieurs filtres sont appliqués à un calcul qui détermine un résultat ou une valeur.

Le contexte de filtre ne remplace pas le contexte de ligne ; il s’applique en plus du contexte de ligne. Par exemple, pour restreindre les valeurs à inclure dans un calcul, vous pouvez appliquer un contexte de filtre qui spécifie non seulement le contexte de ligne, mais indique également une valeur particulière (filtre) dans ce contexte de ligne.

Le contexte de filtre est facilement observable dans des tableaux croisés dynamiques. Par exemple, lorsque vous ajoutez TotalCost à la zone Valeurs, puis ajoutez une année et une région à la ligne ou aux colonnes, vous définissez un contexte de filtre qui sélectionne un sous-ensemble de données par rapport à une année et à une région données.

Pourquoi le contexte de filtre est-il si important dans DAX ? Étant donné que, bien que le contexte de filtre puisse être appliqué plus facilement en ajoutant des étiquettes de colonne et de ligne et des segments dans un tableau croisé dynamique, le contexte de filtre peut également être appliqué dans une formule DAX en définissant un filtre à l’aide de fonctions telles que ALL, RELATED, FILTER, CALCULATE, par relations et par d’autres mesures et colonnes. Par exemple, examinons la formule suivante dans une mesure nommée StoreSales :

Visiblement, cette formule est plus complexe que certaines des autres formules que vous avez déjà vues. Toutefois, pour mieux comprendre cette formule, nous pouvons la décomposer, comme nous l’avons fait avec d’autres formules.

Cette formule contient les éléments de syntaxe suivants :

1. Nom de la mesure StoreSales, suivi d’un signe deux-points :.

2. L’opérateur de signe égal (=) indique le début de la formule.

3. La fonction CALCULATE évalue une expression, en tant qu’argument, dans un contexte modifié par les filtres spécifiés.

4. Les parenthèses () délimitent un ou plusieurs arguments.

5. Mesure [Sales] dans la même table qu’une expression. La mesure Sales a la formule suivante : =SUM(FactSales[SalesAmount]).

6. Une virgule (,) sépare chaque filtre.

7. La colonne référencée et une valeur particulière DimChannel[ChannelName] =« Store », comme filtre.

Cette formule garantit que seules les valeurs de ventes, définies par la mesure Sales, en tant que filtre, sont calculées uniquement pour les lignes de la colonne DimChannel[ChannelName] avec la valeur « Store », en tant que filtre.

Comme vous pouvez l’imaginer, le fait de pouvoir définir un contexte de filtre dans une formule représente une fonction majeure et puissante. Pouvoir faire référence à une valeur particulière uniquement dans une table associée ne constitue qu’un exemple de ce type. Ne vous inquiétez pas si vous ne comprenez pas encore tous les tenants et aboutissants du contexte. À mesure que vous créerez vos propres formules, vous comprendrez mieux le contexte et son importance dans DAX.

Test QuickQuiz sur le contexte

1. Quels sont les deux types de contexte ?

2. Qu’est-ce que le contexte de filtre ?

3. Qu’est-ce que le contexte de ligne ?

Les réponses sont fournies à la fin de cette rubrique.