À quoi servent les langages M et DAX

Sophie Marchand, M.Sc., CPA, CGA, MVPExperte en modélisation d’affairesSite web: lecfomasque.comCell.: 514-605-7112Courriel: [email protected]

À quoi servent les langages M et DAX

dans Power BI?


Sophie Marchand, M.Sc., CPA, CGA, MVP

Le CFO masqué

La seule MVP Excel au Québec

MVP Data Platform (Power BI)

Le titre de MVP (Most Valuable Professional) est décerné par Microsoft aux professionnels, dans le monde, qui se démarquent par leur capacité à utiliser, à vulgariser et à faire connaître les solutions de Microsoft

PRÉSENTATRICE


Sophie Marchand est leader du groupe d’usagers Montreal Modern Excel and Power BI

PRÉSENTATRICE

3

https://community.powerbi.com/t5/Montreal-Modern-Excel-and-Power/gp-p/MontrealMEPUG


LE CFO MASQUÉ

Nouveau modèle d’affaires

Services offerts aux employeurs

Services offerts aux consultants

Services offerts aux professionnels en entreprise

4

Le CFO masqué concentre désormais ses efforts à former des consultants et des professionnels en entreprise et à promouvoir leurs services auprès des employeurs.

En tant que référence francophone en modélisation financière et intelligence d’affaires avec Excel et Power BI, le CFO masqué offre désormais un programme complet de formations en ligne, en classe et en entreprise et un programme complémentaire d’accréditations.

http://www.lecfomasque.com/services/employeurs/

http://www.lecfomasque.com/services/consultants/

http://www.lecfomasque.com/services/professionnels/


LE CFO MASQUÉ

Enregistrement de webinaires passés Power BI – Un tour complet Introduction à Power BI Desktop et Power BI Service Introduction à Power Query, Power Pivot et cie Pourquoi les CFO devraient s’intéresser à Power BI? Galerie de visualisations Power BI Meilleures pratiques d’affaires avec Power Pivot Meilleures pratiques d’affaires avec Power Query Visualisations de données en mode libre-service Cartes géographiques dans Excel et Power BI

5

http://www.lecfomasque.com/power-bi/enregistrements-de-webinaires-passes/

http://www.lecfomasque.com/power-bi/enregistrements-de-webinaires-passes/


Importation de données

Transformation de données

Modélisation de données

Analyse de données

Consommation de données

Power Query Power PivotExcel ou Power

BI ServiceExcel ou Power

BI Service

Éditeur de requête de Power BI Desktop

Section relations de

Power BI Desktop

Power BI Service ou Excel

Power BI Service ou Power BI

Mobile ou Excel

INTRODUCTION

Code M Code DAX


Exploration du langage M


LE LANGAGE M

Les avantages de Power Query ou de l’éditeur de requêtes Power BI Effectuer des transformations de données complexes.

Enregistrer les étapes de transformations.

Répéter automatiquement les étapes de transformations.

Les avantages du langage M Pensez à l’enregistreur de macros dans Excel.

Pensez maintenant à l’éditeur de code VBA.

Si je veux automatiser une tâche simple, l’enregistrement de macros est suffisant mais dès que je veux automatiser une tâche un peu plus complexe, je dois maîtriser le langage VBA.

C’est la même chose pour le langage M, sauf que celui-ci est beaucoup plus facile à apprendre, à écrire, à déboguer, etc.


LE LANGAGE M

Ce qu’il faut savoir Le langage M est utilisé dans chacune des étapes de transformation d’une

requête, qu’il y ait une fonctionnalité rattachée ou non.

Le langage M s’utilise également dans les colonnes personnalisées.

Au fil des mises à jour de Power BI, certaines opérations qui devaient absolument être définies en langage M peuvent maintenant être réalisées par le biais de certaines fonctionnalités (assistant).

Toutefois, il y aura toujours des scénarios où il sera nécessaire de maîtriser le langage M.


LE LANGAGE M

DÉMO

ÉTAPES APPLIQUÉES ET COLONNES PERSONNALISÉES


LE LANGAGE M

Fonctions de conversion


LE LANGAGE M

Fonctions de conversion Text.From

Number.ToText

Date.ToText

Time.ToText

DateTime.ToText

Duration.ToText

