Excel DAX - Formule

DAX è un linguaggio delle formule per la creazione di calcoli personalizzati nelle tabelle di Power Pivot. È possibile usare le funzioni DAX progettate per funzionare con dati relazionali ed eseguire l'aggregazione dinamica nelle formule DAX.

DAX formulassono molto simili alle formule di Excel. Per creare una formula DAX, digita un segno di uguale, seguito da un nome di funzione o da un'espressione e da eventuali valori o argomenti obbligatori.

Funzioni DAX e formule DAX

Le formule DAX possono includere funzioni DAX e sfruttarne l'utilizzo. È qui che le formule DAX tendono a differire dalle funzioni DAX in modi importanti.

  • Una funzione DAX fa sempre riferimento a una colonna completa oa una tabella. Se desideri utilizzare solo valori particolari da una tabella o una colonna, puoi aggiungere filtri alla formula.

  • Se si desidera personalizzare i calcoli riga per riga, Power Pivot fornisce 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 utilizzate per fornire input ad altre funzioni, calcolando così i valori per intere tabelle o colonne.

  • Alcune funzioni DAX forniscono informazioni sul tempo, che consentono di creare calcoli utilizzando intervalli di date significativi e confrontare i risultati in periodi paralleli.

Comprensione della sintassi della formula DAX

Ogni formula DAX ha la seguente sintassi:

  • Ogni formula deve iniziare con un segno di uguale.

  • A destra del segno di uguale, puoi digitare o selezionare il nome di una funzione oppure digitare un'espressione. L'espressione può contenere nomi di tabella e nomi di colonna collegati da operatori DAX.

Di seguito sono riportate alcune formule DAX valide:

  • [column_Cost] + [column_Tax]
  • = Oggi ()

Comprensione della funzionalità IntelliSense

DAX fornisce la funzionalità IntelliSense che ti consentirà di scrivere formule DAX in modo rapido e corretto. Con questa funzionalità, non è necessario digitare completamente i nomi di tabella, colonna e funzione, ma selezionare i nomi pertinenti dall'elenco a discesa durante la scrittura di una formula DAX.

  • Inizia a digitare le prime lettere del nome della funzione. Completamento automatico visualizza un elenco di funzioni disponibili con i nomi che iniziano con quelle lettere.

  • Posiziona il puntatore su uno dei nomi delle funzioni. Viene visualizzato il suggerimento di IntelliSense che consente di utilizzare la funzione.

  • Fare clic sul nome della funzione. Il nome della funzione appare nella barra della formula e viene visualizzata la sintassi, che ti guiderà nella selezione degli argomenti.

  • Digita la prima lettera del nome della tabella che desideri. Il completamento automatico visualizza un elenco di tabelle e colonne disponibili con i nomi che iniziano con quella lettera.

  • Premere TAB o fare clic sul nome per aggiungere un elemento dall'elenco Completamento automatico alla formula.

  • Clicca il Fxper visualizzare un elenco delle funzioni disponibili. Per selezionare una funzione dall'elenco a discesa, utilizzare i tasti freccia per evidenziare l'elemento e fare clic su OK per aggiungere la funzione alla formula.

  • Fornire gli argomenti alla funzione selezionandoli da un elenco a discesa di possibili tabelle e colonne o digitando i valori richiesti.

Si consiglia vivamente di utilizzare questa pratica funzionalità di IntelliSense.

Dove usare le formule DAX?

Puoi usare le formule DAX per creare colonne calcolate e campi calcolati.

  • Puoi usare le formule DAX nelle colonne calcolate, aggiungendo una colonna e quindi digitando un'espressione nella barra della formula. Queste formule vengono create nella finestra di PowerPivot.

  • Puoi usare le formule DAX nei campi calcolati. Crei queste formule:

    • Nella finestra di Excel nella finestra di dialogo Campo calcolato o

    • Nella finestra di PowerPivot nell'area di calcolo di una tabella.

La stessa formula può comportarsi in modo diverso a seconda che la formula venga utilizzata in una colonna calcolata o in un campo calcolato.

  • In una colonna calcolata, la formula viene sempre applicata a ogni riga della colonna, in tutta la tabella. A seconda del contesto della riga, il valore potrebbe cambiare.

  • In un campo calcolato, tuttavia, il calcolo dei risultati dipende fortemente dal contesto. Ovvero, la struttura della tabella pivot e la scelta delle intestazioni di riga e colonna influiscono sui valori utilizzati nei calcoli.

È importante comprendere il concetto di contesto in DAX per scrivere formule DAX. Questo può essere un po 'difficile all'inizio del tuo viaggio DAX, ma una volta capito, puoi scrivere formule DAX efficaci che sono necessarie per l'analisi dei dati complessa e dinamica. Per i dettagli, fare riferimento al capitolo - Contesto DAX.

Creazione di una formula DAX

Hai già imparato a conoscere la funzionalità IntelliSense in una sezione precedente. Ricorda di usarlo durante la creazione di qualsiasi formula DAX.

Per creare una formula DAX, utilizzare i passaggi seguenti:

  • Digita un segno di uguale.

  • A destra del segno di uguale, digita quanto segue:

    • Digita la prima lettera del nome di una funzione o di una tabella e seleziona il nome completo dall'elenco a discesa.

    • Se hai scelto il nome di una funzione, digita le parentesi "(".

    • Se hai scelto il nome della tabella, digita la parentesi "[". Digita la prima lettera del nome della colonna e seleziona il nome completo dall'elenco a discesa.

    • Chiudere i nomi delle colonne con "]" e i nomi delle funzioni con ")".

    • Digita un operatore DAX tra le espressioni o digita "," per separare gli argomenti della funzione.

    • Ripeti i passaggi da 1 a 5 fino al completamento della formula DAX.

Ad esempio, si desidera trovare l'importo totale delle vendite nella regione orientale. È possibile scrivere una formula DAX come illustrato di seguito. East_Sales è il nome della tabella. L'importo è una colonna nella tabella.

SUM ([East_Sales[Amount])

Come già discusso nel capitolo - Sintassi DAX, è consigliabile usare il nome della tabella insieme al nome della colonna in ogni riferimento a qualsiasi nome di colonna. Questo è definito come - "il nome completo".

La formula DAX può variare a seconda che si tratti di un campo calcolato o di una colonna calcolata. Fare riferimento alle sezioni seguenti per i dettagli.

Creazione di una formula DAX per una colonna calcolata

È possibile creare una formula DAX per una colonna calcolata nella finestra di PowerPivot.

  • Fare clic sulla scheda della tabella in cui si desidera aggiungere la colonna calcolata.
  • Fare clic sulla scheda Progettazione sulla barra multifunzione.
  • Fare clic su Aggiungi.
  • Digita la formula DAX per la colonna calcolata nella barra della formula.
= DIVIDE (East_Sales[Amount], East_Sales[Units])

Questa formula DAX esegue le seguenti operazioni per ogni riga nella tabella East_Sales:

  • Divide il valore nella colonna Importo di una riga per il valore nella colonna Unità nella stessa riga.

  • Posiziona il risultato nella nuova colonna aggiunta nella stessa riga.

  • Ripete i passaggi 1 e 2 in modo iterativo finché non completa tutte le righe nella tabella.

Hai aggiunto una colonna per Prezzo unitario a cui queste unità vengono vendute con la formula sopra.

  • Come puoi osservare, le colonne calcolate richiedono anche spazio di calcolo e di archiviazione. Quindi, utilizzare le colonne calcolate solo se necessario. Utilizza campi calcolati ove possibile e sufficiente.

Fare riferimento al capitolo - Colonne calcolate per i dettagli.

Creazione di una formula DAX per un campo calcolato

È possibile creare una formula DAX per un campo calcolato nella finestra di Excel o nella finestra di PowerPivot. Nel caso di campo calcolato, è necessario fornire il nome in anticipo.

  • Per creare una formula DAX per un campo calcolato nella finestra di Excel, utilizzare la finestra di dialogo Campo calcolato.

  • Per creare una formula DAX per un campo calcolato nella finestra di PowerPivot, fare clic su una cella nell'area di calcolo nella tabella pertinente. Avvia la formula DAX con CalculatedFieldName: =.

Ad esempio, Total East Sales Amount: = SUM ([East_Sales [Amount])

Se si utilizza la finestra di dialogo Campo calcolato nella finestra di Excel, è possibile controllare la formula prima di salvarla e renderla un'abitudine obbligatoria per garantire l'uso di formule corrette.

Per maggiori dettagli su queste opzioni, fare riferimento al capitolo - Campi calcolati.

Creazione di formule DAX utilizzando la barra della formula

La finestra di PowerPivot ha anche una barra della formula simile alla barra della formula della finestra di Excel. La barra della formula semplifica la creazione e la modifica delle formule, utilizzando la funzionalità di completamento automatico in modo da ridurre al minimo gli errori di sintassi.

  • Per inserire il nome di una tabella, inizia a digitare il nome della tabella. Completamento automatico formula fornisce un elenco a discesa contenente nomi di tabella validi che iniziano con quelle lettere. È possibile iniziare con una lettera e digitare più lettere per restringere l'elenco, se necessario.

  • Per inserire il nome di una colonna, è possibile selezionarlo dall'elenco dei nomi di colonna nella tabella selezionata. Digita una parentesi "[" a destra del nome della tabella, quindi scegli la colonna dall'elenco delle colonne nella tabella selezionata.

Suggerimenti per l'utilizzo del completamento automatico

Di seguito sono riportati alcuni suggerimenti per l'utilizzo del completamento automatico:

  • È possibile nidificare funzioni e formule in una formula DAX. In tal caso, è possibile utilizzare Completamento automatico formula nel mezzo di una formula esistente con funzioni nidificate. Il testo immediatamente prima del punto di inserimento viene utilizzato per visualizzare i valori nell'elenco a discesa e tutto il testo dopo il punto di inserimento rimane invariato.

  • I nomi definiti che crei per le costanti non vengono visualizzati nell'elenco a discesa Completamento automatico, ma puoi comunque digitarli.

  • La parentesi di chiusura delle funzioni non viene aggiunta automaticamente. Devi farlo da solo.

  • È necessario assicurarsi che ogni funzione sia sintatticamente corretta.

Comprensione della funzione della funzione di inserimento

È possibile trovare il pulsante Inserisci funzione etichettato come fx, sia nella finestra di PowerPivot che nella finestra di Excel.

  • Il pulsante Inserisci funzione nella finestra di PowerPivot si trova a sinistra della barra della formula.

  • Il pulsante Inserisci funzione nella finestra di Excel si trova nella finestra di dialogo Campo calcolato a destra di Formula.

Quando fai clic sul file fxpulsante, viene visualizzata la finestra di dialogo Inserisci funzione. La finestra di dialogo Inserisci funzione è il modo più semplice per trovare una funzione DAX rilevante per la formula DAX.

La finestra di dialogo Inserisci funzione aiuta a selezionare le funzioni per categoria e fornisce brevi descrizioni per ciascuna funzione.

Utilizzo della funzione di inserimento in una formula DAX

Supponi di voler creare il seguente campo calcolato:

Medal Count: = COUNTA (]Medal])

È possibile utilizzare la finestra di dialogo Inserisci funzione utilizzando i seguenti passaggi:

  • Fare clic sull'area di calcolo della tabella dei risultati.
  • Digita quanto segue nella barra della formula:
Medal Count: =
  • Fare clic sul pulsante Inserisci funzione (fx).

Viene visualizzata la finestra di dialogo Inserisci funzione.

  • Selezionare Statistica nella casella Seleziona una categoria come mostrato nella seguente schermata.

  • Selezionare COUNTA nella casella Seleziona una funzione come mostrato nella seguente schermata.

Come puoi osservare, vengono visualizzate la sintassi della funzione DAX selezionata e la descrizione della funzione. Ciò consente di verificare che sia la funzione che si desidera inserire.

  • Fare clic su OK. Conteggio medaglie: = COUNTA (appare nella barra della formula e viene visualizzato anche un suggerimento che mostra la sintassi della funzione.

  • Genere [. Ciò significa che stai per digitare un nome di colonna. I nomi di tutte le colonne e dei campi calcolati nella tabella corrente verranno visualizzati nell'elenco a discesa. È possibile utilizzare IntelliSense per completare la formula.

  • Tipo M. I nomi visualizzati nell'elenco a discesa saranno limitati a quelli che iniziano con "M".

  • Fare clic su Medaglia.

  • Fare doppio clic su Medaglia. Conteggio medaglie: = COUNTA ([Medaglia] verrà visualizzato nella barra della formula. Chiudere la parentesi.

  • Premere Invio. Hai fatto. È possibile utilizzare la stessa procedura anche per creare una colonna calcolata. È inoltre possibile seguire gli stessi passaggi per inserire una funzione nella finestra di dialogo Campo calcolato nella finestra di Excel utilizzando la funzione Inserisci funzione.

  • Fare clic sulla funzione Inserisci (fx) a destra di Formula.

Viene visualizzata la finestra di dialogo Inserisci funzione. Gli altri passaggi sono gli stessi di sopra.

Utilizzo di più funzioni in una formula DAX

Le formule DAX possono contenere fino a 64 funzioni nidificate. Tuttavia, è improbabile che una formula DAX contenga così tante funzioni nidificate.

Se una formula DAX ha molte funzioni annidate, presenta i seguenti svantaggi:

  • La formula sarebbe molto difficile da creare.
  • Se la formula contiene errori, sarebbe molto difficile eseguire il debug.
  • La valutazione della formula non sarebbe molto veloce.

In questi casi, puoi dividere la formula in formule gestibili più piccole e creare la formula grande in modo incrementale.

Creazione di una formula DAX utilizzando aggregazioni standard

Quando esegui l'analisi dei dati, eseguirai calcoli sui dati aggregati. Sono disponibili diverse funzioni di aggregazione DAX, come SUM, COUNT, MIN, MAX, DISTINCTCOUNT e così via, che puoi usare nelle formule DAX.

È possibile creare automaticamente formule utilizzando aggregazioni standard utilizzando la funzionalità Somma automatica nella finestra di PowerPivot.

  • Fare clic sulla scheda Risultati nella finestra di PowerPivot. Verrà visualizzata la tabella dei risultati.
  • Fare clic sulla colonna Medaglia. L'intera colonna - Medaglia sarà selezionata.
  • Fare clic sulla scheda Home sulla barra multifunzione.
  • Fare clic sulla freccia in giù accanto a Somma automatica nel gruppo Calcoli.
  • Fare clic su COUNT nell'elenco a discesa.

Come puoi osservare, il campo calcolato Count of Medal appare nell'area di calcolo sotto la colonna - Medal. La formula DAX viene visualizzata anche nella barra della formula -

Count of Medal: = COUNTA([Medal])

La funzione Somma automatica ha fatto il lavoro per te: ha creato il campo calcolato per l'aggregazione dei dati. Inoltre, AutoSum ha preso la variante appropriata della funzione DAX COUNT, cioè COUNTA (DAX ha le funzioni COUNT, COUNTA, COUNTAX).

Una parola di cautela: per utilizzare la funzione Somma automatica, è necessario fare clic sulla freccia in giù accanto a Somma automatica sulla barra multifunzione. Se invece fai clic su Somma automatica stessa, otterrai:

Sum of Medal: = SUM([Medal])

E viene segnalato un errore in quanto Medal non è una colonna di dati numerici e il testo nella colonna non può essere convertito in numeri.

Puoi fare riferimento al capitolo - DAX Error Reference per i dettagli sugli errori DAX.

Formule DAX e modello relazionale

Come saprai, nel modello di dati di PowerPivot puoi lavorare con più tabelle di dati e connettere le tabelle definendo le relazioni. Ciò consentirà di creare formule DAX interessanti che utilizzano le correlazioni delle colonne tra le tabelle correlate per i calcoli.

Quando crei una relazione tra due tabelle, devi assicurarti che le due colonne utilizzate come chiavi abbiano valori che corrispondono, almeno per la maggior parte delle righe, se non completamente. Nel modello di dati di Power Pivot, è possibile avere valori non corrispondenti in una colonna chiave e creare comunque una relazione, perché Power Pivot non applica l'integrità referenziale (vedere la sezione successiva per i dettagli). Tuttavia, la presenza di valori vuoti o non corrispondenti in una colonna chiave potrebbe influire sui risultati delle formule DAX e sull'aspetto delle tabelle pivot.

Integrità referenziale

La definizione dell'integrità referenziale implica la creazione di una serie di regole per preservare le relazioni definite tra le tabelle quando si immettono o si eliminano dati. Se non lo assicuri esclusivamente, poiché Power Pivot non lo applica, potresti non ottenere risultati corretti con le formule DAX create prima che vengano apportate le modifiche ai dati.

Se imponi l'integrità referenziale, puoi prevenire le seguenti insidie:

  • Aggiunta di righe a una tabella correlata quando non sono presenti righe associate nella tabella principale (ovvero con valori corrispondenti nelle colonne chiave).

  • La modifica dei dati in una tabella primaria che comporterebbe righe orfane in una tabella correlata (ovvero righe con un valore di dati nella colonna chiave che non ha un valore corrispondente nella colonna chiave della tabella primaria).

  • Eliminazione di righe da una tabella primaria quando sono presenti valori di dati corrispondenti nelle righe della tabella correlata.