Modelli DAX in Power Pivot: formule, contesto e best practice

Se lavori con i dati in Excel e Power PivotPrima o poi ti imbatterai nel DAX. Sebbene il nome "Data Analysis Expressions" suoni tecnico e un po' intimidatorioLa realtà è che, con una buona comprensione dei principi fondamentali, diventa uno strumento molto comodo e potente per modellare le informazioni.

In Power Pivot, DAX è il cuore dei modelli tabulari: Viene utilizzato per creare colonne e misure calcolate che alimentano tabelle pivot e graficiTi consente di giocare con il contesto dei filtri, lavorare con le date, gestire gli errori ed effettuare confronti in il tempo E molto altro ancora. Diamo un'occhiata più da vicino a come tutto questo si inserisce nei modelli DAX di Power Pivot e come sfruttarlo senza impazzire.

Che cos'è DAX e perché è fondamentale nei modelli Power Pivot?

DAX (Data Analysis Expressions) è, soprattutto, un linguaggio di formule progettato per modelli di datinon una lingua di programmazione Classico. La sua funzione è definire calcoli personalizzati da applicare a tabelle e colonne nel modello Power Pivot.

Sebbene visivamente possa assomigliare al formule excel, DAX è progettato per funzionare con dati relazionali e aggregazioni dinamiche.Ciò significa che si integra perfettamente con tabelle collegate da relazioni, tabelle pivot e modelli complessi in cui il contesto del filtro cambia a seconda dei campi utilizzati in righe, colonne o slicer.

All'interno di un modello Power Pivot, è possibile utilizzare DAX per Due tipi principali di oggetti: colonne calcolate e misureEntrambi coesistono all'interno dello stesso modello, ma ognuno ha uno scopo diverso e viene valutato in modo diverso, cosa che dovrebbe essere molto chiara per non confondere i concetti.

Inoltre, DAX include funzioni di testo, data e ora, logiche, matematiche, di filtro e di "time intelligence"così come funzioni che restituiscono tabelle complete. Queste ultime funzioni rappresentano una delle principali differenze rispetto a Excel, dove le formule non restituiscono tabelle vere e proprie, ma al massimo matrici all'interno di intervalli di celle.

Panoramica delle formule DAX e della barra delle formule

Le formule DAX seguono una struttura molto simile a quella di Excel: Iniziano con il segno di uguale (=) e poi si scrive l'espressione o la funzione con i suoi argomentiÈ possibile combinare operatori, funzioni, riferimenti a colonne e tabelle, ecc., proprio come si farebbe quando si crea una formula complessa in un foglio di calcolo.

Tuttavia, ci sono delle sfumature importanti. DAX non funziona mai con riferimenti di tipo A1:C10Invece, si fa riferimento a intere colonne o tabelle, ad esempio 'Saldi' o 'Calendario'Questo ha molto senso in un modello tabellare, ma costringe a cambiare mentalità se si proviene dal mondo puramente "cella per cella" di Excel.

Power Pivot ha un barra della formula simile a quella di Excel che semplifica notevolmente la creazione di espressioni. Include il completamento automatico per funzioni, tabelle e colonne: quando si inizia a digitare il nome di una tabella o di una colonna, viene visualizzato un elenco a discesa con le opzioni valide, riducendo gli errori di sintassi e risparmiando tempo.

Per scrivere i nomi delle tabelle, è sufficiente iniziare a digitare e lasciare che venga eseguito. Il completamento automatico suggerisce nomi corrispondentiPer le colonne, puoi aprire una parentesi e selezionare la colonna nella tabella corrente oppure digitare il nome della tabella seguito dalle parentesi e scegliere dall'elenco.

Tuttavia, a differenza di Excel, Power Pivot non chiude automaticamente le parentesi Non li abbina per te. È tua responsabilità assicurarti che le funzioni siano ben formate, con il numero corretto di argomenti e parentesi complete, altrimenti la formula non può essere salvata o utilizzata.

