Fonction DAX COLUMNSTATISTICS dans LuckyTemplates

J'aimerais continuer à explorer les utilisations dynamiques de la fonction DAX COLUMNSTATISTICS() - une nouvelle fonction DAX presque entièrement non documentée ajoutée à LuckyTemplates en août 2021 qui présente des aspects très uniques avec lesquels je pense qu'il sera très intéressant de se familiariser avec . Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

J'ai fait beaucoup d'expérimentations et j'ai appris de nouvelles choses vraiment intéressantes sur le fonctionnement de cette fonction et sur ce que vous pouvez en faire. Dans mon premier blog sur cette fonction, j'ai parlé de quelques utilisations statiques. Aujourd'hui, je vais parler de certaines utilisations dynamiques vraiment assez étonnantes de la fonction d'une manière que même IntelliSense n'est pas encore à jour.

Table des matières

• Fonctionnement de la fonction DAX COLUMNSTATISTICS
• Conclusion

Fonctionnement de la fonction DAX COLUMNSTATISTICS

Pour chaque table et champ de votre modèle de données, COLUMNSTATISTICS produit une table de six champs supplémentaires qui a un nom de table, un nom de colonne, des valeurs min et max pour la colonne, une cardinalité et une longueur max. Cela ne semble pas être beaucoup de données, mais vous pouvez en faire beaucoup.

Les données que j'utilise actuellement ne sont que l'ensemble de données d'entraînement pour les outils externes de LuckyTemplates. Il s'agit d'un modèle de données relativement simple avec cinq tables de dimension, une table de faits et une table de mesures . Un modèle comme celui-ci n'est pas si difficile à suivre, mais un modèle complexe nécessite une surveillance d'ensemble de données beaucoup plus complexe.

Les métadonnées d'un jeu de données complexe deviennent beaucoup plus importantes en termes de cardinalité, qui est le nombre de valeurs uniques dans un champ. Cela a une grande influence sur la taille de votre modèle ainsi que potentiellement sur la vitesse de traitement.

De plus, lorsque vous créez un modèle, il est utile de connaître non seulement le nombre de tables, mais aussi la plage de dates de ces tables. Au fur et à mesure que vous construisez votre table Dates, vous vous assurez de couvrir toute la gamme de données dans votre table de faits.

Et donc, pour cet exemple, essayons d'ajouter une table à ce modèle de données sur lequel je travaille. Dans Power Query, nous irons New Source , puis Blank Query . Et puis, nous irons dans l'éditeur avancé et je vais simplement coller une fonction appelée List.Dates . Nous allons l'utiliser pour créer une table de cardinalité de 10 000, donc 10 000 dates uniques.

Il va renvoyer une liste de 10 000 éléments et nous pouvons simplement la convertir en table, puis renommer cette table.

Nous pouvons changer le champ ici à ce jour. Et si nous cliquons sur Fermer et Appliquer, nous allons voir que notre récit intelligent va se mettre à jour dynamiquement, sans avoir à rafraîchir tout le modèle (comme nous le faisions dans le passé).

La raison pour laquelle c'est entièrement dynamique, c'est que nous faisons tout cela avec des mesures. Ce qui est intéressant, c'est qu'il affiche beaucoup d'erreurs et que la fonction est si nouvelle qu'IntelliSense ne la détecte pas correctement, mais elle fonctionne réellement.

Passons à l'éditeur tabulaire 3, qui, à mon avis, est le meilleur moyen de comprendre ce que font réellement les mesures DAX complexes.

N'oubliez pas que la requête DAX renvoie des tables plutôt que des scalers. Donc, si nous prenons cette mesure et la copions dans une requête DAX , une erreur s'affiche car le résultat ici est toujours un scaler. Nous pouvons utiliser l'approche de débogage consistant à remplacer ce résultat RETURN par des composants individuels de la mesure.

Dans ce cas, nous le remplacerons par ColStats , ce que nous avons fait en haut consiste simplement à placer cette fonction COLUMNSTATISTICS DAX dans une variable. Avec cela, nous obtenons exactement ce que nous attendons, à savoir le tableau standard des statistiques de colonne.

