Création d’une mesure DAX dans power BI

1/ Lieu de création des mesures DAX

Pour créer une mesure DAX, on peut en créer dans une des parties suivantes de power BI :

• « affichage du rapport » (1)
• « affichage table » (2)
• « vue de modèle » (3)
• « affichage des requêtes DAX »(4)(ajouté récemment dans power BI)  

Bien que la partie « affichage des requêtes DAX » semble être la partie où il faut créer les mesures. Elle sert surtout à tester certains fonctionnalité en dax s’appliquant sur des tables ou des colonnes.

2/ DAX : création d’une table calculé via une requête DAX

2.1 /affichage des requêtes DAX et création d’une table date

Exemple suivant : « création d’une table date »

Prenons un rapport power BI vierge et allons dans la partie « affichage des requêtes DAX » et collons une requête DAX comme celle dans l’image suivante :

Cliquons sur le bouton Exécuter. En remontant la fenêtre des résultats, on obtient une table « calendrier » :

Le calendrier commence du 1/01/20217 et se termine au 31/12/2017 ce qui est représenté par la 3^ème ligne de la requêtes DAX :

Power BI a d’abord créé la colonne contenant les dates allant du 01/01/2017 au 31/12/2017 puis à créer les autres colonnes à partir de cette dernière.

Intérêt de cette méthode : l’affichage du résultat de la requête DAX permet d’adapter la requête jusqu’à l’affichage du résultat voulu.

Par contre, l’affichage du calendrier n’a créer aucune table calendrier dans les tableaux dans la partie Données (côté droit de l’image précédente)

Pour créer la table calendrier il faut copier-coller le code dans la recette DAX puis aller dans « affichage Table » et créer une nouvelle table.

Pour faire cela :

