La fonction CALCULATE est un exemple parfait de l’efficacité et la complexité du langage DAX. Elle est utilisée par quasiment tous les utilisateurs de Power BI mais est rarement comprise, ce qui provoque des comportements imprévisibles dans des modèles de données trop intriqués.
Dans cet article, nous allons expliquer quelques concepts fondamentaux de DAX afin de mieux comprendre comment cette fonction opère. Cependant, nous allons simplifier certains détails pour ne pas parasiter une explication déjà laborieuse. Donc nous ne mentionnerons pas EARLIER() ou d’autres fonctions qui permettent de contourner ou de manipuler les contextes mais seulement l’essentiel.
Différents contextes pour effectuer votre calcul DAX
Chaque calcul effectué en DAX possède un contexte associé. Il détermine quelles données sont visibles et comment elles sont filtrées ou calculées dans une formule. Le contexte est essentiel pour obtenir des résultats précis et pertinents dans des rapports et des analyses.
Nous allons en comparer deux, avec deux exemples :
Contexte de ligne
Tous les calculs sont faits relativement à la ligne de la table utilisée. Typiquement, dans une colonne calculée, on appliquera une formule à chaque ligne, relativement à celle-ci. Par exemple, la multiplication du prix d’un produit et de la quantité vendue donnera à chaque ligne le montant total.
Mais par exemple, mettons que l’on demande une somme par ligne de toutes les lignes d’une table (par un SUM). On obtiendra la somme totale de toutes les lignes de la table à l’identique à chaque ligne.
Le moteur de ce concept est « d’itérer » le même calcul sur plusieurs lignes d’une même table. Il se trouve que dans ce premier exemple, à chaque itération, exactement le même calcul est effectué car la table entière ne change pas d’une ligne à l’autre.
Contexte de filtre
Désormais, tous les calculs sont faits relativement à _toutes les données du modèle_ filtrées par le contexte. Ces filtres peuvent provenir d’une visualisation, d’une page ou d’un rapport. Le reste des données ne sont pas accessibles.
Si on demande une somme de toutes les lignes d’une table et qu’on l’applique ligne par ligne, en contexte de filtre on obtiendra une somme totale… de la ligne ! Car le reste a été « filtré » pour n’isoler que les données qui nous intéressent.
La transition de contexte
Alors, quelle est la différence entre le premier exemple et le second, celui en contexte de ligne et celui en contexte de filtre ? La différence est justement la fonction CALCULATE.
En réalité, par « contexte de filtre » il faut comprendre « contexte sur lequel on a appliqué des filtres » et CALCULATE permet de partir d’un contexte de ligne que l’on « filtre » afin d’obtenir un contexte équivalent. C’est ce qu’on appelle la transition de contexte.
Ainsi, dans le second exemple, plutôt que de créer une colonne calculée qui fait la somme de toute la table, on encapsule cette somme dans une fonction CALCULATE. Cela permet d’isoler chaque ligne par des filtres à chaque itération du calcul. Ainsi, on obtient la somme totale… de chaque ligne.
C’est notamment grâce à ce fonctionnement particulier que seule la fonction CALCULATE peut être utilisée pour créer une mesure, car elle s’affranchit de la position dans une table; ou, si elle en est dépendante, cette position est traduite par des filtres.
Attention !
Ces filtres sont appliqués de sorte à isoler la ligne exactement identique à celle qui était au centre du contexte par ligne. Cela signifie que si 2 lignes de la table sont exactement identiques, alors la transition de contexte voudra isoler une seule ligne mais ces 2 resteront.
On peut éviter cet écueil en incluant une clé principale à la table (un nombre différent pour chaque ligne permettant de les identifier) puisque même si les données sont identiques, la clé ne le sera pas, et la ligne ne sera pas dupliquée.
En résumé
Car il s’agit de concepts difficiles à articuler.
- Nous pouvons créer une colonne calculée pour appliquer une formule, mais la formule sera appliquée à toute la table et seulement cette table. Il s’agit d’un contexte de ligne.
- En encapsulant cette formule dans la fonction CALCULATE, on peut atteindre toutes les lignes du modèle en relation avec la ligne d’origine et seulement celles-ci. Il s’agit d’un contexte de filtre.
- Le passage d’un contexte de ligne à filtre est fait… en filtrant les données. Il s’agit d’une transition de contexte.
Mais cette application parait tout de même limitée. Et si la formule que l’on cherche à appliquer concerne aussi d’autres lignes de la table, comme une catégorie bien spécifique ? Et si, pour un cas particulier, on cherche à utiliser la fonction CALCULATE pour atteindre toutes les tables du modèle, mais sans exclure le reste de la table du calcul ?
L’expression et les filtres de CALCULATE
Chaque fonction CALCULATE est composée de deux éléments :
- Une expression qui sera évaluée à chaque fois que CALCULATE sera utilisé
- Une série de « filtres » qui définissent ce qui sera validé malgré le contexte de transition
La marche à suivre de DAX est :
- Effectuer la transition de contexte (si nécessaire) et en sauvegarder le résultat
- Évaluer les différents filtres et en sauvegarder le résultat
- Appliquer la transition puis les filtres
- Appliquer la formule à toutes les données restantes
Oui, cela signifie que les filtres « écrasent » ceux créés par la transition de contexte. Par conséquent, des lignes non inclues dans la transition de contexte peuvent le devenir. Également, des lignes inclues peuvent en être exclues après l’application des filtres !
Par exemple, considérons cette formule :
CALCULATE(
SUM(‘Products'[Quantity] * ‘Products'[Price]),
‘Products'[Category] == « Food »
)
Ici, la transition de contexte, va isoler la ligne correspondante à l’aide de filtres dans un premier temps.
Ensuite, toutes les lignes de la table vont être évaluées à la lumière du filtre. Si l’évaluation renvoie un vrai, ici que la catégorie du produit est « nourriture« , alors la ligne est ajoutée au contexte.
Il est à noter que si la ligne « d’origine » n’est pas de la catégorie « nourriture« , elle sera évaluée fausse et donc retirée du contexte !
Enfin, la formule est appliquée aux colonnes « Quantity » et « Price » des lignes restantes.
Plusieurs filtres peuvent être appliqués et ils seront évalués à rebours, du dernier au premier filtre exprimé.
Il existe aussi plusieurs filtres « spéciaux » dont le but est de modifier plus globalement le contexte.
En voici quelques uns :
- ALL() supprime tous les filtres du contexte
- ALLEXCEPT() supprime tous les filtres sauf ceux précisés
- ALLSELECTED() retourne une table contenant toutes les valeurs sélectionnées par l’utilisateur par des visuels
- RELATED() retourne une valeur associée à partir d’une table liée dans le modèle
- CROSSFILTER() modifie la direction d’une relation entre deux tables uniques pour ce calcul
Par conséquent, plusieurs fonctions CALCULATE imbriquées les unes dans les autres, chacune manipulant le contexte et le modèle, permettent quasiment tout type de calcul sur tout type de modèle de données quelque soit sa complexité.
C’est donc une fonctionnalité puissante et centrale de tout rapport Power BI et comprendre son fonctionnement profond peut vous économiser des heures de travail de fourmi à retracer ses effets à travers un rapport.
Si vous souhaitez avoir plus d’informations sur la fonction CALCULATE de Power BI, n’hésitez pas à contacter nos experts !