Dove vengono utilizzate le formule DAX: colonne calcolate e misure

In un modello Power Pivot è possibile scrivere formule DAX in colonne calcolate e nel misure (chiamati anche campi calcolati nel contesto delle tabelle pivot). Sebbene condividano lo stesso linguaggio, si comportano in modi molto diversi.

Colonne calcolate in Power Pivot

Una colonna calcolata è un nuovo campo che aggiungi a una tabella esistente nel modelloInvece di importare quel valore dall'origine dati, si definisce una formula DAX che viene valutata riga per riga. Il risultato viene memorizzato nella colonna corrispondente a ogni riga della tabella.

Le colonne calcolate vengono applicate uniformemente a tutte le righe: Non è possibile avere una formula diversa per ogni riga.A differenza di Excel, dove è possibile trascinare e rilasciare manualmente le parti, in Power Pivot l'espressione definita viene valutata automaticamente per l'intera colonna e ricalcolata quando i dati vengono aggiornati o viene forzato un ricalcolo del modello.

Questo tipo di colonna può essere basato su altre colonne calcolate o in misureTuttavia, si consiglia di non riutilizzare lo stesso nome sia per la misura che per la colonna per evitare confusione durante il riferimento. È consigliabile utilizzare sempre il riferimento completo alla colonna (Tabella) per evitare di fare riferimento accidentalmente a una misura con lo stesso nome.

Le colonne calcolate sono ideali quando hai bisogno attributi aggiuntivi che vuoi usare in righe, colonne, filtri o slicer di tabelle pivot o come chiavi per relazioni. Pensa, ad esempio, a una colonna "Margine" calcolata come - che puoi quindi utilizzare per raggruppare o filtrare.

Misure o campi calcolati

Le misure, da parte loro, sono calcoli che vengono valutati nel contesto di una tabella pivot o di una visualizzazioneNon vengono memorizzati per riga, ma vengono ricalcolati al volo per ogni combinazione di filtri, righe e colonne attiva nel report.

Un misurazione tipica Potrebbe essere qualcosa di semplice come:

Vendite totali = SOMMA(Vendite)

Questa misura, posta nell'area di Valori Nella tabella pivot, ogni cella viene valutata in base al contesto (ad esempio, per anno, per prodotto, per regione...). Lo stesso calcolo restituisce risultati diversi a seconda dei filtri applicati. e la progettazione della tabella pivot.

Le misure non servono a nulla finché non vengono utilizzate in un report. Vengono memorizzati con il modello di dati e compaiono nell'elenco dei campi delle tabelle pivot. in modo che chiunque possa utilizzarli. Sono fondamentali per calcoli aggregati flessibili, come rapporti, percentuali di contribuzione, totali cumulati, confronti tra periodi, ecc.

Differenze principali tra le funzioni DAX e le funzioni Excel

Sebbene molte funzioni DAX assomiglino alle funzioni di Excel nel nome e nel comportamento generale, Non sono semplicemente intercambiabili.Esistono differenze importanti che influiscono sul modo in cui le formule vengono costruite in un modello Power Pivot.

Primo, DAX non funziona con singole celle o intervalliCome riferimento vengono sempre utilizzate intere colonne o tabelle. Questo obbliga a pensare più in termini di set di dati che di singoli elementi, il che si adatta meglio all'approccio di un modello di dati relazionale.

Nell'intervallo di date, DAX restituisce valori di tipo datetime realiSebbene Excel rappresenti solitamente le date come numeri seriali, questa differenza è evidente nella maggior parte dei casi, ma è importante tenerla presente quando si combinano modelli o si importano dati da altri sistemi.

Un altro punto chiave è che Molte delle nuove funzioni DAX restituiscono tabelle complete (ad esempio, FILTRO, TUTTO, VALORI, ecc.) oppure accettano tabelle come argomenti. Excel, d'altra parte, non ha il concetto di funzione "che restituisce una tabella" nello stesso senso, sebbene esistano formule matriciali.

