DAX, Relazioni Fisiche e Virtuali

In Power BI, una relazione fisica è una connessione definita nel modello dati tra due tabelle, basata su una coppia di colonne che svolgono il ruolo di Primary Key (nella tabella lato “uno”) e Foreign Key (nella tabella lato “molti”). La relazione viene creata direttamente nella struttura del modello, tramite la Model View di Power BI, utilizzando le operazioni di drag and drop tra le colonne chiave delle due tabelle, oppure la funzione “Manage Relationships”.

Relazioni Fisiche

Una relazione fisica ha un ruolo fondamentale: consente la propagazione automatica del Filter Context tra le tabelle coinvolte. Questa propagazione segue le regole definite dalla proprietà Cross Filter Direction, che stabilisce in quale direzione (o direzioni) i filtri possono fluire attraverso la relazione.

DAX e la Gestione delle Relazioni

Sebbene le relazioni vengano definite fisicamente nel modello dati di Power BI, DAX mette a disposizione alcune funzioni chiave per sfruttare, controllare o modificare temporaneamente il comportamento di tali relazioni durante la valutazione delle espressioni.

Le funzioni DAX RELATED e RELATEDTABLE, già affrontate nella discussione del Row Context, servono per navigare una relazione fisica già definita nel modello. RELATED permette di recuperare un valore da una tabella collegata sul lato “uno”, partendo da una riga della tabella lato “molti”; RELATEDTABLE restituisce invece l’insieme delle righe correlate nella tabella lato “molti”, partendo da una riga della tabella lato “uno”. È importante sottolineare che queste funzioni funzionano esclusivamente se nel modello esiste una relazione fisica (attiva o attivabile) tra la tabella corrente e quella da cui si desidera recuperare i dati. In assenza di una relazione, DAX non è in grado di “navigare” tra le tabelle.

La funzione USERELATIONSHIP consente di attivare temporaneamente una relazione fisica inattiva tra due tabelle durante la valutazione di un’espressione DAX. Questa attivazione è limitata alla sola esecuzione dell’espressione in cui la funzione viene utilizzata, e non modifica in modo permanente il modello dati.

La funzione CROSSFILTER, invece, permette di modificare temporaneamente la direzione di propagazione dei filtri tra due tabelle collegate da una relazione, ad esempio forzando una direzione bidirezionale o disabilitando del tutto la propagazione. Anche in questo caso, la modifica ha effetto solo durante la valutazione dell’espressione DAX e non altera la configurazione strutturale del modello.

Vediamo queste due nuove funzioni in dettaglio.

USERELATIONSHIP

La funzione USERELATIONSHIP è un modificatore di CALCULATE che consente di attivare temporaneamente una relazione inattiva tra due tabelle durante la valutazione di un’espressione DAX. USERELATIONSHIP non crea una nuova relazione, ma sfrutta una relazione già presente nel modello, che è disattiva (viene rappresentata con una linea tratteggiata nel diagramma delle relazioni). USERELATIONSHIP ha la seguente sintassi:

USERELATIONSHIP(ColumnName1, ColumnName2)

USERELATIONSHIP è usata quando si ha a che fare con Dimension Table di tipo “Role-Playing”, che possono avere più ruoli diversi all’interno dello stesso modello, a seconda del contesto in cui vengono usate. In una Fact Table, spesso ci sono più campi data (di tipo Date / DateTime) che devono riferirsi alla stessa tabella calendario. Ad esempio:

• Data di Vendita vs Data di spedizione.
• Data Ordine vs Data Consegna.
• Data Produzione vs Data Transazione.

Dato che, tra due tabelle, può esserci solo una relazione può essere attiva per volta, le altre rimangono inattive e non vengono usate dai filtri della tabella calendario. USERELATIONSHIP permette di forzare l’uso di una relazione inattiva, senza modificare il Data Model.

Consideriamo il tab “USERELATIONSHIP”, che mostra i grafici di “Sales By Order Date” e"Sales By Ship Date":

il Visual “Sales By Order Date” mostra l’aggregazione delle vendite partizionate per anno, considerando la relazione tra Sales[OrderDate] e DateTable[Date]. Per fare questo, nel bucket “X-axis” è stata posta la Misura Sales By Order Date:

// Measure
Sales By Order Date =
    CALCULATE(
        SUM(Sales[Sales]),     // sales aggregation
        USERELATIONSHIP(       // CALCULATE modifier:
            Sales[OrderDate],  //   consider the relationship between
            DateTable[Date])   //   Sales[OrderDate] and DateTable[Date]
    )

