Contesto nelle formule DAX

Articolo
09/10/2013

Il contesto consente di eseguire analisi dinamiche, nelle quali i risultati di una formula possono venire modificati per riflettere la riga o la selezione della cella corrente e anche eventuali dati correlati. La comprensione del concetto di contesto e l'utilizzo efficace di quest'ultimo sono fondamentali per la creazione di analisi dinamiche ad elevate prestazioni e per la risoluzione dei problemi riscontrati nelle formule.

In questa sezione vengono definiti i diversi tipi di contesto: contesto di riga, contesto di query e contesto di filtro. Viene illustrato come viene valutato il contesto per le formule presenti nelle colonne calcolate e nelle tabelle pivot.

L'ultima parte di questa sezione fornisce collegamenti a esempi dettagliati che illustrano come i risultati delle formule cambino in base al contesto.

Introduzione al contesto

Le formule di PowerPivot possono essere influenzate dai filtri applicati in una tabella pivot, dalle relazioni tra tabelle e dai filtri utilizzati nelle formule. Il contesto è quanto rende possibile l'esecuzione dell'analisi dinamica. La comprensione del concetto di contesto è importante per la creazione e la risoluzione dei problemi relativi alle formule.

Esistono diversi tipi di contesto: contesto di riga, contesto di query e contesto di filtro.

Il contesto di riga può essere considerato come la "riga corrente". Se è stata creata una colonna calcolata, il contesto di riga corrisponde ai valori in ogni singola riga e ai valori nelle colonne correlate alla riga corrente. Esistono inoltre alcune funzioni (EARLIER e EARLIEST) che ottengono un valore dalla riga corrente che, successivamente, viene utilizzato durante l'esecuzione di un'operazione in un'intera tabella.

Con il termine contesto di query si intende il subset di dati che viene creato in modo implicito per ogni cella di una tabella pivot, in base alle intestazioni di riga e di colonna.

Il contesto di filtro è il set di valori consentiti in ogni colonna, in base ai vincoli di filtro applicati alla riga o definiti dalle espressioni di filtro all'interno della formula.

Nell'argomento seguente vengono illustrati in dettaglio i tipi diversi di contesto: Contesto nelle formule DAX.

Torna all'inizio

Contesto di riga

Se si crea una formula in una colonna calcolata, il contesto di riga per tale formula include i valori di tutte le colonne presenti nella riga corrente. Se la tabella è correlata a un'altra tabella, il contenuto include anche tutti i valori dell'altra tabella che sono correlati alla riga corrente.

Si supponga ad esempio di creare una colonna calcolata, =[Freight] + [Tax], che somma due colonne della stessa tabella. Questa formula ha un comportamento analogo a quello delle formule di una tabella di Excel, che fanno automaticamente riferimento ai valori della stessa riga. Le tabelle sono diverse dagli intervalli: non è possibile fare riferimento a un valore dalla riga precedente alla riga corrente mediante la notazione degli intervalli e non è possibile fare riferimento ad alcun singolo valore arbitrario in una tabella o in una cella. È sempre necessario utilizzare tabelle e colonne.

Il contesto di riga segue automaticamente le relazioni tra tabelle per determinare quali righe nelle tabelle correlate sono associate alla riga corrente.

Nella formula seguente viene ad esempio utilizzata la funzione RELATED per recuperare un valore relativo all'imposta da una tabella correlata, in base all'area in cui è stato eseguito l'ordine. Il valore dell'imposta viene determinato utilizzando il valore per area nella tabella corrente, effettuando la ricerca dell'area nella tabella correlata e quindi ottenendo l'aliquota di imposta per tale area dalla tabella correlata.

= [Freight] + RELATED('Region'[TaxRate])

Questa formula ottiene semplicemente l'aliquota di imposta per l'area corrente dalla tabella Area. Non è necessario conoscere o specificare la chiave che connette le tabelle.

Contesto di più righe

In DAX sono inoltre incluse funzioni che iterano i calcoli in una tabella. Queste funzioni possono presentare più righe correnti e contesti di riga correnti. In termini di programmazione, è possibile creare formule ricorsive in un ciclo interno ed esterno.

Si supponga ad esempio che una cartella di lavoro contenga una tabella Prodotti e una tabella Vendite. Potrebbe essere necessario scorrere l'intera tabella delle vendite, piena di transazioni che riguardano più prodotti, e individuare la quantità più grande ordinata per ogni prodotto in una transazione qualunque.

In Excel questo calcolo richiede una serie di riepiloghi intermedi, che dovrebbero essere ricompilati se i dati venissero modificati. Se si è un utente esperto di Excel, è possibile compilare formule in forma di matrice che eseguano tali calcoli. In alternativa, è possibile utilizzare un database relazionale per scrivere istruzioni sub-SELECT nidificate.

Tuttavia, con DAX è possibile compilare una sola formula che restituisce il valore corretto e i risultati vengono aggiornati automaticamente tutte le volte che si aggiungono dati alle tabelle.

