Power BI X DAX, découvrez toutes les possibilités offertes par ce langage
Introduction
DAX (Data Analysis Expressions) est le langage utilisé dans Power BI pour créer des formules et ainsi compléter un modèle de données pour répondre à une analyse.DAX est un ensemble de fonctions, d’opérateurs et de constantes pouvant être utilisés dans une expression pour retourner une ou plusieurs valeurs.
Ce premier article a pour objectif de poser les bases pour avoir une idée des possibilités offertes par ce langage.
Une notion importante : Colonne vs Mesure
Les fonctions DAX vont être utilisées dans les expressions de colonnes ou de mesures. Il est important de bien comprendre cette notion avant d’aller plus loin :
Attention, certaines fonctions DAX ne seront pas disponibles ou non optimisées en mode DirectQuery. En effet, en mode mémoire, c’est le moteur VertiPaq qui va interpréter les différentes fonctions DAX, mais en mode DirectQuery, les fonctions peuvent être traduites pour le moteur relationnel de destination qui ne prend pas en compte toutes les fonctions. Certaines fonctions ne sont tout simplement pas compatibles et d’autres peuvent être compatibles, mais poser des problèmes de performances en mode DirectQuery. Voici un lien pour en savoir plus sur la compatibilité des fonctions DAX entre les 2 modes d’accès aux données.
Découvrez la comptabilité des formules DAX en mode DirectQuery
Utilisation des variables
L’utilisation de variables en DAX permet une meilleure lisibilité et un développement bien plus aisé, et améliore souvent la performance de l’expression.
Considérons le besoin suivant : “Est-ce que l’on a un produit dans nos stocks qui correspond à plus de 2% de nos ventes totales en termes de revenus ?
Ci-dessous deux approches différentes pour répondre à ce besoin :
Avant le mot clé « return », on déclare nos variables et après le mot clé « return » on les utilise.
Il est ainsi plus facile de comprendre une formule complexe composée de variable. Par ailleurs, une variable peut être réutilisée indéfiniment dans la formule.
Opérations sur les dates
Pour une utilisation optimale des fonctions temporelles dans Power BI, il est recommandé d’utiliser une table « calendrier » dans notre modèle de données.
Il existe plusieurs méthodes pour rajouter cette table. Soit elle existe déjà dans notre source de données, dans ce cas nous pouvons l’utiliser, sinon il est possible grâce à la fonction DAX CALENDARAUTO() de créer cette table directement dans notre modèle de données Power BI.
Voici un exemple de script permettant la création d’une table de date complète :
Cette table doit être marquée comme table de date pour pouvoir utiliser les fonctions de dates.
Prenons un exemple avec la fonction TOTALYTD qui doit nous retourner une somme cumulée. Dans le cas ou notre table de date n’est pas marquée comme table de date nous obtenons ce résultat :
Ici, on peut constater que la colonne TOTAL_YTD contient les mêmes valeurs que notre colonne SalesAmount.
Après avoir « marqué » la table des dates, nous obtenons le résultat attendu, nous avons bien un total cumulé :
D’autres opérations sur les dates
- L’expression ci-dessous ajoute deux colonnes à notre table ‘Table_name’ qui correspondent à la première entrée de date pour chaque ligne (relation 1 à plusieurs) ainsi qu’à la date actuelle, de manière à échelonner les graphiques sur une base temporelle adaptée.
Table = ADDCOLUMNS(‘Table_name’; »DateFirst »;CALCULATE(FIRSTDATE(‘Date'[Date])); »Datenow »;CALCULATE(NOW()))
- Considérons la mesure suivante qui retourne le montant total des ventes :
Total Sales = SUM(Sales[Revenue])
Dans le but de comparer cette mesure avec les données obtenues au même moment un an auparavant, nous pouvons utiliser la fonction DAX suivante :
LY Sales = CALCULATE([Total Sales];SAMEPERIODLASTYEAR(‘Date'[Date]))
Ce type de manipulation permet d’obtenir des comparaisons année par année, comme l’illustre la visualisation suivante. Commençons par créer une mesure qui va calculer la différence entre nos 2 mesures calculées précédemment :
Sales Var = [Total Sales] – [LY Sales]
Nous obtenons le résultat suivant en traçant le total des ventes par année sur un graphique en cascade :
Power BI dispose de sa propre fonction intégrée pour ce type de manipulation. En revanche, il est tout à fait possible d’étendre cette notion de « même période l’année dernière » à n’importe quelle autre entité temporelle répertoriée dans notre table de dates. Par exemple, considérons le champ « Mois » dans notre table de dates :
LM Sales = CALCULATE([Total Sales];DATEADD(‘Date'[Date];-1;MONTH))
Nous obtenons le résultat suivant, en ciblant l’année 2014 :
Sécurité au niveau des lignes (Row Level Security)
La sécurité dans Power BI peut être géré au niveau de Power BI Services en affectant des utilisateurs dans les différents espaces de travail ou modèle de données. Mais la sécurité peut également être gérée au niveau des lignes d’une source de données. A l’image de SSAS, on va pouvoir ajouter la notion de rôle au niveau d’un rapport et y affecter utilisateurs, groupes ou listes de distribution.
Pour afficher des données en fonction des utilisateurs connectés, on va pouvoir utiliser deux fonctions DAX :
Username() : cette fonction retournera le nom d’utilisateur(domain\user-name) dans Power BI Desktop (en local). Cette fonction peut être utilisée par exemple pour récupérer le nom de domaine et/ou le nom de l’utilisateur. Sur Power BI services cette fonction retournera l’email de l’utilisateur.
UserPrincipalName() : cette fonction peut être utilisée dans une mesure ou dans la définition d’un rôle, mais ne peut pas être utilisée dans une colonne. Cette fonction retournera l’e-mail de l’utilisateur.
Bien entendu, il sera nécessaire d’avoir un modèle de données contenant des informations sur les utilisateurs pour pouvoir faire le lien avec l’utilisateur connecté.
Quelques liens utiles
Le guide complet avec toutes les fonctions
Un outil de développement de requête DAX
3 tutoriels complets
(chercher DAX dans la zone de recherche)
Blog très complet sur l’utilisation de la sécurité dans SSAS et Power BI
A propos des auteurs
Julien Larcher / Consultant BI Actinvision
Julien Oudille / Consultant BI Actinvision