Et maintenant, nous voulons juste regarder la colonne Nom de la table, et nous voulons en retirer les valeurs distinctes et les compter. Cela va être le nombre de tables dans notre modèle de données.

Tout d'abord, nous allons sélectionner des colonnes sur notre table ColStats et renvoyer simplement ce champ de nom de table. En règle générale, dans une mesure DAX ou une requête DAX, vous souhaitez renvoyer un nom de champ avec le nom de la table devant. Mais dans ce cas, nous ne savons pas à quoi le nom de table approprié fait référence car il n'existe que virtuellement. Il ne semble pas accepter le nom de la variable comme nom de table.

Donc, dans ce cas, nous devons rester avec ce qui ressemble à une mesure, mais c'est en fait une référence de colonne sans la référence de table devant. Et cela fonctionne toujours même si c'est déroutant dans la nomenclature. Maintenant, si nous remplaçons cette instruction RETURN par notre TabCol , nous obtenons exactement ce que nous attendons, c'est-à-dire ce champ de tables de modèle.

Et puis pour le résultat, nous faisons juste un décompte des colonnes de table distinctes. Donc, si nous remplaçons ceci par DISTINCT(TabsCol) , nous obtenons nos sept tables.

Examinons maintenant la cardinalité la plus élevée dans la table des cardinalités les plus élevées et voyons comment nous les avons obtenues. C'est en fait un modèle important que vous pouvez utiliser pour beaucoup de choses différentes où vous recherchez, pas seulement le nombre maximum, mais l'attribut max associé à ce nombre.

Jetons donc un coup d'œil à la MaxCardiality . Même s'il s'agit d'un scaler, nous pouvons le transformer en un tableau à une cellule en mettant simplement des crochets autour. Et si nous atteignons cinq, nous verrons que c'est la valeur de cardinalité de 10 000.

Alors maintenant, la question est de savoir comment prendre cela et renvoyer la table associée à cette cardinalité.

Pour ce faire, nous utilisons ce modèle très courant utilisant . Nous le copions ensuite dans notre instruction RETURN et cela nous donnera la ligne que nous nous attendions à obtenir, qui est la colonne 10 000 dates. Au lieu d'avoir une ligne (parce que c'est TOPN), nous en obtenons deux parce qu'il y a égalité.

Ce que ColStats fait également, c'est qu'il crée, pour chaque table, un index dans une ligne d'index cachée appelée RowNumber , qui est un identifiant unique pour chaque ligne de la table. Ainsi, MaxCardinality sera toujours reflété par le RowNumber s'il s'agit d'un identifiant unique.

Et donc, peu importe que nous ayons deux lignes, car ce que nous examinons est le Max of Table Name. Et ce max est là juste pour renvoyer une valeur qui, sinon, ne serait qu'une colonne nue. Mais nous devons mettre une agrégation autour de cela, et dans ce cas, nous utilisons .

Ensuite, si nous plaçons MaxCardinality dans notre instruction RETURN et que nous la plaçons entre parenthèses pour renvoyer une table plutôt qu'un scaler, elle renvoie la valeur de Test.

Ce modèle TOPN DAX est un très bon modèle à retenir lorsque vous souhaitez essentiellement déterminer une valeur max ou min, puis renvoyer un attribut associé à ce min ou max.

Optimiser les formules LuckyTemplates à l'aide de DAX avancé
Analyse des mesures DAX : décomposer les mesures DAX longues
Mesures DAX dans LuckyTemplates à l'aide de la ramification des mesures

Conclusion

Je vous ai donné l'approche générale et le contexte que nous pouvons utiliser pour que vous utilisiez au mieux la fonction DAX COLUMNSTATISTICS de manière dynamique. Étant capable de suivre cela de manière dynamique en termes de modèle complexe, je pense que cette fonction a une valeur énorme.

Plus nous expérimentons avec la fonction COLUMNSTATISTICS et avec sa capacité à gérer dynamiquement les métadonnées dans DAX, plus nous trouverons d'utilisations intéressantes. Donc, si vous avez déjà trouvé des utilisations intéressantes pour cette fonction DAX, faites-le moi savoir dans les commentaires ci-dessous.

Tous mes vœux!