Le langage DAX pour Power Pivot Excel Approfondissement Partie 1

Le langage DAX pour Power Pivot Excel Approfondissement Partie 1.

On peut suivre les différentes manipulations décrites dans les exemples de cette section en consultant

 l a v i déo YOUTUBE acce ssi bl e à l ’ ai de du Q R C ode ci -après et de son smartphone.

Video 13 : https://youtu.be/GmgjAtdLdYQ

[pic 1]

Lien du fichier à télécharger :

https://e1.pcloud.link/publink/show?code=kZDDdRZAxFT32nM4rQNEJRWHSwgM4PWliQV

Comment fonctionne la recherche dans le langage DAX .

La fonction RELATED retourne une valeur unique à partir d'une autre table qui est associée par une relation du modèle de données à la valeur actuelle dans la table actuelle.

Dans un exemple précédent on a vu que dans la table Facturier, qui sont les lignes de factures, pour déterminer le montant de vente de la ligne de facture il fallait aller chercher le prix de vente qui se trouve dans la table Articles, afin de pouvoir le multiplier avec la quantité vendue.

En effet dans un modèle relationnel on évite les redondances de données et donc on ne répète pas à

plusieurs endroits et plusieurs fois la même donnée, ici le prix de vente de l’article.

Exemple utilisé.

Pour cet approfondissement du langage DAX de Excel Power Pivot nous allons utiliser l’exemple du classeur Tableau de Bord Ventes.xlsx, dont le cube Power Pivot regroupe les données des différents classeurs :

• Tab_Articles.xlsx la liste des articles vendus, la table articles.
• Tab_Clients.xlsx la liste des clients, la table clients.
• Tab_commerciaux.xlsx, le fichier des commerciaux.
• Tab_DateFacture.xlsx, l’en-tête des factures, la table facture.
• Tab_Facturier.xlsx, les lignes de facture, la table facturier.
• Tab_LignesFactures.xlsx historique des lignes de factures soit la table Ventes du cube.
• Tab_StatVentes.

Pour appeler le Cube il faut ouvrir le classeur Tableau de Bord Ventes.xlsx et cliquer sur :

Power Pivot , puis Gérer.

La fonction RELATEDTABLE.

Lorsque l’on utilise RECHERCHEV dans Excel, en principe seule la première valeur trouvée est renvoyée par la fonction. Ce qui pose un problème car il peut y avoir plusieurs fois la valeur qui répond au critère. La fonction RELATEDTABLE suit une relation existante et retourne une table qui contient toutes les lignes correspondantes de la table spécifiée.

Dans l’industrie on a coutume de travailler la comptabilité industrielle (analytique, budgétaire, …) en

termes de coût standard par exemple.

Si on désire chiffrer les ventes réalisées en coût standard dans la table Articles, on va se rendre dans la table Articles et rechercher dans la table Facturier les quantités vendues et il est évident dans ce cas de figure que certaines lignes de la tables Article ne comporteront pas de données en retour, puisque on ne vend pas la totalité des articles que l’on a en stock pour une période.

Alors que dans l’exemple précédent (voir plus haut) on utilisait la table des lignes de facture, la table Facturier, pour laquelle on est sûr que toutes les lignes de cette table on un correspondant dans la table Articles, car bien sûr on ne vend pas un article non référencé.

[pic 2]

Pour réaliser cette manipulation, on se rend sur la table Articles, on clique sur[pic 3]

on se place sur la première ligne de la nouvelle colonne, on appelle le symbole des fonctions ∫𝑥       et on écrit la formule :[pic 4]

[pic 5]

En suivant les propositions de la fonction. On donne comme titre de colonne Valorisation Coût STD.

Retour sur les Colonnes Calculées et Mesures en DAX : AVERAGE ou AVERAGEX?

Comment déterminer si on doit utiliser une colonne calculée ou une mesure ?

Nous allons utiliser l’exemple en cours Tableau de Bord Ventes.xlsx en intégrant une nouvelle table : Tab_StatVentes qui provient du classeur Excel du même nom. Ce sont des statistiques de ventes d’articles sur deux ans environ.

On rappelle qu’il faut utiliser l’élément menu : Obtenir des données externes A partir d’autres sources

pour intégrer cette nouvelle table.

Mesures :

Ce calcul est effectué à l’aide d’une expression DAX, pas nécessairement au niveau des lignes, mais plutôt pour une colonne de la table, comme dans Excel lorsque l’on fait un calcul avec une fonction en bas d’une colonne.

Les mesures sont créées dans une table, mais nous pouvons ensuite les déplacer de façon à les regrouper.

Calculons trois mesures, soit la moyenne de chaque mois de janvier de la table Tab_StatVentes. Plaçons-nous dans la zone de calcul et sous la colonne de chaque mois de janvier, on écrit les formules :

• Moyenne Janvier 2020:=AVERAGE([janv#-20])[pic 6]
• Moyenne Janvier 2021:=AVERAGE('StatVentes'[janv#-21])
• Moyenne Janvier 2022:=AVERAGE('StatVentes'[janv#-22])

Afin de visualiser plus facilement les valeurs de ces moyennes sans être obligé d’agrandir et de réduire les colonnes, il est possible de les regrouper dans un TCD que l’on va construire dans la feuille Excel de départ qui permet d’ouvrir le cube de Power Pivot.

Dans cette feuille qui au départ se nomme feuil1 on insère un TCD et l’on retrouve dans les champs à sélectionner nos trois valeurs de moyenne que l’on peut placer dans la zone Σ Valeurs et les positionner en ligne ou en colonne, en déplaçant le champ Σ Valeurs en lignes ou en colonnes.

Colonnes calculées :

La colonne calculée créée par l’expression DAX s’ajoute physiquement à la table dans le modèle de

données, au lieu d’être recherchée dans la source de données.

Le calcul effectué se propage automatiquement dans toutes les cellules de la colonne et ce calcul est effectué au niveau des lignes de la table. Il faut être vigilant dans certains cas avec les résultats.

Nous allons essayer de calculer une moyenne pour ces mois de janvier, mais là au niveau des lignes, donc en créant une colonne calculée. Pour ce faire, dans la table Tab_StatVentes, on se positionne dans une nouvelle colonne en cliquant sur Ajouter une colonne , on se place sur la première ligne de la nouvelle colonne, on appelle le symbole des fonctions (fx) et on écrit la formule :[pic 7]