Les principes fondamentaux du DAX

Les conventions d’écriture du DAX

Dans cet ouvrage, nous respecterons les conventions suivantes :

Dans cet exemple, [Montant] est une mesure, [Libellé catégorie] est une colonne issue de la table Catégories, CALCULATE est la fonction :

CALCULATE( 
    [Montant], 
    'Catégories'[Libellé catégorie]="Littérature" 
  ) 

En ce qui concerne le nom que vous donnez à vos champs calculés :

Les mesures et les colonnes

La mesure comme la colonne permettent de créer de nouvelles données à partir des données existantes, à l’aide d’une formule DAX.

Mais la colonne est physiquement stockée dans le fichier, dont la taille est donc augmentée, ce qui n’est pas optimal du point de vue de la performance de l’application. La mesure, elle, n’est calculée qu’au moment de son utilisation, et ne prend pas de place ; elle utilise la puissance du processeur pour ses calculs.

Créer une mesure plutôt qu’une colonne présente donc trois avantages significatifs : la formule n’étant calculée qu’au moment de son utilisation, le temps d’actualisation est plus court, et le poids du fichier n’augmente pas. De plus, si la formule est utilisée dans un visuel filtré (ce qui est le plus souvent le cas), le calcul n’est effectué que pour les lignes résultants du filtrage, et par conséquent pour un ensemble de lignes réduits par rapport à la table (ou les tables) initiale(s).

Il est essentiel pour les utilisateurs d’Excel, d’acquérir le réflexe de créer des mesures plutôt que des colonnes.

Le seul cas où la création d’une colonne est recommandée, c’est celui où la donnée calculée servira à filtrer ou à créer un segment - cela concerne donc quasi-exclusivement des données texte ou date - rarement des données numériques (dans ce cas, la donnée est considérée non agrégative, et elle est traitée comme un code, ou donnée discrète).

Poussons la logique un peu plus loin : la règle est de ne jamais laisser une colonne numérique issue de la source telle quelle, mais...

Le premier script DAX : créer une table du temps avec Power BI

Après les mesures et les colonnes, il y a un troisième type d’entité que vous pouvez créer grâce au DAX : les tables. Et la table du temps en est le meilleur exemple !

Une table de dates complète et indépendante des transactions elles-mêmes (date de commande, date de facture, etc.) est un gage de qualité pour le modèle de données. L’idée est notamment de pouvoir référencer toutes les dates, y compris celles où il ne s’est rien passé (une absence de commande est en soi une information).

Cette table est le plus souvent disponible dans une base de données, mais d’autres sources peuvent ne pas en être pourvues.

Dans ce cas, il est essentiel de créer une table du temps solide, qui permettra notamment à Power BI de s’appuyer sur les fonctions Time Intelligence dont nous reparlerons plus loin.

Pour créer une table de dates :