in questo caso, l’uso di USERELATIONSHIP è ridondante, in quanto, nel Model View, tale relazione è quella attiva di default. il Visual “Sales By Ship Date” mostra l’aggregazione delle vendite partizionate per anno, considerando la relazione tra Sales[ShipDate] e DateTable[Date]. Per fare questo, nel bucket “X-axis” è stata posta la Misura “Sales By Ship Date”:

// Measure
Sales By Ship Date =
    CALCULATE(
        SUM(Sales[Sales]),    // sales aggregation
        USERELATIONSHIP(      // CALCULATE modifier:
            Sales[ShipDate],  //    consider the relationship between
            DateTable[Date])  //   Sales[ShipDate] and DateTable[Date]
    )

in questo caso, l’uso di USERELATIONSHIP è necessaria, in quanto, nel Model View, tale relazione è disattiva (a livello di Data Model, tra due tabelle, può esserci solo una relazione attiva).

CROSSFILTER

La funzione CROSSFILTER agisce come modificatore di CALCULATE, e serve a cambiare temporaneamente la direzione di propagazione dei filtri tra due tabelle collegate da una relazione. La modifica è valida solo per la durata della Misura o del calcolo in cui la funzione è usata. CROSSFILTER ha la seguente sintassi:

CROSSFILTER(LeftColumnName, RightColumnName, CrossFilterType)

Come esempio, consideriamo il tab “CROSSFILTER”, dove un Visual di tipo Table indica i vini, e il numero di clienti che hanno effettuato acquisti per ognuno di essi:

La colonna No. Of Customers KO utilizza l’omonima Misura, che ritorna dei risultati errati:

// Measure
No. Of Customers KO =
    COUNTROWS(Customers)

La tabella indica sempre il valore 89, che è anche il numero totale di clienti. Questo accade perchè l’attributo “Cross FIlter Direction” della relazione tra la Fact Table Winesales e la Dimension Table Customers è Single, e quindi il filtro su Wine[WINE NAME] arriva a Winesales, ma non procede oltre:

La colonna No. Of Customers OK utilizza l’omonima Misura, che ritorna dei risultati corretti:

// Measure
No. Of Customers OK =
    CALCULATE(
        // DAX expression to calculate
        COUNTROWS(Customers),

        // Calculate Modifier that specifies the cross filtering direction
        // to use in the evaluation of a DAX expression
        CROSSFILTER(
            Winesales[CUSTOMER ID],
            Customers[CUSTOMER ID],
            Both)
    )

In questo caso, il conteggio delle righe di Customers per un particolare vino è corretto, perchè CROSSFILTER cambia la direzionalità della relazione tra le due tabelle da Single a Both. Quindi il filtro definito su Wine[WINE NAME] arriva a Winesales, e poi — tramite la relazione definita dalle colonne CUSTOMER ID — procede correttamente su Customers.

Relazioni Virtuali

Una Relazione Virtuale è una relazione di tipo logico tra tabelle che non viene definita fisicamente all’interno del modello dati. Essa viene invece creata dinamicamente (“on the fly”) all’interno di un’espressione DAX, esclusivamente per lo scopo del calcolo in cui è utilizzata. Essa è quindi temporanea: esiste solo durante la valutazione della formula DAX e non persiste nel modello, né viene rappresentata nel diagramma delle relazioni. Terminata l’esecuzione della Misura, la relazione virtuale cessa di esistere. Questo tipo di relazione viene tipicamente utilizzato nei seguenti scenari:

1. le tabelle contengono dati logicamente correlati, ma non è possibile o non è desiderabile creare una relazione fisica tra di esse. Un caso comune è quello in cui una relazione fisica genererebbe ambiguità nel modello, o conflitti nella propagazione del Filter Context.
2. si vuole applicare una relazione solo in Misure specifiche, e non a livello globale nel modello dati. In questo modo, la relazione influenza esclusivamente il calcolo della Misura interessata, senza modificare il comportamento delle altre Misure o dei visual.

Una Relazione Virtuale viene implementata in DAX tramite la funzione TREATAS, che consente di creare dinamicamente un collegamento logico tra tabelle all’interno di una Misura o di un’espressione DAX. Come vedremo di seguito, TREATAS permette di applicare i valori di una o più colonne come filtri su un’altra tabella, simulando il comportamento di una relazione fisica limitamente alla durata del calcolo e senza modificare in modo permanente il modello dati.

TREATAS

La funzione TREATAS permette di trasferire un filtro da una tabella a un’altra anche in assenza di una relazione fisica nel modello dati. In altre parole, TREATAS crea una relazione virtuale applicando i valori provenienti da una tabella (o da una o più colonne) come filtri su un’altra tabella. Questa funzione simula il comportamento di una relazione tradizionale, ma esclusivamente all’interno dell’espressione DAX in cui viene utilizzata. La relazione virtuale creata da TREATAS non è persistente e non influisce in alcun modo sulla struttura del modello. In termini concettuali, TREATAS:

