Contact: 514-605-7112 / info@lecfomasque.com

DAX: L’utile fonction USERELATIONSHIP

Publié le : 20 février 2020

Une question posée sur le forum m’a inspiré l’article suivant, qui démontrera l’utilité de la fonction USERELATIONSHIP en DAX, le langage utilisé par Power Pivot et Power BI.

 

Données de départ et objectif visé

Un usager du forum a fourni des données de départ semblables à celles ci-dessous (nous les avons simplifiées pour fins de démonstration). On y retrouve différentes tâches, à différentes dates. Ces tâches sont reçues par un agent (AgentRec) et sont ensuite traitées par ce même agent ou par un autre agent (AgentTrait). Dans cette même table, l’usager avait ajouté deux colonnes calculées. La première, LuiMeme, affiche un « O » lorsque l’agent traitant la demande est le même que l’agent recevant la demande et un « N » dans le cas contraire. La deuxième, Autre, affiche un « O » lorsque l’agent traitant la demande est différent de celui qui la reçoit et un « N » dans le cas contraire. À noter qu’une seule colonne aurait été suffisante pour réaliser cette tâche. Mieux encore, on aurait pu éviter d’ajouter ces colonnes (ce qui est toujours préférable en DAX) et utiliser plutôt une mesure itérative.

 

Toutefois, pour les fins de cet exemple, nous avons repris les données de l’usager du forum. Donc, dans mes visualisations de données ci-dessous, vous apercevez un 1 vis-à-vis les tâches traitées par l’agent qui les as reçues (dans la colonne ParLui) et un 1 vis-à-vis les tâches traitées par un autre agent (dans la colonne ParAutre). Ces deux colonnes sont générées par des mesures (ParLui et ParAutre) qui seront présentées plus loin.

 

Matrice Power BI

 

L’objectif était d’obtenir les trois informations suivantes, par le biais d’un seul et même segment, permettant aux usagers d’obtenir les informations pour un agent en particulier:

 

Power BI Résultat recherché

 

On peut effectivement valider, dans le tableau de départ, que le nombre de tâches reçues et traitées par l’agent A est égal à 3.

 

3 tâches

 

De même, on peut valider que le nombre de tâches reçues par l’agent A et traitées par un autre agent sont de l’ordre de 6.

6 tâches

 

Et finalement, on peut aussi valider que le nombre de tâches qui ont été reçues par un autre agent que l’agent A et qui ont été traitées par l’agent A sont au nombre de 4.

 

4 tâches

 

Modèle de données

La première grande problématique rencontrée dans les données soumises par l’usager du forum était liée à ses données sources, qui logeaient dans une seule et même table, plutôt que dans un modèle de données. Pour utiliser Power BI ou Power Pivot de façon efficace, tout usager devrait d’abord apprendre à créer des modèles de données qui respectent les meilleurs pratiques d’affaires. À cet effet, je vous recommande notre formation Excel – Introduction à Power Pivot et aux modèles de données.

 

J’ai donc corrigé la situation et généré le modèle de données ci-dessous. Notez qu’il y a deux relations entre la table de faits Tâches et la table de dimension DimAgents.

 

Modèle de données

 

Plus précisément, la première relation est établie entre le champ Agents de la table DimAgents et le champ AgentRec de la table Tâches.

 

Relation 1

 

Et la deuxième relation est établie entre le champ Agents de la table DimAgents et le champ AgentTrait de la table Tâches.

 

Relation 2

 

Notez toutefois qu’il n’est pas possible d’avoir deux relations « actives » en même temps entre deux tables du modèle de données. La deuxième relation est donc présentée en pointillée et n’est donc pas active. Cela signifie que par défaut, toutes les mesures en DAX utiliseront la relation active. Mais il y aura toutefois un moyen, quand les circonstances l’exigeront, d’utiliser la relation inactive et cela, grâce à la fonction USERELATIONSHIP.

 

Relation inactive

 

Utilisation de la fonction USERELATIONSHIP

Dans un premier temps, il est plutôt aisé de calculer le nombre de tâches qu’un agent sélectionné a reçues et traitées lui-même et le nombre de tâches qu’il a reçues et n’a pas traitées lui-même. Cela est possible grâce aux mesures suivantes:

 

