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

Publié le 18 juin 2020
par Sophie Marchand M.Sc.
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. À 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é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]

 

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.

Formations Power BI disponibles 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ésultat recherché

 

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:

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 :

Le-CFO-masque_Commentaires-Introduction-langage-DAX
CFO-Masque_Formations-en-ligne_FBLa 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é.

Laisser un commentaire

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

Retour en haut