Contact: 514-605-7112 / info@lecfomasque.com

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

Publié le : 18 juin 2020

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

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)


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

smarchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire