Editions ENI Livres | Vidéos | e-Formations
Utiliser les variables
Rôle et déclaration des variables avec VAR
Les formules peuvent incorporer des variables : il s’agit de calculs ou données utilisés dans la formule. Plutôt que de refaire le calcul à chaque fois (ce qui est coûteux en temps, surtout sur des tables importantes), le déclarer en variable permet de ne l’effectuer qu’une seule fois et de le stocker en mémoire tout le temps de l’exécution de la formule ; d’où un premier avantage de la variable : un gain de performance.
Une variable est déclarée à l’aide des mots-clés VAR et RETURN :
VAR qtemax = MAX( [qté])
RETURN Attention, le nom de la variable ne peut pas comporter d’accent ou d’espace.
Chaque variable créée dans une formule est définie après le mot-clé VAR, souvent sur la même ligne. Elle est calculée immédiatement et peut donc servir pour les variables déclarées ensuite, elles aussi précédées de VAR. Le résultat est stocké en mémoire et ne varie pas par la suite (quelles que soient les modifications du contexte de filtre). La variable est donc… constante !
Le mot-clé RETURN clôt la séquence de déclaration de l’ensemble des variables.
Notez qu’une variable peut aussi être déclarée plus tard, à l’intérieur de la formule. Nous en verrons un exemple plus loin et nous verrons pourquoi cela peut être utile, en lien avec le contexte de filtre.
Il est important de noter qu’une variable peut être une table :
VAR GrosVolume =
FILTER(
ALL(transactions)
, transactions[quantité] > 100
) Ici, FILTER génère une table - une version...
Exemple de lisibilité facilitée et de performance améliorée
La moyenne mobile dans Power BI ou Excel est un sujet particulièrement intéressant : l’idée centrale, c’est - dans le contexte de filtre défini par l’intervalle (mettons, le jour), de créer la « vue » couvrant toute la période (de J-30 à J pour une moyenne mobile à 30 jours) pour pouvoir en faire la moyenne. C’est donc un pur exercice de modification du contexte de filtre.
L’utilisation de variables dans la formule permet d’éclaircir considérablement la lecture, et de jouer sur le moment où est calculée la variable.
Voici par exemple la formule d’une moyenne mobile à 30 jours :
La colonne Index est un numéro identifiant de manière unique chaque date de la table
Remarquez l’utilisation de la variable longueur (il suffit de changer ce paramètre pour passer à une moyenne à 50 ou 200 jours).
La variable ref donne la valeur de l’index du jour, et permet de calculer la période, c’est-à-dire la « vue » sur laquelle se base le calcul de la moyenne. Cette variable (periode) est en effet une table (FILTER). Le nombre de lignes de la table est déterminé par la différence entre ref (le jour présent dans le contexte de filtre) et la longueur ; ainsi, si nous sommes sur la ligne du 30 novembre, la période va du 1er novembre au 30 novembre.
Dans ce calcul basé sur FILTER, la variable ref est utilisée deux fois : elle parcourt une table de transactions, ce qui pourrait donc être « coûteux ». Mais dans la mesure où ref a été déclarée plus haut, le calcul n’est effectué qu’une fois, le résultat...
Correction de formules avec les variables
Il n’existe pas, dans DAX, de moteur d’analyse des formules permettant d’en arrêter l’exécution au moment où une erreur est détectée. Pour des formules complexes, longues, ceci peut être un problème.
C’est là qu’une utilisation astucieuse des variables peut vous aider.
Pour illustrer ce point, je me sers d’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], " , ") & " et plus...",
CONCATENATEX ( DISTINCT ( 'Produit'[Couleur] ), 'Produit'[Couleur], " , " )
) Vous remarquerez tout de suite que le code est très confus, donc difficile à lire, et par conséquent difficile à corriger.
Et en effet, ce code renvoie une erreur. Mais où se situe-t-elle ?
Le premier nettoyage consiste à formater le code selon les règles courantes, par exemple en le passant à la moulinette de www.daxformatter.com
Voici le même code correctement mis en forme :
Couleurs = ...Les variables et le contexte de filtre
Il est essentiel, lors de l’utilisation de variables, de bien comprendre dans quel contexte de filtre elles sont calculées.
Prenons la formule suivante, utilisée lors de la création d’une colonne. Les numéros de ligne ont été ajoutés pour une meilleure compréhension :
1 Produits[Classement] =
2 VAR PrixCourant = Produits[prix unitaire]
3 RETURN
4 COUNTROWS (
5 FILTER (
6 Produit,
7 ‘Produit'[prix unitaire] > PrixCourant
8 )
9 ) Puisque nous sommes dans le cadre de la création d’une colonne, il y a un contexte de ligne au moment où démarre l’exécution de la formule (celui de la ligne sur laquelle se trouve le curseur). C’est dans ce contexte qu’est calculée la variable (ligne 2). La valeur est placée en mémoire. Puis la formule se poursuit, jusqu’à l’apparition de la fonction FILTER. Celle-ci étant un itérateur, elle parcourt toutes les lignes de la table Produits, puis évalue la condition de la ligne 7, et écarte un certain nombre de lignes. Puis le nombre de lignes restantes est compté, ce qui donne le classement.
Ici, tout se joue sur la ligne 7 : ce que nous comparons, c’est Produits[prix unitaire] par rapport à la variable PrixCourant, c’est-à-dire Produits[prix unitaire] ! Comment se fait-il que Produits[prix unitaire] > Produits[prix unitaire] puisse retourner autre chose que 0 ?...
Déclarer une variable à l’intérieur de la formule
Voici d’abord la formule dans son ensemble, qui permet de calculer une moyenne exponentielle (ou pondérée) :
Vous remarquez à la ligne 15 la déclaration d’une variable.
SUMX est une fonction itérative. Elle va donc parcourir chaque ligne de periode (calculée une fois pour toutes au-dessus) pour calculer le cours pondéré, et introduit un contexte de ligne. À cet endroit de la formule, le contexte de filtre a changé : c’est maintenant celui qui est défini par la première ligne de la table periode).
, VAR ponderation =
longueur - (ref - prix_or_1an[Index])
RETURN Là, nous avons un évènement moins courant : une variable est définie à l’intérieur d’un calcul. La variable étant évaluée dans le contexte où elle est définie, la pondération est calculée par rapport à la ligne sur laquelle nous nous trouvons.
prix_or_1an[cours_clot] * ponderation Pour chaque ligne de la table periode, le cours est multiplié par la pondération - différente à chaque fois, puisque SUMX parcourt chaque ligne de periode.
La déclaration d’une variable (de VAR à RETURN) est considérée comme une expression (un terme qui désigne le calcul lui-même) : ainsi la variable n’est valide que dans le cadre de cette expression. Dans notre exemple, l’expression est liée à la somme - SUMX(table, expression). Une fois le SUMX terminé, la variable ponderation n’existe plus.
En résumé
Les variables sont une part indispensable de la création de formule en DAX. Elles ont quatre avantages :
-
Faciliter la lecture, et la compréhension des formules, lorsqu’elles deviennent complexes (ou longues).
-
Améliorer la performance de votre logiciel en limitant le recours aux calculs répétitifs : la variable n’est calculée qu’au moment où elle est déclarée et sa valeur est stockée en mémoire.
-
Permettre une exécution progressive de la formule : la variable retournée est celle qui est mentionnée après RETURN, pas nécessairement la dernière variable déclarée. Ceci permet d’afficher une à une chaque variable pour déceler une erreur.
-
Enfin la distinction entre le moment où est déclarée la variable et le moment où elle est utilisée permet de jouer avec le contexte de filtre : la variable est calculée dans le contexte de filtre où elle est déclarée, et ne bouge pas par la suite.
Ces différentes raisons doivent vous inciter à utiliser le plus possible les variables, et nous y aurons quant à nous recours de manière intensive dans la suite de cet ouvrage.
Exercices
Les exercices sont présentés à partir de ce point dans Power BI exclusivement, bien que la plupart des manipulations puissent aussi être exécutées dans Excel.
Pour réaliser les exercices, vous pouvez continuer sur vos propres fichiers chap3_librairie.pbix et chap3_or.pbix, ou prendre ceux que vous trouverez dans le répertoire sources, nommés chap4_librairie.pbix et chap4_or.pbix. Ces fichiers correspondent à la fin des exercices du chapitre précédent.
Attention, les formules proposées sont destinées à Power BI. Pour Excel, pensez à chaque fois à remplacer les virgules par des points-virgules.
Renforcer la lisibilité et améliorer la performance
Dans ce premier exercice, basé sur le fichier chap4_librairie.pbix, nous allons nous intéresser à l’apport des variables en termes de lisibilité et de performance : le but est de transformer la formule ci-dessous, dans laquelle les variables ne sont pas utilisées, en une formule (strictement) équivalente. Pour cela, une fois la formule améliorée, vous devrez tester le résultat dans un tableau et obtenir le même résultat qu’avec la mesure initiale.
AnnéesemaineISO =
ADDCOLUMNS(
CALENDAR(DATE(2019,1,1),DATE(2020,12,31)),
"Annee_Sem_ISO" ,
IF(
AND (WEEKNUM([Date],21) < 5, WEEKNUM([Date],2) > 50),
YEAR([Date])+1,
IF(
AND (WEEKNUM([Date],21)...Corrigés
Renforcer la lisibilité et améliorer la performance
La formule de départ était :
annéesemaineISO =
ADDCOLUMNS(
CALENDAR(DATE(2019,1,1),DATE(2020,12,31)),
"Annee_Sem_ISO" ,
IF(
AND (WEEKNUM([Date],21) < 5, WEEKNUM([Date],2) > 50),
YEAR([Date])+1,
IF(
AND (WEEKNUM([Date],21) > 50, WEEKNUM([Date],2) < 5),
YEAR([Date])-1,
YEAR([Date])
)
)
& "-W" & FORMAT(WEEKNUM([Date],21),"00")
) L’idée ici est double : renforcer la lisibilité et optimiser la formule.
Renforcer la lisibilité consiste à faire ressortir, en les plaçant en début de script, la date de début et la date de fin. Ceci permet de changer plus facilement les bornes au besoin. La troisième variable est optionnelle. Son intérêt est d’alléger l’écriture de la fonction ADDCOLUMNS, en en simplifiant le premier argument.
En ce qui concerne l’optimisation de la formule, opération qui consiste donc à n’effectuer chaque calcul qu’une seule fois, nous voyons ici la répétition à...
