Objectif

Ce quatrième chapitre permet de se familiariser avec le fonctionnement particulier du langage DAX.

Ses principales fonctions y seront abordées à travers différents exercices d’application.

En fin de chapitre, nous aborderons la génération d’expressions DAX grâce à Copilot.

Les fichiers relatifs à ces exemples sont disponibles en téléchargement.

Il est par contre nécessaire de faire les exercices dans l’ordre proposé car ils constituent une progression logique.

Le langage DAX et l’évolution des tableaux croisés dynamiques

Des millions d’utilisateurs d’Excel sont habitués à utiliser des formules pour effectuer des calculs. Ces opérations peuvent aller de la simple addition à des simulations financières ou scientifiques complexes.

Toutefois, dans tous les cas, ces formules sont construites à l’aide d’une combinaison d’opérateurs de base et de fonctions Excel placés au niveau d’une ou de plusieurs cellules de calcul.

Un changement de paradigme : tableur vs modèle de données

Avec Power Pivot, Excel n’est plus seulement un tableur. Comme nous l’avons vu dans le chapitre précédent, l’application possède désormais certaines des fonctionnalités d’une base de données relationnelle.

Le langage DAX a été développé en 2009 par l’équipe SQL Server Analysis Services de Microsoft dans le cadre d’un projet nommé Gemini. Dès sa conception, il s’agissait donc plutôt d’un outil destiné à travailler avec un modèle de données et non pas avec un tableur.

Le langage a ensuite été intégré dans la première version du complément Power Pivot pour Excel 2010.

Depuis, grâce aux succès des outils de Business Intelligence...

Le langage DAX par la pratique : présentation des données

Tout au long de cette section, nous allons utiliser les données du fichier Analyse_commerciale.xlsx.

Les données retracent les ventes de la société Elena sur les exercices 2023 et 2024. La société Elena est une entreprise commerciale spécialisée dans la vente de produits textiles : pulls, bonnets, vestes, gants.

La société comporte quatre magasins situés à Lyon, Mulhouse, Strasbourg et Dijon.

Depuis 2023, la société a investi dans de nombreuses campagnes publicitaires et il serait intéressant d’analyser l’impact de ses investissements sur l’évolution du chiffre d’affaires.

La résolution des différents exemples que nous allons aborder se trouve dans le fichier Analyse_commerciale_résolu.xlsx.

Les données sont décomposées en quatre tables :

La table T_Produits retrace les différents produits de la société, il s’agit d’une table de description. La clé primaire de cette table est N° Produit.

La table T_Magasins retrace les différents magasins de la société Elena, il s’agit d’une table de description. La clé primaire de cette table est N°_Magasin.

La table T_Clients retrace les différents clients de la société...

Préparation du modèle de données

Mise en place d’une table de calendrier

Une table de calendrier (appelée aussi table de date) est une table contenant une liste de dates ainsi que divers attributs de ces dates.

Cette table sera utile pour suivre l’évolution des ventes dans le temps. Elle sera notamment nécessaire pour utiliser les fonctions d’intelligence temporelles du langage DAX.

Nous aborderons les fonctions d’intelligence temporelles à la fin de ce chapitre.

La clé primaire de la table calendrier sera la date. Chaque colonne sera un attribut de cette date (mois, année, etc.).

Ouvrez le fichier Analyse_commerciale.xlsx.

Dans le ruban d’Excel, cliquez sur l’onglet Power Pivot, puis cliquez sur le bouton Gérer.

La fenêtre de Power Pivot s’ouvre.

Dans le ruban de Power Pivot, sélectionnez l’onglet Conception puis dans le groupe Calendriers, cliquez sur Table de dates puis sur Nouveau.

Une nouvelle table nommée Calendrier apparaît, elle retrace toutes les dates du 01/01/1950 au 31/12/2024 mais aussi, le Mois, le Numéro du mois, l’Année, le Jour de la semaine et le Numéro du jour de la semaine.

Cependant, les données des ventes de la société vont du 01/01/2023 au 31/12/2024.

Nous allons donc modifier la table pour supprimer les dates antérieures au 01/01/2023....

Les principes fondamentaux du langage DAX

Il existe deux manières d’utiliser le langage DAX dans Power Pivot :

créer des colonnes calculées,
créer des mesures (aussi appelées champs calculés).

Les colonnes calculées

Les colonnes calculées permettent d’ajouter de nouvelles colonnes à l’intérieur d’une table à l’aide de formules.

Elles sont établies au niveau de chaque ligne de la table et les valeurs sont stockées en mémoire dans le modèle de données.

Les colonnes calculées prennent donc en compte le contexte de chacune des lignes de la table.

D’expérience, les utilisateurs d’Excel ont tendance à vouloir créer de nombreuses colonnes calculées afin d’avoir un maximum d’informations dans une seule grande table. Cependant, les colonnes calculées sont stockées dans la table et cela consomme de la mémoire. Si les données deviennent trop importantes, des problèmes de lenteur peuvent apparaître.

