OFFSET – una nuova (e benvenuta) funzione DAX

Aggiornamento 29/01/2023: l’uso di una misura come criterio di ordinamento è, in effetti, possibile. Si rimanda a questo articolo, più aggiornato.

In un recentissimo meeting in cui ero chiamato a risolvere qualche problema di DAX, il mio carissimo amico Marco Raule, Direttore HR di Eridania Italia, mi ha messo al corrente dell’esistenza di una funzione DAX che, come la nuovissima EVALUATEANDLOG per il processo di debugging, è ben nascosta e non ancora supportata da Intellisense ma che funziona alla perfezione già adesso ed è molto utile: OFFSET.

Marco aveva saputo lui stesso di questa funzione da un articolo del blog Data – Marc (ecco il link all’articolo: https://data-marc.com/2022/09/21/how-offset-in-dax-will-make-your-life-easier/) e io ne sono stato subito interessatissimo. Prima di proseguire, dunque, ringrazio Marco e, indirettamente, il creatore del blog Marc Lelijveld.

La funzione OFFSET, non ancora documentata e dunque non ancora supportata, risolve un problema annoso del DAX: la necessità di scrivere codice complesso quando si voglia referenziare un elemento diverso dal corrente in un contesto di riga.

Useremo, in questo articolo, un modello molto semplice composto da soltanto due tabelle: una dimensione (Product) e una tabella dei fatti (Sales). Il modello è mostrato in figura 1.

Sviluppo

Il caso d’uso di OFFSET è più semplice da mostrare in applicazione che da spiegare in astratto. Si consideri la figura 2 a seguire in cui è mostrata una matrice che mostra, per ogni colore di prodotto (colonna ‘Product'[Color]), le vendite (misura Ricavi). Si noti, in figura, che la colonna ‘Product'[Color] è ordinata in senso alfabetico crescente. Il codice di Ricavi è:

Ricavi =

Si supponga, adesso, di volere calcolare, per ogni colore, il valore di Ricavi in corrispondenza del colore subito precedente in senso alfabetico, in modo da potere, poi, calcolarne la differenza (figura 3):

Ottenere il risultato in figura 3, fino ad ora, è stato piuttosto complesso. Una persona relativamente poco esperta di DAX non sarebbe mai stata in grado di generare la misura Ricavi OFFSET -1 Color, pur essendo un calcolo, in apparenza, molto semplice (soprattutto se si fa – erroneamente – riferimento alla facilità di realizzare un calcolo del genere in Excel).

Ecco il codice della misura Ricavi OFFSET -1 Color che usa la funzione OFFSET (in un’immagine visto che il codice non è ancora formattabile con DAX formatter per ottenerne la versione HTML), in figura 4:

Ricordate che Intellisense mostrerà un errore, dunque non potrete formattare neanche con Bravo, fino a quando OFFSET non sarà rilasciata ufficialmente (stessa cosa per EVALUATEANDLOG).

Osservando la figura 4, è abbastanza semplice dedurre la sintassi di OFFSET, che consta di tre input:

1. l’entità dello sfasamento, cioè il numero di righe da saltare e la relativa direzione (segno + o -);
2. la tabella su cui creare il contesto a cui applicare lo sfasamento;
3. l’ordinamento dei valori in tabella, in modo da chiarire cosa si intenda per precedente/successivo.

Nel caso mostrato, lo sfasamento è stato posto pari a -1 (valore immediatamente precedente al quello attuale – è possibile cambiarlo o renderlo selezionabile dall’utente in modo dinamico), la tabella di riferimento è stata ALLSELECTED(‘Product'[Color]) – in generale sarebbe sufficiente, in una matrice del genere, ALL(‘Product'[Color]) per accedere anche ai colori diversi da quello del contesto attuale, l’uso di ALLSELECTED è in ogni caso molto utile in caso ci siano filtraggi esterni alla visual sulla colonna ‘Product'[Color] – e, infine, l’ordinamento è stato posto in senso alfabetico sulla colonna ‘Product'[Color] tramite la funzione ORDERBY, fino ad ora utilizzabile soltanto in DAX Studio.

Non è (ancora) possibile ottenere un meccanismo analogo nel caso in cui si ordini la matrice in senso, per esempio, decrescente sui valori della misura, un caso molto diffuso. Si osservi la figura 5 in cui la matrice mostra i valori della misura Ricavi ordinati in senso decrescente e i colori non hanno, di conseguenza, un ordinamento alfabetico.

È evidente, in figura 5, che non c’è corrispondenza con i valori precedenti del colore questa volta, perché la misura Ricavi OFFSET -1 Color usa un ordinamento che non è quello attualmente usato nella matrice. Lo scopo qui è sempre lo stesso: calcolare il valore di Ricavi in corrispondenza del colore immediatamente precedente (o sfasato in modo più esteso, basterà cambiare il valore dello sfasamento in valore assoluto e segno) ma questa volta seguendo l’ordinamento dei valori della misura Ricavi.

Il tentativo mostrato in figura 6, che arricchisce la tabella secondo parametro di OFFSET con ADDCOLUMNS, non porta, purtroppo, che ad un errore (stessa cosa se si generasse una variabile prima con lo stesso codice):

È, dunque, necessario in questo caso, usare la tecnica già esistente, un po’ articolata. Una possibile soluzione è la seguente misura:

Ricavi OFFSET -1 Color MISURA =

In figura 7 è possibile notare come i valori siano adesso corretti secondo l’ordinamento decrescente della misura, anche in presenza di un filtro sui colori, grazie all’uso fatto di ALLSELECTED:

Conclusioni

OFFSET è una funzione molto potente e pratica. Semplificherà molto la scrittura del DAX e speriamo che arrivino altre funzioni di questo tipo per aiutare a diffondere il DAX e Tabular. Non è (ancora) possibile usare OFFSET quando si voglia cambiare elemento del contesto ma basandosi su un ordinamento che consti dei valori di una misura, nel qual caso bisogna usare le tecniche già esistenti, piuttosto articolate in quanto, spesso, usano RANKX.

Autore del Post