Etc.


LE LANGAGE M

Nouveau Colonnes à partir d’exemples

Démo


LE LANGAGE M

Fonction conditionnelle par le biais d’un assistant


LE LANGAGE M



LE LANGAGE M


Fonction conditionnelle impossible par le bais d’un assistant

Démo: Classification de clients


LE LANGAGE M

Aller plus loin que les fonctions proposées par l’assistant

Le « Group by » n’a pas d’écart-type. Si on veut un écart-type, on peut faire la somme et ensuite remplacer List.Sum par List.StandardDeviationou si on veut l’item qui apparaît le plus souvent dans la liste, on peut remplacer par List.Mode.


LE LANGAGE M

Création d’une table de dates

Aujourd’hui: DateTime.Date(DateTime.LocalNow())


LE LANGAGE M

Fonctions permettant de récupérer certaines informations Excel.CurrentWorkbook()

Récupère les tables qui se trouvent dans le fichier sous-jacent

Utile pour utiliser des paramètres inscrits dans des tables dans le fichier sous-jacent dans PQ


LE LANGAGE M

Créer un paramètre et utiliser ce paramètre à l’intérieur d’une fonction


LE LANGAGE M

Débogage de formules


LE LANGAGE M



LE LANGAGE M



LE LANGAGE M



LE LANGAGE M

Calculer le nombre de jours ouvrables http://www.lecfomasque.com/power-query-trouver-le-nombre-de-jours-

ouvrables/

http://www.lecfomasque.com/power-query-trouver-le-nombre-de-jours-ouvrables/


LE LANGAGE M

Répartir des montants entre différentes dates http://www.lecfomasque.com/power-query-repartir-des-montants-entre-

differentes-dates-2-de-2/

http://www.lecfomasque.com/power-query-repartir-des-montants-entre-differentes-dates-2-de-2/


LE LANGAGE M

Processus récursifs Récupérer des données affichées sur plusieurs pages sur le web:

Exemple groupe Meetup

http://www.lecfomasque.com/power-bi-astuce-pour-analyser-les-donnees-dun-groupe-meetup/

http://www.cathyastuce.com/powerbi/power-query/694-api-meetup.html

Exemple météo historique

http://www.lecfomasque.com/power-query-importer-les-donnees-meteo-du-site-climate-weather-gc-ca/

http://www.lecfomasque.com/power-bi-astuce-pour-analyser-les-donnees-dun-groupe-meetup/

http://www.cathyastuce.com/powerbi/power-query/694-api-meetup.html

http://www.lecfomasque.com/power-query-importer-les-donnees-meteo-du-site-climate-weather-gc-ca/


LE LANGAGE M

Apprendre Power Query et/ou l’éditeur de requêtes de Power BI Exige du temps et de la formation de base

Outre le langage M, les transformations avancées exigent aussi du temps et de la formation de base

Ressources #shared : http://www.lecfomasque.com/power-query-ressources-pour-apprendre-le-code-m/

Formations en classe, en ligne, en entreprise : http://www.lecfomasque.com/formations/

Conclusion Le langage M permet donc de mieux préparer les données en les présentant dans

divers formats

Le langage DAX ingère les données préparées par le langage M et les convertit en informations utiles pour l’analyse

http://www.lecfomasque.com/power-query-ressources-pour-apprendre-le-code-m/

http://www.lecfomasque.com/formations/


Exploration du langage DAX


LE LANGAGE DAX

30

Ce qu’il faut savoir Mesures implicites vs explicites Colonnes calculées vs mesures Contexte d’évaluation L’utilisateur qui ne maîtrise pas bien le DAX peut proposer des mesures qui

risquent de ne pas avoir du tout le comportement escompté Le danger est de mettre des informations à la disposition des usagers et que

ceux-ci s’en servent pour faire des analyses et qu’ils en arrivent à des conclusions erronées


POURQUOI AVONS-NOUS BESOIN DU DAX AU JUSTE?

Exemple de tableau croisé dynamique sans DAX

LE LANGAGE DAX

31


POURQUOI AVONS-NOUS BESOIN DU DAX AU JUSTE?

Exemple de tableau croisé dynamique sans DAX

LE LANGAGE DAX

32


SUITE…