Infine, in DAX si presume che Tutti i valori in una colonna condividono lo stesso tipo di datiSe i tipi di dati sono misti, il motore dati forzerà la conversione dell'intera colonna nel tipo più adatto a tutti i record, il che può talvolta comportare sorprese se le origini dati non vengono controllate attentamente.

Tipi di dati in DAX e tipo di tabella

Quando si importano informazioni in un modello Power Pivot, I dati vengono convertiti in uno dei tipi di dati supportati dal motore. (numeri, testo, valori booleani, date e ore, valuta, ecc.). Questo tipo di dati determina quali operazioni sono valide e come verranno valutate le formule.

Una nuova importante funzionalità rispetto al classico Excel è la tipo di dati della tabellaMolte funzioni DAX accettano un'intera tabella come argomento e restituiscono un'altra tabella come risultato. Ad esempio, FILTER accetta una tabella e una condizione e restituisce una tabella contenente solo le righe che soddisfano la condizione.

Combinazione di funzioni che restituiscono tabelle con funzioni di aggregazione come SUMX, AVERAGEX o MINXÈ possibile sviluppare calcoli altamente sofisticati che operano su sottoinsiemi di dati definiti dinamicamente. Ciò si traduce in aggregazioni personalizzate che si adattano ai filtri attivi in ​​un dato momento.

Relazioni, contesto e modello relazionale in Power Pivot

La finestra di Power Pivot è quella in cui viene creato il modello di dati relazionali. Qui puoi importare più tabelle e creare relazioni tra di esse. (ad esempio, Vendite con Prodotti, Vendite con Calendario, Vendite con Clienti, ecc.). Queste relazioni costituiscono la base per consentire alle formule DAX di passare da una tabella all'altra.

Quando le tabelle sono correlate, È possibile scrivere formule che aggiungano valori da una tabella correlata e utilizzarli nella tabella da cui si sta scrivendo l'espressione. È anche possibile controllare quali righe partecipano a un calcolo applicando filtri a colonne specifiche.

È importante porre attenzione Tutte le righe in una tabella Power Pivot devono avere lo stesso numero di colonneOgni colonna deve mantenere un tipo di dati coerente in tutte le sue righe. Se le chiavi di relazione presentano valori non corrispondenti (vuoti, valori orfani, ecc.), le formule di ricerca e le tabelle pivot potrebbero restituire risultati imprevisti.

Un altro concetto fondamentale è il contestoIn DAX, vengono utilizzati principalmente i termini contesto di riga e contesto di filtro. Il contesto di riga è la riga "corrente" su cui viene valutata una colonna calcolata o un iteratore; il contesto di filtro è l'insieme dei filtri attivi (dalla tabella pivot, dagli slicer, dalle relazioni, da funzioni come CALCULATE, ecc.).

Giocando con funzioni come CALCULATE, ALL, ALLEXCEPT o FILTER, puoi Modificare il contesto del filtro per cambiare il modo in cui viene valutata una misura.Ciò consente, ad esempio, di calcolare la percentuale di vendite di un prodotto rispetto al totale, oppure di confrontare le performance di una divisione con quelle dell'azienda nel suo complesso, senza filtri.

Aggiornamento dei dati e ricalcolo della formula DAX

In un modello che utilizza formule complesse o grandi volumi di dati, è fondamentale comprendere come funziona l'aggiornamento. È importante distinguere tra l'aggiornamento dei dati e il ricalcolo delle formule.che sono processi correlati ma indipendenti.

L'aggiornamento dei dati consiste in portare nuovi record nel libro da fonti esterne (database(file, servizi online, ecc.). È possibile avviare questo aggiornamento manualmente quando necessario o programmarlo se il libro è pubblicato in SharePoint o un altro ambiente compatibile. Spesso questo processo viene eseguito utilizzando Power Query in Excel per preparare e trasformare i dati prima di caricarli nel modello.