En règle générale, n’utilisez pas de colonnes calculées pour agréger des données. Pour effectuer cette opération, utilisez plutôt une mesure, comme nous le verrons par la suite dans cette section.

Exemple de colonnes calculées : création d’une colonne conditionnelle

Nous allons créer une colonne conditionnelle au niveau de la table Ventes :

Si la quantité commandée est supérieure à 3, la valeur renvoyée sera grosse commande, sinon, la valeur renvoyée sera petite commande.

Dans Power Pivot, onglet Accueil - groupe Affichage, cliquez sur le bouton Vue de données puis sélectionnez la table Ventes.

Activez la dernière colonne de la table (Ajouter une colonne).

Dans la barre de formule, saisissez =IF.

L’outil de complétion de formule IntelliSense s’active. Il permet une aide à la saisie.

Double cliquez sur IF.

Une parenthèse ouvrante apparaît dans la barre de formule ainsi que la syntaxe de la fonction IF.

Premier argument

Le test logique se fera sur la colonne Quantité.

Saisissez Qu.

À nouveau, intelliSense s’active, double cliquez sur le champ Ventes[Quantité]...

Les principales fonctions spécifiques au langage DAX

Certaines fonctions du langage DAX ressemblent aux fonctions d’Excel. On y retrouve certaines des fonctions statistiques, de manipulation de texte, conditionnelles, etc.

Pour vous faire une idée, allez dans l’onglet Conception de la fenêtre Power Pivot.

Dans le groupe Calculs, cliquez sur le bouton Insérer une fonction.

La boîte de dialogue Insérer une fonction apparaît à l’écran :

Parcourez la liste des fonctions disponibles.

Cependant, en langage DAX, les calculs se font toujours sur des colonnes ou des tables entières et fonctionnent dans le cadre d’un modèle de données.

C’est cette dernière spécificité qui fait que certaines des fonctions sont propres au langage DAX, dans cette section nous allons aborder certaines d’entre elles.

La fonction RELATED

La fonction RELATED fonctionne de manière semblable à la fonction RECHERCHEV d’Excel.

Elle utilise les relations définies par le modèle de données, les clés primaires et étrangères pour rechercher les valeurs d’une table dans une nouvelle colonne de la table active.

Sa syntaxe est :

Comme cette fonction nécessite un contexte de ligne, elle ne peut être utilisée que dans le cadre d’une colonne calculée.

Exemple : mise en place de la colonne Prix de vente unitaire dans la table Ventes

Les données relatives au prix de vente par produit se trouvent dans la table Produits :

Nous allons utiliser la fonction RELATED pour créer la colonne Prix de vente unitaire dans la table Ventes.

La recherche va se faire grâce à la clé primaire N°_Produit de la table Produits qui est aussi une clé étrangère de la table Ventes.

Dans la fenêtre Power Pivot, sélectionnez l’onglet Ventes.

Sélectionnez la dernière colonne de la table (Ajouter une colonne).

Dans la barre de formule, saisissez la formule suivante :

=RELATED(Produits[Prix de vente])

Appuyez sur la touche Entrée.

Renommez la colonne Prix de vente unitaire.

Le résultat est le suivant :

Excel a recherché le prix de vente unitaire pour chacune des lignes de la table Ventes.

Les fonctions logiques...

L’utilisation des variables en langage DAX

Dans le but de rendre le code plus lisible, en DAX comme dans la plupart des langages informatique, il est possible d’utiliser des variables.

Vous retrouverez la résolution de cet exercice dans l’onglet Variables du fichier Analyse_commerciale_résolu.xlsx.

Imaginons que nous souhaitons retrouver le chiffre d’affaires des clients suisse de plus de 40 ans par catégorie de produit.

En DAX, il est possible d’écrire :

=CALCULATE([CA];  
    Clients[Pays] = "Suisse";  
    Clients[age] > 40  
    )

La fonction CALCULATE a été utilisée pour filtrer la mesure CA selon deux critères : Clients[Pays] = "Suisse" et Clients[age] > 40.

Créez un nouveau tableau croisé dynamique à partir du modèle de données.

Placez le champ Catégorie de la table Produits dans la zone Lignes.

Créez une nouvelle mesure, affectez-la à la table Ventes et nommez-la CA_suisse_et_age_sup_40.

Dans la liste Catégorie, sélectionnez Nombre, réduisez les Décimales à 0 et cochez la case Utilisez le séparateur de milliers ().

Le résultat est le suivant :

À présent, en utilisant des variables, la même formule...

Les fonctions d’intelligence temporelle

Les fonctions DAX d’intelligence temporelle (Time Intelligence) permettent de réaliser des calculs et des analyses qui répondent à un besoin fréquent en business intelligence : manipuler ou comparer des données selon un critère temporel.