=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])

Per una procedura dettagliata di questa formula, vedere Funzione EARLIER.

In breve, la funzione EARLIER archivia il contesto di riga dall'operazione che ha preceduto l'operazione corrente. La funzione archivia sempre in memoria due set di contesto: uno rappresenta la riga corrente del ciclo interno della formula e l'altro rappresenta la riga corrente del ciclo esterno della formula. DAX utilizza automaticamente i valori tra due cicli in modo che sia possibile creare aggregazioni complesse.

Contesto di query

Il contesto di query fa riferimento al subset di dati che viene recuperato in modo implicito per una formula. Quando si inserisce una misura o un altro campo del valore in una cella di una tabella pivot, il motore di PowerPivot esamina le intestazioni di riga e colonna, i filtri dei dati e i filtri dei report per determinare il contesto. Quindi, PowerPivot esegue i calcoli necessari per popolare ogni cella nella tabella pivot. Il set di dati recuperato è il contesto di query per ogni cella.

Poiché il contesto può essere modificato in base a dove si posiziona la formula, anche i risultati della formula possono essere modificati a seconda del fatto che la formula venga utilizzata in una tabella pivot con numerosi raggruppamenti e filtri o in una colonna calcolata senza filtri e contesto minimo.

Si prenda ad esempio in considerazione questa semplice formula, in cui vengono sommati i valori della colonna Profit della tabella Sales: =SUM('Sales'[Profit]). Se tale formula viene utilizzata in una colonna calcolata all'interno della tabella Vendite, i risultati saranno uguali per l'intera tabella, in quanto il contesto di query per la formula è sempre l'intero set di dati della tabella Vendite. I risultati indicheranno i profitti per tutte le aree, tutti i prodotti, tutti gli anni e così via.

In genere, tuttavia, non è necessario visualizzare lo stesso risultato centinaia di volte, poiché è più utile ottenere i profitti per un anno, un paese, un prodotto specifico o una combinazione di tali elementi, per pervenire quindi a un totale complessivo.

Per modificare il contesto in una tabella pivot è sufficiente aggiungere o rimuovere intestazioni di colonna e di riga, nonché aggiungere o rimuovere filtri dei dati. È possibile creare una formula, come quella sopra riportata, in una misura, quindi inserirla in una tabella pivot. Ogni qualvolta si aggiungono intestazioni di riga o di colonna alla tabella pivot, si modifica il contesto di query nel quale viene valutata la misura. Anche le operazioni di applicazione di filtri influiscono sul contesto. Pertanto la stessa formula, utilizzata in una tabella pivot, viene valutata in un contesto di query diverso per ogni cella.

Contesto di filtro

Il contesto di filtro viene aggiunto quando si specificano vincoli del filtro sul set di valori consentito in una colonna o una tabella utilizzando gli argomenti di una formula. Il contesto di filtro viene applicato ad altri contesti, ad esempio il contesto di riga o il contesto di query.

Ad esempio, in una tabella pivot i valori vengono calcolati per ogni cella in base alle intestazioni di riga e di colonna, come descritto nella sezione precedente relativa al contesto di query. Tuttavia, all'interno delle misure o delle colonne calcolate che vengono aggiunte alla tabella pivot, è possibile specificare espressioni di filtro per controllare i valori utilizzati dalla formula. È inoltre possibile deselezionare i filtri in modo selettivo in colonne specifiche.

Per ulteriori informazioni sulla creazione di filtri nelle formule, vedere Funzione FILTER.

Per un esempio della modalità di deselezione dei filtri per creare totali complessivi, vedere Funzione ALL.

Per esempi di deselezione e applicazione di filtri in modo selettivo all'interno di formule, vedere Funzione ALLEXCEPT.

È pertanto necessario controllare la definizione delle misure o delle formule utilizzate in una tabella pivot in modo da tenere presente il contesto di filtro nell'interpretazione dei risultati delle formule.

Determinazione del contesto nelle formule

Quando si crea una formula, PowerPivot per Excel verifica prima la sintassi generale, quindi controlla i nomi delle colonne e delle tabelle forniti in base alle possibili colonne e tabelle del contesto corrente. Se PowerPivot non è in grado di individuare le colonne e le tabelle specificate dalla formula, verrà visualizzato un messaggio di errore.

Il contesto viene determinato come descritto nelle sezioni precedenti, utilizzando le tabelle disponibili nella cartella di lavoro, eventuali relazioni tra le tabelle ed eventuali filtri applicati.

Se ad esempio sono appena stati importati dati in una nuova tabella e non è stato applicato alcun filtro, tutto il set di colonne della tabella fa parte del contesto corrente. Se più tabelle sono collegate da relazioni e si utilizza una tabella pivot filtrata aggiungendo intestazioni di colonna e utilizzando filtri dei dati, il contesto include le tabelle correlate ed eventuali filtri sui dati.

