CALCULATE() est certainement l’une des fonctions les plus puissantes et les plus utiles du langage DAX.
On peut la comparer à la fonction SUMIF() d’Excel, sans ses limitations.
Anatomie de la fonction :
=CALCULATE(<aggrégateur>, <filtre1>, <filtre2>, … )
Agrégateurs
SUM ([Colonne])
SUM ([Colonne1]) / MIN ([Colonne2])
MIN(),AVERAGE(),SUMX() …Ou une autre mesure définie [Mesure 1].
Filtres
Avec CALCULATE() nous pouvons non seulement réaliser n’importe quel calcul, mais nous pouvons appliquer autant de filtres que nous le souhaitons sur les données source !
Par exemple, créons une nouvelle mesure dans notre table ‘Gd livre’.
MesureExemple = CALCULATE(
SUM(‘Gd livre'[Solde]);’Gd livre'[C.j]= »ACH »;
‘Gd livre'[Racine]=607;
‘Gd livre'[Centre Analytique]= »Centre A »
)
Cette mesure calcule le total des soldes des écritures du journal des Achats, pour chaque compte dont la racine est 607, et uniquement pour le Centre analytique « Centre A ».
CAlCULATE() + ALL() : Du lourd !!
Nous n’avons pas jusqu’à présent rencontré encore la fonction ALL().
Son utilité est simple à décrire : ALL() neutralise tous les filtres appliqués.
Reprenons notre exemple précédent, en transformant la carte en histogramme groupé.
Maintenant glissons le champs [MoisNom] dans la case Axe.
Notre [mesure1] est maintenant « découpée » par mois, c’est-à-dire qu’en arrière-plan, un nouveau filtre s’applique sur chaque barre :
Barre du mois de Janvier : [MoisNom]= « Janvier »
Barre du mois de Février : [MoisNom]= « Février»
Etc..
Tout cela est fort pratique et représente la mécanique normale des PowerTools, mais que faire si nous ne VOULONS PAS que notre [mesure1] soit filtrée par le graphique, par exemple pour représenter un Total ?
Rajoutons un filtre ALL() à notre [mesure1].
Mesure1 = CALCULATE(
SUM(‘Gd livre'[Solde]);’Gd livre'[C.j]= »ACH »;
‘Gd livre'[Racine]=607;
‘Gd livre'[Centre Analytique]= »Centre A »;
ALL(Calendrier
)
)
Par ce biais, la mesure retourne le montant total, indépendamment des filtres appliqués par le graphique ou tout autre filtre du rapport, sur la table ‘Calendrier’.
Que nous sélectionnions l’année 2015, 2016 ou 2017, le montant mensuel retourné, sera toujours le même.
Si nous voulions neutraliser uniquement le filtre « mois »en maintenant le filtre année actif, nous devrions modifier notre mesure et utiliser ALLEXCEPT() plutôt que ALL() :
Mesure1 = CALCULATE(
SUM(‘Gd livre'[Solde]);’Gd livre'[C.j]= »ACH »;
‘Gd livre'[Racine]=607;
‘Gd livre'[Centre Analytique]=
« Centre A »;
ALLEXCEPT(Calendrier;Calendrier[Année]
)
)
ALLEXCEPT() va supprimer tous les filtres créés par le contexte, à l’exception du filtre sur le champs [année].
NB : Nous aurions pu aussi neutraliser l’ensemble des filtres mois en utilisant ALL(Calendrier[MoisCalendrier];Calendrier[MoisNom];Calendrier[MoisNombre])
Mais avouons que c’est moins esthétique.