Data Analysis Expressions (DAX) din Power Pivot

DAX (Data Analysis Expressions) sună puțin intimidant la început, dar nu vă lăsați păcălit de nume. Noțiunile de bază despre DAX sunt într-adevăr destul de ușor de înțeles. În primul rând, DAX NU este un limbaj de programare. DAX este un limbaj pentru formule. Puteți utiliza DAX pentru a defini calcule particularizate pentru coloane calculate și pentru măsuri (numite și câmpuri calculate). DAX include unele dintre funcțiile utilizate în formulele Excel și funcții suplimentare proiectate să lucreze cu date relaționale și să efectueze agregări dinamice.

Înțelegerea formulelor DAX

Formulele DAX sunt foarte asemănătoare cu formulele Excel. Pentru a crea una, tastați semnul egal, urmat de numele unei funcții sau al unei expresii și orice valori sau argumente necesare. La fel ca Excel, DAX oferă o varietate de funcții pe care le puteți utiliza pentru a lucra cu șiruri, a efectua calcule utilizând date și ore sau a crea valori condiționate.

Totuși, formulele DAX sunt diferite în următoarele moduri importante:

• Dacă doriți să particularizați calculele rând cu rând, DAX include funcții care vă permit să utilizați valoarea rândului curent sau o valoare asociată pentru a efectua calcule care variază în funcție de context.
• DAX include un tip de funcție care returnează un tabel ca rezultat, nu o singură valoare. Aceste funcții pot fi utilizate pentru a furniza informații către alte funcții.
• Funcțiile Time Intelligence dinDAX permit calcule utilizând intervale de date și compară rezultatele în perioade paralele.

Unde se utilizează formulele DAX

Puteți crea formule în Power Pivot fie în coloane calculate, fie în câmpuri calculate.

Coloane calculate

O coloană calculată este o coloană pe care o adăugați la un tabel Power Pivot existent. În loc să lipiți sau să importați valorile din coloană, creați o formulă DAX care definește valorile din coloană. Dacă includeți tabelul Power Pivot într-un raport PivotTable (sau PivotChart), coloana calculată poate fi utilizată la fel ca orice altă coloană de date.

Formulele din coloanele calculate sunt foarte asemănătoare cu formulele pe care le creați în Excel. Spre deosebire de Excel, însă, nu puteți crea o formulă diferită pentru rânduri diferite dintr-un tabel; În schimb, formula DAX se aplică automat la întreaga coloană.

Atunci când o coloană conține o formulă, valoarea este calculată pentru fiecare rând. Rezultatele sunt calculate pentru coloană imediat ce creați formula. Valorile din coloane sunt recalculate numai dacă datele subiacente sunt reîmprospătate sau dacă se utilizează recalcularea manuală.

Puteți crea coloane calculate care se bazează pe măsuri și alte coloane calculate. Totuși, evitați să utilizați același nume pentru o coloană calculată și o măsură, deoarece acest lucru poate duce la rezultate confuze. Atunci când faceți referire la o coloană, se recomandă să utilizați o referință de coloană complet calificată, pentru a evita invocarea accidentală a unei măsuri.

Pentru informații mai detaliate, consultați Coloanele calculate în Power Pivot.

Măsuri

O măsură este o formulă creată special pentru a fi utilizată într-un raport PivotTable (sau PivotChart) care utilizează date Power Pivot. Măsurile se pot baza pe funcții de agregare standard, cum ar fi COUNT sau SUM, sau vă puteți defini propria formulă utilizând DAX. O măsură este utilizată în zona Valori dintr-un raport PivotTable. Dacă doriți să plasați rezultatele calculate într-o altă zonă a unui raport PivotTable, utilizați în schimb o coloană calculată.

Atunci când definiți o formulă pentru o măsură explicită, nu se întâmplă nimic până când nu adăugați măsura într-un PivotTable. Când adăugați măsura, formula este evaluată pentru fiecare celulă din zona Valori din raportul PivotTable. Deoarece se creează un rezultat pentru fiecare combinație de anteturi de rând și coloană, rezultatul pentru măsură poate fi diferit în fiecare celulă.