Il sera ainsi possible à partir d’un tableau croisé dynamique, de comparer des données sur plusieurs périodes et de mettre en lumière des tendances afin d’établir des prévisions.

Le langage DAX offre un grand nombre de fonctions d’intelligence temporelle.

Dans le cadre de cet ouvrage, nous nous limiterons aux fonctions les plus utilisées.

En pratique, il y a deux grands types d’opérations utilisant ces fonctions :

La comparaison entre périodes.
Le calcul d’un total cumulé selon un intervalle précis : un total cumulé par mois, par trimestre, etc.

Si les fonctions d’intelligence temporelle permettent d’exécuter facilement des tâches qui étaient laborieuses à réaliser auparavant avec Excel, elles demandent un certain nombre de conditions afin de fonctionner correctement :

Le modèle de données doit contenir une table de dates (notre table Calendrier).
La clé primaire de la table de dates doit contenir un ensemble de dates contiguës couvrant chacun des jours de la période analysée.
Chacune des dates doit exister une et une seule fois dans la table de dates.
Vous ne pouvez pas ignorer de dates (par exemple, vous ne pouvez pas ignorer les week-ends ou les jours fériés).

La comparaison entre périodes

Pour réaliser cette opération, nous allons utiliser la fonction SAMEPERIODLASTYEAR.

Comme son nom l’indique, cette fonction permet de réaliser une comparaison sur une période par rapport à la même période de l’année précédente.

La syntaxe de la fonction est : =SAMEPERIODLASTYEAR(Dates)

Application : comparaison du chiffre d’affaire de l’année par rapport...

Les indicateurs de performance clés (KPI)

Un indicateur de performance (ou KPI pour Key Performance Indicator) est une mesure ou un ensemble de mesures portant sur un aspect critique de la performance globale d’une organisation.

Son but est de donner une idée rapide et claire d’une situation afin d’aider les décideurs.

D’une manière générale, les KPI sont des indicateurs visuels dont le but est de déterminer si un objectif a été atteint ou non.

Power Pivot permet de mettre en place des KPI à l’intérieur d’un tableau croisé dynamique.

Mise en place d’un KPI à partir d’une mesure

La société Elena a fait de gros investissements publicitaires afin de mieux faire connaître ses produits. La responsable commerciale souhaiterait savoir si ces investissements ont été fructueux ou non. L’augmentation de chiffre d’affaires 2024 escompté devrait se situer entre +10 % et +20 % par rapport à l’exercice 2023.

L’idéal serait de pouvoir ventiler l’évolution du chiffre d’affaires par ville et par produit afin d’effectuer une analyse fine.

La résolution de ce cas pratique se trouve dans l’onglet KPI du fichier Analyse_commerciale_résolu.xlsx.

Première étape : mise en place du tableau croisé dynamique

Créez un rapport de tableau croisé dynamique vierge à partir du modèle de données dans une nouvelle feuille de calcul.

Dans le volet Champs de tableau croisé dynamique, dans la table Calendrier, cliquez sur Plus de champs, faites glisser le champ Année dans la zone...

Génération de code DAX avec Copilot

Dans cette section, nous allons aborder comment Copilot peut faciliter la création de code DAX efficace et personnalisé tout en réduisant les risques de fautes de syntaxe. Pour cela, nous allons nous appuyer sur un exemple concret.

Présentation des données

L’exemple traite des données de satisfaction client de la société Elena. Ces données sont essentielles pour l’entreprise. Elles reflètent directement la perception des clients sur la qualité des produits et des services offerts. Récemment, la société Elena à chercher à optimiser sa stratégie de relation client, examinons si ces actions ont été efficaces.

Les données de l’exemple se trouvent dans le fichier Dax_Copilot.xlsx. La résolution de ce cas pratique se trouve dans le fichier Dax_Copilot_résolu.xlsx.

Les données sont organisées en deux tables :

La table T_Score affiche la date, le nom et le score de satisfaction donné par le client.

Dans Power Pivot, la table Calendar est une table de dates. Elle permet de situer chaque score dans le temps, offrant ainsi une dimension temporelle indispensable à l’analyse des évolutions.

Toujours dans Power Pivot, une relation a été créée entre les tables T_Score et Calendar à partir du champ Date.

Paramétrage de Copilot

Pour commencer, nous allons configurer Copilot pour Microsoft 365 afin de vérifier qu’il intègre bien notre contexte.

Référencer le fichier

Pour que Copilot puisse prendre connaissance des données, nous allons commencer par référencer le fichier.

Enregistrez le fichier Dax_Copilot.xlsx dans One Drive.

Invite :

Prends connaissance de ce fichier Excel Dax_Copilot.xlsx

Exemple de réponse de Copilot :

Je suis ravi de vous aider avec le fichier Excel "Dax_Copilot.xlsx". Selon les informations que j’ai, le fichier "Dax_Copilot.xlsx" contient des données sur des scores de satisfaction client.

Améliorer la compréhension du fichier par Copilot