• “preleva” un insieme di valori da una o più colonne sorgenti,
• e li “tratta come se fossero filtri” applicati alle colonne di un’altra tabella.

Ha la seguente sintassi:

TREATAS(TableExpression, ColumnName1, ColumnNam2, ..., ColumnNameN)

Durante la valutazione dell’espressione, TREATAS analizza la tabella di input per ottenere un elenco di valori provenienti da una o più colonne sorgenti. Questi valori vengono poi utilizzati per filtrare le colonne della tabella di destinazione. In pratica, ogni colonna di destinazione (ColumnName1...N) viene filtrata in modo da mantenere esclusivamente le righe che corrispondono ai valori forniti dalla tabella sorgente, replicando l’effetto che si otterrebbe con una relazione fisica tra le due tabelle, ma solo per la durata del calcolo. Ad esempio, possiamo creare una relazione virtuale tra Regions[Region] e Sales[Region] in questo modo:

// Measure
Sales Amount by Region =
    CALCULATE(
        // Total Sales expression
        SUM(Sales[Amount]),

        // create a virtual relationship
        // between Regions[Region] and Sales[Region],
        // filtering any value from Sales[Region]
        // that is not present in Regions[Region]
        TREATAS(
            VALUES(Regions[Region]), // TableExpression (Source Column)
            Sales[Region])           // Target Column
    )

Un Esempio di Relazione Virtuale

Consideriamo le schede “TREATAS #1” e “TREATAS #1”, e supponiamo di voler confrontare le vendite annuali dei singoli venditori con i rispettivi obiettivi di vendita. Nel data model, la tabella Targets è correlata alla tabella SalesPeople in una relazione molti-a-uno:

[Targets]* ---> 1[SalesPeople]

Consideriamo le due Misure Total Sales e Target:

// Measure:
// compute Total Sales for a given Salesperson and year
// (must be in the Filter Context)
Total Sales = SUMX (
    Winesales,
    Winesales[CASES SOLD] * RELATED(Wines[PRICE PER CASE])
)

// Measure:
// compute Sales Target for a given Salesperson and year
// (must be in the Filter Context)
Target = SUM(Targets[TARGET])

Tuttavia, considerando le vendite annuali, da quale tabella prendiamo la colonna YEAR per poter filtrare entrambe le tabelle Winesales e Targets? Se le informazioni relative all’anno fiscale provengono da DateTable, la Misura Total Sales è corretta, ma Target è errata. Infatti:

• il filtro DateTable[YEAR] viene correttamente propagato alla Fact Table, filtrando Winesales per ogni anno;
• ma lo stesso filtro non viene propagato alla tabella Targets a causa della proprietà “Cross Filter Direction” singola tra la Fact Table e SalesPeople:

Di contro, se la colonna YEAR proviene dalla tabella Targets, la Misura Target è corretta, ma Total Sales è errata:

• il filtro Targets[YEAR] filtra correttamente la tabella Target,
• ma lo stesso filtro non viene propagato alla tabella Winesales per filtrare le vendite:

L’immagine seguente mostra un report con le due casistiche:

Possiamo risolvere questo problema creando una relazione virtuale tra le colonne DateTable[YEAR] e Targets[YEAR]. Grazie a questa relazione, all’interno di un’espressione DAX, un filtro applicato alla colonna DateTable[YEAR] viene propagato correttamente sia alla tabella Winesales, sia alla tabella Targets. In questo modo, entrambe le tabelle vengono valutate nello stesso contesto temporale, garantendo coerenza tra i dati di vendita e i relativi target:

Scriviamo una nuova Misura Target #2, e usiamo la funzione TREATAS per creare la relazione virtuale tra DateTable[YEAR] e Targets[YEAR]:

// Measure
Target #2 =
    // calculate the Total Target Sales
    // (for the current DateTable[YEAR] in the Filter Context),
    // building a virtual relationship between DateTable[YEAR] and Targets[YEAR],
    // and using DateTable[YEAR] to filter Targets[YEAR]
    CALCULATE (
        // Total Target Sales expression
        SUM(Targets[TARGET]),

        // create a virtual relationship
        // between DateTable[YEAR] and Targets[YEAR],
        // filtering any value from Targets[YEAR]
        // that is not present in DateTable[YEAR]
        TREATAS(
            VALUES(DateTable[YEAR]),  // TableExpression (Source Column)
            Targets[YEAR]             // Target Column
        )
    )

Il primo argomento di TREATAS utilizza VALUES per creare una tabella virtuale contenente la colonna YEAR presa dalla DateTable. Il secondo argomento indica la colonna da filtrare, Targets[YEAR]. Il risultato delle due Misure ora appare corretto: