Bon nombre de questions concernant Power BI sur le forum sont en lien avec une mauvaise compréhension des contextes d’évaluation. Si vous utilisez Power Pivot et/ou Power BI, vous n’y échapperez pas. Vous devrez absolument maîtriser les contextes d’évaluation pour écrire de bonnes mesures en DAX. Nous offrirons d’ailleurs un webinaire sur le sujet très bientôt. Par exemple, de nombreux usagers croient que le visuel de matrice présente un total qui devrait faire la somme des lignes précédentes. Ce n’est pas comme ça que fonctionne ce visuel. Cet article n’a toutefois pas pour but de mettre spécifiquement le focus sur les contextes d’évaluation mais plutôt sur une approche qui vous permettra de mieux intégrer ce concept dans l’avenir et surtout, d’arriver par vous-mêmes à créer les mesures dont vous avez besoin pour vos modèles de données, que ce soit dans Power BI Desktop ou dans Power Pivot. À tout le moins, cet article vous permettra de déboguer une mesure en DAX quand vous êtes bloqué.
Données utilisées pour illustrer la démarche proposée
Un usager a demandé sur le forum comment faire pour que la matrice ci-dessous présente la somme de 5,5 + 4,5, donc 10,0 au lieu de 5,00. C’est à partir de cet exemple qu’on expliquera comment déboguer une mesure en DAX.
Règle #1 : Étudier le modèle de données et le réajuster au besoin
La première étape que j’ai franchie avant de me lancer à la recherche d’une solution est de m’approprier le modèle de données. Dans ce cas-ci, le modèle consistait en une seule table et deux mesures. Ce fût donc assez rapide. La table utilisée était la suivante:
La première mesure utilisée était la suivante:
NB_INV = DISTINCTCOUNT ( Feuil1[INVOICE_ID] )
Et la deuxième mesure, la suivante:
DROP_SKU =
COUNT ( Feuil1[ITEM_ID] ) / [NB_INV]
Vous devez analyser de grandes quantités de données et les présenter dans des rapports et tableaux de bord, avec des indicateurs de performance pertinents ? Développez vos compétences avec nos formations en Power BI en français ou en anglais. |
|---|
Règle #2 : Comprendre le raisonnement à la base du comportement inattendu
Ici, l’usager s’attendait à voir un total de 10 et il obtient 5. Son prochain réflexe devrait donc être de comprendre l’équation qui produit ce résultat. C’est ce qu’il faut faire quand on veut déboguer une mesure en DAX. Dans ce cas-ci, voici comment on devrait raisonner ce comportement:
- Pour la ligne avec le vendeur 1, le modèle compte le nombre d’items associés au vendeur 1, , i.e. 11 et le divise par le nombre distinct de factures pour ce vendeur, i.e. 2. Résultat: 5,5.
- Pour la ligne avec le vendeur 2, le modèle compte le nombre d’items associés au vendeur 2, i.e. 9 et le divise par le nombre distinct de factures pour ce vendeur, i.e. 2. Résultat: 4,5.
- Mais pour la ligne du total, au risque de me répéter, la matrice ne fait pas la somme des deux éléments précédents, elle applique simplement la même mesure, avec la même logique mais cette fois-ci, il n’y a pas de filtre sur le vendeur (c’est ça, le contexte d’évaluation). Donc, le modèle compte le nombre d’items total, i.e. 20 et le divise par le nombre de factures distinctes au total, i.e. 4. Résultat: 5,0.
Règle #3 : Schématiser le comportement recherché
Ici, pour déboguer sa mesure, l’usager devrait revenir à la source et se détacher du langage DAX pour un moment. Qu’est-ce qu’on souhaite obtenir exactement comme résultat? Et bien, tel que défini à l’étape précédente, l’usager aimerait que Power BI ou Power Pivot compte le nombre de factures et le nombre d’items par vendeur, présente le nombre d’items par facture pour chacun des vendeurs et fasse ensuite l’addition des deux résultats obtenus. On peut donc schématiser le comportement recherché comme suit:
Règle #4 : Valider les résultats intermédiaires de la mesure en DAX
À l’étape précédente, on a déterminé que Power BI ou Power Pivot devait présenter une colonne avec le résultat de la division de valeurs contenues dans deux autres colonnes, et ce, pour chacune des lignes de la table. On a aussi déterminé qu’on voulait ensuite voir le résultat de la somme de toutes ces lignes. Du moment qu’on souhaite effectuer une opération sur toutes les lignes d’une table et appliquer ensuite une opération d’agréation sur ces lignes, nous sommes en présence d’une formule itérative (comme toutes les formules qui se terminent par X notamment). On a donc un premier élément de réponse. On aura besoin d’une fonction SUMX.
Mais ce n’est pas tout, on a aussi besoin d’une table agrégée par vendeur, sans toutefois altérer la table originale. Pour cela, on aura besoin de la fonction SUMMARIZE. Donc à cette étape-ci, on devrait d’abord tenter de générer cette table. Dans Power BI, on peut directement créer des tables en DAX. La formule serait la suivante:
=
SUMMARIZE (
Feuil1,
Feuil1[SALESMAN_ID],
“NbreFactures”, DISTINCTCOUNT ( Feuil1[INVOICE_ID] ),
“NbreItems”, COUNT ( Feuil1[ITEM_ID] )
)
Cette formule permet de créer une table qui fait un regroupement de lignes par vendeur et crée ensuite deux colonnes. Le DISTINCTCOUNT permet d’obtenir le nombre de factures différentes par vendeur et le COUNT permet d’obtenir le nombre total d’items par vendeur.
Pour tester ce résultat intermédiaire, on peut l’insérer dans une matrice et observer le tout:
Si ce n’est pas ce que l’on recherche, il faudra recommencer jusqu’à ce que l’on atteigne le résultat souhaité.
Règle #5 : Définir une formule qui répond au comportement recherché
On peut finalement imbriquer les résultats intermédiaires à l’intérieur d’une formule, dans ce cas-ci, une formule SUMX, tel que précisé plus tôt. Ainsi, on pourra écrire une formule comme la suivante:
DROP_SKU =
SUMX (
SUMMARIZE (
Feuil1,
Feuil1[SALESMAN_ID],
“NbreFactures”, DISTINCTCOUNT ( Feuil1[INVOICE_ID] ),
“NbreItems”, COUNT ( Feuil1[ITEM_ID] )
),
[NbreItems] / [NbreFactures]
)
La fonction SUMMARIZE permet de recréer la table recherchée (i.e. la table par vendeur, avec les colonnes NbreFactures et NbreItems) alors que la fonction SUMX permet de créer une colonne “virtuelle” dans laquelle on procédera d’abord à la division du nombre d’items par le nombre de factures et où on fera ensuite la somme de tous les éléments de cette colonne. Ne reste plus qu’à tester la mesure dans une matrice pour s’assurer qu’elle produit bien le résultat recherché.
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Si vous souhaitez approfondir vos connaissances en langage DAX, je vous recommande notre formation en ligne : Introduction au langage DAX (Power BI et Power Pivot)
Voici quelques commentaires d’apprenants ayant suivi cette formation :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
