Il collega e amico Fabio Zambelli dello Studio associato BDZ mi ha posto, qualche giorno fa, un challenge interessante: creare in DAX una tabella che lui aveva già creato in Power Query.
Il challenge è interessante anche perché pone una questione che solletica tutti gli sviluppatori di Power BI, prima o poi: dovendo creare una tabella calcolata o una colonna calcolata, è meglio farlo in Power Query (M) o in Analysis Services (DAX)? Rispondere a questa domanda, tuttavia, va oltre lo scopo di questo articolo.
Lo scopo della tabella da creare è il seguente: si supponga di avere, in una tabella dei fatti, delle righe indicanti un conto (si tratta di dati contabili), un centro di costo, una data e un valore corrispondente al dato conto, pertinente al dato centro di costo in quella data. Si supponga anche che, tra i centri di costo, ce ne sia uno – lo si indicherà come DA ALLOCARE – che deve essere allocato sugli altri. Infine, si supponga di avere, in una seconda tabella dei fatti, per ogni coppia conto – centro di costo in cui il centro di costo sia DA ALLOCARE, le quote percentuali del valore del conto da attribuire agli altri centri di costo.
Lo scopo è ottenere una terza tabella dei fatti, “espansa” (non confondiamo il concetto con quello delle tabelle espanse in DAX!), cioè riportante soltanto i centri di costo diversi da DA ALLOCARE. In altri termini, in questa tabella, ogni conto è allocato ai centri di costo propri. La denominazione “espansa” mi è venuta in mente per indicare che il numero di righe di questa terza tabella dei fatti sarà, in generale, maggiore di quello della tabella dei fatti di partenza.
L’esercizio, non banale per chi è giovane in DAX, è un’ottima occasione per introdurre una delle funzioni meno usate ma più potenti del DAX: GENERATE.
GENERATE è poco usata per due ragioni: 1) non ne si conosce, in molti casi, l’esistenza e 2) una volta conosciuta, appare piuttosto temibile.
Ecco un’immagine del modello in uso (figura 1).
La tabella Fatti contiene i fatti da allocare, eccone il contenuto (figura 2). Si tratta di 9 righe.
In figura 3, è mostrato il contenuto della tabella Quote Allocazioni. Si tratta di otto righe. Ogni conto ha, in quattro righe, le quote percentuali del suo valore da attribuire agli altri quattro centri di costo quando il suo centro di costo di partenza sia DA ALLOCARE.
I centri di costo sono, infatti, cinque in tutto e sono visibili esaminando il contenuto della dimensione Centro di Costo (figura 4).
Infine, il modello ha un’altra dimensione che riporta l’elenco dei conti (figura 5).
Sviluppo
Quante righe dovrà avere la tabella dei fatti da creare (Fatti Allocati)? Per ogni riga della tabella Fatti, avrà una o più righe: una nel caso in cui il centro di costo della riga di Fatti sia diverso da DA ALLOCARE, in altri termini tale riga deve essere identica in Fatti Allocati; quattro nel caso in cui il centro di costo della riga di Fatti sia pari a DA ALLOCARE, ogni riga delle quattro riporterà la quota del valore della riga di Fatti da attribuire ad uno dei quattro centri di costo di destinazione (CC1, CC2, CC3 e CC4).
Le righe 2, 6 e 7 di Fatti dovranno, dunque, risultare complessivamente in dodici righe (quattro per ognuna) nella tabella Fatti Allocati. Le altre sei righe di Fatti, invece, saranno riportate senza modifiche in Fatti Allocati, risultando in sei righe. Dunque, sono attese diciotto righe nella tabella Fatti Allocati.
Come immaginare la costruzione della tabella Fatti Allocati (e dunque del codice DAX)? Ragionando: dovrà essere iterata la tabella Fatti e, per ogni sua riga, dovranno essere riportate le corrispondenti righe di Quote Allocazioni. Dunque, un prodotto cartesiano? No, perché il conto e il centro di costo dovranno essere gli stessi in Fatti e in Quote Allocazioni (il centro di costo di Fatti, in altri termini, dovrà essere pari a DA ALLOCARE). La quantità di righe creata da GENERATE sarà limitata dall’imposizione di questa condizione. In assenza di limitazioni, GENERATE creerà un prodotto cartesiano completo.
Ecco la sintassi di GENERATE, una funzione tabellare e, allo stesso tempo, un iteratore.
GENERATE ( EspressioneTabella1, EspressioneTabella2 )
GENERATE itererà la tabella risultante da EspressioneTabella1 (creerà, cioè, un row context su di essa) e, per ogni sua riga, valuterà EspressioneTabella2 nel row context creato su EspressioneTabella1. Dunque, la tabella risultante avrà tutte le colonne di entrambe le espressioni tabellari in input (questo può generare qualche grattacapo quando nelle due tabelle ci siano colonne con lo stesso nome, si veda la nota a fine articolo). Tuttavia, esaminando la semantica da DAX Guide (https://dax.guide/generate/), si nota che GENERATE non porta in uscita le righe di EspressioneTabella1 che non abbiano una corrispondenza in EspressioneTabella2 cioè in corrispondenza delle quali EspressioneTabella2 restituisce una tabella vuota. Nel nostro caso, tutte le righe di Fatti con un centro di costo diverso da DA ALLOCARE non vedrebbero, dunque, la luce in Fatti Allocati. Questa semantica di GENERATE non è in linea con quanto richiesto in questo caso. Per fortuna, esiste GENERATEALL che, in ogni caso, riporta in uscita tutte le righe della prima tabella in input e, per il resto, è identica a GENERATE.
Senza ulteriori indugi, ecco il DAX che genera la tabella Fatti Allocati:
Fatti Allocati =— per rinominare le colonne a nostro piacimento
ADDCOLUMNS (
GENERATEALL (
Fatti,
FILTER (
SUMMARIZE (
‘Quote Allocazioni’,
‘Quote Allocazioni'[Conto da Allocare],
‘Quote Allocazioni'[Centro di Costo Originario],
‘Quote Allocazioni'[Centro di Costo Destinazione],
‘Quote Allocazioni'[Quota allocazione pct]
),
— condizione di FILTER per evitare di generare combinazioni inutili
‘Quote Allocazioni'[Conto da Allocare] = Fatti[Conto]
&& ‘Quote Allocazioni'[Centro di Costo Originario] = Fatti[Centro di costo]
)
),
“@CentrodiCostoFinale”,
SWITCH (
TRUE,
Fatti[Centro di costo] <> “DA ALLOCARE”, Fatti[Centro di costo],
‘Quote Allocazioni'[Centro di Costo Destinazione]
),
“@ValoreFinale”,
IF (
‘Quote Allocazioni'[Quota allocazione pct] <> BLANK (),
Fatti[Valore] * ‘Quote Allocazioni'[Quota allocazione pct],
Fatti[Valore]
)
),
“Conto”, Fatti[Conto],
“Data”, Fatti[Data],
“Centro di Costo”, [@CentrodiCostoFinale],
“Valore”, [@ValoreFinale]
)
Nel codice sopra riportato, il raggruppamento tramite SUMMARIZE non è strettamente necessario, si potrebbe usare la tabella Quote Allocazioni così com’è visto che essa contiene soltanto le colonne minime necessarie al calcolo. Tuttavia, nel caso essa contenga colonne non strettamente necessarie, è bene raggruppare soltanto quelle utili, ecco il perché del codice proposto che resta, in questo modo, generale. Ecco la tabella Fatti Allocati vera e propria (figura 6), consistente in diciotto righe, come desiderato.
In figura 7 è mostrata una matrice che evidenzia, conto per conto e centro di costo per centro di costo, le allocazioni effettuate.
Nella matrice mostrata in figura 7, nella sezione Righe sono state inserite le colonne Conto[Conto] e ‘Centro di Costo'[Centro di Costo]. Le misure inserite in valori hanno i seguenti codici DAX.
Valori Fatti =Infine, ecco come la tabella Fatti Allocati è stata inserita nel modello dati, giustificando i risultati mostrati in figura 7 (figura 8).
Conclusioni
Attraverso GENERATE e GENERATEALL è possibile creare tabelle complesse in modo relativamente semplice. Il codice mostrato è meno complesso di quanto appaia. Tuttavia, non si può nascondere che GENERATE e GENERATEALL non appaiano semplici. E non sono facili da usare. Come, del resto, il DAX.
Attenzione: se il nome di alcune colonne nelle due tabelle input di GENERATE è uguale, GENERATE restituisce un errore e bisogna usare SELECTCLOUMNS per cambiare i nomi in una delle due tabelle, in memoria, durante l’esecuzione del calcolo.
Rimane una domanda: è meglio fare questo tipo di elaborazioni in Power Query – cioè mediante M – o in Analysis Services – cioè mediante DAX -, quando l’opzione di scelta esiste? Questo interrogativo sarà oggetto del prossimo articolo!
Autore del Post