Datum = 
VAR DebDate = DATE(2018,1,1) 
VAR FinDate = DATE(2019,12,31) 
RETURN 
ADDCOLUMNS(CALENDAR(DebDate,FinDate), 
           "Annee", FORMAT([Date],"YYYY"), 
           "Trimestre"...

Création de visuels avec Power BI

Afin de pouvoir constater et vérifier le résultat de vos formules DAX, il faut savoir créer quelques visuels simples. Cette section est destinée à vous rappeler, si besoin est, comment créer un tableau, un tableau croisé, une carte et un segment.

Créer un tableau

Il existe différentes manières de créer un visuel : je vous en propose une que j’appliquerai à chaque fois.

Pour créer le tableau :

 Dans la vue Rapport, cliquez sur Table dans le volet Visualisations.

 Cochez ensuite les cases devant les champs que vous souhaitez ajouter au tableau.

 Intervertissez au besoin l’ordre des colonnes dans le puits Valeurs.

Le volet Visualisations, le puits Valeurs et les champs cochés

La table résultant des manipulations

Créer un tableau croisé

L’avantage du tableau croisé est qu’il est très compact, et permet donc de faire figurer beaucoup d’informations sur la page.

 Cliquez sur le visuel Matrice dans le volet Visualisations.

 Dans le volet Champs, cochez ensuite les cases devant les champs que vous souhaitez ajouter.

 Intervertissez au besoin les champs entre le puits Lignes et le puits Colonnes.

La table résultat :

Créer une carte

La carte est un visuel particulier qui permet d’afficher une valeur globale, ou unique.

 Cliquez sur le visuel Carte dans le volet Visualisations.

 Dans le volet Champs, cochez ensuite la case devant la mesure que vous souhaitez ajouter.

La carte obtenue :

Création de visuels avec Excel et Power Pivot

Afin de pouvoir constater et vérifier le résultat de vos formules DAX, il faut les afficher dans un tableau croisé dynamique. Cette section est destinée à vous rappeler, si besoin est, comment créer ce tableau, ainsi qu’un segment, pour filtrer le tableau et vérifier l’impact sur les mesures.

Créer un tableau croisé dynamique

 Cliquez sur l’onglet Insertion dans Excel.

 Cliquez sur le bouton Tableau croisé dynamique (à la gauche du ruban).

 Validez la fenêtre Créer un tableau croisé dynamique.

 Dans le volet Champs de tableau croisé dynamique, glissez les champs de la partie supérieure vers les zones Colonnes, Lignes et Valeurs.

Le tableau croisé obtenu :

Pour réafficher le volet Champs de tableau croisé dynamique, cliquez n’importe où dans le tableau.

Créer une carte (donnée unique)

Pour vérifier la valeur d’une mesure, en tant que donnée isolée, vous pouvez créer un tableau croisé dynamique et ajouter une mesure dans la zone Valeurs, tout simplement :

Créer un segment

 Cliquez sur l’onglet Insertion dans Excel.

 Cliquez sur le bouton Segment du groupe Filtres :

 Dans la fenêtre Connexions existantes, affichez l’onglet Modèle de données et double cliquez sur Tables dans le modèle de données de classeur.

 Dans la fenêtre Insérer des segments, cochez la case du champ avec lequel vous souhaitez filtrer, puis validez.

 Attention : pour affecter le segment à un ou plusieurs tableaux, faites un clic droit sur le segment et choisissez le menu Connexions de rapport.

Le segment filtre sur cet exemple sur la catégorie Art

Si vous souhaitez effacer le filtre...

Le contexte de filtre et le contexte de ligne

Le contexte d’évaluation, qui regroupe le contexte de filtre et le contexte de ligne, est comme je l’indiquais dans l’introduction, le concept central du DAX. C’est aussi un sujet passionnant !

Dans ce chapitre, je vais donc rappeler les bases, évoquer la propagation du filtre et vous proposer un exemple de formule complexe, que je commenterai au regard de l’évolution du contexte. En effet, comprendre les différents états du contexte dans la formule que vous écrivez est capital.

Le contexte de ligne

Il y a deux types de contextes dans Power BI : le contexte de filtre et le contexte de ligne, qui ont tous deux pour effet de filtrer le résultat du calcul demandé. Les deux contextes peuvent s’appliquer isolément ou simultanément.

Le contexte de ligne existe uniquement lors de la création d’une colonne ou lors de l’utilisation d’une fonction itérative (SUMX, AVERAGEX, COUNTX, etc.., ainsi que la fonction FILTER).

De ce point de vue, il est très pratique, lorsque vous utilisez une fonction, de savoir ou de vérifier si elle est de type itératif, et si elle implique un contexte de ligne. La page www.dax.guide, malgré l’anglais, est à ce titre très utile :

La fonction FILTER est un itérateur qui travaille dans un contexte de ligne (row context)

Lors de la création d’une colonne dans une table de 100 lignes, le calcul sera effectué 100 fois, avec un contexte de ligne différent à chaque fois.

Lors de la création d’une mesure, par exemple dans la formule de la mesure montant :

montant facture = 
SUMX( ...

La fonction CALCULATE

Maintenant que vous avez une bonne compréhension du contexte de filtre (implicite), il est temps d’introduire CALCULATE.

Car en effet, CALCULATE est LA fonction DAX par excellence, dans le sens où elle permet de « jouer » avec le contexte de filtre, en précisant explicitement quel contexte appliquer. 

CALCULATE ( <Expression> [ , <Filtre1> ] [ , <Filtre2> ] [ , ...]) 

CALCULATE( 
    [qté] , 
    produits[couleur] = "Pourpre" 
) 

Les trois façons de modifier le contexte de filtre (remplacer, ajouter, supprimer)

Prenons comme base le visuel suivant. Aucun segment ou autre visuel ne le filtre. Le contexte de filtre est donc défini exclusivement par la colonne couleur :

Rappel : ici, la mesure montant facture est définie par la formule :

montant facture = 
SUMX( 
    transactions , 
    [qté] * RELATED(produits[prix unitaire]) 
) 

CALCULATE peut opérer trois interventions :

• Remplacer le contexte de filtre : lorsque le filtre explicite donné comme argument de CALCULATE porte sur la même colonne que celle qui définit le contexte implicite (celui que génère le rapport), CALCULATE...

L’éditeur de DAX de Power BI

L’éditeur de formule fourmille de raccourcis-clavier permettant d’accélérer la saisie ou de faciliter la correction des formules DAX.

Avec un minimum de pratique, vous gagnerez un temps précieux pour remettre en forme, corriger à plusieurs endroits en même temps, intervertir les lignes, etc.

Voici quelques-uns de ces raccourcis.

Les cinq raccourcis-clavier les plus utiles :

Manipuler les lignes :

Saisir du texte :

Exercices

Pour cet ensemble d’exercices, utilisez les fichiers chap3_librairie.pbix (ou chap3_librairie.xlsx) et chap3_or.pbix (ou chap3_or.xlsx).

Corrigés

Les mesures, les colonnes

Créer des mesures dans le fichier librairie

 Créez la mesure qté qui est simplement la somme de la colonne quantité :

qté = SUM('Détail des commandes'[Quantité]) 

C’est la plus basique des mesures, mais elle est très courante. Dans de rares cas, ce n’est pas la somme qui est utilisée mais la moyenne (AVERAGE), le minimum (MIN) ou le maximum (MAX).

montant fait appel à deux colonnes, il faut donc recourir à SUMX :

 Créez la mesure suivante :

montant = 
SUMX( 
    'Détail des commandes' , 
    'Détail des commandes'[Quantité] 
    * 'Détail des commandes'[PV] 
) 

Attention, dans Power Pivot (Excel), pensez à changer la virgule en point-virgule.

Créer des mesures dans le fichier or

 Créez la mesure vol qui est simplement la somme de la colonne volume :

vol = SUM(CoursOr[Volume]) 

 En ce qui concerne le format de cette mesure, activez le séparateur de milliers pour mieux lire le chiffre (élevé). Il s’agit du bouton dans Power BI et de l’option Utilisez le séparateur de milliers() dans Power Pivot.