Niveau: intermédiaire
J'ai écrit pas mal d'articles contenant des références intégrées aux requêtes DAX et aux fonctions de table DAX dans le passé. Aujourd'hui, j'écris un article qui présente le langage de requête DAX à partir de zéro afin que vous puissiez apprendre à l'utiliser et comment il vous aidera à devenir une superstar de Power BI.
Qu'est-ce qu'un langage de requête?
Un langage de requête est un langage de programmation qui permet d'envoyer une requête à une base de données puis de recevoir des données sous forme de tableau. Si vous n'avez que quelques centaines de lignes de données, ce n'est pas trop utile. Et, d'un autre côté, contient des millions de lignes de données, ça devient très utile.
La différence la plus importante entre DAX lorsqu'il est utilisé dans des mesures et des colonnes calculées et DAX en tant que langage de requête est que le langage de requête renvoie toujours une table. Le tableau résultant sera composé d'une ou plusieurs colonnes, aucune ou plusieurs lignes et données dans aucune ou plusieurs cellules du tableau.
Pourquoi ai-je besoin d'un langage de requête?
Lorsque vous créez un modèle de données dans Power BI ou Power Pivot pour Excel, vous chargez essentiellement des données tabulaires dans une base de données de rapports dédiée à des fins expresses d'analyse et / ou des rapports. Une fois chargé, toi (l'auteur du rapport) vous passez généralement des heures à créer le modèle de données afin qu'il puisse être utilisé pour ajouter de la valeur à votre entreprise / travail. Finalement, une fois tout cela fait, la façon la plus courante d'utiliser votre nouveau modèle de données consiste à créer des rapports et des tableaux croisés dynamiques qui présentent les données à un utilisateur d'une manière facile à comprendre.
Ensuite, montre un exemple de rapport Power BI interactif qui s'exécute à partir d'un modèle de données Power BI.
Et voici un tableau croisé dynamique exécuté sur un modèle de données Excel Power Pivot.
Les deux visualisations ci-dessus sont des résumés des données sous-jacentes et dans les deux cas en réalité, ne peut voir aucune des données sous-jacentes elle-même, juste le résumé. Les outils de visualisation ci-dessus sont excellents et extrêmement utiles. cependant, ce ne sont généralement pas les meilleurs outils si vous voulez voir les données sous-jacentes elles-mêmes ou si vous voulez extraire de très grands ensembles de données pour une raison quelconque.
Une fois que vous avez investi tous ces efforts dans la construction de votre modèle de données, voudra l'utiliser au maximum. Power Pivot et Power BI peuvent gérer plusieurs millions de lignes de données (contrairement à Excel traditionnel) Oui, donc, la “voie ancienne” de simplement regarder la table sous-jacente n'est pas vraiment pratique dans ce nouveau monde courageux.
Montrez-moi les données sous-jacentes!
Si vous prenez le tableau croisé dynamique que j'ai montré ci-dessus, la plupart des utilisateurs d'Excel savent qu'ils peuvent double-cliquer sur n'importe quelle cellule de valeur (disons le 31,6% représente les ventes de vélos de montagne pour les personnes entre 30 Oui 40 âge). Lorsque vous double-cliquez sur un tableau croisé dynamique traditionnel comme celui-ci, quelque chose de magique arrive: une copie des données sous-jacentes est extraite de la source et apparaît comme par magie dans une nouvelle feuille dans Excel. Une fois que vous avez terminé de regarder la copie des données, vous pouvez supprimer la nouvelle feuille et vous serez de retour à l'endroit où vous avez commencé: la vie est belle.
Lorsque vous double-cliquez sur un tableau croisé dynamique traditionnel comme celui-ci, vous envoyez effectivement une requête à la base de données sous-jacente et la réponse à la requête est une table de données.
Mais est-ce que ça marche avec Power Pivot?
Finalement, oui et non selon ce que vous recherchez. L'expérience est différente si vous disposez d'un modèle de données Excel Power Pivot ou si vous êtes connecté à une source de données SSAS externe. Dans mon expérience, les résultats sont au mieux incohérents et, dans le pire des cas, ils peuvent ne pas livrer ce que vous attendez. Mais la bonne nouvelle est qu'avec DAX comme langage de requête, vous pouvez facilement écrire n'importe quelle requête et extraire exactement le sous-ensemble de données dont vous avez besoin. Vous n'avez plus besoin de double-cliquer sur votre tableau croisé dynamique pour interroger vos données.
Il est temps d'apprendre DAX comme langage de requête
Bien, assez parlé, il est temps de parler sérieusement de DAX en tant que langage de requête. Vous avez investi du temps et des efforts dans la création de votre modèle de données dans Power BI ou Power Pivot et maintenant, avec raison, vous souhaitez extraire un sous-ensemble de vos données afin de pouvoir les consulter. Commençons.
Sélectionnez d'abord l'outil approprié
Il y a 3 approches générales que vous pouvez adopter pour extraire un tableau de données de votre fabuleux modèle de données.
- Utilisez un outil dédié comme DAX Studio ou SQL Server Management Studio. J'ai écrit un article décrivant comment installer et utiliser DAX Studio ici.
- Écrire un “nouveau tableau” et Power BI Desktop. j'ai écrit un article sur l'utilisation de cette fonctionnalité ici.
- Utiliser un “tour de table” d'Excel j'ai appris de Marco Russo à extraire un tableau directement dans Excel. Je couvrirai cette astuce plus tard dans cette série d'articles..
Je vais utiliser DAX Studio dans cet article de blog, mais vous pouvez utiliser Power BI "New Table" si vous voulez continuer et n'avez pas (ou tu veux) Studio DAX. En réalité, si tu veux apprendre ces choses, alors je devrais toujours utiliser l'une ou l'autre approche. Dans mon expérience, vous ne pouvez pas apprendre de nouveaux concepts simplement en lisant. Cependant, notez que l'utilisation de Power BI et Excel comme outil de requête doit être considérée comme une extraction temporaire des données. Une fois que vous avez regardé les données que vous voulez, Je supprimerais normalement la table pour que vous ne finissiez pas par stocker des données redondantes (vous pouvez toujours les extraire à nouveau si vous en avez besoin).
Commençons par la déclaration d'évaluation
Toutes les requêtes DAX doivent commencer par l'instruction EVALUATE. Cependant, il y a une exception. lorsque vous utilisez le bouton “Nouveau tableau” dans Power BI pour écrire une requête, omettez simplement cette instruction EVALUATE. C'est certainement un autre exemple de “Syntaxe Sucre” où les développeurs protègent l'utilisateur de certaines des syntaxes les plus déroutantes. Si vous suivez et testez mes requêtes ci-dessous dans Power BI, omettez simplement l'instruction EVALUATE lorsque vous écrivez les requêtes. En outre, si jamais vous souhaitez déplacer votre requête DAX Studio vers une table dans Power BI, vous pouvez supprimer l'instruction EVALUATE à ce moment-là.
Connexion DAX Studio à la base de données
Quand je lance DAX Studio, Je suis invité à me connecter à ma base de données. Dans ce cas, J'ai une instance de Power BI Desktop avec un modèle de données Adventure Works s'exécutant sur mon PC et DAX Studio me donne la possibilité de m'y connecter. Vous pouvez également vous connecter aux modèles de données Excel Power Pivot et aux serveurs SSAS tabulaires.
Pour une description plus complète de l'interface utilisateur de DAX Studio, lis mon autre message que j'ai mentionné ci-dessus. Au contraire, allons-y.
Retourne une table existante
la requête dax la plus simple que vous pouvez taper consiste simplement à renvoyer une copie complète d’une table existante.
EVALUATE
Customers
Lorsque j’exécute cette requête dans DAX Studio (qui montre comme 1 ensuite) puis appuyez sur F5 pour exécuter, le tableau complet est retourné (qui montre comme 2). Un total de 18,484 lignes de données (montré comme 3).
Cela en soi n'est pas très utile car vous avez probablement déjà cette table sur un système source.
Liste des valeurs uniques
Dans DAX, il existe de nombreuses fonctions qui renvoient des tables au lieu de valeurs scalaires. J'ai couvert ce sujet en profondeur la semaine dernière, vous pouvez donc le lire ici si vous le souhaitez..
Voyons maintenant la fonction ALL.
ÉVALUER TOUS(Des produits[Catégorie])
Lorsque j'exécute cette fonction de table en tant que requête, Je peux vraiment “regarder” les résultats de la consultation. Dans ce cas, la requête renvoie une table à colonne unique composée de 4 Lignes, toutes les catégories de produits uniques.
Ensuite, voyons les VALEURS.
EVALUATE
VALUES(Des produits[Catégorie])
Dans ce cas, la fonction de table des valeurs renvoie exactement le même résultat.
Jetons maintenant un coup d’œil à DISTINCT.
Evaluate
DISTINCT(Des produits[Catégorie])
Et la même chose encore ici.
les trois fonctions de table renvoient la même table dans ce cas. En réalité, tout le monde fait des choses différentes dans différents scénarios de filtrage. C’est l’une des meilleures choses à propos des requêtes DAX et c’est que vous pouvez “inspecter” ce qui se passe avec ces fonctions de table dans différents scénarios. Plus à ce sujet plus tard.
Un tableau à deux colonnes
La fonction ALL peut prendre 1 ou plusieurs colonnes en entrée (ou un tableau). Dans l'exemple suivant, J'ai utilisé ALL pour matérialiser un tableau de toutes les combinaisons de catégories de produits et de sous-catégories de produits. Il y a 37 combinaisons uniques au total.
Un tableau filtré
La plupart des gens qui connaissent DAX connaîtront la fonctionnalité FILTER. la fonction de filtre renvoie une copie filtrée d’une table. par exemple, voir exemple ci-dessous:
EVALUATE
FILTER(Des produits, [Ventes totales] > 200000)
Il s’agit d’une requête beaucoup plus intéressante car elle renvoie une table contenant tous les produits qui se sont vendus plus de $ 200,000 dans les ventes tout le temps. Il y a 49 de ces produits.
Modifier le contexte du filtre
Lorsque vous tapez une mesure DAX ou une colonne calculée, il n’y a qu’une seule façon de changer le contexte du filtre et c’est en utilisant la fonction CALCULATE. CALCULATE renvoie une valeur scalaire après avoir appliqué des filtres. CALCULATETABLE est un frère de CALCULATE. Comme vous pouvez probablement le deviner, CALCULATETABLE renvoie une table après avoir appliqué des filtres.
cette requête suivante renvoie exactement le même résultat que la dernière requête.
EVALUATE
CALCULATETABLE(Des produits, FILTRE(Des produits, [Ventes totales] > 200000))
la principale différence avec l’utilisation de calculatetable par rapport à l’utilisation d’un filtre est que calculatetable peut renvoyer une table différente à la fonction de filtre.
EVALUATE
CALCULATETABLE(Ventes, FILTRE ( Des produits, [Ventes totales] > 200000))
La requête ci-dessus utilise la même fonction FILTER que précédemment, mais au lieu de retourner un tableau contenant les produits qui se sont vendus plus de $ 200,000, renvoie toutes les transactions de vente réelles qui ont contribué à la qualification de ces produits (voir les résultats ci-dessous).
Combiner des fonctions de table
Maintenant que vous voyez comment certaines des fonctions de table standard fonctionnent dans une requête, voyons comment les combiner dans une seule requête.
EVALUATE
CALCULATETABLE(
VALEURS(Des produits[Sous-catégorie]),
FILTRE(Des produits, [Ventes totales] > 50000)
)
Cette requête applique d'abord un filtre à tous les produits qui se sont vendus plus de $ 50,000 (ligne 4 ensuite) puis renvoie une liste de sous-catégories de produits uniques qui contiennent ces produits.
Une chose intéressante à propos des résultats de la requête précédente est que le “Casques” sont sur la liste (la seule sous-catégorie qui n'est pas pour les vélos). Pour voir quels sont les produits exacts là où ils ont été vendus, il s'agit simplement d'écrire une autre requête comme suit.
EVALUATE
CALCULATETABLE(
VALEURS(Des produits[Nom du produit]),
FILTRE(Des produits, [Ventes totales] > 50000),
Des produits[Sous-catégorie]="Casques"
)
Notez comment j'ai pu réutiliser la structure de requête d'origine pour renvoyer la liste des noms de produits avec juste une petite modification de la requête.
il y en aura plus la semaine prochaine
C'est un sujet fantastique, trop gros pour un seul article de blog. Cette semaine, j'ai introduit le sujet des requêtes DAX, J'ai montré comment les requêtes peuvent être écrites pour renvoyer des tables et comment les fonctions de table peuvent être combinées pour interroger des données. La semaine prochaine, je présenterai de nouvelles fonctions de requête spéciales qui étendent encore plus les capacités.
