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)

15 + 6 =

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 :

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.

Construire et lire une moyenne mobile dans Power BI

Construire et lire une moyenne mobile dans Power BI

Dans ce billet, je vous propose une étude très poussée de la moyenne mobile et de sa mise en œuvre dans Power BI. La moyenne mobile est un outil primordial pour « lire » les données, en particulier lorsque les variations sont importantes, mais aussi un outil prédictif. Et notamment, tout un pan des stratégies d’investissement repose sur la lecture de ces moyennes

Le point sur mon implication dans Power BI

Le point sur mon implication dans Power BI

Depuis juillet 2109, date à laquelle mon premier livre sur Power BI est sorti, il s’est passé beaucoup de choses, et plus encore au cours des derniers mois. Dans ce billet, je veux vous proposer un tour d’horizon des ressources que j’ai publiées sur le thème de Power BI, et de ce que je prévois pour la suite.