Introduction

Le rôle central du langage DAX dans l’analyse des données n’est plus à démontrer : aussi bien pensée que soit votre source de données, aussi bien construit que soit votre modèle, il ne s’agit que des pierres sur lesquelles vous allez pouvoir fonder vos analyses, et celles-ci seront toujours basées sur des formules DAX.

Ce chapitre est centré sur LA fonction DAX par excellence : CALCULATE.

Parce que c’est la fonction pivot, celle qui demande de maîtriser les deux notions clés du DAX, à savoir les fonctions de table et le contexte de filtre et, par conséquent de maîtriser toute une batterie d’autres fonctions considérées à juste titre comme centrales.

Et parce que derrière une apparente simplicité - après tout, CALCULATE se contente de modifier le contexte de filtre avant de calculer une expression -, se cache un mécanisme d’une richesse et parfois d’une complexité remarquables.

Après un préambule destiné à poser les bases du travail (rappel des notions clés, debugging, validation d’un calcul), nous aborderons CALCULATE, nous évoquerons des modèles de formule, nous entrerons dans les arcanes de la fonction, et nous verrons comment CALCULATE permet de réaliser des analyses courantes.

Depuis la mise à...

Préambule

Cette section traite d’un ensemble de points fondamentaux et généraux de DAX. Vous n’y trouverez donc pas d’exercices d’application, mais je vous encourage à appliquer ces concepts et règles dans toutes vos formules DAX. Il est essentiel que vous lisiez très attentivement les pages qui suivent. Et peut-être, que vous les relisiez par la suite !

Les mesures, leur format, leur nom

Pour l’essentiel, les formules doivent être utilisées dans le cadre de la création de mesures, et plus rarement pour la création de colonne.

Rappelons en effet que les mesures ne prennent pas de place dans votre modèle (n’utilise pas d’espace de stockage), et garantissent donc un document plus léger et plus performant. Rappelons que les mesures ne sont calculées qu’au moment de leur utilisation, selon le contexte dans lequel elles apparaissent, et pas pour chaque ligne de la table dans laquelle, à l’inverse, une colonne est « physiquement » créée.

