Tables développées dans LuckyTemplates

Dans ce didacticiel, nous allons examiner un scénario très spécifique que vous avez certainement tous déjà rencontré en travaillant avec DAX - la table étendue . Les tableaux étendus peuvent perturber votre calcul si vous ne savez pas comment ils fonctionnent réellement. Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

Pour cet exemple, nous utilisons la base de données Contoso, qui contient la table des ventes , la table des clients , la table des dates , la table des produits , la table des catégories de produits et la table des sous-catégories de produits .

Sur le côté gauche, nous avons une relation un-à-plusieurs entre les produits, la catégorie, la sous-catégorie et les tables de ventes. Nous avons également une relation un à plusieurs entre le client et les ventes, ainsi que les dates et les ventes.

Table des matières

• Exemple 1
• Exemple #2
• Ventes rouges #1
• Ventes rouges #2
• Ventes rouges #3
• Vérification des tables développées
• Définition de tables développées
• Découpage de la colonne de catégorie et utilisation de tableaux étendus
• Expliquer les ventes rouges 2
• Expliquer les ventes rouges 3
• Utilisation de DAX Studio pour vérifier les tables étendues
• Conclusion

Exemple 1

Supposons que nous souhaitions créer un rapport à partir de cette colonne de catégorie dans la table des catégories et utiliser une mesure qui calcule le nombre de clients dans la table des clients. Nous voulons découper le nombre de la table du client par catégorie.

Si je mets un filtre sur la table des catégories, ce filtre ira à la table des sous-catégories, puis il atteindra la table des produits, et enfin, il atteindra et filtrera la table des ventes. Mais ce filtre ne pourra pas filtrer la table du client à moins que nous n'activions le filtrage bidirectionnel.

Revenons au rapport pour fixer ce calcul. Nous pouvons encapsuler ce calcul dans la fonction CALCULATE, puis écrire Sales. Nous pouvons voir que cette fois nous ne répétons pas le même nombre pour chaque cellule du visuel.

Pour vérifier si ce calcul est correct, nous pouvons importer la clé client de la table des ventes pour l'agréger, puis effectuer un comptage (distinct) par-dessus.

Vous pouvez voir que nous renvoyons la même valeur pour chaque ligne. En utilisant la table des ventes dans la fonction CALCULATE, nous avons pu corriger le calcul.

Exemple #2

Passons à l'exemple suivant. Pour celui-ci, nous voulons identifier le montant des ventes pour les produits rouges en 2007, 2008 ou 2009. Selon la sélection de la trancheuse, je veux aussi remonter d'un an.

Par exemple, si je sélectionne 2009, je souhaite déclarer les ventes rouges pour 2008. Si je sélectionne 2008, je souhaite également déclarer les ventes rouges pour 2007.

Ventes rouges #1

Le calcul de nos ventes totales est essentiellement une somme des données de ventes. Dans le contexte de ligne, nous allons multiplier la quantité par le prix net.

Créons une nouvelle mesure et nommons-la Red Sales. Nous allons écrire CALCULATE, puis Total Sales. Nous allons la table Sales pour montrer que la couleur des produits

Ensuite, nous écrirons sur les dates dans la colonne de date.

Lorsque nous apportons ce calcul dans un visuel de carte, nous obtenons un blanc.

Ventes rouges #2

Que se passe-t-il ici? Essayons de réécrire ce calcul et voyons si nous pouvons obtenir un résultat. Nous allons créer une nouvelle mesure et la nommer Red Sales 2 et utiliser la première partie du calcul pour Red Sales 1.

Nous allons lancer un autre CALCULER sur le premier CALCULER.

Apportons cette mesure dans la matrice et voyons les résultats. Si nous sélectionnons 2008, nous obtenons 51 947. Si nous sélectionnons 2009, nous obtenons 24 343. Enfin, si nous sélectionnons 2010, nous obtenons 39 724.

Ventes rouges #3

Il existe une autre manière d'écrire ce calcul. Nous allons écrire une nouvelle mesure et l'appeler Red Sales 3, puis utiliser la fonction CALCULATE.

Nous allons calculer le total des ventes et écrire que pour les produits, la couleur est égale au rouge. Utilisez ensuite la fonction SAMEPERIODLASTYEAR sur les dates.

Si nous faisons glisser la mesure #3 dans le visuel de la carte, vous pouvez voir que ces deux valeurs de carte renvoient la même valeur, ce qui est correct.

Mais il y a quelque chose qui ne va pas avec notre premier calcul, car nous renvoyons un blanc et non la bonne valeur.

Voyons ce qui se passe réellement ici. Maintenant que nous avons vu quelques calculs sur des tables étendues, comprenons la théorie qui les sous-tend.