Il ricalcolo, d'altra parte, è il processo mediante il quale Le formule DAX vengono rivalutate per riflettere le modifiche nei dati o nelle espressioni stesse.Per le colonne calcolate, se si modifica la formula, l'intera colonna deve essere ricalcolata contemporaneamente. Per le misure, il ricalcolo avviene quando il contesto viene modificato (filtri, campi riga/colonna della tabella pivot) o quando le tabelle pivot vengono aggiornate manualmente.

Questi ricalcoli possono avere un impatto sulle prestazioni, in particolare se vengono utilizzate molte colonne complesse calcolate o funzioni iterative intensive in tabelle di grandi dimensioniPertanto, una buona pratica è quella di spostare la maggior parte della logica sulle misure anziché sulle colonne, ove possibile.

Rilevamento e correzione degli errori nelle formule DAX

Quando si scrivono formule DAX, è comune riscontrare tre tipi di errori: errori sintattici, errori semantici ed errori di calcoloOgnuno ha le sue circostanze e il suo modo di correggersi.

Gli errori di sintassi sono i più semplici: parentesi mancanti, virgole fuori posto, nomi di funzioni scritti in modo erratoecc. La guida di completamento automatico e il riferimento alla funzione DAX ti salvano da molte di queste insidie.

Errori semantici e di calcolo si verificano quando, nonostante la sintassi sia corretta, La formula fa qualcosa che non ha senso nel contesto del modello.Ad esempio, fare riferimento a una tabella o a una colonna inesistente, passare un numero errato di argomenti a una funzione, mescolare tipi incompatibili o dipendere da una colonna con errori precedenti.

In questi casi, DAX di solito segna L'intera colonna è stata calcolata come errata.Non solo una riga specifica, perché la colonna è considerata un'unità. Se una colonna contiene solo metadati ma non è ancora stata elaborata (non ha dati caricati), apparirà in grigio e le formule che dipendono da essa non saranno in grado di valutare correttamente.

Un caso speciale sono i valori NaN (non un numero)Questi valori possono apparire, ad esempio, dividendo 0 per 0. Se una colonna contiene valori NaN, l'ordinamento o la classificazione di questi valori può produrre risultati anomali, poiché i valori NaN non possono essere confrontati nel modo consueto con altri numeri. In questi casi, è consigliabile utilizzare istruzioni IF o altre funzioni logiche per sostituire i valori NaN con 0 o un altro valore numerico gestibile.

Compatibilità con modelli tabulari e modalità DirectQuery

Le formule DAX create in Power Pivot sono, in generale, compatibile con i modelli tabulari di SQL Server Analysis ServicesCiò significa che puoi migrare il tuo modello su un server tabulare e continuare a sfruttare la logica che hai già creato.

Tuttavia, quando un modello tabulare viene implementato in modalità DirectQueryPotrebbero presentarsi delle limitazioni: Alcune funzioni DAX non sono supportate direttamente su determinati database relazionali. oppure potrebbero restituire risultati leggermente diversi a causa del modo in cui vengono delegate le query.

In questi scenari, è importante rivedere la documentazione specifica per il motore tabulare e convalidare le misure critiche per confermare che continuino a funzionare come previsto dopo l'attivazione di DirectQuery.

Scenari pratici: calcoli complessi con CALCULATE e filtri

Uno dei punti di forza del DAX è la sua capacità di eseguire calcoli complessi che si basano su aggregazioni personalizzate e filtri dinamiciLe funzioni CALCULATE e CALCULATETABLE sono fondamentali in questo tipo di scenario.

CALCOLA consente ridefinire il contesto del filtro su cui viene valutata un'espressioneAd esempio, puoi richiedere "la somma delle vendite filtrate per un anno specifico, anche se la tabella pivot mostra altri anni" oppure "il totale senza applicare determinati filtri di prodotto".