Rappelons également que toute donnée numérique issue de la source et destinée à être analysée (montant des ventes, quantité, résultat de test, etc.), doit être masquée, et remplacée par une mesure équivalente ([Montant] = SUM(Ventes[Montant des ventes]). Nous verrons plus loin le lien avec CALCULATE.

Il est par ailleurs utile, lors de la création de formule, de prendre le réflexe de formater tout de suite la mesure nouvellement créée. Enfin pour ce qui concerne le nom de la mesure, évitez les lettres accentuées dans les quelques premiers caractères (afin qu’Intellisense retrouve rapidement votre mesure).

Le contexte de filtre, le contexte de ligne

Il ne s’agit pas pour nous ici de reprendre intégralement ces notions, mais simplement de rappeler que le contexte de ligne est généré automatiquement lors de la création d’une colonne par le biais d’une formule, ainsi que lors de l’utilisation d’une fonction de type itérateur (en particulier les fonctions X - SUMX, etc. - et la fonction FILTER).

Le contexte de filtre, lui, est défini par les visuels (tables, graphiques, segments) présents...

CALCULATE, les principes

Toutes les formules proposées à partir de cette section sont créées à partir du fichier chap3_CALCULATE_Base.pbix que vous trouverez avec les fichiers exemples du chapitre 3.

Pour chaque formule, je vous encourage vivement à :

créer une mesure,
reproduire la formule,
vérifier son format d’affichage,
la tester (l’afficher) dans un tableau simple.

Les formules proposées ici suivent les paramètres localisés. Si vous optez plutôt pour les paramètres recommandés, pensez à remplacer les points-virgules par des virgules.

Pourquoi, dans cet ouvrage avancé, consacrer un chapitre entier à CALCULATE ? C’est que cette fonction est au cœur du langage DAX de Power BI, et qu’elle fait appel, pour son utilisation, aux fonctions clés du langage DAX, à savoir les fonctions permettant de manipuler les tables et les fonctions permettant de manipuler le contexte de filtre.

Sur le papier, CALCULATE est simple : elle permet de calculer une expression, mettons, une somme, dans un contexte de filtre différent de celui défini par le visuel et son environnement. Dans le détail, les choses sont bien plus précises que cela, bien plus complexes dans le bon sens du terme, et la maîtrise de ces détails seule permet de bien utiliser CALCULATE.

Dans ce chapitre, nous commencerons par évoquer les principes de son fonctionnement, puis nous balayerons une série de cas - des modèles de formules que j’appelle motifs - en creusant à chaque fois les implications, pour enfin travailler sur un ensemble d’analyse courante, et leur traduction à l’aide de CALCULATE.

Cette étude approfondie de CALCULATE doit beaucoup à l’ouvrage de référence sur le DAX, The Definitive Guide to DAX, 2d edition, de Marco Russo et Alberto Ferrari.

La syntaxe

Elle est très simple :

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

Une première remarque s’impose : CALCULATE peut être utilisée avec pour seul argument l’expression qu’il s’agit de calculer. En effet, c’est un rôle secondaire de la fonction : lorsqu’elle invoquée dans un contexte de ligne...

Les motifs avec ALL

Retrouvez l’ensemble des exemples développés à partir de ce point dans le fichier chap3_CALCULATE_Livres.pbix. Je vous recommande cependant de reproduire les exemples sur votre fichier chap3_CALCULATE_Base.pbix.

Les mesures représentant des motifs se trouvent dans le répertoire _motifs de la table Global commandes.

J’appelle motif une structure de formule courante, une formule-type que vous rencontrerez dans de nombreuses situations. Connaître ces motifs permet de travailler de manière efficiente et de se concentrer sur les situations moins courantes qui demandent une analyse plus longue.

Au cœur de DAX, CALCULATE se prête particulièrement bien à ces motifs, et je vous propose donc une « bibliothèque » de cas courants basés sur cette fonction.

Vous pouvez parcourir ces motifs en identifiant visuellement le résultat que vous souhaitez atteindre, grâce aux captures d’écran qui illustrent chaque cas, ou mémoriser les motifs eux-mêmes.

Dans la plupart, voire dans tous ces exemples, je me servirai du calcul du ratio, mais ces motifs peuvent s’appliquer à d’autres types d’analyses, comme nous le verrons dans la section suivante.

#1 ratio toutes catégories

Le calcul du ratio est bien entendu un besoin fréquent. Parce qu’il repose sur deux niveaux de détail - donc deux contextes de filtres - différents, et simultanés (il faut connaître sur la même ligne à la fois le montant détaillé et le montant global), c’est par excellence un cas relevant de CALCULATE.

C’est la formule la plus simple :

#1 ratio toutes catégories =  
DIVIDE( 
   [montant] ; 
   CALCULATE( 
       [montant] ; 
       ALL('Catégories'[Catégorie de livres]) 
   ) 
)

Elle fournit le résultat suivant :

Il faut cependant noter un point important : le filtre de CALCULATE, ALL(’Catégories’[Catégorie de livres]), enlève le filtre sur la colonne Catégorie de livres. C’est donc une mesure qui n’a de sens que dans un tableau où figure cette colonne.

Il est possible d’ajouter d’autres colonnes...

Les autres motifs

Retrouvez l’ensemble des exemples développés dans le fichier chap3_CALCULATE_Livres.pbix.

Dans cette section, nous étudions d’autres motifs, de ALL ...VALUES à USERELATIONSHIP, en passant par KEEPFILTERS et ALLSELECTED.

#8 all ... values

Le motif CALCULATE(<mesure> ; ALL(table_de_faits) ; VALUES (<dimension>)) est intéressant dans la mesure où il permet d’enlever tous les filtres, internes ou externes, mais d’en restaurer un de manière explicite.

La structure du rapport peut donc évoluer, mais tant que la dimension (ou les dimensions) mentionnée dans VALUES est présente, le résultat sera correct :

Les deux tableaux n’ont pas la même structure, et pourtant la mesure continue à fonctionner, et à retourner le montant annuel. C’est un point de comparaison fixe.

En voici la formule :

#8 all ... values =  
CALCULATE( 
   [montant] ; 
   ALL('Global commandes') ; 
   VALUES(Datum[Annee]) 
)

Une formule très proche permet d’obtenir le même résultat, en s’appuyant cette fois-ci sur la fonction ALLEXCEPT(Table ; Colonnes). Cette dernière enlève tous les filtres sur la table indiquée, à l’exception de ceux portant sur les colonnes mentionnées. Remarquez que dans la formule, la colonne n’appartient pas nécessairement à la table indiquée :

#8b allexcept =  
CALCULATE( ...

Les types d’analyses courantes et CALCULATE

Retrouvez l’ensemble des exemples développés dans le fichier chap3_CALCULATE_Livres.pbix. Les mesures de type analyses courantes sont dans le répertoire du même nom dans la table Global commandes.

Après avoir vu les principes de CALCULATE, puis les motifs, abordons maintenant cette fonction par l’angle des analyses types qu’elle permet.

Les analyses types proposées sont les suivants :

Comparaison
Cumul
Tendance
Classement
Contribution
Variance (ou variation)
Pareto
Moyenne mobile

La comparaison

Comparer, c’est agréger une mesure au niveau de la catégorie à comparer, et le plus souvent classer les résultats par ordre croissant ou décroissant, par exemple, le montant par ville, ou les quantités par catégories de livres.

C’est ce que fait naturellement Power BI, et par conséquent, ici, aucun besoin de CALCULATE.

Le cumul

Le cumul est un type d’analyse tellement courant que diverses fonctions existent dans Power BI pour le calculer. Mais savoir effectuer cette opération avec CALCULATE vous donne une capacité d’analyse accrue.

Le principe au cœur de ce calcul consiste à comparer la date du contexte de filtre et l’ensemble des dates de la table du temps.

Le tableau ci-dessus établit un cumul annuel, dans le sens où ce cumul est remis à zéro à chaque changement d’année. Le mécanisme qui permet de calculer le cumul consiste à retenir toutes les dates qui sont antérieures à la date du contexte de filtre (MAX(Datum[Date])). Pour la remise à zéro, il s’agit de s’assurer que l’année ces dates est la même que l’année de la date du contexte de filtre.

La formule MAX(Datum[Date]) ne peut pas directement être utilisée dans l’argument de CALCULATE, il faut donc avoir recours à une variable.

Enfin, quelques tests de l’environnement permettent de s’assurer que le calcul n’apparaît que là où il est pertinent. Voici la formule complète :

#1 cumul annuel =  
VAR ddate = MAX(Datum[Date]) 
RETURN 
IF( 
   ISINSCOPE(Datum[Annee mois]) ; 
   IF( 
       [montant]...

La transition de contexte

Difficile de parler de CALCULATE sans évoquer la transition de contexte, c’est-à-dire cette opération qui consiste à transformer le contexte de ligne en contexte de filtre équivalent, et que seules CALCULATE et CALCULATETABLE peuvent déclencher, à condition d’être exécutées dans un contexte de ligne (lors de la création d’une colonne ou dans une fonction itérative - les fonctions X et FILTER notamment).

Dans cette courte section donc, quelques rappels, des exemples et des aspects particuliers de la transition de contexte, que vous devez garder en tête pour comprendre certains comportements de vos formules.

Propagation des contextes

Rappelons donc d’abord que le contexte de ligne ne se propage pas, et que le contexte de filtre lui se propage à l’ensemble du modèle le long des relations 1-à-N.

Ainsi, lors de la création d’une colonne dans la table Livres, une formule aussi simple que SUM(’Global commandes’[Quantité]) fournit un résultat faux :

Le fait d’être sur la première ligne de la table Livres n’a aucun impact sur les quantités retournées.

Mais voici un premier usage intéressant de CALCULATE : utilisée sans autre argument que l’expression, CALCULATE opère une transition de contexte.

Ainsi, la formule suivante donne-t-elle le bon résultat :

quantités OK =  
CALCULATE( 
   SUM('Global commandes'[Quantité]) 
)

Dans cette situation, CALCULATE est appelé dans le cadre de la création d’une colonne, et par conséquent dans un contexte de ligne : la transition de contexte va donc bien avoir lieu.

Pour chaque ligne de la table Livres, CALCULATE va donc transformer le contexte de ligne en un contexte de filtre équivalent, et ceci en posant un filtre sur toutes les colonnes de la table, le numéro, le titre, l’auteur, etc., mais aussi le prix, et même la colonne quantités NOK précédemment créée.

Ce filtre va se propager le long des relations 1-à-N, donc dans notre cas vers la table Global_commandes, et filtrer les lignes correspondantes, en s’appuyant sur le numéro. La fonction SUM ne travaillera...