1. On va sur « affichage table »
2. On clique sur « Nouvelle Table » ce qui nous génère une table d’une seul colonne et une seul ligne vide et son nom par défaut est « table »
3. Dans la partie où il est écrit  « table = » on inscrit notre requête dax que nous avons précédemment testé sans le Evaluate puis on clique sur entrer (ou n’importe où sur le rapport du moment que c’et en dehors du cadre où est renseigné la requête DAX.
4. La requête remplace notre table vide par la table calendrier que nous avons aperçu précédemment sur « affichage requête DAX ». 

Remarque : on peut changer le nom table avant le = et le remplacer par calendrier si l’on souhaite changer le nom.

La différence avec « l’affichage de requêtes dax est que la table « Table » est vraiment incorporé au modèle de données puisqu’elle est présent dans la partie Données présentes dans la partie droite de l’image précédente. Et à chaque fois que l’on crée une nouvelle table, cette nouvelle table s’ajoute au modèle de données.

Intérêt de créer une table en DAX (ou table calculé) : les tables en DAX n’affecte pas le temps de chargement des données dans le rapport power BI, les requêtes DAX ne s’appliquent qu’après le chargement des données.

Afin d’évitez un temps long de chargement des données, il est préférable d’envisager des requêtes dax pour certaines opérations afin de diminuer ce temps.

3/ création d’une mesure DAX.

Pour créer une mesure DAX, il faut imaginer deux tables, une table visible et une table invisible.

La table visible est celle que l’on voit dans la partie « Affichage table », la table invisible est une copie de la table affichée sur laquelle la mesure DAX que l’on souhaite créer va utiliser pour faire ses opérations.

3.1/ exemple avec une table de ventes de Navision

Sur l’image ci-dessous, nous avons chargé et affiché une table sales line (et une table sales header non affiché mais présente à droite de l’image dans données) issu de la base de données cronus navision de démonstration :

Nous allons créer une mesure avec la requête dax suivante :

somme montant =SUM(‘CRONUS France S_A_$Sales Line'[Amount])

Pour cela :

aucun résultat ne s’affiche sur le tableau.

Question : qu’en est-il du résultat de notre mesure ? Comment peut-on l’afficher ?

Réponse : le résultat d’une mesure DAX ne peut être afficher que dans un visuel du rapport ou si elle est appelée pour créer une nouvelle colonne.

Voyons les deux cas :

1/ création d’un visuel

Dans la partie « Affichage rapport » cliquons sur le visuel carte et glissons et déposons notre mesure « somme montant » dans Champs dans le volet « Visualisations » comme dans l’image ci-dessous :

Mettons en dessous un autre visuel carte et plaçons la colonne « amount » (montant en anglais ) dans  champs dans le volet visualisation pour comparer :

Quand une colonne est placé dans Champs pour le visuel carte, si c’est une colonne ne contenant que des nombres, power BI applique par défaut la somme des éléments de cette colonne (peut-être modifié).

On constate que les deux sont exactement les mêmes, donc notre mesure « somme montant » renvoie bien le total de la somme de la colonne « Amount ».

2/ Colonne Calculé

Créons une colonne calculé pour comparer :

Pour cela :

• On clique sur « Nouvelle Colonne » (1)
• On renseigne dans « Colonne » la partie droite de notre mesure « somme montant »
• On clique sur la touche Entré ou n’importe où sur le rapport power BI hors le champ où est inscrit la requête DAX.

Résultat une colonne avec une valeur apparaît (3) 

On peut remarquer que le résultat de la requête DAX s’applique sur chaque ligne de la colonne et est le même que dans les visuels

Maintenant créons une nouvelle colonne et appelons notre mesure « somme montant » en entrant son nom après le =.

On constate que la « Colonne 2 » a appliqué pour chaque ligne de la table un montant différent, comparons avec la colonne « Amount »

On en déduit deux points :

1. Une colonne calculée effectue d’abord une opération puis l’affecte aux différentes lignes de la colonne dans la colonne.
2. Une mesure effectue en arrière-plan une opération sur une table invisible et stocke un résultat qui s’adapte en fonction des données présentes dans la ligne.

Si l’on reprend la requête DAX utilisé pour « somme montant » :

somme montant = SUM(‘CRONUS France S_A_$Sales Line'[Amount])

la requête DAX effectue une extraction de la colonne « Amount »  issu de la table « Cronus France S_A_$Sales Line » et la stocke en arrière-plan.

Voyons une autre façon de calculé la somme des montants :

somme montant 2 = SUMX(‘CRONUS France S_A_$Sales Line’ , ‘CRONUS France S_A_$Sales Line'[Amount])

Cette requête DAX utilise la fonction SUMX qui a besoin de deux paramètres séparés par une virgule :

• La table sur laquelle effectué l’opération
• La colonne de référence de la table contenant les valeurs à sommer

Le résultat dans un visuel ou dans une table sera le même qu’avec notre mesure « somme montant », mais elle met en avant le fait qu’une mesure dax effectue une opération sur une table en arrière-plan non visible pour l’utilisateur et de ce fait si l’on créer une colonne calculée avec notre mesure, la mesure dax effectue une superposition des valeurs de la table invisible sur la table visible. Ce qui montre le lien entre notre table Sales line et nos deux mesures dax « somme montant » et « somme montant 2 »

Remarque :  Dans notre exemple actuel, la fonction SUMX utilise une table visible par l’utilisateur, mais l’utilisateur peut dans la fonction SUMX en créer une nouvelle. (Partie suivante)

Le lien entre la table et la mesure se voit également dans les visuels du rapport. Si l’on affiche une table dans la partie « affichage rapport » contenant la liste des articles et la somme des montant :

On retrouve bien le même total que celui affiché dans nos visuels cartes et on voit que pour chaque description d’article, on a le montant associé toute période confondu.

Et si l’on veut afficher le résultat pour un article précis, on peut ajouter un segment. Ajoutons un segment qui filtre sur la « Description » de l’article « Bicyclette » :

On remarque que :

• dans notre table, il n’y a qu’une seule ligne, celle dédié à la « Bicyclette » ainsi que sa valeur « somme montant » associée.
• dans notre visuel carte, la valeur de somme montant est la même que celle présente dans la table alors que la colonne « Description » n’est pas présente. Le visuel carte ne contient que la mesure « somme montant ».

En conclusion, lors de la création d’une mesure DAX, l’utilisateur doit faire attention au lien qu’il met en place entre la table de référence utilisé (pas forcément visible dans le modèle, voir partie suivante) et sa nouvelle mesure créée.

Il est tout de même possible pour une mesure DAX de faire abstraction de son lien avec les tables voyons cela dans la partie suivante.

3.2/ les fonctionnalités DAX : Calculate, ALL , selectedvalue

Créons la mesure dax suivantes :

somme montant sans lien = CALCULATE(sum(‘CRONUS France S_A_$Sales Line'[Amount]),all(‘CRONUS France S_A_$Sales Line’))

(Cliquez sur nouvelle mesure et copier-coller le texte précédent)

Affichons le résultat de notre nouvelle mesure dans un visuel carte

On constate que la valeur affichée est la valeur total somme des montants de tous les articles. Si l’on change d’article dans notre segment :

La valeur de notre nouvelle mesure « somme montant sans lien » reste inchangé.

Il n’y a donc aucun lien entre notre mesure « somme montant sans lien » et notre table « Sales line »

  Revenons sur notre mesure DAX :

somme montant sans lien =

 CALCULATE( sum(‘CRONUS France S_A_$Sales Line'[Amount]), all(‘CRONUS France S_A_$Sales Line’) )

Notre mesure commence par CALCULATE qui est une fonctionnalité qui indique que tel calcul doit être effectué avec tel contraintes.

Dans notre cas, « somme montant sans lien » doit effectué la somme des éléments de la colonne « Amount » de la table Sales line mais sous la condition de faire le calcul sur toute la table Sales line qui est représenté par all.

Le schéma suivant récapitule :

Et si l’on veut le montant des bicyclettes, on ajoute une autre condition comme dans l’exemple suivant :

somme montant sans lien bicyclette = CALCULATE(sum(‘CRONUS France S_A_$Sales Line'[Amount]),all(‘CRONUS France S_A_$Sales Line’),’CRONUS France S_A_$Sales Line'[Description]= »Bicyclette »)

la fonction renverra la valeur des montant pour l’article bicyclette quelque soit les segments appliquées

On peut rendre dynamique la somme montant en fonction de la description de l’article uniquement :

somme montant sans lien description article = CALCULATE(sum(‘CRONUS France S_A_$Sales Line'[Amount]),all(‘CRONUS France S_A_$Sales Line’),’CRONUS France S_A_$Sales Line'[Description]=SELECTEDVALUE(‘CRONUS France S_A_$Sales Line'[Description]))

La fonctionnalité SELECTEDVALUE adapte le calcul en fonction de la valeur sélectionné dans un segment :

Dans notre cas, bien qu’une date de livraison planifié ait été sélectionné pour l’article Bicyclette, le résultat de somme montant sans lien description article est toujours le même 312 000

Si l’on choisit un autre article :

Notre mesure « somme montant sans lien description article » à la même valeur que somme montant. Si l’on sélectionne une « planned Delivery Date » :

Le résultat de notre mesure restent inchangé, elle ne dépend que du choix de la « description » de l’article.

En résumé, Pour des résultats plus poussés, il faut connaître les fonctionnalités qui permettent de se passer de certains liens, les plus utilisées étant calculate, all et selectedvalue

3.4/ Intérêt des Fonctions SUMX, MINX, MAXX

Dans la partie 2.2, nous avons vu une deux façons différentes de calculer la somme des montants.

Dans cette partie, nous allons voir comment créer une table résumer et son intérêt dans les fonctions sumx, minx, et maxx

Créons une nouvelle table comme dans l’image suivante :

Cette nouvelle table résume (fonction summarize) pour chaque article de la table sale lines :

• la 1^ère date de première livraison planifié
• la dernière date de livraison planifié
• le montant total

Cette nouvelle table calculé créer ne changera de valeur qu’après une actualisation des données et/ou une modification de la requête DAX qui l’a généré.

Si l’on génère un visuel table avec les colonnes de notre table calculé et que l’on ajoute un segment sur la colonne « Planned Delivery Date »

On constate que pour la période sélectionné, les dates de 1^ère et dernière livraison de la 1^ère ligne du tableau sont toujours présente alors qu’avec la période sélectionnée, elle aurait du disparaître.

Par ailleurs on peut également constater que le montant total est le même qu’elle que soit la période sélectionné.

Se passe-t-il la même chose avec une mesure ?

Créons une nouvelle mesure

Dans notre « mesure résumer », après le sumx, on retrouve la requête DAX qui a servi a créer notre table résumer.

Et à partir de la colonne « montant » la mesure effectue une somme.

On affiche une visuel carte avec la valeur de la mesure :

Sur toutes les dates de livraison planifié la somme total est la même.

Mais sur une autre période :

La valeur totale n’est pas la même pour la mesure et pour la table. la raison est du au fait que dans la mesure la table résumer est recalculer à chaque sélection de valeur dans un segment.

Dans notre cas, la mesure a d’abord recréé la table résumer en utilisant les lignes dont la date de livraison planifié est comprise entre le 03/05/2017 et le 24/10/2017 puis fait la somme.

Ce qui fait que les mesures DAX offre plus de dynamisme que de créer une nouvelle table qui alourdi le modèle.

4/ Conclusion

Colonne / Table calculée	Mesure DAX
Calcul effectué en arrière-plan et affecte le résultat à chaque ligne de la colonne et/ou table concerné Valeur qui ne change qu’après une actualisation des données Alourdit le modèle de données	Calcul effectué en arrière-plan et ne renvoi qu’une seule valeur visible uniquement dans un visuel et/ou dans une colonne où la mesure est appelée Valeur qui change après une actualisation des données et/ou une sélection de valeur dans un segment Difficulté de mise en place car l’utilisateur doit imaginer la table et les opérations nécessaires pour obtenir le résultat souhaité.