Niveau: intermédiaire
Comme les lecteurs réguliers de mon blog le savent, J'aime aider les gens sur les forums qui ont besoin d'aide avec DAX, Power Query ou Power BI. J'ai récemment répondu à pas mal de questions pour les personnes qui pourraient bénéficier d'une solution DAX « Banding » et, donc, J'ai décidé d'écrire ce post en tant que lien permanent vers le sujet. J'ai découvert la technique de baguage DAX d'Alberto Ferrari à http://sqlbi.com
Que sont les bandes?
Le clustering est une technique qui permet de “Grouper” O “stratifier” vos données dans un tableau en “bandes” (parfois appelée analyse de cohorte). Disons que vous avez une table client comme celle-ci (indiqué ci-dessous). Dans mon exemple de base de données, j'ai 18.000 clients et je connais l'âge de chacun.
Cela permet d'analyser les données de vente en fonction de l'âge afin que pour savoir si des personnes d'âges différents achètent différemment. Vous trouverez ci-dessous un tableau croisé dynamique illustrant ce qui est possible avec les données. Mais le problème est qu'il y a trop de détails pour être utiles.
Je ne veux vraiment pas cibler les clients de 35 ans différemment des clients de 36 ans. Idéalement, je voudrais grouper (la stratification) aux clients dans “bandes” pouvoir traiter un groupe de clients similaires de la même manière. Pour cela, vous voudrez peut-être regrouper mes clients comme ceci.
Une fois que vous avez regroupé les clients par âge, Je peux créer un tableau croisé dynamique comme celui ci-dessous.
Notez qu'il est beaucoup plus facile de trouver des informations sur les données lorsqu'il y a moins de détails. Je peux immédiatement voir dans le tableau croisé dynamique ci-dessus qu'à mesure que les gens vieillissent, ont tendance à être plus intéressés par les vélos de montagne et de randonnée et moins par les vélos de route (bien sûr, c'est une fausse base de données, donc les idées ne sont pas forcément réalistes). .
Vous aurez besoin d'une colonne calculée pour les bandes
Je suis un ardent défenseur de l'utilisation des mesures (pas de colonnes calculées) lorsque cela est possible. Vous pouvez en savoir plus à ce sujet dans ma base de connaissances ici. Mais dans ce cas, une mesure ne fonctionnera pas. Les mesures ne peuvent être utilisées que dans la section des valeurs d'un tableau croisé dynamique – vous ne pouvez pas les utiliser dans les zones de “filtré” comme les trancheuses, Lignes, Colonnes et Filtres. Dans le tableau croisé dynamique ci-dessus, les bandes sont utilisées pour diviser le tableau croisé dynamique en lignes et, donc, une mesure ne fonctionnera pas.
Vous pouvez écrire une instruction If
Ensuite le but est de créer une colonne calculée dans la table client qui regroupe les personnes selon l'âge dans une bande stratifiée. Tout utilisateur d'Excel saurait que vous pouvez écrire une instruction IF pour cela (DAX a une syntaxe très similaire pour l'instruction IF dans Excel normal). La syntaxe DAX pour une seule instruction IF est la suivante
= SI(Les clients[Âge] <= 18, "18 et sous", "Plus de 18")
La colonne calculée DAX ci-dessus renverra l'une des deux valeurs possibles en fonction de l'âge du client. La complexité survient lorsque vous voulez avoir plusieurs conditions. Comme je l'ai mentionné plus tôt dans ce post, il y a 6 groupes possibles que j'ai en tête (montré ici encore).
Pour ce faire avec les instructions IF, vous auriez besoin d'imbriquer plusieurs instructions IF les unes dans les autres comme suit.
=
SI(
Les clients[Âge] <= 18,
"18 et sous",
SI(
Les clients[Âge] > 18
&& Les clients[Âge] <= 30,
">18 et <=30",
SI(
Les clients[Âge] > 30
&& Les clients[Âge] <= 40,
">30 et <=40",
SI(
Les clients[Âge] > 40
&& Les clients[Âge] <= 50,
">40 et <=50",
SI(Les clients[Âge] > 50 && Les clients[Âge] <= 60, ">50 et <=60", ">60" )
)
)
)
)
Voyez-vous le problème? Cette colonne calculée fonctionne, mais c'est très difficile à écrire, lire et modifier. Et ce qui est plus important, Il y a un meilleur moyen: utiliser la technique du baguage.
Processus de création de bandes
La meilleure façon implique le processus suivant.
- Créer un tableau dans Excel contenant les noms des groupes et les limites d'âge inférieures / supérieur
- Charger la table dans Power Pivot
- Ne connectez pas la table à une autre table, c'est une table déconnectée
- Écrivez une colonne calculée DAX qui compare l'âge de chaque client aux limites inférieures / en haut de votre table déconnectée et renvoyez la seule ligne de la table des bandes qui correspond à chaque client.
Voici le détail de la marche à suivre.
Créer un tableau dans Excel
Voici à quoi ressemble un tableau dans Excel.
Les principales caractéristiques à considérer sont
- Il y a une colonne ID: cela sera utilisé pour trier la colonne Bande plus tard
- Une colonne de “Bande”: c'est l'étiquette qui décrit chaque groupe. Notez que les groupes sont mutuellement exclusifs et collectivement exhaustifs (MECE).
- il y a une colonne “depuis” Oui “jusqu'à” qui fixe les limites inférieure et supérieure pour chaque tranche d'âge. Notez que l'âge supérieur d'un groupe coïncidera avec l'âge inférieur du groupe suivant. J'ai utilisé DAX pour m'assurer qu'il n'y a pas de chevauchement dans la colonne calculée.
Écrire une colonne calculée
Le but de la colonne calculée est de filtrer la table des bandes déconnectées afin que 1 et seulement 1 être rangée “visible” O “sans filtre” pour chaque client. C'est la formule
= CALCULER(
VALEURS(Tranches d'âge[Bande]),
FILTRE(Tranches d'âge,
Les clients[Âge] > Tranches d'âge[De] &&
Les clients[Âge] <= tranches d'âge[À]
)
)
Le rôle de la partie FILTRE de cette formule est de filtrer la table AgeBands pour une seule ligne n'est pas filtrée. Ce que fait le FILTRE, c'est appliquer 2 règles de filtrage pour s'assurer que le client est supérieur à la colonne “depuis” Oui “moins qu'égal” que la colonne “jusqu'à”. C'est cette portion inférieure ou égale à celle qui assure qu'il n'y a pas de croisements d'individus en groupes multiples.. (Mutuellement exclusif).
La fonction VALEURS a une capacité unique. VALUES renvoie une table à une seule colonne. Si cette table a également une seule ligne, VALUES convertit le tableau en une valeur scalaire et permet à cette valeur d'être utilisée comme résultat dans une colonne calculée ou en fait dans une cellule de tableau croisé dynamique. Puisque la formule garantit que 1 et seulement 1 ligne non filtrée dans cette table de bandes, alors VALUES sera activé, extraire la balise pour le groupe d'AgeBands[Bande] colonne et mettre cette valeur dans la colonne calculée.
Trier la nouvelle colonne calculée
Il y a encore un problème à résoudre: les noms des nouveaux groupes ne sont pas triés correctement dans le tableau croisé dynamique. Par défaut, les colonnes de données seront triées par ordre alphanumérique. Ensuite, les étiquettes seront triées comme ceci:
Pour résoudre ce problème, J'ai créé une deuxième colonne calculée qui apporte l'identifiant de rang à la table client. C'est simple à faire – copiez simplement la formule dans la première colonne calculée, puis mettez à jour la partie VALUES avec la colonne “identifiant” de la manière suivante.
= CALCULER( VALEURS(Tranches d'âge[identifiant]), FILTRE(Tranches d'âge, Les clients[Âge] > Tranches d'âge[De] && Les clients[Âge] <= tranches d'âge[À]) )
Une fois cette deuxième colonne calculée créée, il est possible de dire à Power Pivot d'utiliser la nouvelle colonne de valeurs d'identifiant de rang comme ordre de tri pour la colonne de tranche d'âge (indiqué ci-dessous).
Compression des colonnes calculées
Comme je l'ai mentionné précédemment, Je ne suis pas un grand fan des colonnes calculées, sauf là où ils sont vraiment nécessaires. Il y a quelques directives sur quand il est correct d'utiliser une colonne calculée; vous pouvez en savoir plus sur chacun d'eux dans le lien que j'ai fourni à ma base de connaissances plus tôt. Cependant, les points clés à retenir ici sont.
- Ces colonnes calculées sont dans la table de recherche (Les clients). Les colonnes calculées dans les tables de recherche sont généralement correctes.
- Les colonnes calculées ont une faible cardinalité (c'est-à-dire, un faible nombre de valeurs uniques). En général, c'est bien aussi, même dans une grande table de données si nécessaire.
Maintenant pour utiliser la nouvelle colonne calculée
Maintenant que la table Customers a cette nouvelle colonne de “Tranche d'âge”, cette colonne peut être utilisée dans les visualisations. Peut être utilisé en rangées, Colonnes, filtres et segmentations. Voici un exemple de lignes:
Et un exemple de cutter:
Apprendre à écrire DAX
Le sujet des bandes n'est qu'une des nombreuses techniques que je couvre dans mes livres. “Supercarga Power BI” Oui “Surcharge Excel". Si vous êtes un utilisateur Power BI ou un utilisateur Excel et que vous apprenez à utiliser DAX, mes livres offrent une couverture complète de tous les sujets dont vous avez besoin pour commencer votre aventure en tant que ninja Power Pivot.
