Meet investigator : une formule DAX pour retrouver les valeurs vides

Proposé par André Meyer-Roussilhon

23 octobre 2020

Dax

Restez informé, abonnez-vous à la newsletter (2 fois / mois)

1 + 15 =

Poser le problème

Mettons-nous dans la situation où vous souhaitez afficher les ventes par catégorie de produit :

Vous remarquez la première ligne, vide ? Près de 50 000 € sans référence, c’est beaucoup ! Power BI ajoute toujours une ligne blanche aux tables de dimensions par parer ce type de situation, où la table des transactions référence des ventes qui ne sont liées à aucun produit (ou catégorie pour le coup) dans la table des produits.

Dans une base « propre », ça n’arrive pas, ou très peu. On parle d’intégrité référentielle : aucune transaction ne peut être enregistrée si la référence produit n’existe pas. Mais bon, un produit peut disparaître, et une erreur de saisie, ça arrive !

Dans ce type de situation, il s’agit de retrouver les lignes de la table des transactions qui génèrent ce chiffre.

Meet investigator

Il y a bien sûr un tas de façons de résoudre ce problème. Mais la solution que je vous propose me paraît simple, rapide, et transposable (il suffit de changer le nom des deux colonnes identifiants).

Je cherche donc à identifier une référence produit qui existe dans la table des ventes (‘Sales by store’) mais pas dans la table des produits (‘Product lookup’).

Pour ça, je vais créer une table « technique » qui liste les références produits de la table des ventes, et compte leur apparition dans la table des produits :

Notez l’usage de la fonction DISTINCT sur la première table, du décompte sur la deuxième, et l’activation de la relation à double sens avec CALCULATE et CROSSFILTER (pour faire remonter le filtre des ventes vers les produits – donc de N vers 1).

(Et aussi : j’utilise une convention de mise en forme où je mets la virgule en début de ligne, plutôt qu’en fin de ligne. Je trouve ça beaucoup plus simple quand il s’agit de debugger un script en mettant des lignes en commentaire)

Si vous regardez la colonne Compte, vous verrez qu’il y a deux valeurs, 1 ou (Vide) :

C’est bien sûr la ou les valeurs vides qui nous intéressent. Nous pourrions retrouver les références manuellement, mais ça manquerait d’ « élégance » !

Améliorer le script

Une version plus aboutie du script nous fournit directement la réponse. Il suffit de filtrer et de ne garder la valeur vide (ISBLANK) :

C’est la référence 72, dans la table des ventes, qui génère la ligne vide.

Reste à vérifier avec une simple table produit-ventes (produit vient de la table des ventes), filtrée sur la référence 72 :

Le script de création de la table peut être facilement adapté à toutes situations, le principe restera toujours le même.

investigator =
FILTER(
    ADDCOLUMNS(
        DISTINCT('Sales by Store'[product_id])
        , "compte" , CALCULATE(
                        COUNT('Product Lookup'[product_id])
                        , CROSSFILTER(
                            'Sales by Store'[product_id]
                            , 'Product Lookup'[product_id]
                            , Both
                        )
                    )
                   
    ) ,
    ISBLANK([compte])
)

Articles associés :

Créer un graphique à base zéro pour suivre l’évolution

Créer un graphique à base zéro pour suivre l’évolution

Dans un graphique à base zéro, le premier point sert de référence (le point zéro), et tous les autres sont calculés en pourcentage de variation par rapport à cette référence. Ce type de graphique est utilisé par exemple dans le domaine financier, pour suivre l’évolution du cours d’une action – mais il peut servir pour suivre l’évolution de tout type de donnée.
Le graphique est intimidant, mais sa réalisation est en fait très facile, et repose sur des formules DAX étonnamment simples.

Délai entre deux dates, week-end et jours fériés

Délai entre deux dates, week-end et jours fériés

Une table du temps qui calcule automatiquement les jours fériés ? Vous en rêviez, daxone.fr vous l’offre ! Et j’en profite pour vous donner la méthode pour calculer le nombre de jours écoulés entre deux dates, en excluant les dimanches et les jours fériés (je suis sûr que ça va vous servir)

SUMMARIZECOLUMNS : la super-fonction du DAX

SUMMARIZECOLUMNS : la super-fonction du DAX

Vous ne connaissez peut-être pas cette fonction, et c’est pourtant celle que Power BI utilise le plus fréquemment pour répondre à vos demandes : il suffit de regarder le code généré par Power BI pour afficher votre visuel pour s’en convaincre.
Avec SUMMARIZECOLUMNS , la création d’une table issue de données provenant de différentes autres tables du modèle, incluant de nouveaux indicateurs, et filtrée sur des données précises, devient un jeu d’enfant.