Definiția măsurii pe care o creați este salvată cu tabelul de date sursă. Acesta apare în lista de câmpuri PivotTable și este disponibil tuturor utilizatorilor registrului de lucru.

Pentru informații mai detaliate, consultați Măsuri în Power Pivot.

Crearea formulelor utilizând bara de formule

Power Pivot, la fel ca Excel, oferă o bară de formule pentru a simplifica crearea și editarea formulelor și funcționalitatea de Completare automată, pentru a reduce la minimum erorile de tastare și de sintaxă.

Pentru a introduce numele unui tabel Începeți să tastați numele tabelului. Completare automată formulă furnizează o listă verticală care conține nume valide care încep cu acele litere.

Pentru a introduce numele unei coloane Tastați o paranteză, apoi alegeți coloana din lista de coloane din tabelul curent. Pentru o coloană din alt tabel, începeți să tastați primele litere ale numelui tabelului, apoi alegeți coloana din lista verticală de Completare automată.

Pentru mai multe detalii și o prezentare generală a formulelor, consultați Crearea formulelor pentru calcule în Power Pivot.

Sfaturi pentru utilizarea Completării automate

Puteți utiliza Completare automată formulă în mijlocul unei formule existente cu funcții imbricate. Textul aflat imediat înainte de punctul de inserare este utilizat pentru afișarea valorilor în lista verticală, iar tot textul de după punctul de inserare rămâne nemodificat.

Numele definite pe care le creați pentru constante nu se afișează în lista verticală de Completare automată, dar le puteți tasta în continuare.

Power Pivot nu adaugă parantezele de închidere ale funcțiilor și nu potrivește automat parantezele. Trebuie să vă asigurați că fiecare funcție este corectă sintactic, altfel nu puteți să salvați sau să utilizați formula. 

Utilizarea mai multor funcții într-o formulă

Puteți imbrica funcții, adică utilizați rezultatele unei funcții ca argument al altei funcții. Puteți imbrica până la 64 de niveluri de funcții în coloane calculate. Cu toate acestea, imbricarea poate îngreuna crearea sau depanarea formulelor.

Multe funcții DAX sunt proiectate să fie utilizate doar ca funcții imbricate. Aceste funcții returnează un tabel, care nu poate fi salvat direct ca rezultat; Ar trebui să fie furnizat ca intrare pentru o funcție de tabel. De exemplu, funcțiile SUMX, AVERAGEX și MINX necesită un tabel ca prim argument.

Compararea funcțiilor DAX și a funcțiilor Excel

Biblioteca de funcții DAX se bazează pe biblioteca de funcții Excel, dar bibliotecile prezintă multe diferențe. Această secțiune rezumă diferențele și asemănările dintre funcțiile Excel și funcțiile DAX.

• Multe funcții DAX au același nume și același comportament general ca funcțiile din Excel, dar au fost modificate pentru a prelua tipuri diferite de intrări și, în unele cazuri, pot returna un alt tip de date. În general, nu puteți utiliza funcții DAX într-o formulă Excel sau să utilizați formule Excel în Power Pivot fără unele modificări.
• Funcțiile DAX nu iau niciodată ca referință la o celulă sau o zonă, ci, în schimb, funcțiile DAX iau o coloană sau un tabel ca referință.
• Funcțiile DAX de dată și oră returnează un tip de date datetime. În schimb, funcțiile Excel pentru dată și oră returnează un întreg care reprezintă o dată ca număr serial.
• Multe dintre noile funcții DAX fie returnează un tabel de valori, fie efectuează calcule pe baza unui tabel de valori ca intrare. În schimb, Excel nu are funcții care returnează un tabel, dar unele funcții pot funcționa cu matrice. Capacitatea de a face referire cu ușurință la tabele și coloane complete este o caracteristică nouă din Power Pivot.
• DAX oferă noi funcții de căutare care sunt similare funcțiilor de căutare matrice și vector din Excel. Totuși, funcțiile DAX necesită stabilirea unei relații între tabele.
• Se așteaptă ca datele dintr-o coloană să fie întotdeauna de același tip de date. Dacă datele nu sunt de același tip, DAX modifică întreaga coloană în tipul de date care cuprinde cel mai bine toate valorile.