Avant de comprendre ce qu'est une table étendue, vous devez comprendre que toutes les tables que nous avons ici sont appelées tables de base .

Alors, quand ces tables deviennent-elles des tables étendues ? Une fois que vous avez créé une relation plusieurs-à-un entre une table et une autre table, la table de base devient la table développée.

Vérification des tables développées

Mais comment pouvons-nous vérifier qu'une expansion de table se produit réellement ? Eh bien, vous pouvez utiliser le mot clé associé sur n'importe quelle table. Si vous pouvez accéder à la colonne d'un côté, vous saurez qu'une extension de table peut se produire.

Allons à la table des ventes et créons une nouvelle colonne calculée.

Supposons que nous souhaitions récupérer la couleur du produit dans la table des produits pour cette clé de produit particulière. Nous utiliserons RELATED, qui ne donnera que la liste des colonnes dans IntelliSense qui peuvent réellement être développées à partir de la table des ventes.

Nous pouvons voir que la table des clients et la table des ventes ont une relation plusieurs-à-un. Nous pouvons également voir la liste des colonnes de la table des clients, des colonnes de la table des dates et des colonnes des produits.

Lorsque nous sélectionnons Products[Color], nous pouvons créer une nouvelle colonne dans la table des ventes à l'aide du mot-clé RELATED. RELATED ne nous donne accès qu'aux colonnes de la table auxquelles la table de base peut réellement se développer.

Si nous changeons la nature de cette relation de plusieurs à un à plusieurs, ce calcul cessera de fonctionner.

Changeons la nature de cette relation en plusieurs à plusieurs. Nous pouvons voir en bas que nous obtenons un symbole d'avertissement.

Lorsque nous revenons à la table des ventes, nous voyons le message d'erreur indiquant que la colonne Products[Color] n'existe pas ou n'a de relation avec aucune table.

Dans le contexte actuel, l'avertissement que nous recevons n'est pas très convivial. Fondamentalement, cela signifie que la table des ventes ne peut pas s'étendre à la table des produits car nous ne pouvons pas accéder à une seule valeur pour cet élément de ligne particulier.

Et puisque nous utilisons la relation plusieurs à plusieurs, l'expansion de la table ne se produit pas et RELATED ne fonctionne pas.

Revenons à la vue du diagramme et corrigeons ce calcul. Nous allons changer la nature de la relation en plusieurs à un et activer cette relation pour que le calcul fonctionne.

Définition de tables développées

Avant de commencer à examiner les calculs que nous avons déjà effectués dans le rapport, réitérons la définition du tableau élargi.

Si vous avez un modèle de données avec un schéma en étoile , la table de faits s'étendra à toutes les tables du modèle de données s'il existe une relation plusieurs-à-un entre les dimensions et la table de faits.

Si vous avez un schéma en flocon de neige , la table des sous-catégories de produits et la table des catégories s'étendront jusqu'à la table de base, qui dans ce cas est la table des produits. La table des ventes est la table de base, qui s'étend à toutes les autres tables.

Derrière la scène, la table des ventes aura toutes les colonnes dans une seule table. Notez que l'expansion de table n'est qu'un concept logique, elle ne va donc pas étendre et augmenter la taille de votre modèle de données.

La table développée n'apparaît dans l'image que lorsque vous faites référence à une table (qui est la table de base) et lorsque vous avez une relation plusieurs-à-un avec d'autres tables.

Découpage de la colonne de catégorie et utilisation de tableaux étendus

Essayons de corriger les calculs que nous avons déjà effectués dans la vue du rapport. Dans cet exemple, nous découpons par la colonne de catégorie de la table de catégorie de produits, et nous essayons de compter le nombre de clients.

Alors qu'est-ce qu'on fait vraiment ici ? Nous utilisons le tableau des ventes étendu comme référence. Une fois que le contexte de filtre contient une valeur de la table des catégories, ce filtre atteindra la table des ventes à partir des produits de la sous-catégorie et directement vers les ventes.

Étant donné que les ventes sont une table développée et que nous utilisons cette référence dans la fonction CALCULATE, la table des ventes contiendra également la colonne de la table des clients. Lorsque nous appliquons un filtre sur la table des ventes, indirectement, nous filtrons également la table des clients.

Revenons à nos calculs de ventes rouges et essayons de comprendre ce qui se passe réellement. Nous allons commencer par le calcul à blanc. Si nous sélectionnons cette mesure, vous pouvez voir que nous écrivons un code imbriqué. Nous avons CALCULATE, puis Total Sales, FILTER over the sales, et SAMEPERIODLASTYEAR.

Décomposons ce calcul étape par étape. Tout d'abord, nous devons identifier le contexte de filtre externe qui existe en dehors du calcul.