Ovunque una funzione DAX accetti una tabella come argomento, Puoi passare una versione filtrata di quella tabellaQuesto può essere fatto utilizzando FILTER o specificando condizioni all'interno di CALCULATE. In questo modo è possibile creare misure che si adattano a migliaia di combinazioni di condizioni senza dover creare colonne intermedie.

È anche possibile rimuovere selettivamente i filtri esistenti Utilizzando funzioni come ALL o ALLEXCEPT. Ad esempio, per calcolare il contributo di un rivenditore specifico rispetto al numero totale di rivenditori, è possibile utilizzare una misura che divida il valore nel contesto corrente per il valore nel contesto "ALL" (senza filtri per rivenditore).

In altri casi, sarà necessario utilizzare valori di un “ciclo esterno”Vale a dire, per fare riferimento alla riga precedente o al contesto di iterazione. È qui che entrano in gioco funzioni come EARLIER, che consentono fino a due livelli di cicli annidati e sono molto utili per creare classifiche, totali di gruppo o calcoli che dipendono da un contesto di riga precedente.

Lavoro con testo, date e chiavi in ​​DAX

DAX offre anche molti strumenti per manipolare testo e dateCiò è fondamentale quando le origini dati contengono date in formati insoliti, chiavi composte o campi di testo che devono essere convertiti in valori di tempo.

Power Pivot non li supporta direttamente chiavi composte nelle relazioniSe la tua origine utilizza più colonne come chiavi, in molti casi dovrai creare una colonna calcolata che concatena tali parti in un'unica chiave e utilizzarlo come campo relazionale.

Quando le date sono in formati non riconosciuti dal motore (ad esempio, una data in un formato regionale insolito o un numero intero come 01032009 importato come testo), è possibile creare formule come questa:

=DATA(DESTRA(,4), SINISTRA(,2), CENTRO(,3,2))

Con questo tipo di espressione, Si ricostruisce una data SQL Server valida da frammenti estratti dalla stringa, che consente quindi di utilizzare le funzioni di time intelligence senza problemi.

È anche possibile modificare i tipi di dati utilizzando le formuleMoltiplica per 1,0 per convertire date o stringhe numeriche in numeri, oppure concatena con una stringa vuota per trasformare un numero o una data in testo. Inoltre, sono disponibili funzioni specifiche per controllare il tipo di ritorno (troncamento dei decimali, forzatura degli interi, ecc.).

Valori condizionali e gestione degli errori in colonne e misure

Proprio come in Excel, DAX include funzioni per restituire risultati in base alle condizioni e gestire gli errori in modo elegante. Ad esempio, è possibile etichettare i rivenditori come "Preferiti" o "Valore" in base al loro volume di vendite annuale utilizzando istruzioni IF nidificate.

In una colonna calcolata, tuttavia, Non puoi permetterti che alcune righe contengano errori e altre no.Se una riga produce un errore, l'intera colonna viene contrassegnata come errata. Ciò richiede un controllo degli errori più rigoroso rispetto a un foglio di calcolo convenzionale.

Per evitare che una semplice divisione per zero o un valore vuoto provochi l'arresto anomalo dell'intera colonna, si consiglia includere le operazioni sensibili nei controlli precedenti utilizzando le funzioni SE e informazione, restituendo sempre un valore valido anche quando la combinazione di dati è strana.

Quando si costruisce il modello, può essere utile Lascia che gli errori appaiano all'inizio, così potrai individuarli e correggerli.Ma una volta pubblicato per altri utenti, è importante assicurarsi che le formule siano sicure e che non compaia mai alcun messaggio di errore nelle tabelle pivot o nelle visualizzazioni.

Intelligenza temporale: totali cumulativi, confronti e periodi personalizzati

Le funzionalità di time intelligence rappresentano una delle grandi attrazioni di DAX. Consentono di lavorare con intervalli di date, calcolare totali cumulativi, confrontare periodi e generare finestre temporali personalizzate. con relativa facilità, a patto di avere una tabella del calendario ben configurata.