Tipuri de date DAX

Puteți importa date într-un model de date Power Pivot din mai multe surse de date diferite care pot accepta tipuri de date diferite. Atunci când importați sau încărcați datele, apoi le utilizați în calcule sau în rapoarte PivotTable, datele sunt convertite într-unul dintre tipurile de date Power Pivot. Pentru o listă a tipurilor de date, consultați Tipurile de date din modelele de date.

Tipul de date tabel este un tip de date nou din DAX care este utilizat ca intrare sau ieșire pentru multe funcții noi. De exemplu, funcția FILTER preia un tabel ca intrare și scoate un alt tabel care conține numai rândurile care îndeplinesc condițiile de filtrare. Combinând funcții de tabel cu funcții de agregare, puteți efectua calcule complexe pe seturi de date definite dinamic. Pentru mai multe informații, consultați Agregările în Power Pivot.

Formulele și modelul relațional

Fereastra Power Pivot este o zonă în care puteți să lucrați cu mai multe tabele de date și să conectați tabelele într-un model relațional. În acest model de date, tabelele sunt conectate între ele prin relații, care vă permit să creați corelații cu coloanele din alte tabele și să creați calcule mai interesante. De exemplu, puteți să creați formule care însumează valorile pentru un tabel asociat, apoi să salvați valoarea respectivă într-o singură celulă. Sau, pentru a controla rândurile din tabelul asociat, puteți aplica filtre la tabele și coloane. Pentru mai multe informații, consultați Relațiile între tabelele dintr-un model de date.

Deoarece puteți lega tabele utilizând relații, rapoartele PivotTable pot include, de asemenea, date din mai multe coloane care provin din tabele diferite.

Însă, deoarece formulele pot funcționa cu tabele și coloane întregi, trebuie să proiectați calculele altfel decât procedați în Excel.

• În general, o formulă DAX dintr-o coloană se aplică întotdeauna întregului set de valori din coloană (niciodată doar câtorva rânduri sau celule).
• Tabelele din Power Pivot trebuie să aibă întotdeauna același număr de coloane în fiecare rând și toate rândurile dintr-o coloană trebuie să conțină același tip de date.
• Când tabelele sunt conectate printr-o relație, trebuie să vă asigurați că cele două coloane utilizate ca chei au valori care se potrivesc, în cea mai mare parte. Deoarece Power Pivot nu impune integritatea referențială, este posibil să aveți valori care nu se potrivesc într-o coloană cheie și să creați totuși o relație. Totuși, prezența valorilor necompletate sau care nu se potrivesc poate afecta rezultatele formulelor și aspectul rapoartelor PivotTable. Pentru mai multe informații, consultați Căutările în formulele Power Pivot.
• Când legați tabele utilizând relații, măriți domeniul sau contextul în care sunt evaluate formulele. De exemplu, formulele dintr-un raport PivotTable pot fi afectate de orice filtre sau de titluri de coloană sau de rând din raportul PivotTable. Puteți să scrieți formule care manipulează contextul, dar contextul poate, de asemenea, să determine modificarea rezultatelor în moduri pe care probabil nu le-ați anticipat. Pentru mai multe informații, consultați Contextul în formulele DAX.

Actualizarea rezultatelor formulelor

Reîmprospătarea datelor și recalcularea sunt două operațiuni separate, dar înrudite, pe care ar trebui să le înțelegeți atunci când proiectați un model de date care conține formule complexe, volume mari de date sau date obținute din surse de date externe.