Nous avons sélectionné 2008 dans le trancheur de numéro d'année civile.

À partir de ce contexte de filtre, la table des ventes sera évaluée. Le tableau des ventes ne contiendra que la ligne correspondant au numéro de l'année civile 2008 et la couleur du produit sera rouge. Nous avons ici deux filtres : un qui est créé par le contexte de filtre et un qui est créé par le filtre .

Le SAMEPERIODLASTYEAR est évalué dans le contexte de filtre où l'année est 2008. Il va recevoir une liste de dates en 2008 et il déplacera ces dates de 2008 à 2007. La table qui va être renvoyée par this ne contiendra que les dates de 2007.

Une fois ces deux opérations terminées, la fonction CALCULATE prépare le contexte de filtre et applique ces deux filtres dans le contexte de filtre. Lorsque nous appliquons cela, nous avons un contexte de filtre dans lequel la couleur du produit est égale au rouge, et sur la colonne de l'année, nous avons un filtre sur 2007 et 2008.

Ainsi, pour ce modèle de données, aucune transaction unique n'existe au cours de deux années différentes. Lorsque CALCULATE essaie de fusionner ces deux filtres dans une condition et , il indique que l'année doit être 2008 et 2007 et que la couleur du produit doit être rouge.

Lorsque nous filtrons la table des ventes avec le contexte de filtre de l'année 2008, nous filtrons également indirectement la table des dates. L'écurie des ventes a une relation de plusieurs à un avec l'écurie des dates.

Créons un nouveau calcul pour identifier le nombre de lignes dans la table des dates pour les dates associées. Nous allons écrire CALCULATE, puis de la table des dates. Ensuite, nous allons FILTRER TOUT le tableau des ventes, et nous allons dire que les dates LIÉES dans le numéro de l'année civile doivent être égales à 2008.

Dans CALCULATE, nous n'avons référencé aucun filtre sur la table des dates. Nous vérifions simplement que les dates dans le numéro de l'année civile doivent être à partir de 2008. Idéalement, ce filtre ne devrait pas pouvoir filtrer le tableau des dates. Nous utilisons également la fonction , qui ignorera le contexte de filtre qui viendra du slicer.

Créons une nouvelle carte pour ce calcul. vous pouvez voir que nous renvoyons 348 lignes.

Alors, comment est-il possible qu'à partir d'une table de dates de 2500 lignes, nous ne renvoyions que 348 lignes ? Si nous allons utiliser une table développée, nous allons également filtrer indirectement l'autre table, qui est connectée via une relation plusieurs-à-un.

Bien que nous n'ayons aucun filtre sur l'année en cours, nous limitons toujours le nombre de lignes visibles pour la table de dimension que nous avons d'un côté.

Lorsque nous renvoyons le tableau des ventes, nous renvoyons également une version filtrée du tableau des dates, du tableau des clients, du tableau des produits, du tableau des catégories de produits et du tableau des sous-catégories de produits.

Expliquer les ventes rouges 2

Passons au calcul suivant, qui est Red Sales 2. Nous commencerons par le CALCULATE externe car si nous imbriquons cette fonction dans n'importe quel scénario, le CALCULATE externe doit préparer le contexte de filtre pour le CALCULATE interne.

Sur le segment, nous sélectionnons l'année civile 2008. Lorsque la fonction SAMEPERIODLASTYEAR obtient la date de 2008, elle va décaler ces dates en 2007 et ce sera le contexte FILTER pour le calcul interne. Ce CALCUL intérieur évaluera l'année 2007.

La fonction FILTER filtrera la table des ventes, et la table des ventes ne sera limitée que pour la ligne où l'année est 2007. Une fois que nous aurons les lignes pour 2007, nous allons vérifier que les produits associés sont égaux au rouge.

Contrairement au premier calcul, nous ne renvoyons pas deux niveaux différents. Ces deux niveaux différents ne sont pas appliqués sur le contexte FILTER, de sorte que la table renvoyée par la fonction FILTER contiendra toutes les lignes de la table des ventes.

Une fois ce filtre appliqué, nous allons obtenir le montant des ventes de l'année 2007, ainsi que les produits qui sont égaux au rouge, alors que dans le premier calcul, nous retournions les années 2008 et 2007.

Cette fois, nous avons préparé le contexte de filtre pour la table des ventes en utilisant la fonction SAMEPERIODLASTYEAR et en imbriquant la fonction CALCULATE dans une autre CALCULATE.

Expliquer les ventes rouges 3

Pour le troisième exemple, nous avons une fonction CALCULATE très simple avec deux instructions : la première est que la couleur du produit est égale au rouge, puis la SAMEPERIODLASTYEAR sur les dates.

