DAX et relations

En bref

Objectifs : lire une valeur d'une table liée (RELATED, RELATEDTABLE) ; activer une relation inactive (USERELATIONSHIP) ; changer le sens du filtre le temps d'un calcul (CROSSFILTER) ; filtrer une table non reliée (TREATAS, LOOKUPVALUE).
Prérequis : « Relations » (partie 3), « CALCULATE et transition de contexte ».
Niveau : intermédiaire.

Pourquoi ?

Le modèle en étoile relie les tables de faits aux dimensions, mais les besoins réels débordent souvent du schéma : analyser par date de livraison alors que la relation active porte sur la date de commande, compter les produits vendus dans un pays, rapprocher des objectifs saisis par pays sans relation avec le modèle. Les fonctions de relation exploitent ou complètent les relations du modèle, sans le modifier.

Notions clés

Fonction	Rôle	Contexte requis
RELATED	Lit une valeur côté « un » de la relation (vers le haut)	Contexte de ligne côté « plusieurs »
RELATEDTABLE	Renvoie les lignes liées côté « plusieurs » (vers le bas)	Contexte de ligne côté « un »
USERELATIONSHIP	Active une relation inactive le temps d'un CALCULATE	Argument de CALCULATE
CROSSFILTER	Change le sens de filtrage d'une relation (BOTH, ONEWAY, NONE) le temps d'un CALCULATE	Argument de CALCULATE
TREATAS	Applique les valeurs d'une table comme filtre sur une colonne d'une table non reliée	Argument de CALCULATE
LOOKUPVALUE	Cherche une valeur selon des critères, sans relation	Aucun

Le modèle du PBIP FonctionsRelation : Ventes reliée à Produit, Geographie et Calendrier (date de commande) par des relations actives à sens unique ; une relation inactive entre Ventes[DateLivraison] et Calendrier[Date] ; une table Objectifs (Pays, Objectif) volontairement non reliée.

[Capture : vue Modèle du PBIP FonctionsRelation, relation inactive en pointillés entre Ventes[DateLivraison] et Calendrier[Date], table Objectifs isolée]

Exemple

FonctionsRelation.zip53 KB⬇

RELATED : suivre la relation vers le haut

Couleur (RELATED) = RELATED ( Produit[Couleur] )

Montant catalogue (RELATED) = SUMX ( Ventes, Ventes[Quantite] * RELATED ( Produit[PrixUnitaire] ) )

% remise accordée = 1 - DIVIDE ( [Montant vente], [Montant catalogue (RELATED)] )

La première formule est une colonne calculée de Ventes : l'équivalent d'une RECHERCHEV qui suit la relation du modèle.
RELATED exige un contexte de ligne : colonne calculée ou itérateur (SUMX, FILTER...).
Le montant facturé intègre des remises de 0, 5 ou 10 % ; le montant catalogue permet de calculer la remise accordée.

RELATEDTABLE : suivre la relation vers le bas

Nb ventes (colonne) = COUNTROWS ( RELATEDTABLE ( Ventes ) )

Nb produits à plus de 18 ventes = COUNTROWS ( FILTER ( Produit, COUNTROWS ( RELATEDTABLE ( Ventes ) ) > 18 ) )

RELATEDTABLE équivaut à CALCULATETABLE ( Ventes ) : elle déclenche la transition de contexte. Dans la colonne calculée de Produit, le résultat est figé à l'actualisation ; dans la mesure, il suit les filtres du rapport. Choisissez un pays : la mesure change, la colonne reste identique.

USERELATIONSHIP : analyser par date de livraison

Montant par date de livraison =
CALCULATE ( [Montant vente], USERELATIONSHIP ( Ventes[DateLivraison], Calendrier[Date] ) )

Écart livraison - commande = [Montant par date de livraison] - [Montant vente]

Entre deux tables, une seule relation peut être active ; les autres sont inactives (pointillés dans la vue Modèle). La relation doit exister dans le modèle, même inactive.
Montant vente range chaque vente au mois de la commande ; Montant par date de livraison la range au mois de la livraison (2 à 12 jours plus tard). L'écart apparaît sur les ventes commandées en fin de mois.
Une seule table de dates et des relations inactives évitent de dupliquer le calendrier (dimension de rôle, voir « Table de dates »).

[Capture : PBIP FonctionsRelation, page USERELATIONSHIP, matrice par mois avec Montant vente, Montant par date de livraison et l'écart]

CROSSFILTER : faire remonter le filtre

Nb produits = COUNTROWS ( Produit )

Nb produits vendus (CROSSFILTER) =
CALCULATE ( COUNTROWS ( Produit ), CROSSFILTER ( Ventes[ProduitID], Produit[ProduitID], BOTH ) )

Par défaut, Produit filtre Ventes, pas l'inverse : sans CROSSFILTER, Nb produits vaut 22 dans chaque pays. Avec BOTH, le filtre Pays passe par Ventes puis remonte vers Produit : seuls les produits vendus sont comptés. C'est préférable à une relation bidirectionnelle permanente, source d'ambiguïtés et de lenteurs.

TREATAS : une relation virtuelle

Objectif (sans relation) = SUM ( Objectifs[Objectif] )

Objectif (TREATAS) =
CALCULATE ( SUM ( Objectifs[Objectif] ), TREATAS ( VALUES ( Geographie[Pays] ), Objectifs[Pays] ) )

% objectif atteint = DIVIDE ( [Montant vente], [Objectif (TREATAS)] )

Sans TREATAS, chaque pays affiche le total des objectifs. TREATAS applique les pays visibles comme filtre sur Objectifs[Pays], comme le ferait une relation.
TREATAS accepte plusieurs colonnes, pour les objectifs définis à une granularité différente (par exemple par catégorie et par année) :

TREATAS ( <table>, <column1> [, <column2> [, … ] ] )

Montant promotion =
CALCULATE (
    [Montant vente],
    TREATAS ( SUMMARIZE ( Promotion, Promotion[Categorie], Promotion[Annee] ), Produit[Categorie], Calendrier[Annee] )
)

Ce dernier exemple, repris de l'ancienne page Fonctions, suppose une table Promotion (catégorie, année) absente des PBIP.

[Capture : PBIP FonctionsRelation, page TREATAS, matrice par continent et pays avec Objectif (sans relation), Objectif (TREATAS) et % objectif atteint]

LOOKUPVALUE : chercher une valeur

LOOKUPVALUE ( <result_columnName>, <search_columnName>, <search_value> [, <search2_columnName>, <search2_value>]… [, <alternateResult>] )

Objectif (LOOKUPVALUE) =
LOOKUPVALUE ( Objectifs[Objectif], Objectifs[Pays], SELECTEDVALUE ( Geographie[Pays] ) )

SELECTEDVALUE renvoie le pays de la ligne, ou vide si plusieurs pays sont visibles. Sur les lignes Continent et Total, le résultat est donc vide, alors que TREATAS additionne les objectifs. Si aucune ligne ne correspond, LOOKUPVALUE renvoie vide (ou le résultat de remplacement) ; si plusieurs valeurs différentes correspondent, elle renvoie une erreur.

Pièges et bonnes pratiques

Ordre de préférence sans relation : TREATAS d'abord ; INTERSECT ou FILTER avec CONTAINS restent possibles mais sont plus longs et moins performants.
RELATED contre LOOKUPVALUE : si une relation existe, utilisez RELATED ; réservez LOOKUPVALUE aux cas sans relation possible.
Relations bidirectionnelles : préférez CROSSFILTER dans la mesure qui en a besoin plutôt qu'une relation bidirectionnelle dans le modèle.
Lignage : TREATAS fonctionne parce que les valeurs d'une colonne gardent la trace de leur colonne d'origine ; une valeur transformée (par exemple avec SELECTCOLUMNS et une expression) perd ce lignage (voir l'article SQLBI ci-dessous).
Relations et calculs visuels : RELATED, RELATEDTABLE et USERELATIONSHIP ne fonctionnent pas dans les calculs visuels.

À retenir

RELATED monte vers le côté « un », RELATEDTABLE descend vers le côté « plusieurs ».
USERELATIONSHIP active une relation inactive, CROSSFILTER change le sens d'une relation, le temps d'un CALCULATE.
TREATAS crée une relation virtuelle vers une table non reliée, y compris sur plusieurs colonnes.
LOOKUPVALUE cherche une valeur unique, sans relation.

Pour aller plus loin

Source (Microsoft Learn, fonctions de relation)
Source (Microsoft Learn, USERELATIONSHIP)
Source (Microsoft Learn, TREATAS)
Source (Microsoft Learn, relations actives et inactives)
Source (SQLBI, TREATAS)

Exercice

Mise en pratique DAX, thème 6 | Objectifs et budget (TREATAS sur le pays et la date) et thème 7 | Délais et logistique, question 7.3 (USERELATIONSHIP).