CALCULATE et les modifications du filtre

La syntaxe

Elle est très simple :

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

Mais ceci amène à de nombreuses remarques.

CALCULATE et la transition de contexte

Tout d’abord, CALCULATE peut s’utiliser avec pour seul argument l’expression qu’il s’agit de calculer. En effet, c’est un rôle second de la fonction : lorsqu’elle est invoquée dans un contexte de ligne (c’est-à-dire lors de la création d’une colonne ou lors de son utilisation dans une fonction itérative - SUMX par exemple), la formule CALCULATE(<expression>) déclenche une transition de contexte, et la ligne dans son intégralité devient un filtre, qui se propage donc aux tables du modèle.

Ce phénomène est loin d’être rare : en fait, il est essentiel de se rappeler qu’à chaque fois qu’une mesure est appelée dans une formule, un CALCULATE implicite est systématiquement ajouté.

Prenons un exemple. La mesure [Montant facturé] est définie par la formule :

[Montant facturé] = SUM(Ventes[Montant]) 

Si l’on souhaite connaître le montant moyen facturé chaque jour, la formule s’écrit :

[Montant moyen facturé] = 
AVERAGEX ( 
  Date[Date] , 
  [Montant facturé] 
) 

Soit, en développant la formule en incluant le CALCULATE implicite :

[Montant moyen facturé] = 
AVERAGEX ( ...

Sur une colonne

Par filtre complexe, il faut comprendre un filtre portant plusieurs fois sur la même colonne, pour encadrer des dates par exemple ou encore, indiquer plusieurs catégories.

Deux cas se présentent : celui où les deux conditions doivent s’appliquer simultanément et celui où l’une ou l’autre des conditions doit s’appliquer.

Lorsque la date doit être comprise entre deux bornes, alors les conditions doivent s’appliquer simultanément. C’est ce qui se passe lorsque vous indiquez plusieurs filtres dans CALCULATE :

montant janv-mars 2019 = 
CALCULATE( 
    [montant] , 
    Datum[Date] >= DATE(2019,01,01) , 
    Datum[Date] <= DATE(2019,03,31) 
) 

Par défaut, les deux conditions dans CALCULATE sont liées par un ET (AND). La formule précédente pourrait aussi s’écrire à l’aide de l’opérateur...

FILTER

C’est une des fonctions centrales du DAX, pour les raisons évoquées ci-dessus, mais aussi parce que c’est un argument explicite ou implicite de CALCULATE.

De même, une des façons de décrire le DAX consiste à dire qu’utiliser ce langage revient à créer en permanence des tables temporaires - j’utilise le terme de « vues » - et à opérer ensuite un calcul (l’expression). FILTER est une des clés pour effectuer ce travail.

Son expression générique est :

FILTER( table, condition) 

FILTER parcourt chaque ligne de la table (c’est donc un itérateur) et, pour chaque ligne, vérifie la condition. Le résultat de l’opération est une table qui contient les mêmes colonnes que la table passée en paramètre :

Que se passe-t-il maintenant si nous souhaitons filtrer à l’aide de plusieurs conditions ? Par exemple, les produits rouges dont le prix unitaire est supérieur à 50 €.

La première tentative est un échec, et nous ne pouvons même pas terminer la formule : en effet, FILTER ne peut recevoir que deux arguments (la table et la condition).

En revanche, cette formule, basée sur AND (ou &&)...

ALL, ALLEXCEPT

ALL est une fonction primordiale. Elle a une syntaxe simple :

ALL( [<table> | <column>[, <column>[, <column>[,...]]]] ) 

Elle peut être utilisée comme fonction de table, c’est-à-dire fonction générant une table, dans des expressions DAX (par exemple une fonction itérative comme SUMX), ou comme argument d’un CALCULATE.

Toutes les syntaxes ci-dessous sont correctes :

Lorsqu’elle est utilisée dans un CALCULATE, ce qui est très souvent le cas, la fonction ALL joue le rôle de modificateur de CALCULATE, plutôt que de filtre. Cette nuance a son importance dans la mesure où les modificateurs sont pris en compte avant les filtres, dans l’ordre d’exécution du CALCULATE. Ce point est précisé un peu plus loin.

Les principes de CALCULATE

Comprendre le contexte de filtre

Dans cet exercice, il s’agit de réfléchir au contexte de filtre. Il n’y a pas de manipulation ou de fichier associé.

Imaginez deux mesures. La première est définie par :

calculate FILTER = 
CALCULATE ( 
      [montant] , 
      FILTER ( 
            produits , 
            produits[couleur] = "Rouge" 
      ) 
) 

La seconde mesure est définie par :

calculate simple = 
CALCULATE ( 
      [montant] , 
      produits[couleur] = "Rouge" 
) 

Les colonnes famille produit et couleur sont toutes deux dans la table produits.

Expliquez (en décrivant le contexte de filtre) pourquoi elles donnent le même résultat dans le premier tableau, mais un résultat différent dans le deuxième :

Les arguments de filtres complexes...

Les principes de CALCULATE

Combien de livres par catégorie ?

Attention, ici, il s’agit bien de créer une colonne.

Une erreur consiste à utiliser simplement la fonction COUNT :

COUNT(Livres[Numéro livre]) 

Rappelez-vous en effet que lors de la création d’une colonne, vous êtes dans un contexte de ligne. Celui-ci ne se propage pas, contrairement au contexte de filtre. La valeur retournée par le code précédent retourne donc toujours le même chiffre, quelle que soit la catégorie. 

Il y a deux façons de procéder : la première consiste à transformer le contexte de ligne en contexte de filtre (c’est la transition de contexte). C’est un des rôles de la fonction CALCULATE :

nb livres par catégorie = 
CALCULATE( 
    COUNT(Livres[Numéro livre]) 
) 

Vous remarquez ici que je n’ai fait qu’ajouter CALCULATE à la formule précédente.

Une autre manière de faire est d’utiliser COUNTROWS et RELATEDTABLE, puisque cette fonction entraîne elle aussi une transition de contexte :

nb livres par catégorie 2 = 
COUNTROWS( 
    RELATEDTABLE(Livres) 
) 

La table Catégorie dans la vue Données

Attention, la transition de contexte peut être une opération coûteuse lorsqu’elle est utilisée sur une grande table (par exemple la table des faits). Ici, les tables Catégories et Livres sont de taille modeste.