5 règles en or pour déboguer une mesure en DAX

Publié le 18 juin 2020
par Sophie Marchand M.Sc., CPA, CGA, MVP
DAX

5 règles en or pour déboguer une mesure en DAX

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.

 

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.

Résultat obtenu par usager

 

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:

Données sources Summarize

 

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]

 

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. 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, 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ésultat recherché

 

Règle #4 : Valider les résultats intermédiaires

À 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:

Création de table

 

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é.

Ma solution

 


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 :

Christian Vachon
Écrit il y a 6 mois
Merci!

J'ai très apprécié cette formation. Beaucoup de petits truc ici et là.

frédéric POIRETTE
Écrit il y a 1 an
Bien conçu

toujours très bien conçu

Yoann CLOMBE
Écrit il y a 1 an
Excellent

Excellente formation Sophie, merci !

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top