Le funzioni WINDOW in DAX, (OFFSET, INDEX e WINDOW) sono state già descritte in un precedente articolo. Per aiutare i lettori a comprenderne la potenza, in questo articolo affronteremo un caso pratico la cui soluzione è, seguendo l’unico approccio possibile prima dell’ avvento delle funzioni WINDOW, piuttosto complessa e, usando le funzioni WINDOW, piuttosto semplice.
Il modello è costituito da un’unica tabella. Non è un suggerimento di lavorare con un’unica tabella: Autoexist è sempre in agguato ed è sempre meglio uno star-schema, ma qui si vuole riprodurre il problema esattamente come è stato segnalato e risolto.
Le prime righe della tabella, di nome Dati, sono visibili in figura 1.
La tabella ha 4.157 righe e mostra la data di consegna richiesta (Dati[Data Richiesta]), il tipo di ordine (Dati[Tipo Ordine]), il prodotto ordinato (Dati[Prodotto]), la quantità ordinata (Dati[Quantita]) e la descrizione del tipo di ordine (Dati[Descrizione Tipo Ordine]). Le quantità in ogni riga possono essere positive o negative.
Sviluppo
La problematica è emersa nella Community Power BI, in cui un annuncio chiedeva di creare una misura che permettesse di osservare il valore cumulato delle quantità, indipendentemente dalla colonne usate per raggruppare. Abbiamo risposto alla richiesta di supporto e risolto il problema.
Non è tuttavia, purtroppo, possibile risolvere in pieno la richiesta perché Tabular non permette ancora di leggere i metadati; si potrebbe di certo verificare per ogni singola colonna del modello la condizione ISFILTERED ( ) ma se venisse poi aggiunta una nuova colonna calcolata o importata il problema si ripresenterebbe. In questo articolo, dunque, presentiamo i passi fatti per offrire una semi-soluzione: si è deciso, in accordo con chi chiedeva supporto, di arrivare fino all’uso di tre colonne: Dati[Data Richiesta], Dati[Prodotto] e Data[Descrizione Tipo Ordine]. Si farà un passo alla volta, partendo da una sola colonna di raggruppamento, con l’approccio privo di funzioni WINDOW. Si presenterà, infine, la versione che le usa, in particolare la funzione WINDOW. In questo modo si apprezzerà la semplificazione del codice.
A seguire una tabella di Power BI Desktop con il raggruppamento soltanto per Dati[Data Richiesta], una misura di aggregazione delle quantità (QTY) e la prima versione della soluzione (QTY Cumulata V1).
Il codice delle misure mostrate in figura 2 è a seguire.
QTY =La complessità è, fino a questo punto, piuttosto contenuta per chi conosce un minimo di DAX. Essa, tuttavia, cresce notevolmente – seguendo l’approccio classico del DAX fino ad oggi – all’inserimento del prodotto e poi della descrizione del tipo di ordine, rendendo non corretta la prima versione della soluzione (figura 3).
La misura QTY Cumulata V1 non funziona più perché adesso nel filter context è presente il prodotto. QTY Cumulata V2, che funziona in questo contesto e rappresenta il secondo passo di soluzione, è estremamente più complessa di QTY Cumulata V1 perché deve stabilire un ordinamento per data richiesta e prodotto (nella logica del codice a seguire è stata introdotta anche la descrizione del tipo ordine, in modo che questa sia la misura definitiva che risolve il problema con l’approccio DAX classico):
QTY Cumulata V2 =INT ( MAX ( Dati[Data Richiesta] ) ) & MAX ( Dati[Prodotto] )
& MAX ( Dati[Descrizione Tipo Ordine] )
VAR ALLReferenceLessThanCurrentReference =
FILTER (
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZE (
Dati,
Dati[Data Richiesta],
Dati[Prodotto],
Dati[Descrizione Tipo Ordine]
),
ALLSELECTED ()
),
“@Ref”,
INT ( Dati[Data Richiesta] ) & Dati[Prodotto] & Dati[Descrizione Tipo Ordine],
“@Qty”, CALCULATE ( SUM ( Dati[Quantita] ) )
),
[@Ref] <= CurrentReference
&& NOT ISBLANK ( [@Qty] )
)
RETURN
CALCULATE ( SUM ( Dati[Quantita] ), ALLReferenceLessThanCurrentReference )
Senza commentare il codice DAX sopra riportato per la ragione descritta ad inizio articolo, l’inserimento, infine, della colonna Data[Descrizione Tipo Ordine] nel report non inficia la correttezza della misura QTY Cumulata V2, essendo stato già previsto nel codice di avere nel filter context anche la descrizione del tipo di ordine (figura 4).
Senza ulteriori commenti, ecco la versione del codice alternativa a QTY Cumulata V2 che usa la funzione WINDOW:
QTY Cumulata WINDOW =VAR QTY =
SUM ( Dati[Quantita] )
RETURN
IF (
NOT ( ISBLANK ( QTY ) ),
CALCULATE (
SUM ( Dati[Quantita] ),
WINDOW (
1,
ABS,
0,
REL,
ALLSELECTED (
Dati[Descrizione Tipo Ordine],
Dati[Tipo Ordine],
Dati[Prodotto],
Dati[Data Richiesta]
),
ORDERBY ( Dati[Data Richiesta], ASC,
Dati[Prodotto], ASC,
Dati[Tipo Ordine], ASC,
Dati[Descrizione Tipo Ordine], ASC )
)
)
)
Ecco la misura in azione (figura 5):
La misura QTY Cumulata WINDOW fornisce gli stessi risultati di QTY Cumulata V2 e funziona, come QTY Cumulata V2, in ognuno dei contesti fino ad ora descritti. Tuttavia, oltre ad essere scritta con un codice più corto e semplice di quello di QTY Cumulata V2, QTY Cumulata WINDOW è più facile da interpretare e manutenere.
Conclusioni
L’ avvento della funzioni WINDOW comporta una buona semplificazione del codice DAX in alcuni scenari. Non è questo, probabilmente, il caso più in uso in quanto la richiesta appare un po’ inusuale. Tuttavia, il codice DAX viene semplificato e crediamo sia stato un esempio utile a comprendere il potenziale delle funzioni WINDOW, che ricordiamo sono ancora in preview alla data di pubblicazione di questo articolo.
Non abbiamo ancora effettuato test di prestazione delle funzioni WINDOW rispetto al classico approccio DAX, usando DAX Studio. Lo faremo su una base dati più corposa per rendere la stima sensata e pubblicheremo i risultati. Consigliamo di studiare il codice DAX classico mostrato allo scopo di esercizio sulle funzioni tabellari e su RANKX. Saremo lieti di ricevere suggerimenti per semplificarlo o migliorarlo.
Buon DAX!
Autore del Post
