Data Analysis Expressions (DAX) in Power Pivot

Data Analysis Expressions (DAX) può sembrare un po' intimidatorio all'inizio, ma non lasciarti ingannare dal nome. Le basi di DAX sono davvero abbastanza facili da capire. Per prima cosa, DAX NON è un linguaggio di programmazione. DAX è un linguaggio delle formule. È possibile usare DAX per definire calcoli personalizzati per le colonne calcolate e per le misure , noti anche come campi calcolati. DAX include alcune delle funzioni utilizzate nelle formule di Excel e funzioni aggiuntive progettate per lavorare con i dati relazionali ed eseguire l'aggregazione dinamica.

Informazioni sulle formule DAX

Le formule DAX sono molto simili alle formule di Excel. Per crearne uno, digitare un segno di uguale, seguito dal nome di una funzione o da un'espressione e dagli eventuali valori o argomenti necessari. Come Excel, DAX offre un'ampia gamma di funzioni che è possibile usare per usare stringhe, eseguire calcoli usando date e ore o creare valori condizionali.

Tuttavia, le formule DAX sono diverse nei modi importanti seguenti:

• Se si desidera personalizzare i calcoli riga per riga, DAX include funzioni che consentono di utilizzare il valore della riga corrente o un valore correlato per eseguire calcoli che variano in base al contesto.
• DAX include un tipo di funzione che restituisce una tabella come risultato, anziché un singolo valore. Queste funzioni possono essere usate per fornire input ad altre funzioni.
• Le funzioni di Business Intelligence per le gerarchietemporali in DAX consentono calcoli usando intervalli di date e confrontano i risultati tra periodi paralleli.

Dove usare le formule DAX

È possibile creare formule in Power Pivot sia in colonne calcolate che in campi calcolati.

Colonne calcolate

Una colonna calcolata è una colonna aggiunta a una tabella di Power Pivot esistente. Invece di incollare o importare i valori nella colonna, creare una formula DAX che definisce i valori della colonna. Se si include la tabella di Power Pivot in una tabella pivot o in un grafico pivot, la colonna calcolata può essere usata come qualsiasi altra colonna di dati.

Le formule nelle colonne calcolate sono molto simili a quelle create in Excel. Tuttavia, a differenza di Excel, non è possibile creare una formula diversa per righe diverse in una tabella. La formula DAX viene invece applicata automaticamente all'intera colonna.

Quando una colonna contiene una formula, il valore viene calcolato per ogni riga. I risultati vengono calcolati per la colonna non appena si crea la formula. I valori delle colonne vengono ricalcolati solo se i dati sottostanti vengono aggiornati o se viene utilizzato il ricalcolo manuale.

È possibile creare colonne calcolate basate su misure e altre colonne calcolate. Evitare tuttavia di usare lo stesso nome per una colonna calcolata e una misura, poiché ciò può creare risultati confusi. Quando si fa riferimento a una colonna, è consigliabile usare un riferimento di colonna completo, per evitare di richiamare accidentalmente una misura.

Per informazioni più dettagliate, vedere Colonne calcolate in Power Pivot.

Misure

Una misura è una formula creata appositamente per l'uso in una tabella pivot (o grafico pivot) che usa i dati di Power Pivot. Le misure possono essere basate su funzioni di aggregazione standard, ad esempio CONTEGGIO o SOMMA, oppure è possibile definire una formula personalizzata usando DAX. viene usata una misura nell'area Valori di una tabella pivot. Se si vogliono inserire i risultati calcolati in un'area diversa di una tabella pivot, usare invece una colonna calcolata.

Quando si definisce una formula per una misura esplicita, non succede nulla finché non si aggiunge la misura in una tabella pivot. Quando si aggiunge la misura, la formula viene valutata per ogni cella nell'area Valori della tabella pivot. Poiché viene creato un risultato per ogni combinazione di intestazioni di riga e colonna, il risultato della misura può essere diverso in ogni cella.

