Fonctions de fenêtre

En bref

Objectifs : comprendre le principe d'une fonction de fenêtre (relation, tri, partition) ; comparer une ligne à la précédente avec OFFSET ; calculer un cumul ou une moyenne mobile avec WINDOW ; classer avec RANK et retrouver une ligne avec INDEX.
Prérequis : « Itérateurs et variables », « Fonctions de table ».
Niveau : avancé.

Pourquoi ?

Comparer un mois au mois précédent, cumuler le long d'un axe, classer des produits : ces calculs reposent sur l'ordre des lignes. Les fonctions de time intelligence ne fonctionnent qu'avec une table de dates, et RANKX ne fait que classer. Les fonctions de fenêtre généralisent ces calculs à n'importe quel ordre (dates, numéros de mois, montants), avec des partitions (par année, par catégorie). Elles sont aussi le moteur des calculs visuels, où elles s'écrivent plus simplement.

Notions clés

Le principe

Une fonction de fenêtre renvoie une ou plusieurs lignes d'une table, choisies par rapport à la ligne courante ou par position, une fois la table triée et éventuellement découpée en partitions. Elle se combine avec CALCULATE (comme filtre) ou avec un itérateur (SUMX, AVERAGEX).

Paramètre	Rôle
`relation`	La table dans laquelle chercher les lignes, souvent `ALLSELECTED ( colonnes du visuel )`
`ORDERBY ()`	Le tri : colonnes ou expressions, `ASC` (par défaut) ou `DESC`
`PARTITIONBY ()`	Le découpage : le calcul repart dans chaque partition (par exemple chaque année)
`MATCHBY ()`	Les colonnes qui identifient la ligne courante, si le tri ne suffit pas
`blanks`	Place des valeurs vides dans le tri : `DEFAULT`, `FIRST` ou `LAST`

Les fonctions

Fonction	Renvoie	Syntaxe
OFFSET	La ligne située à N positions de la ligne courante	`OFFSET ( <delta> [, <relation>] [, <orderBy>] [, <blanks>] [, <partitionBy>] [, <matchBy>] )`
WINDOW	Une plage de lignes, bornes absolues (`ABS`) ou relatives (`REL`)	`WINDOW ( from [, from_type], to [, to_type] [, <relation>] [, <orderBy>] [, <blanks>] [, <partitionBy>] [, <matchBy>] )`
INDEX	La ligne à une position absolue (1 = première, -1 = dernière)	`INDEX ( <position> [, <relation>] [, <orderBy>] [, <blanks>] [, <partitionBy>] [, <matchBy>] )`
RANK	Le rang de la ligne courante, `SKIP` (par défaut) ou `DENSE` en cas d'égalité	`RANK ( [<ties>] [, <relation>] [, <orderBy>] [, <blanks>] [, <partitionBy>] [, <matchBy>] )`
ROWNUMBER	Le numéro unique de la ligne courante	`ROWNUMBER ( [<relation>] [, <orderBy>] [, <blanks>] [, <partitionBy>] [, <matchBy>] )`

Dans un calcul visuel, relation peut être remplacée par un axe (ROWS, COLUMNS) et un paramètre reset est disponible.

Exemple

Les exemples ci-dessous utilisent le modèle des PBIP de démonstration (par exemple FonctionsDate), dans une matrice Année > Mois ; la colonne Mois y est triée par NumMois.

FonctionsDate.zip53 KB⬇

OFFSET : le mois précédent

Montant mois précédent (OFFSET) =
CALCULATE (
    [Montant vente],
    OFFSET (
        -1,
        ALLSELECTED ( Calendrier[Annee], Calendrier[NumMois] ),
        ORDERBY ( Calendrier[Annee], ASC, Calendrier[NumMois], ASC )
    )
)

OFFSET renvoie la ligne (année, mois) qui précède la ligne courante dans le tri ; CALCULATE l'applique comme filtre. Janvier 2025 renvoie décembre 2024, car la partition n'est pas découpée par année. Avec PARTITIONBY ( Calendrier[Annee] ), janvier serait vide.

[Capture : matrice Année > Mois avec Montant vente, Montant M-1 (DATEADD) et Montant mois précédent (OFFSET), valeurs identiques au niveau du mois]

WINDOW : cumul depuis janvier

Cumul annuel (WINDOW) =
SUMX (
    WINDOW (
        1, ABS,
        0, REL,
        ALLSELECTED ( Calendrier[Annee], Calendrier[NumMois] ),
        ORDERBY ( Calendrier[NumMois], ASC ),
        PARTITIONBY ( Calendrier[Annee] )
    ),
    [Montant vente]
)

La fenêtre part de la première ligne de la partition (1, ABS) et s'arrête à la ligne courante (0, REL) ; la partition par année fait repartir le cumul en janvier. Une moyenne mobile sur 3 mois utiliserait la fenêtre -2, REL, 0, REL avec AVERAGEX.

RANK : classer sans RANKX

Dans une matrice par pays :

Rang pays (RANK) =
RANK ( DENSE, ALLSELECTED ( Geographie[Pays] ), ORDERBY ( [Montant vente], DESC ) )

ORDERBY accepte une expression : les pays sont triés par montant décroissant. DENSE donne le même rang aux ex aequo sans laisser de trou.

INDEX : retrouver le meilleur mois

Meilleur mois (INDEX) =
MAXX (
    INDEX ( 1, ALLSELECTED ( Calendrier[Mois], Calendrier[NumMois] ), ORDERBY ( [Montant vente], DESC ) ),
    Calendrier[Mois]
)

INDEX renvoie la première ligne de la table triée par montant décroissant ; MAXX en lit le nom du mois. Sur la ligne d'une année, la mesure donne le meilleur mois de cette année.

[Capture : matrice par année avec Montant vente et Meilleur mois (INDEX)]

Pièges et bonnes pratiques

Relation et visuel : la ligne courante est identifiée par les colonnes de la relation présentes dans le contexte. Si le visuel n'affiche pas ces colonnes, le résultat peut être faux ou provoquer une erreur ; utilisez MATCHBY ou alignez la relation sur les colonnes du visuel.
Valeurs uniques : les colonnes d'ORDERBY et de PARTITIONBY doivent permettre d'identifier chaque ligne ; sinon DAX ajoute lui-même des colonnes de tri quand il le peut, ou renvoie une erreur.
Préférez le calcul visuel pour un calcul propre à un visuel : PREVIOUS ( [Montant vente] ) ou RUNNINGSUM ( [Montant vente] ) remplacent OFFSET et WINDOW en une ligne (voir « Calculs visuels »).
Préférez la time intelligence pour les comparaisons calendaires classiques (N-1, cumul annuel) quand le modèle a une table de dates marquée : les formules sont plus courtes.
Testez vos fenêtres dans la vue de requête DAX avant de les intégrer à une mesure.

À retenir

Une fonction de fenêtre trie une table (ORDERBY), la découpe éventuellement (PARTITIONBY) et renvoie des lignes par rapport à la ligne courante.
OFFSET pour la ligne précédente ou suivante, WINDOW pour une plage (cumul, moyenne mobile), INDEX pour une position, RANK et ROWNUMBER pour un rang.
Elles s'utilisent comme filtre dans CALCULATE ou comme table d'un itérateur.
Dans un visuel isolé, les calculs visuels offrent le même résultat plus simplement.

Pour aller plus loin

Source (Microsoft Learn, OFFSET)
Source (Microsoft Learn, WINDOW)
Source (Microsoft Learn, INDEX)
Source (Microsoft Learn, RANK)
Source (Microsoft Learn, ROWNUMBER)
Source (SQLBI, articles sur les fonctions de fenêtre)

Exercice

Dans le PBIP du thème 4 | Classements et Top N, refaites la question 4.1 avec RANK au lieu de RANKX ; dans celui du thème 2, refaites la question 2.3 avec OFFSET.