PowerBI : Utiliser un segment pour choisir la mesure à observer dans un graphique

Un cas client qui revient de temps à autre : disposer d’un rapport PowerBi dans lequel un segment permet de changer la mesure observée dans la page :

 

L’implémentation proposée ici repose sur un tout petit peu de DAX et une table statique dans le modèle.  A noter que cette implémentation est également valable pour Excel 2016 avec PowerPivot ou un modèle SSAS Tabulaire.

Ajout d’une table statique au modèle

Partons d’un rapport simple contenant une seule table « ventes » à analyser.

Dans cette table, deux mesures pour notre exemple :

  • Nombre de commandes = COUNTROWS(Ventes)
  • Nombre de produits commandés = Sum(Ventes[Quantité])

Que nous souhaitons observer tour à tour dans le même visuel.

Nous allons ajouter une table « Mesure observée » au modèle via la fonction « Entrer des données » qui servira de support au segment.

L’astuce DAX

Nous allons ajouter une mesure dans la table « Mesure observée » intitulée valeur :

Valeur = If(HASONEVALUE('Mesure observée'[Mesure]);SWITCH(VALUES('Mesure observée'[Mesure]);"Quantité commandée";[Nombre de produits commandés];"Nombre de commande";[Nombre de commandes]);[Nombre de commandes])

Le HasOneValue permet de déterminer si une seule valeur a été sélectionnée dans le segment ou non. Si tel est le cas, suivant la valeur sélectionnée (fonction Switch), nous afficherons la mesure correspondante. Dans le cas contraire, nous utiliserons une mesure par défaut, ici, Nombre de commande.

La configuration du visuel

Dans la partie rapport, nous allons déposer un segment qui présente la colonne Mesure de la table Mesure observée pour permettre la sélection de la mesure.

Et des visuels, qui vont tous présenter la mesure valeur de la table Mesure observée plutôt que directement les mesures de la table vente.

Et le tour est joué, suivant le libellé de mesure sélectionné dans le segment, la mesure observée est différente 🙂

 

Power BI : Création une table à partir d’une non-équi-jointure (utilisation d’un produit cartésien intermédiaire)

Lors de la création d’un P.O.C. Power BI pour un client j’ai été confronté au besoin de réaliser une non-équi-jointure pour alimenter une table à partir de deux onglets Excel. La solution adoptée n’étant pas immédiate, je vous la soumets.

Cas d’utilisation :

Création d’un calendrier prévisionnel pour une liste de contrats fournis dans un onglet Excel sur la base d’un calendrier fourni dans un autre document Excel.

Fichier Contrat

Nom Date Début contrat Date Fin contrat
Adam 01/04/2015 31/01/2016
Bob 01/06/2015 29/02/2016
Joe 01/01/2016

Fichier Calendrier

Mois Nb Jours ouvrés
01/01/2016 22
01/02/2016 20
01/03/2016 23

Première étape : Créer une table dans le dataset pour le fichier calendrier

Table calendrier

Deuxième étape : Produit cartésien pour obtenir une ligne par mois et par personne

Nous importons désormais le fichier Contrats.
Dans le requête permettant l’alimentation de la table Contrats, nous ajoutons une colonne de clé fictive qui va nous permettre de réaliser le produit cartésien. Cette clé fictive aura la valeur 1 et sera présente dans les deux jeux de données à lier. Dans la table contrats :
Cle Fictive Contrat
Nous faisons de même dans la requête d’import de la table Calendrier
Cle Fictive Calendrier
Nous utilisons ici la jointure interne basée sur la clé fictive (fusion de requêtes) de manière à ce que chaque ligne de la table Calendrier joigne avec chaque ligne de la table Contrat.
Fusion
Nous développons ensuite la table jointe pour en conserver les colonnes pertinentes « Mois » et « Nb Jours ouvrés ».
Développement de la table jointe
Nous avons réalisé un produit cartésien !

Le code M de la requête :

let
    Source = Excel.Workbook(File.Contents("source.xlsx"), null, true),
    Contrats_Sheet = Source{[Item="Contrats",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(Contrats_Sheet),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Nom", type text}, {"Début", type date}, {"Fin", type date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "CleFictive", each 1),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Personnalisée ajoutée",{"CleFictive"},Calendrier,{"CleFictive"},"NewColumn",JoinKind.Inner),
    #"NewColumn développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "NewColumn", {"Période", "NbJours"}, {"NewColumn.Période", "NewColumn.NbJours"})
in
    #"NewColumn développé"

Troisième étape : Filtrer le résultat de la deuxième étape pour limiter à la période comprise dans le contrat

Nous utilisons ici la fonction Table.SelectRows pour ne conserver que les lignes comprises dans l’intervalle du contrat.
La condition de jointure est : debut ≤ periode && (fin ≥ periode or fin is null)
L’utilisation de cette fonction n’est pas associée à ma connaissance à un designer, nous allons donc éditer la requête et introduire une sélection de ligne juste avant le « in » :

let
    Source = Excel.Workbook(File.Contents("source.xlsx"), null, true),
    Contrats_Sheet = Source{[Item="Contrats",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(Contrats_Sheet),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Nom", type text}, {"Début", type date}, {"Fin", type date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "CleFictive", each 1),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Personnalisée ajoutée",{"CleFictive"},Calendrier,{"CleFictive"},"NewColumn",JoinKind.Inner),
    #"NewColumn développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "NewColumn", {"Période", "NbJours"}, {"NewColumn.Période", "NewColumn.NbJours"}),
    #"periodes sélectionnées" = Table.SelectRows(#"NewColumn développé", each ([Début] = [NewColumn.Période] or [Fin] = null)))
in
    #"periodes sélectionnées"


NB : Exploitant un produit cartésien intermédiaire, il faut être sûr d’en maîtriser la volumétrie avant d’appliquer cette méthode au risque d’obtenir des temps de chargement du dataset exponentiels.
Bien entendu cette méthode n’a d’intérêt que si le traitement ne peut être réalisé sur la source comme c’est le cas ici.