La definizione della misura creata viene salvata insieme alla tabella dati di origine. Si trova nell'elenco Campi tabella pivot ed è disponibile per tutti gli utenti della cartella di lavoro.

Per altre informazioni dettagliate sui campi calcolati, vedere Misure in Power Pivot.

Creazione di formule con la barra della formula

Power Pivot, come Excel, include una barra della formula per semplificare la creazione e la modifica di formule e la funzionalità di completamento automatico per ridurre al minimo gli errori di digitazione e di sintassi.

Per immettere il nome di una tabella Iniziare a digitare il nome della tabella. Completamento automatico formule include un elenco a discesa contenente i nomi validi che iniziano con tali lettere.

Per immettere il nome di una colonna Digitare una parentesi quadra e quindi scegliere la colonna dall'elenco di colonne nella tabella corrente. Per una colonna di un'altra tabella, iniziare a digitare le prime lettere del nome della tabella e quindi scegliere la colonna nell'elenco a discesa Completamento automatico.

Per altri dettagli e una procedura dettagliata su come creare formule, vedere Creare formule per i calcoli in Power Pivot.

Suggerimenti per l'uso di Completamento automatico

È possibile usare Completamento automatico formule al centro di una formula esistente con funzioni annidate. Il testo immediatamente prima del punto di inserimento viene usato per visualizzare i valori nell'elenco a discesa e tutto il testo dopo il punto di inserimento rimane invariato.

I nomi definiti creati per le costanti non vengono visualizzati nell'elenco a discesa Completamento automatico, ma è comunque possibile digitarli.

Power Pivot non aggiunge le parentesi di chiusura delle funzioni né associa automaticamente le parentesi. È necessario verificare che ogni funzione sia sintatticamente corretta, altrimenti non sarà possibile salvare o usare la formula. 

Uso di più funzioni in una formula

È possibile annidare le funzioni, ossia usare i risultati di una funzione come argomento di un'altra funzione. È possibile annidare fino a 64 livelli di funzioni nelle colonne calcolate. Tuttavia, l'annidamento può rendere difficile la creazione o la risoluzione dei problemi delle formule.

Molte funzioni DAX sono progettate per essere utilizzate esclusivamente come funzioni annidate. Queste funzioni restituiscono una tabella, che non può essere salvata direttamente. Deve essere fornito come input per una funzione di tabella. Le funzioni SOMMA.X, MEDIA.X e MINX, ad esempio, richiedono tutte una tabella come primo argomento.

Confronto tra le funzioni DAX e le funzioni di Excel

La libreria di funzioni DAX si basa sulla libreria di funzioni di Excel, ma presenta molte differenze. In questa sezione sono riepilogate le differenze e le analogie tra le funzioni di Excel e le funzioni DAX.

• Molte funzioni DAX hanno lo stesso nome e lo stesso comportamento generale delle funzioni di Excel, ma sono state modificate per accettare tipi di input diversi e, in alcuni casi, potrebbero restituire un tipo di dati diverso. In generale, non è possibile usare funzioni DAX in una formula di Excel o usare formule di Excel in PowerPivot senza alcune modifiche.
• Le funzioni DAX non accettano mai un riferimento di cella o un intervallo come riferimento, ma invece le funzioni DAX prendono una colonna o una tabella come riferimento.
• Le funzioni di data e ora DAX restituiscono un tipo di dati DateTime. Le funzioni di data e ora di Excel, invece, restituiscono un numero intero che rappresenta una data come numero seriale.
• Molte delle nuove funzioni DAX restituiscono una tabella di valori o eseguono calcoli basati su una tabella di valori come input. Excel non dispone invece di funzioni che restituiscono una tabella, ma alcune funzioni possono essere usate con le matrici. La possibilità di fare riferimento facilmente a tabelle e colonne complete è una nuova funzionalità di Power Pivot.
• DAX offre nuove funzioni di ricerca simili alle funzioni di ricerca su matrice e vettoriale in Excel. Le funzioni DAX richiedono tuttavia che venga stabilita una relazione tra le tabelle.
• Si prevede che i dati in una colonna siano sempre dello stesso tipo. Se i dati non sono dello stesso tipo, DAX modifica l'intera colonna nel tipo di dati più adatto a contenere tutti i valori.

