Meet investigator : une formule DAX pour retrouver les valeurs vides

Proposé par André Meyer-Roussilhon

23 octobre 2020

Daxologie

dax | valeur vide

Restez à l'écoute, abonnez-vous à la newsletter (2 fois / mois)

12 + 2 =

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 :

Comprendre le message d’erreur « Dépendance circulaire détectée » et le corriger

Comprendre le message d’erreur « Dépendance circulaire détectée » et le corriger

C’est une erreur courante, et pas seulement lorsque l’on débute avec Power BI. Le message « Une dépendance circulaire a été détectée » repose sur les ressorts fondamentaux du DAX, et implique CALCULATE, la transition de contexte, les mesures et les colonnes, ou encore les filtres. A travers cette erreur, nous allons passer en revue quatre règles fondamentales du DAX.

Le DAX : maîtriser l’analyse et la modélisation de données dans Power BI

Le DAX : maîtriser l’analyse et la modélisation de données dans Power BI

Le troisième livre que je consacre à Power BI sort demain 14 avril : 328 pages entièrement dédiées au DAX, les concepts-clés (et en particulier le contexte d’évaluation), CALCULATE, la modification du modèle physique, l’analyse temporelle, etc., 328 pages également accompagnées d’exercices pratiques, corrigés et commentés (à la fois pour Power BI et pour Excel).

Une étude du contexte de filtre

Le contexte de filtre est assurément le cœur du DAX, le langage d’analyse de Power BI et de Power Pivot dans Excel. Comprendre le contexte de filtre, c’est comprendre 90% du DAX : ce n’est pas moi qui le dis, mais les deux sommités mondiales sur ce langage (Marco et Alberto, de SQLBI). Dans ce billet, je vous propose de regarder une formule et de commenter le contexte de filtre et ses évolutions.