Reîmprospătarea datelor este procesul de actualizare a datelor din registrul de lucru cu date noi de la o sursă de date externă. Puteți reîmprospăta datele manual, la intervalele pe care le specificați. Sau, dacă ați publicat registrul de lucru pe un site SharePoint, puteți programa o reîmprospătare automată din surse externe.

Recalcularea este procesul de actualizare a rezultatelor formulelor pentru a reflecta orice modificări ale formulelor propriu-zise și pentru a reflecta acele modificări din datele subiacente. Recalcularea poate afecta performanța în următoarele moduri:

• Pentru o coloană calculată, rezultatul formulei trebuie întotdeauna recalculat pentru întreaga coloană, ori de câte ori se modifică formula.
• Pentru o măsură, rezultatele unei formule nu sunt calculate până când măsura nu este plasată în contextul PivotTable sau PivotChart. Formula va fi, de asemenea, recalculată atunci când modificați orice titlu de rând sau de coloană care afectează filtrele din date sau atunci când reîmprospătați manual raportul PivotTable.

Depanarea formulelor

Erori la scrierea formulelor

Dacă primiți o eroare atunci când definiți o formulă, formula poate conține o eroare sintactică, o eroare semantică sau o eroare de calcul.

Erorile sintactice sunt cel mai simplu de rezolvat. De obicei, acestea implică lipsa unei paranteze sau a unei virgule. Pentru ajutor cu sintaxa funcțiilor individuale, consultați Referința funcțiilor DAX.

Celălalt tip de eroare apare atunci când sintaxa este corectă, dar valoarea sau coloana la care se face referire nu are sens în contextul formulei. Astfel de erori semantice și de calcul pot fi cauzate de oricare dintre următoarele probleme:

• Formula se referă la o coloană, un tabel sau o funcție care nu există.
• Formula pare să fie corectă, dar atunci când motorul de date preia datele, găsește o nepotrivire de tip și generează o eroare.
• Formula transmite un număr incorect sau un tip de parametri unei funcții.
• Formula se referă la o altă coloană care conține o eroare și, prin urmare, valorile sale sunt nevalide.
• Formula se referă la o coloană care nu a fost procesată, ceea ce înseamnă că are metadate, dar nu există date reale de utilizat pentru calcule.

În primele patru cazuri, DAX semnalizează întreaga coloană care conține formula nevalidă. În ultimul caz, DAX estompează coloana pentru a indica faptul că aceasta este într-o stare neprocesată.

Rezultate incorecte sau neobișnuite la ierarhizarea sau ordonarea valorilor din coloane

Atunci când ierarhizați sau ordonați o coloană ce conține valoarea NaN (nu un număr), este posibil să obțineți rezultate greșite sau neașteptate. De exemplu, atunci când un calcul împarte 0 la 0, se returnează un rezultat NaN.

Acest lucru se datorează faptului că motorul de formule ordonează și clasifică prin compararea valorilor numerice; Cu toate acestea, NaN nu poate fi comparat cu alte numere din coloană.

Pentru a asigura rezultate corecte, puteți utiliza instrucțiuni condiționale care utilizează funcția IF pentru a testa valorile NaN și a returna o valoare numerică 0.

Compatibilitatea cu modelele tabelare Analysis Services și modul DirectQuery

În general, formulele DAX pe care le construiți în Power Pivot sunt complet compatibile cu modelele tabelare Analysis Services. Totuși, dacă migrați modelul Power Pivot într-o instanță Analysis Services, apoi implementați modelul în modul DirectQuery, există câteva limitări.

• Unele formule DAX pot returna rezultate diferite dacă implementați modelul în modul DirectQuery.
• Unele formule pot provoca erori de validare atunci când implementați modelul în modul DirectQuery, deoarece formula conține o funcție DAX care nu este acceptată pentru o sursă de date relațională.

Pentru mai multe informații, consultați documentația de modelare tabelară Analysis Services în SQL Server 2012 BooksOnline.