Introduzione a DAX, Sintassi e Calculated Column

DAX (Data Analysis Expressions) è un linguaggio di formule sviluppato da Microsoft e utilizzato in Power BI, Analysis Services Tabular e Power Pivot in Excel. Consente di creare Calculated Column, Measure e di effettuare calcoli avanzati, tra cui quelli di Time Intelligence (come analisi su intervalli temporali, YTD, MTD, confronti con l’anno precedente, variazioni percentuali, e così via). DAX permette inoltre di definire logiche personalizzate e trasformazioni dinamiche sui dati, lavorando direttamente sul modello tabulare e arricchendo così le capacità analitiche dei report.

Sintassi di Base DAX

La sintassi di DAX ricorda quella delle formule di Excel, ma il funzionamento è molto diverso: invece di operare su singole celle, DAX lavora su tabelle, colonne e sui contesti di valutazione (Row Context e Filter Context). Questo significa che ogni calcolo tiene conto della struttura del modello, delle relazioni tra le tabelle e — quando usiamo le Measure — dei filtri attivi nel report, permettendo analisi dinamiche e flessibili rispetto alle classiche formule di Excel. Una formula DAX ha sempre questa struttura:

Formula Name = [DAX Expression]

Ad esempio:

Total Sales = SUM(SalesTable[Total Cost])

dove SalesTable[Total Cost] identifica la colonna Total Cost della tabella SalesTable; mentre SUM è una funzione della libreria DAX, che permette di sommare tutti i valori di una colonna della tabella. Nell’esempio precedente, l’espressione ritorna il valore scalare della sommatoria del campo di Total Cost.

Le espressioni DAX sono case insensitive. Ad esempio:

= COUNTROWS(Winesales)

è equivalente a:

= countrows(winesales)

Una delle differenze fondamentali tra DAX ed Excel è che in DAX non esistono le “celle”: il linguaggio non permette di puntare a una posizione specifica come accade nei fogli di calcolo. Le uniche entità a cui è possibile fare riferimento direttamente sono tabelle e colonne.

Come già accennato, per richiamare una tabella in un’espressione DAX è sufficiente utilizzare il suo nome, mentre per riferirsi a una colonna si usa la notazione TableName[ColumnName]. Questo approccio orientato alle tabelle rende i calcoli più coerenti con la logica dei modelli relazionali e con la struttura del modello dati in Power BI. Ad esempio:

// count the rows in the Winesales table
= COUNTROWS(Winesales)

Se il nome della tabella contiene uno spazio, il nome della tabella deve essere racchiuso tra virgolette singole:

// count the rows in the 'Wine Sales' table
= COUNTROWS('Wine Sales')

Per referenziare una colonna, racchiudiamo il nome della colonna tra parentesi quadre ([]) e facciamo sempre precedere il nome della colonna dal nome della tabella:

// sum the CASES SOLD column in the Winesales table
= SUM(Winesales[CASES SOLD])

In DAX, l’operatore AND logico è definito come &&. L’espressione “se Winesales[CASES SOLD] è maggiore di 50 e minore di 100” può essere codificata come:

// Condition1 && Condition2
// Parenthesis are used only for readability purpose
(Winesales[CASES SOLD] > 50) && (Winesales[CASES SOLD] < 100)

Ad esempio, possiamo definire un’espressione If-Then-Else tramite la funzione IF come:

= IF(
    Winesales[CASES SOLD] > 50 && Winesales[CASES SOLD] < 100,
    "yes",
    "no"
)

L’operatore logico OR è definito da ||. Ad esempio:

// Condition1 && Condition2
// Parenthesis are used only for readability purpose
(Winesales[SALESPERSON ID] = 2) || (Winesales[SALESPERSON ID] = 6)

Possiamo definire un’espressione If-Then-Else con tale espressione come:

= IF(
    Winesales[SALESPERSON ID] = 2 || Winesales[SALESPERSON ID] = 6,
    "yes",
    "no"
  )

Un singolo simbolo & viene utilizzato come operatore di concatenazione di stringhe, proprio come in Excel:

= Customers[County] & " - " & Customers[Area]

Per aggiungere commenti al codice DAX, possiamo utilizzare quanto segue:

• -- : commento a singola linea
• // : commento a singola linea
• /* : inizio commento multi-linea
• */ : fine commento multi-linea

Ad esempio:

/* commented code
VAR CasesSoldInRange =
    IF(Winesales[CASES SOLD] > 50 && Winesales[CASES SOLD] < 100,
       "yes", "no")
*/

Gli Oggetti DAX

Gli oggetti su cui DAX opera sono principalmente tre:

• Tabelle:
  rappresentano insiemi di dati organizzati in righe e colonne. Possono essere tabelle fisiche, importate direttamente dalle sorgenti, oppure tabelle virtuali, generate dinamicamente tramite funzioni tabellari come FILTER, VALUES, ALL, e molte altre. Esempi tipici sono FactSales, Calendar, DimProduct.

• Colonne:
  sono i campi che compongono una tabella. Vengono utilizzate nei calcoli riga per riga (come avviene nelle calculated column) oppure come input per funzioni di aggregazione nelle misure.

• Misure (Measure):
  sono espressioni DAX valutate in modo dinamico, il cui risultato cambia in base al Filter Context del report. Sono lo strumento principale per creare metriche, KPI e analisi numeriche all’interno del modello.

Il comportamento di un’espressione DAX varia in base al tipo di oggetto che stiamo definendo. Quando utilizziamo DAX per creare tabelle o colonne calcolate, l’espressione viene valutata senza considerare il Filter Context, cioè senza tener conto dei filtri applicati in un visual. In questi casi, DAX analizza tutte le righe della tabella — o delle tabelle — coinvolte, seguendo unicamente la logica definita nella formula, ed eventuali filtri definiti al suo interno. Quando invece l’espressione definisce una Measure (Misura), il comportamento diventa più articolato: la misura viene valutata dinamicamente in base al Filter Context attivo, e il risultato può cambiare in funzione dei filtri applicati nel report.

Calculated Column

Prima di introdurre il concetto di “Calculated Column” (Colonna Calcolata) in Power BI, facciamo un passo indietro, e vediamo brevemente cosa si intende per “Computed Column” nel mondo dei database relazionali. In un RDBMS, una “Computed Column” è una colonna il cui valore viene generato dinamicamente sulla base di un’espressione o di una funzione applicata ad altre colonne della stessa tabella. Di norma, questa colonna è virtuale: il suo contenuto non viene memorizzato fisicamente nel database, ma viene calcolato al momento della richiesta. Solo quando la colonna viene esplicitamente contrassegnata come PERSISTED, il risultato del calcolo viene materializzato e archiviato nella tabella, riducendo il tempo di ricalcolo ma aumentando lo spazio occupato nel database. Ad esempio:

CREATE TABLE dbo.Products
(
   ProductID int IDENTITY (1,1) NOT NULL,
   QtyAvailable smallint,
   UnitPrice money,

   -- Computed Column
   InventoryValue AS QtyAvailable * UnitPrice
);

Power BI riprende il concetto di “Computed Column”, ma offre due modalità principali per creare colonne derivate dai dati originali:

• le Calculated Column, create tramite DAX direttamente nel modello;
• le Custom Column, definite nel Power Query Editor utilizzando il linguaggio M (Mashup).

Concentriamoci su DAX. Similmente a quanto accade nei database relazionali, una Calculated Column è una colonna che non esiste nel datasource, ma che creiamo direttamente nel modello dati di Power BI utilizzando una formula DAX. La colonna calcolata viene generata da Power BI riga per riga, utilizzando i valori presenti nelle altre colonne della stessa tabella (o di altre tabelle poste in relazione con essa). Una volta calcolata, la colonna viene materializzata, cioè salvata fisicamente nel modello e compressa da VertiPaq, il motore interno di Power BI che si occupa di caricare, comprimere e leggere i dati in memoria.

Al contrario, le Misure sono ottimizzate per eseguire calcoli aggregati in base al contesto di filtro definito da visual, slicer o relazioni. Non aggiungono dati alla tabella, ma calcolano dinamicamente il risultato necessario per l’analisi. Se un calcolo serve solo in aggregazione, usare una Measure è quasi sempre più efficiente.

Uso delle Variabili

Prima di analizzare alcuni esempi di Calculated Column, è utile introdurre l’uso delle variabili DAX. Come in altri linguaggi di programmazione, anche DAX consente di dichiarare variabili, ovvero valori intermedi che possono essere memorizzati all’interno di una formula e riutilizzati più volte senza dover ripetere il calcolo originale. Le variabili in DAX si dichiarano con la parola chiave VAR. Ad esempio:

VAR x = 10

E’ possibile dichiarare più variabili, e la dichiarazione di una variabile può utilizzare il valore di un’altra variabile dichiarata in precedenza:

VAR x = 10
VAR y = x * 2

Un espressione che utilizza almeno una variabile deve essere terminata tramite la parola chiave RETURN, che (nel contesto delle Calculated Column) specifica quale risultato finale restituire per ogni riga:

My Calculated Column =
  VAR x = 10
  VAR y = 5
  RETURN
      x * y

Il blocco VAR serve a definire valori intermedi, mentre RETURN (obbligatorio quando usiamo almeno una variabile) indica la parte finale dell’espressione calcolata.

Le variabili DAX sono read only (a sola lettura): una volta che una variabile è stata dichiarata e inizializzata, non può essere sovrascritta con un altro valore:

VAR x = 1
// error!
x = 2

Le variabili in DAX non sono dichiarate specificando il relativo datatype, ma esse prendono il datatype del valore che viene loro assegnato:

// number
VAR x = 1
// string
VAR y = "Hello"
// boolean
VAR z = True

La scrittura e il debug di alcune espressioni DAX possono diventare operazioni complesse. L’utilizzo delle variabili all’interno del codice DAX, se il loro nome è scelto con criterio, aumenta la leggibilità del codice. Ad esempio:

// Calculated Column
UnitProfit =
    VAR unitCost  = 'Product'[UnitCost]
    VAR unitPrice = 'Product'[UnitPrice]
    RETURN
        unitPrice - unitCost

Ecco del codice DAX più complesso, usato per per creare una Calculated Table, che illustra meglio i vantaggi dell’uso delle variabili:

// Calculated Table
Deluxe Products Top 20 Sellers =

    // get a table containing all the products having "Deluxe" ClassName
    VAR deluxeProducts =
        FILTER(
            'Product',
            'Product'[ClassName] = "Deluxe"
        )

    // takes the deluxeProducts table, groups it by [ProductName],
    // then adds two new aggregated columns, which calculate:
    // 1) the sum of [SalesQuantity]
    // 2) the sum of [SalesAmount]
    // for each product
    VAR deluxeProductsSummary =
        SUMMARIZE(
            deluxeProducts,
            'Product'[ProductName],
            "TotalSalesQuantity", SUM(Sales[SalesQuantity]),
            "TotalSalesAmount",   SUM(Sales[SalesAmount])
        )

    // get the 20 top rows of 'deluxeProductsSummary' table,
    // sorted by [TotalSalesQuantity]
    VAR top20Sales =
        TOPN(
            20,
            deluxeProductsSummary,
            [TotalSalesQuantity]
        )

    // return the table containing the 20 top deluxeProducts
    RETURN
        top20Sales

Senza l’uso di variabili, possiamo riscrivere l’esempio precedente in modo più compatto, ma forse un po’ più difficile da capire:

Deluxe Products Top 20 Sellers =
    TOPN(
        20,
        SUMMARIZE(
            FILTER(
                'Product',
                'Product'[ClassName] = "Deluxe"
            ),
            'Product'[ProductName],
            "TotalSalesQuantity", SUM(Sales[SalesQuantity]),
            "TotalSalesAmount",   SUM(Sales[SalesAmount])
        ),
        [TotalSalesQuantity]
    )

Quando un’espressione DAX deve utilizzare più volte il risultato di un calcolo “pesante” o articolato, è utile memorizzare quel valore in una variabile, così da poterlo riutilizzare senza eseguire nuovamente tale computazione. In generale, ogni espressione DAX che richiama più volte la stessa logica o lo stesso frammento di codice è un ottimo candidato per l’utilizzo delle variabili. Ad esempio:

// TotalSales is computed once, and referenced twice
Margin Percent =
    VAR TotalCost  = Sales[UnitCost] * Sales[Quantity]
    VAR TotalSales = Sales[UnitPrice] * Sales[Quantity]
    VAR Margin     = TotalSales - TotalCost
    RETURN
        DIVIDE(Margin, TotalSales)

Alcuni Esempi di Calculated Column

Vediamo ora alcuni esempi didattici di Calculated Column, che ci permetteranno di introdurre gradualmente l’utilizzo di alcune tra le principali funzioni offerte dalla libreria DAX. Attraverso questi esempi analizzeremo il comportamento delle colonne calcolate e comprenderemo meglio come DAX elabora i dati riga per riga, fornendo una base solida per affrontare calcoli più complessi.

Total Price

Consideriamo una tabella Sales dove ogni record ha le seguenti colonne: OrderID, ProductID, Quantity, UnitPrice. Tramite il pulsante “New Column”, è possibile utilizzare DAX per creare una colonna calcolata Total Price che moltiplichi UnitPrice con Quantity:

Total Price = Sales[Quantity] * Sales[Unit Price]

La nuova Calculated Column Total Price risultante:

Full Name

Un altro esempio, dove usiamo l’operatore & per concatenare il nome e il cognome del cliente:

Full Name = 'Customer'[First Name] & " " & 'Customer'[Last Name]

Month Year

Nella colonna calcolata Month Year, l’obiettivo è quello di restituire una colonna “mese-anno” ne formato YYYY-MM:

Month Year =
  VAR monthNumber = 'Date (Order)'[Month Number of Year]

  // RIGHT returns the last character(s) in a text string,
  // based on the number of characters you specify.
  // For instance, "09" remains the same, but "010" becomes "10"
  VAR monthYear = RIGHT("0" & monthNumber, 2)
  VAR year = 'Date (Order)'[Year]
  RETURN monthYear & "-" & year

Possiamo ottenere lo stesso risultato in modo molto più semplice tramite la funzione FORMAT, che consente di prendere una colonna di numeri o di date e di personalizzarla in vari modi:

Month Year Format = FORMAT('Date'[Date], "MM-YYYY")

Customer Age

Se abbiamo la data di nascita del Customer, e ci interessa sapere la sua età (la differenza in anni tra la data di nascita del Customer e la data restituita dalla funzione TODAY), possiamo usare questo codice:

Customer Age =
    // get the two-digit month and two-digit day for current customer birth date. Example:
    //  25/09/1973 -> 925  (birth date)
    //  25/07/2022 -> 725  (today)
    var birthDateMonthDay = FORMAT('Customer'[Birth Date], "MMDD")
    var todayMonthDay = FORMAT(TODAY(), "MMDD")

    return
        // the DATEDIFF function returns the number of interval boundaries between two dates.
        // If the customer birth date (MMDD) is > that Today (MMDD), subtract one year
        IF ((birthDateMonthDay > todayMonthDay),                 // Logical Test
            DATEDIFF('Customer'[Birth Date], TODAY(), YEAR) -1,  // True Statement
            DATEDIFF('Customer'[Birth Date], TODAY(), YEAR)      // False Statement
        )

Age Breakdown

Possiamo segmentare i clienti in base alla loro età utilizzando la funzione SWITCH, che è preferibile alla funzione IF quando si eseguono più test logici in una singola formula DAX:

Age Breakdown =
    VAR custAge = 'Customer'[Customer Age]

    // SWITCH evaluates an expression against a list of values
    // and returns one of multiple possible result expressions:
    // SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
    // https://docs.microsoft.com/en-us/dax/switch-function-dax
    VAR custAgeGroup = SWITCH(
        TRUE(),
        custAge >= 55, "55+",
        custAge >= 45, "45-54",
        custAge >= 35, "35-44",
        "18-34"
      )
    RETURN custAgeGroup

Calculated Column e Prestazioni del Modello

In Power BI, le Calculated Column dovrebbero essere utilizzate con parsimonia, perché possono avere un impatto significativo sulle risorse del sistema. Quando una Calculated Column utilizza formule complesse o molto articolate, il motore VertiPaq deve valutarla per ogni riga della tabella durante il refresh del modello. Questo può aumentare:

• il tempo di elaborazione (CPU) necessario per calcolare la colonna;
• la memoria occupata, poiché il risultato viene fisicamente memorizzato nel modello;
• la dimensione complessiva del file pbix, che può peggiorare le prestazioni in fase di caricamento e di utilizzo del report.

Per queste ragioni, per ottenere prestazioni migliori è generalmente consigliabile:

• Creare le Custom Column” in Power Query:
  quando il calcolo può essere effettuato in fase di preparazione dei dati. In questo modo il risultato viene materializzato una volta sola durante il refresh del modello (e non in memoria), riducendo il carico durante la visualizzazione dei report. Power Query esegue i calcoli prima che i dati vengano caricati nel modello VertiPaq. Quindi la Custom Column:

  • viene calcolata solo una volta, quando il modello viene aggiornato (refresh);
  • dopo il calcolo, il risultato viene salvato fisicamente nella tabella del modello dati.

  Una volta che i dati entrano nel modello tabulare, la colonna esiste già come dato compresso, non viene ricalcolata in memoria, perché il calcolo è già stato fatto da Power Query.

• Preferire le Measure alle “Calculated Column”:
  le misure vengono calcolate dinamicamente solo quando necessario, rispettando il Filter Context, e non aumentano la dimensione del modello. Al contrario, le Calculated Column aggiungono nuovi dati fisici alle tabelle, con un impatto diretto sul peso del file e sulla memoria utilizzata da VertiPaq.