IMPORTANT: Le tableau croisé dynamique présente ces calculs en fonction de ce qui est affiché seulement dans le tableau et non pas en fonction du modèle de données derrière.

LE LANGAGE DAX

33


SUITE…

LE LANGAGE DAX

34

Exemple de tableau croisé dynamique avec DAX


SUITE…

Exemple de visualisation PBI avec mesures en DAX

LE LANGAGE DAX

35

2 mesures:• les ventes• les ventes YTD

Le langage DAX dispose d’une panoplie de fonctions appelées Time Intelligence Fuctions.


Autres raisons pour lesquelles le DAX est nécessaire:

Modèles de données rendus disponibles aux usagers Dans certains cas, seules les valeurs d’un modèle de données définies comme

mesures seront reconnues comme des valeurs par un engin de visualisation Analyse d’un modèle de données PBI dans Excel Migration d’un modèle de données dans SSAS Démo

Pour toutes ces raisons, il est pratiquement impossible de penser utiliser Power Pivot et/ou Power BI Destkop sans savoir utiliser le DAX, à moins que vous ne soyez qu’un simple consommateur de rapports que d’autres ont déjà élaborés pour vous (en utilisant judicieusement le DAX).

SUITE…

LE LANGAGE DAX

36


LE LANGAGE DAX

37

Nouveau (avril 2017): Mesures rapides

Permet aux usagers de créer des mesures, sans écrire de fonctions DAX (le code est généré automatiquement par l’assistant)

Cette fonctionnalité ne pourra jamais remplacer les connaissances d’un analyste en langage DAX

Pour en savoir davantage sur cette fonctionnalité et pour mieux comprendre pourquoi il demeure pertinent d’apprendre le langage DAX, prenez connaissance de l’article de blogue suivant: Power BI: Découvrez les nouvelles mesures rapides.

http://www.lecfomasque.com/power-bi-decouvrez-les-nouvelles-mesures-rapides/


INTRODUCTION AU LANGAGE DAX

LE LANGAGE DAX

38

Utilisation du langage DAX Mesures, colonnes et tables calculées dans Power Query et Power BI


DAX vs Excel

• Les fonctions DAX ne prennent jamais une cellule ou une plage comme référence, mais plutôt une colonne ou une table.

• Les fonctions DAX de date et heure retournent un type de données datetime. Par opposition, les fonctions de date et heure Excel retournent un entier qui représente une date sous la forme d’un numéro de série.

• Un grand nombre des nouvelles fonctions DAX retournent une table de valeurs ou effectuent des calculs basés sur une table de valeurs comme entrée. Par opposition, Excel n’a aucune fonction qui retourne une table, mais certaines fonctions peuvent utiliser des tableaux ou des plages de cellules.


LE LANGAGE DAX

39


DAX vs Excel (suite)

• DAX fournit de nouvelles fonctions de recherche, semblables aux fonctions de recherche dans Excel. Toutefois, les fonctions DAX sont plus puissantes et requièrent la création d’une relation entre les tables.

• Les données d’une colonne sont supposées être toujours du même type. Si les données ne sont pas du même type, le DAX modifie la colonne entière en type de données convenant le mieux à l’ensemble des valeurs.

• Si vous voulez personnaliser des calculs en fonction de chaque ligne, le DAX fournit des fonctions qui vous permettent d’utiliser la valeur de ligne actuelle ou une valeur associée pour effectuer des calculs qui varient selon le contexte.


LE LANGAGE DAX

40


DAX vs Excel• Relations entre les tables• Agrégations dynamiques• Modifications de contextes

Les tables et les relations requièrent le type de fonctions suivantes:• Fonctions qui naviguent à travers les tables par les relations (plus performant qu’un

recherchev)• Fonctions qui prennent des tables comme arguments (agrégation d’une table, filtre

d’une table, etc.)• Fonctions qui produisent des tables comme résultat (ce qui devient une valeur

d’entrée pour une autre fonction)


LE LANGAGE DAX

41


Les mesures d’agrégation dynamiques requièrent le type de fonctions suivantes:

• Fonctions qui identifient le contexte courant pour un calcul (détermine si un filtre est apposé)

• Fonctions qui modifient le contexte de calcul (ignore certains filtres)• Fonctions qui peuvent manipuler les données de temps (même période l’année

précédente, mois précédent, année-à-date, etc.)