Quello di contesto è un concetto articolato e complesso, che può rendere difficile risolvere i problemi relativi alle formule. Si consiglia di iniziare con formule e relazioni semplici per comprendere il funzionamento del contesto, sperimentando quindi l'utilizzo di formule semplici nelle tabelle pivot. Nella sezione seguente vengono forniti anche alcuni esempi del modo in cui le formule utilizzano tipi diversi di contesto per restituire risultati in modo dinamico.

Esempi di contesto nelle formule

La funzione RELATED espande il contesto della riga corrente per includere i valori di una colonna correlata. Ciò consente di eseguire ricerche. Nell'esempio di questo argomento viene illustrata l'interazione del contesto di filtro e del contesto di riga.
La funzione FILTER consente di specificare le righe da includere nel contesto corrente. Negli esempi di questo argomento viene inoltre illustrato come incorporare filtri in altre funzioni che eseguono aggregazioni.
La funzione ALL imposta il contesto all'interno di una formula. È possibile utilizzarla per eseguire l'override dei filtri applicati come risultato del contesto di query.
La funzione ALLEXCEPT consente di rimuovere tutti i filtri, ad eccezione di quello specificato. In entrambi gli argomenti sono inclusi esempi che illustrano la compilazione di formule e forniscono informazioni sui contesti complessi.
Le funzioni EARLIER e EARLIEST consentono di eseguire il ciclo in tabelle mediante l'esecuzione di calcoli, facendo riferimento nel contempo a un valore proveniente da un ciclo interno. Se si ha familiarità con il concetto di ricorsione e con i cicli interni ed esterni, sarà più facile apprezzare l'efficacia offerta dalle funzioni EARLIER ed EARLIEST. Se non si ha familiarità con tali concetti, è necessario seguire gli esempi dettagliati per informazioni sulle modalità di utilizzo dei contesti interni ed esterni nell'esecuzione di calcoli.

Integrità referenziale

In questa sezione vengono illustrati alcuni concetti relativi ai valori mancanti nelle tabelle di PowerPivot connesse da una relazione. Questa sezione può risultare utile se si dispone di cartelle di lavoro con più tabelle e formule complesse e si desidera un supporto per l'interpretazione dei risultati.

Se non si ha familiarità con i concetti dei dati relazionali, si consiglia di leggere prima l'argomento introduttivo, Panoramica sulle relazioni.

Integrità referenziale e relazioni PowerPivot

In PowerPivot non è necessaria l'applicazione dell'integrità referenziale tra due tabelle per definire una relazione valida. Viene invece creata una riga vuota sul lato "uno" di ogni relazione uno-a-molti utilizzata per gestire tutte le righe della tabella correlata per cui non viene trovata alcuna corrispondenza. con un comportamento efficace analogo a quello di un outer join SQL.

Nelle tabelle pivot se si raggruppano i dati in base al lato uno della relazione, i dati senza corrispondenza sul lato molti della relazione vengono raggruppati insieme e verranno inclusi nei totali con un'intestazione di riga vuota. L'intestazione vuota è approssimativamente equivalente al "membro sconosciuto".

Informazioni sul membro sconosciuto

Il concetto di membro sconosciuto è probabilmente familiare se si sono utilizzati sistemi di database multidimensionali, ad esempio SQL Server Analysis Services. In caso contrario, nell'esempio seguente vengono illustrati il membro sconosciuto e la modalità con la quale tale membro influisce sui calcoli.

Si supponga ad esempio di voler creare un calcolo che consenta di sommare le vendite mensili di ogni negozio, ma in una colonna della tabella Sales manchi il valore relativo al nome del negozio. Partendo dal presupposto che le tabelle Store e Sales sono connesse in base al nome del negozio, si provi a ipotizzare il risultato della formula e a immaginare il modo in cui i dati sulle vendite non correlati a un negozio esistente devono essere raggruppati o visualizzati nella tabella pivot.

Si tratta di un problema comune dei data warehouse, in cui le tabelle di dati dei fatti di grandi dimensioni devono essere correlate logicamente alle tabelle delle dimensioni contenenti informazioni sui negozi, sulle aree e sugli altri attributi utilizzati per suddividere e calcolare i fatti. Per risolvere il problema, qualsiasi nuovo fatto non correlato a un'entità esistente viene assegnato temporaneamente al membro sconosciuto. Questo è il motivo per cui fatti non correlati verranno visualizzati raggruppati in una tabella pivot sotto un'intestazione vuota.

Modalità di gestione dei valori vuoti rispettoalla riga vuota

I valori vuoti sono diversi dalle righe vuote aggiunte per contenere il membro sconosciuto. Il valore vuoto è un valore speciale utilizzato per rappresentare valori Null, stringhe vuote e altri valori mancanti. Per ulteriori informazioni sul valore vuoto nonché sugli altri tipi di dati DAX, vedere Tipi di dati supportati nelle cartelle di lavoro di PowerPivot.