Tipi di dati DAX

È possibile importare dati in un modello di dati di Power Pivot da molte origini dati diverse che potrebbero supportare tipi di dati diversi. Quando si importano o caricano i dati e quindi si usano nei calcoli o nelle tabelle pivot, i dati vengono convertiti in uno dei tipi di dati di Power Pivot. Per un elenco dei tipi di dati, vedere Tipi di dati nei modelli di dati.

Il tipo di dati table è un nuovo tipo di dati in DAX che viene usato come input o output per molte nuove funzioni. La funzione FILTRO, ad esempio, accetta una tabella come input e restituisce un'altra tabella contenente solo le righe che soddisfano le condizioni di filtro. Combinando le funzioni di tabella con le funzioni di aggregazione, è possibile eseguire calcoli complessi su set di dati definiti dinamicamente. Per altre informazioni, vedere Aggregazioni in Power Pivot.

Formule e modello relazionale

La finestra Power Pivot è un'area in cui è possibile usare più tabelle di dati e connettere le tabelle in un modello relazionale. All'interno di questo modello di dati, le tabelle sono connesse tra loro da relazioni, che consentono di creare correlazioni con le colonne in altre tabelle e creare calcoli più interessanti. Ad esempio, è possibile creare formule che sommano i valori per una tabella correlata e quindi salvare tali valori in una singola cella. In alternativa, per controllare le righe della tabella correlata, è possibile applicare filtri a tabelle e colonne. Per altre informazioni, vedere Relazioni tra tabelle in un modello di dati.

Poiché è possibile collegare le tabelle usando relazioni, le tabelle pivot possono includere anche dati di più colonne provenienti da tabelle diverse.

Tuttavia, poiché le formule possono funzionare con intere tabelle e colonne, è necessario progettare i calcoli in modo diverso rispetto a Excel.

• In generale, una formula DAX in una colonna viene sempre applicata all'intero set di valori della colonna (mai solo ad alcune righe o celle).
• Le tabelle in Power Pivot devono sempre contenere lo stesso numero di colonne in ogni riga e tutte le righe di una colonna devono contenere lo stesso tipo di dati.
• Quando le tabelle sono connesse da una relazione, è necessario assicurarsi che le due colonne usate come chiavi abbiano per lo più valori corrispondenti. Poiché Power Pivot non applica l'integrità referenziale, è possibile che in una colonna chiave siano presenti valori non corrispondenti e che venga comunque creata una relazione. Tuttavia, la presenza di valori vuoti o non corrispondenti potrebbe influire sui risultati delle formule e sull'aspetto delle tabelle pivot. Per altre informazioni, vedere Ricerche nelle formule di Power Pivot.
• Quando si collegano le tabelle usando le relazioni, si allarga l'ambito o contesto in cui vengono valutate le formule. Ad esempio, le formule in una tabella pivot possono essere interessate da eventuali filtri o intestazioni di colonna e di riga nella tabella pivot. È possibile scrivere formule che modificano il contesto, ma il contesto può anche causare modifiche ai risultati in modi che non si potrebbero prevedere. Per altre informazioni, vedere Contesto nelle formule DAX.

Aggiornamento dei risultati delle formule

L'aggiornamento e il ricalcolo dei dati sono due operazioni distinte ma correlate che è necessario comprendere quando si progetta un modello di dati contenente formule complesse, grandi quantità di dati o dati ottenuti da origini dati esterne.

L'aggiornamento dei dati è il processo di aggiornamento dei dati nella cartella di lavoro con nuovi dati provenienti da un'origine dati esterna. È possibile aggiornare i dati manualmente a intervalli specificati. In alternativa, se la cartella di lavoro è stata pubblicata in un sito di SharePoint, è possibile pianificare un aggiornamento automatico da origini esterne.

Il ricalcolo è il processo di aggiornamento dei risultati delle formule per riflettere le modifiche apportate alle formule stesse e ai dati sottostanti. Il ricalcolo può influire sulle prestazioni nei modi seguenti:

• Per una colonna calcolata, il risultato della formula dovrebbe essere sempre ricalcolato per l'intera colonna, ogni volta che si modifica la formula.
• Per una misura, i risultati di una formula non vengono calcolati finché la misura non viene inserita nel contesto della tabella pivot o del grafico pivot. La formula verrà ricalcolata anche quando si modifica un'intestazione di riga o di colonna che influisce sui filtri nei dati o quando si aggiorna manualmente la tabella pivot.

Risoluzione dei problemi relativi alle formule

Errori durante la scrittura delle formule

Se viene visualizzato un errore durante la definizione di una formula, la formula potrebbe contenere un errore sintattico, un errore semantico o un errore di calcolo.

Gli errori sintattici sono i più facili da risolvere. In genere comportano la mancanza di una parentesi o di una virgola. Per informazioni sulla sintassi delle singole funzioni, vedere la Guida di riferimento alle funzioni DAX.

L'altro tipo di errore si verifica quando la sintassi è corretta, ma il valore o la colonna a cui si fa riferimento non ha senso nel contesto della formula. Tali errori semantici e di calcolo potrebbero essere causati da uno dei seguenti problemi:

• La formula fa riferimento a una colonna, una tabella o una funzione inesistente.
• La formula sembra corretta, ma quando il motore dati recupera i dati rileva una mancata corrispondenza del tipo e genera un errore.
• La formula passa un numero o un tipo di parametri non corretto a una funzione.
• La formula fa riferimento a una colonna diversa che presenta un errore e pertanto i relativi valori non sono validi.
• La formula fa riferimento a una colonna che non è stata elaborata, ossia include metadati ma nessun dato effettivo da usare per i calcoli.

Nei primi quattro casi, DAX contrassegna l'intera colonna contenente la formula non valida. Nell'ultimo caso, DAX disattiva la colonna per indicare che si trova in uno stato non elaborato.

Risultati non corretti o insoliti durante la classificazione o l'ordinamento dei valori delle colonne

Quando si classifica o si ordina una colonna che contiene il valore NaN (non un numero), si potrebbero ottenere risultati errati o imprevisti. Ad esempio, quando un calcolo divide 0 per 0, viene restituito un risultato NaN.

Ciò è dovuto al fatto che il motore delle formule esegue l'ordinamento e la classificazione confrontando i valori numerici. tuttavia, NaN non può essere confrontato con altri numeri nella colonna.

Per garantire risultati corretti, è possibile usare istruzioni condizionali usando la funzione SE per verificare l'eventuale presenza di valori NaN e restituire un valore numerico 0.

Compatibilità con i modelli tabulari di Analysis Services e la modalità DirectQuery

In generale, le formule DAX compilate in Power Pivot sono completamente compatibili con i modelli tabulari di Analysis Services. Se tuttavia si esegue la migrazione del modello di Power Pivot a un'istanza di Analysis Services e quindi si distribuisce il modello in modalità DirectQuery, esistono alcune limitazioni.

• Alcune formule DAX possono restituire risultati diversi se si distribuisce il modello in modalità DirectQuery.
• Alcune formule potrebbero causare errori di convalida quando si distribuisce il modello in modalità DirectQuery, perché la formula contiene una funzione DAX non supportata in un'origine dati relazionale.

Per ulteriori informazioni, vedere la documentazione sulla modellazione tabulare di Analysis Services nella documentazione online di SQL Server 2012.