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 fondations sur lesquelles vous allez pouvoir bâtir 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.

Nous aborderons CALCULATE, nous évoquerons des modèles de formule, nous entrerons dans les arcanes de la fonction, et nous verrons des exemples courants de formules invoquant CALCULATE.

Utiliser les variables pour évaluer la formule

Ce chapitre présente les variables et leurs utilisation. Il ne s’agit pas de mise en pratique.

Il n’existe pas, dans Power BI, de moteur d’analyse des formules permettant d’en arrêter l’exécution au moment où une erreur est détectée (comme c’est le cas dans Excel notamment). Pour des formules complexes, longues, ceci peut être un problème.

C’est là qu’une utilisation astucieuse des variables peut vous aider.

Les variables sont déclarées, et exécutées, au début du script. Elles sont couramment utilisées pour n’exécuter un calcul qu’une fois, même si celui-ci est appelé plusieurs fois dans la formule. Leur première utilité est donc d’améliorer la performance de la formule.

Leur deuxième raison d’être est d’améliorer la lisibilité de la formule.

Leur troisième intérêt est d’effectuer un calcul sur l’état du contexte de filtre au début de l’exécution de la formule (en effet, le calcul n’étant effectué qu’une fois, la valeur de la variable est figée pendant toute la durée de la formule). Ce point, important, est développé à la fin de cette section.

Pour illustrer ce point, nous utiliserons un exemple proposé par Marco Russo dans un tutoriel de Guy In A Cube : https://www.youtube.com/watch?v=9SV2VnYbgg4

Nous allons commencer par voir les trois états de mise en forme de la formule : d’abord sans mise en forme, puis avec, et enfin avec l’introduction des variables.

Cette formule retourne une liste de couleurs dont le nombre varie en fonction du choix fait par l’utilisateur à l’aide d’un segment Couleurs_seg.

Premier état :

Couleurs =  
IF (  
   COUNTROWS ( DISTINCT ( 'Produit'[Couleur] ) ) > SELECTEDVALUE  
( 'Couleurs_seg'[Nombre de Couleurs] ),  
   CONCATENATEX ( TOPN ( SELECTEDVALUE ( 'Couleurs_seg'[Nombre de  
Couleurs] ), VALUES ( 'Produit'[Couleur] )),'Produit'[Couleur]...

Méthodologie de validation d’une formule

Une fois la mesure (ou la colonne) créée, il est essentiel de tester son résultat : cela semble un poncif, mais dans le cadre de Power BI, ce travail de validation suppose de prêter attention à un certain nombre de situations, liées essentiellement au contexte de filtre.

Le contexte de filtre :

est déterminé par le visuel où est utilisée la mesure, mais aussi par les autres visuels, ainsi que par les segments,
est différent selon le niveau de granularité (il est différent selon que vous vous trouvez sur les lignes de détail de votre tableau, sur les lignes de sous-totaux, ou au niveau du total général),
se propage naturellement dans le sens 1-à-N de la relation, et pas l’inverse.

Il est donc important de disposer d’un environnement (un rapport) qui va vous permettre de vérifier le comportement de votre mesure par rapport à l’ensemble de ces facteurs.

Le rapport devra donc contenir :

Une matrice (tableau croisé) composée au moins de trois dimensions (pour pouvoir afficher sous-totaux et total) - sauf si votre mesure est une valeur unique (dans ce cas, une carte fera l’affaire).
Des trois dimensions, les deux premières appartiennent à la même table, la troisième à une autre table.
Deux segments, dont...

CALCULATE, les principes

Toutes les formules proposées à partir de cette section sont créées à partir du fichier 04-Livres-Calculate.pbix que vous trouverez avec les fichiers exemples. Partez de celui-ci, qui est modélisé différemment du fichier des chapitres précédents.

Pour chaque formule proposée à partir de la section Les motifs avec ALL, je vous encourage vivement à :

créer une mesure en ajoutant vos initiales au nom de la mesure, sans quoi vous auriez un message vous signalant que la mesure existe déjà (elles sont en effet toutes présentes dans le fichier),
reproduire la formule,
vérifier son format d’affichage,
la tester (l’afficher) dans un tableau simple.

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.

Ce n’est d’ailleurs pas le seul rôle de CALCULATE : cette fonction est aussi en mesure de changer la manière dont le filtre se propage dans le modèle, en inversant le sens d’une relation, ou encore en activant une relation inactive. Ce point est traité dans la section Les autres motifs.

Enfin, un dernier rôle de CALCULATE, caché, mais très courant, est de provoquer une transition de contexte : vous trouverez des explications détaillées dans la section La transition de contexte.

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...

Les motifs avec ALL

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

Si vous créez les mesures dansle fichier 04-Livres-Calculate.pbix, pensez à leur donner un nm unique, en ajoutant par exemple vos initiales, sans quoi vous auriez un message vous indiquant que la mesure existe déjà.

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

Nous appellerons ici 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 nous allons donc détailler 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, nous utiliserons un 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])  
   )  
)

Vous pouvez, dans ce contexte, utiliser REMOVEFILTERS à la place de ALL.

Elle...

Les autres motifs

Retrouvez l’ensemble des exemples développés dans le fichier 04-Livres-Calculate.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 :

Je rappelle que la dernière colonne de ce tableau, le plus souvent, n’est pas affichée telle quelle, mais sert de base pour construire d’autres calculs.

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...

Les types d’analyses courantes et CALCULATE

Retrouvez l’ensemble des exemples développés dans le fichier 04-Livres-Calculate.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 (TOTALYTD, TOTALQTD, TOTALMTD, et, tout récemment, TOTALWTD, respectivement pour un cumul annuel, trimestriel, mensuel et hebdomadaire) . Mais savoir effectuer cette opération avec CALCULATE vous donne une capacité d’analyse accrue : par exemple, un cumul qui ne se réinitialiserait pas au changement d’année.

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 perpétuel, dans le sens où ce cumul n’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 : c’est le but de la variable MAX(Datum[Date]). Dans ce visuel, ce MAX est égal au dernier jour du mois.

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

ISINSCOPE permet de s’assurer que le calcul n’apparaît que là où il est pertinent (c’est-à-dire, pas dans la ligne de total).

Enfin, un filtre sur le visuel, Active = true, permet...

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, 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...

Maîtriser CALCULATE