ParLui = CALCULATE(COUNT(Taches[Lui-même]),Taches[Lui-même]= »O »)

ParAutre = CALCULATE(COUNT(Taches[Autre]),Taches[Autre]= »O »)

 

Étant donné que par défaut, les mesures ci-dessus vont utiliser la relation active entre la table  DimAgents et la table Tâches, ce qui se passe, c’est que le segment où l’usager choisit l’agent (dans notre image, l’agent A), filtre la table de tâches sur la colonne AgentRec. Ce faisant, toutes les lignes qui présentent l’agent B, dans cette colonne, sont écartées. Par la suite, les deux mesures ci-dessus ajoutent un deuxième filtre. La première mesure ajoute un filtre sur la colonne Taches[Lui-même] et la deuxième mesure ajoute un filtre sur la colonne Taches[Autres]. Tout fonctionne donc à merveille.

 

Toutefois, pour le troisième résultat recherché, i.e. le nombre de tâches traitées par l’agent A mais qui n’ont pas été reçues par l’agent A, on a un problème. En effet, on part avec un modèle qui par défaut, est filtré sur la colonne AgentRec pour l’agent A. Et ce que l’on cherche à obtenir, c’est justement l’inverse, i.e. les lignes où l’agent qui reçoit la demande n’est pas l’agent A. Il faut donc ici cesser d’utiliser la relation active et utiliser plutôt la relation inactive, en insérant la fonction USERELATIONSHIP dans notre fonction CALCULATE.

 

Indirect = CALCULATE(

COUNT(Taches[AgentTrait]),

USERELATIONSHIP(Taches[AgentTrait],DimAgents[Agents]),

Taches[Autre]= »O »)

 

Pour utiliser la relation inactive, on doit donc insérer la fonction USERELATIONSHIP et indiquer le champ de la table de faits et le champ de la table de dimension que l’on souhaite utiliser dans la relation. Ceci nous permettra de filtrer le modèle de données pour ne conserver que les lignes qui présentent l’agent A dans la colonne AgentTrait. Ensuite, le filtre Taches[Autre]= »O » permet de vérifier si l’agent qui reçoit la demande est le même que l’agent qui la traite. Nous, on veut qu’ils soient différents donc on cherche les « O ».

 

Conclusion

Encore une fois, on peut constater ici que pour être en mesure de créer les visualisations de données que l’on souhaite, il s’avère important de maîtriser le langage DAX. Pour apprendre la langage DAX, je vous propose notre formation Introduction au langage DAX (Power Pivot et Power BI Desktop).

 


NOTRE OFFRE DE FORMATIONS


Nos formations sont éligibles à la subvention PACME du Québec

et notre entreprise est datadockée en France

Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse Patrick Laroche ,

    Salut Sophie,
    Premièrement j’aimerais te remercier pour les capsules, elles sont vraiment intéressantes et instructives. Bien content de connaître cette nouvelle fonction.
    Dans ton exemple, j’aimerais te faire part humblement de 2 commentaires qu’il n’est pas nécessaire de publier sur ton post.
    1- je me suis accroché sur le « O » de ton filtre car je croyais encore que nous étions dans la notion de 1 ou 0, donc je confondais « O » et 0 jusqu’à ce que je me rende compte des guillemets et que « O » signifiait oui et que lorsque je me référais au tableau cela était égal à 1.
    2- J’aurais simplifié ta table de fait en ajoutant qu’une seule mesure soit celle de « ParLui-même » car lorsque cette mesure est à « N » cela veut dire « ParAutre ». Bien sûr, il faut modifier les mesures en conséquence.
    Ceci dit merci pour tout ce que tu fais pour la communauté.
    Au plaisir.

    • réponse Sophie Marchand ,

      Bonjour Patrick,

      Merci pour vos bons mots et désolé pour la confusion. Je viens de corriger la première section de l’article en fonction de vos commentaires. Laissez-moi savoir si c’est plus clair à présent!

      Au plaisir,

      Sophie

    • réponse Patrick Laroche ,

      Merci d’avoir lu mon commentaire. Sache que tes capsules et formations sont très claires et ton talent pédagogique est hors pair. Bravo et merci pour ton apport.

      Répondre à Patrick Laroche Annuler