Le misure possono essere create vendite cumulative al giorno, mese, trimestre o annoCalcola i saldi di apertura e chiusura per ciascun periodo oppure confronta le vendite di un anno con quelle dell'anno precedente, di un trimestre all'altro, ecc., utilizzando funzioni temporali specifiche.

Inoltre, puoi recuperare set di date personalizzatiad esempio "i primi 15 giorni dopo l'inizio di una promozione" o "lo stesso periodo dell'anno scorso", quindi passare tale insieme a una funzione che aggrega i dati in quella specifica finestra di date.

Funzioni come PARALLELPERIOD e altre relative ai periodi paralleli Facilitano il confronto tra intervalli spostati nel tempo.Ad esempio, per analizzare se una campagna ha migliorato i risultati rispetto allo stesso periodo di un altro anno.

Classifica e confronto dei valori: top N e classifiche dinamiche

Quando hai bisogno di mostrare solo gli elementi più rilevanti (ad esempio, i 10 prodotti più venduti), hai due percorsi principali: utilizzare le funzioni di filtro di Excel sulla tabella pivot o creare una classifica dinamica con DAX.

Excel offre filtri di tipo "Top 10" nelle tabelle pivot, molto facile da configurare Per visualizzare solo gli elementi sopra o sotto un determinato campo numerico. È possibile filtrare per numero di elementi, percentuale cumulativa o somma dei valori.

Il problema con questo approccio è che Il filtro ha una funzione puramente di presentazione.Se i dati sottostanti cambiano, è necessario aggiornare manualmente la tabella pivot affinché il filtro venga applicato correttamente. Inoltre, non è possibile riutilizzare tale classificazione come parte di altre formule DAX.

L'alternativa è crearne uno colonna o misura calcolata che assegna una classifica a ciascun elemento utilizzando DAX. Questa opzione è più dispendiosa in termini di calcolo, ma presenta dei vantaggi: la classifica viene ricalcolata dinamicamente e può essere utilizzata nei data slicer, consentendo all'utente di scegliere se visualizzare i primi 5, i primi 10, i primi 50, ecc.

Tuttavia, nei modelli con milioni di righe, Le classifiche dinamiche possono essere macchinose. ed è necessario valutare se il costo in termini di prestazioni è compensato dal beneficio funzionale che forniscono.

Procedure consigliate per la progettazione di modelli DAX in Power Pivot

Affinché un modello DAX in Power Pivot sia gestibile e funzioni bene, non è sufficiente che le formule "funzionino". È consigliabile seguire una serie di buone pratiche che fanno una grande differenza nei progetti reali.

Una raccomandazione ricorrente è dare priorità alle misure rispetto alle colonne calcolate quando il calcolo è in realtà un'aggregazione dinamica e non un attributo fisso. Le colonne calcolate occupano memoria e vengono ricalcolate tutte in una volta, mentre le misure vengono valutate solo quando necessario.

È anche molto utile utilizzo di variabili in DAX (VAR) Per semplificare le formule complesse, evita di ripetere lo stesso calcolo più volte e migliora la leggibilità. Questo migliora sia le prestazioni che la comprensione del modello quando qualcun altro lo esamina.

Infine, nomi chiari e una documentazione interna minima fanno la differenza. Assegna nomi descrittivi alle misure e alle colonneEvita abbreviazioni poco chiare e documenta le formule più importanti. Questo riduce la curva di apprendimento per i nuovi utenti e ti risparmia grattacapi quando tornerai al modello mesi dopo.

Per padroneggiare DAX in Power Pivot non è necessario memorizzare tutte le funzioni, ma comprendere come le formule interagiscono con il modello relazionale, il contesto del filtro e gli aggiornamenti dei dati. Con solide basi in colonne calcolate, misure, funzioni temporali, gestione degli errori e buone pratiche di progettazioneI modelli tabulari diventano più flessibili, molto più facili da analizzare e, soprattutto, in grado di rispondere a complesse domande aziendali semplicemente trascinando alcuni campi in una tabella pivot.