LE LANGAGE DAX

42


Colonnes calculées:• Génèrent des valeurs dans une nouvelle colonne d’une table du modèle de données.• Doivent ensuite être considérées comme n’importe quelle autre colonne d’une table.• Peuvent être utilisées dans un tableau croisé dynamique, au même titre que

n’importe quelle autre colonne.• Peuvent être utilisées dans des mesures.

Mesures:• Ne sont pas définies dans une colonne d’une table.• Peuvent être utilisées dans un tableau croisé dynamique, au même titre que

n’importe quelle autre valeur.• Le résultat de la mesure est évalué lorsqu’elle est placée dans la zone Valeur du

tableau croisé dynamique, en considérant tous les filtres, et elle est évaluée pour chaque cellule.


LE LANGAGE DAX

43


LE LANGAGE DAX

44


POURQUOI NE PAS INSÉRER UNE COLONNE POUR CALCULER LES MARGES EN %?

Colonnes calculées vs mesures

LE LANGAGE DAX

45


POURQUOI NE PAS INSÉRER UNE COLONNE POUR CALCULER LES MARGES EN %?

On pourrait alors utiliser la moyenne des % mais les résultats présenteraient alors la moyenne des pourcentages au lieu du total des marges en $ sur le total des ventes en $, ce qui n’est pas la même chose.

Solution: Créer 3 mesures

LE LANGAGE DAX

46


Contexte de lignes

• La formule est calculée pour toutes les lignes de la table.• On ne doit pas mentionner sur quelle ligne aller chercher les données.• Le contexte est défini implicitement, ligne par ligne.

Contexte de filtres

• Comprend tous les filtres qui sont apposés à la base de données, avant de faire l’évaluation de l’expression.

• L’expression demeure la même mais elle est adaptée au contexte de filtres et produit donc des résultats différents, selon les filtres du contexte.

• Chacune des cellules est évaluée à la croisée des filtres, selon son expression de base.

LE LANGAGE DAX

47


Que retourne l’expression suivante dans une colonne calculée?

Ventes$ = SUM(Ventes[Ventes])

• L’expression retourne les ventes pour cette ligne, donc une valeur différente pour chaque ligne

• L’expression retourne le total des ventes pour toutes les lignes, donc la même valeur pour toutes les lignes

• L’expression retourne une erreur, il n’est pas possible d’utiliser une somme à l’intérieur d’une colonne calculée

AVANT D’ALLER PLUS LOIN

LE LANGAGE DAX

48


Quel résultat retourne l’expression suivante dans une mesure?

Marge$ = Ventes[Ventes]-Ventes[Coûts]

• L’expression fonctionne correctement, il faudrait par contre valider le résultat dans un rapport

• L’expression retourne un message d’erreur, il n’est pas possible d’écrire une telle formule

• L’expression peut être définie mais elle va générer un message d’erreur lorsqu’elle sera utilisée dans une requête ou dans un tableau croisé dynamique


LE LANGAGE DAX

49



Remarquez les deux formules ci-dessous.Ce sont les mêmes formules mais elles affichent des résultats différents à cause du contexte de lignes.

LE LANGAGE DAX

50


Conclusion

• Le langage DAX est la clé pour créer des modèles de données faciles à analyser et consommer.

• Le langage DAX est complexe et bien qu’il repose sur les mêmes bases que le langage Excel, il est à bien des égards, très différent.

• Maîtriser le langage DAX prend du temps, surtout pour les usagers Excel, qui ne sont pas habitués à écrire des formules basées non seulement sur des tables plutôt que sur des cellules mais sur des tables liées entre elles.

• Maîtriser le langage DAX nécessite de bien comprendre ce que signifie un contexte d’évaluation.


LE LANGAGE DAX

51


Recommandations Se former

Pratiquer

Se créer une banque d’exemples

Se former à nouveau

Découvrez toutes nos formations: En ligne

En classe

En entreprise

CONCLUSION

http://www.lecfomasque.com/formations/formations-en-ligne/

http://www.lecfomasque.com/formations/formations-publiques/

http://www.lecfomasque.com/formations/formations-en-entreprise/


Questions?

Data & Analytics

À quoi servent les langages M et DAX