La fonction SAMEPERIODLASTYEAR sera évaluée dans un contexte de filtre, qui ne contiendra que les dates de 2007. La couleur des produits appliquera un contexte de filtre rouge sur la table des produits, qui à son tour filtrera la table des ventes et ne contiendra que les lignes pour les produits rouges.

Lorsque ces deux valeurs sont appliquées dans le contexte du filtre, elles vont filtrer la table des ventes mais pas la table des ventes étendue. vous pouvez voir que nulle part dans le code nous n'avons fait référence à la table des ventes étendue.

C'est le scénario idéal que vous devez utiliser si vous essayez de référencer la table développée. Lors de l'utilisation du tableau développé, les calculs peuvent devenir vraiment alambiqués et parfois, vous n'êtes pas en mesure d'identifier pourquoi vos calculs renvoient un résultat incorrect.

Si vous ne comprenez pas le concept de tables étendues, vous pouvez développer ce code particulier en tant que mesure modèle, puis le déployer en production. D'autres utilisateurs peuvent commencer à créer d'autres mesures sur cette mesure, et ils peuvent ne pas être en mesure de comprendre pourquoi les calculs ne fonctionnent pas, car ils ne possèdent pas simplement ce niveau de compréhension du langage DAX.

Si vous travaillez avec DAX, essayez toujours de placer le filtre sur une seule colonne, car lorsque vous utilisez une seule colonne, le concept de tables étendues n'est pas appliqué. Par exemple, lorsque vous utilisez PRODUCTS [Color] = "Red" , le filtre atteint la table des ventes, mais ce filtre ne peut pas atteindre la table des clients car nous n'utilisons pas la table des ventes étendue.

Lors de l'utilisation d'une seule colonne, nous ne faisons pas référence au concept de tableaux étendus. Nous appliquons simplement un filtre de base sur la table des ventes, qui ne se propage pas aux autres tables.

Utilisation de DAX Studio pour vérifier les tables étendues

Avant de conclure, nous allons utiliser le studio DAX pour vérifier notre code pour Red Sales 3. Allons dans l'onglet Affichage, cliquez sur Performance Analyzer, puis démarrez l'enregistrement. Nous allons actualiser ce visuel et sélectionner une carte vierge.

A partir des outils externes, nous allons lancer DAX studio

Nous pouvons voir que dans l'écran des résultats en bas, nous obtenons un blanc.

Nous allons activer les synchronisations de serveur pour comprendre les requêtes générées en arrière-plan. Nous allons accéder à l'onglet Server Timings et le développer pour tout voir.

Nous effectuons un JOIN sur la table des ventes sur le côté gauche. Nous effectuons également un JOIN sur la clé de produit de la table des produits.

Dans la clause WHERE, il est indiqué que les dates du numéro de l'année civile doivent être égales à 2008 et que la couleur du produit doit être rouge.

Ensuite, nous avons une autre condition qui indique Dates [Date], puis suivie d'une plage de dates. Quelles sont donc ces dates ? Idéalement, ces dates doivent appartenir à l'année 2008, mais elles datent de 2007 lorsque l'on change la mise en forme de ces nombres dans Excel.

C'est pourquoi nous renvoyons une valeur vide. Nous disons que l'année civile doit être 2008 et appliquons une condition ET que les dates doivent être en 2007. Cette année 2007 est due à la fonction SAMEPERIODLASTYEAR que nous avons utilisée dans le calcul.

Modifions la mesure et la référence à Red Sales 2. Une fois que nous avons exécuté le code, vous pouvez voir que nous obtenons les dates du tableau des dates, puis nous filtrons la colonne des dates pour le numéro de l'année civile 2008.

Cette fois-ci, nous sommes en mesure de renvoyer un résultat car nous n'appliquons pas de filtre sur la clause WHERE et nous disons que les dates dans le numéro de l'année civile doivent être égales à 2008.

Où trouver le piège DAX caché
Compter les clients au fil du temps à l'aide de DISTINCTCOUNT dans LuckyTemplates
Analyse des nouveaux clients à l'aide de DAX avancé

Conclusion

J'espère que ce didacticiel vous a aidé à comprendre ce qu'est un tableau étendu et comment il peut perturber vos calculs. Les tableaux étendus ne sont pas vraiment intuitifs et ils ne sont pas non plus faciles à comprendre.

Pour plus d'informations sur les tableaux étendus, n'oubliez pas de vous abonner à la chaîne de télévision LuckyTemplates. Nous avons une énorme quantité de contenu qui sort tout le temps de moi-même et d'une gamme de créateurs de contenu, tous dédiés à l'amélioration de la façon dont vous utilisez LuckyTemplates et Power Platform.