Scenariile DAX în PowerPivot

Crearea calculelor particularizate pentru un raport PivotTable

CALCULATE și CALCULATETABLE sunt funcții puternice, flexibile, utile pentru definirea câmpurilor calculate. Aceste funcții vă permit să modificați contextul în care se va efectua calculul. De asemenea, puteți particulariza tipul de agregare sau de operație matematică de efectuat. Consultați următoarele subiecte pentru exemple.

• CALCULATE

• CALCULATETABLE

Aplicarea unui filtru la o formulă

În majoritatea locurilor unde funcția DAX preia un tabel ca argument, puteți trece, de obicei, într-un tabel filtrat, fie utilizând funcția FILTER în locul numelui de tabel, fie specificând o expresie de filtru ca argument al funcției. Următoarele subiecte oferă exemple despre cum să creați filtre și modul în care filtrele afectează rezultatele formulelor. Pentru mai multe informații, consultați Filtrarea datelor în formulele DAX.

Funcția FILTER vă permite să specificați criterii de filtrare utilizând o expresie, în timp ce celelalte funcții sunt proiectate special pentru a filtra după valori necompletate.

• FILTER

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

Eliminarea selectivă a filtrelor pentru a crea un raport dinamic

Prin crearea de filtre dinamice în formule, puteți răspunde cu ușurință la întrebări cum ar fi următoarele:

• Care a fost contribuția vânzărilor din produsul curent la totalul vânzărilor din an?

• Cât a contribuit această împărțire la profiturile totale pentru toți anii de operare, comparativ cu alte divizii?

Formulele pe care le utilizați într-un raport PivotTable pot fi afectate de contextul PivotTable, dar puteți modifica selectiv contextul prin adăugarea sau eliminarea filtrelor. Exemplul din subiectul ALL vă arată cum să faceți acest lucru. Pentru a găsi raportul dintre vânzările pentru un anumit reseller și vânzările tuturor resellerilor, creați o măsură care calculează valoarea contextului curent împărțită la valoarea pentru contextul ALL.

Subiectul ALLEXCEPT oferă un exemplu de ștergere selectivă a filtrelor din cadrul unei formule. Ambele exemple vă arată cum se modifică rezultatele în funcție de proiectarea raportului PivotTable.

• ALL

• ALLEXCEPT

Pentru alte exemple despre cum se calculează ratele și procentele, consultați următoarele subiecte:

• ISBLANK

• ISERROR

Utilizarea unei valori dintr-o buclă externă

Pe lângă utilizarea valorilor din contextul curent în calcule, DAX poate utiliza o valoare dintr-o buclă anterioară în crearea unui set de calcule asociate. Următorul subiect furnizează o prezentare a modului de a construi o formulă care face referire la o valoare dintr-o buclă exterioară. Funcția EARLIER acceptă până la două niveluri de bucle imbricate.

• Funcția EARLIER

Pentru a afla mai multe despre contextul rândurilor, tabelele asociate și cum să utilizați acest concept în formule, consultați Contextul în formulele DAX.

Crearea unei coloane cheie prin concatenare

Power Pivot nu permite chei compuse; Prin urmare, dacă aveți chei compuse în sursa de date, poate fi necesar să le combinați într-o singură coloană cheie. Următorul subiect oferă un exemplu despre cum să creați o coloană calculată bazată pe o cheie compusă.

• Funcția CONCATENATE

Compose a date based on date parts extracted from a text date

Power Pivot utilizează un tip SQL Server date dată/oră pentru a lucra cu datele; Prin urmare, dacă datele externe conțin date formatate diferit - de exemplu, dacă datele sunt scrise într-un format de dată regional care nu este recunoscut de motorul de date Power Pivot sau dacă datele utilizează taste surogate întregi - poate fi necesar să utilizați o formulă DAX pentru a extrage părțile de dată și a compune apoi părțile într-o reprezentare validă dată/oră.

De exemplu, dacă aveți o coloană de date care au fost reprezentate ca numere întregi și apoi importate ca șir text, puteți efectua conversia șirului la o valoare dată/oră utilizând următoarea formulă:

=DATE(RIGHT([Valoare1],4),LEFT([Valoare1],2),MID([Valoare1],2))

Următoarele subiecte furnizează mai multe informații despre funcțiile utilizate pentru a extrage și a compune date.

• Funcția DATEVALUE

• Funcția TIMEVALUE

• Funcția DATE

Definirea unui format de dată sau de număr particularizat

Dacă datele conțin date sau numere care nu sunt reprezentate într-unul dintre formatele de text standard Windows, aveți posibilitatea să definiți un format particularizat pentru a vă asigura că valorile sunt gestionate corect. Aceste formate sunt utilizate la conversia valorilor în șiruri sau din șiruri. Următoarele subiecte furnizează, de asemenea, o listă detaliată a formatelor predefinite care sunt disponibile pentru lucrul cu date și numere.

• Formatele numerice predefinite pentru funcția FORMAT

• Formate numerice particularizate pentru funcția FORMAT

• Formatele predefinite de dată și oră pentru funcția FORMAT

• Formate particularizate de dată și oră pentru funcția FORMAT

Modificarea tipurilor de date utilizând o formulă

În Power Pivot, tipul de date al rezultatului este determinat de coloanele sursă și nu puteți specifica în mod explicit tipul de date al rezultatului, deoarece tipul de date optim este determinat de Power Pivot. Cu toate acestea, puteți utiliza conversiile implicite ale tipurilor de date efectuate de Power Pivot pentru a manipula tipul de date de ieșire. 

• Pentru a transforma o dată sau un șir numeric într-un număr, înmulțiți cu 1,0. De exemplu, următoarea formulă calculează data curentă minus 3 zile, apoi returnează valoarea întreagă corespunzătoare.

  =(TODAY()-3)*1,0

• Pentru a efectua conversia unei valori dată, număr sau monedă într-un șir, concatenați valoarea cu un șir gol. De exemplu, următoarea formulă returnează data de astăzi ca șir.

  =""& TODAY()

De asemenea, se pot utiliza următoarele funcții pentru a vă asigura că un anumit tip de date este returnat:

Conversia numerelor reale la numere întregi

• Funcția ROUND

• Funcția CEILING

• Funcția FLOOR

  Conversia numerelor reale, a numerelor întregi sau a datelor în șiruri

• Funcția FIXED

• Funcția FORMAT

  Conversia șirurilor la numere sau date reale

• Funcția VALUE

• Funcția DATEVALUE

• Funcția TIMEVALUE

Crearea unei valori pe baza unei condiții

Puteți utiliza condițiile IF imbricate pentru a testa valori și a genera condiționat valori noi. Următoarele subiecte conțin câteva exemple simple de procesare condiționată și valori condiționale:

• IF

• Funcția OR

• Funcția CONCATENATE

Testați pentru a de eroare în cadrul unei formule

Spre Excel, nu puteți avea valori valide într-un rând al unei coloane calculate și valori nevalide în alt rând. Aceasta înseamnă că, dacă există o eroare în orice parte Power Pivot unei coloane, întreaga coloană este semnalizată cu o eroare, astfel încât trebuie să corectați întotdeauna erorile de formule care au ca rezultat valori nevalide.

De exemplu, dacă creați o formulă care se împarte la zero, este posibil să obțineți rezultatul infinit sau o eroare. Unele formule nu vor reuși nici dacă funcția întâlnește o valoare necompletată atunci când se așteaptă o valoare numerică. În timp ce dezvoltați modelul de date, cel mai bine este să permiteți apariția erorilor, astfel încât să puteți să faceți clic pe mesaj și să depanați problema. Cu toate acestea, atunci când publicați registre de lucru, ar trebui să încorporați gestionarea erorilor pentru a împiedica valorile neașteptate să provoace nereușirea calculelor.

Pentru a evita returnarea erorilor într-o coloană calculată, utilizați o combinație de funcții logice și de informații pentru a testa erori și a returna întotdeauna valori valide. Următoarele subiecte vă oferă câteva exemple simple de cum să faceți acest lucru în DAX:

• Funcția ISBLANK

• Funcția IFERROR

Calcularea vânzărilor cumulative

Următoarele subiecte conțin exemple despre cum se calculează soldurile de închidere și de deschidere. Exemplele vă permit să creați solduri periodice din diferite intervale, cum ar fi zile, luni, trimestre sau ani.

• Funcția CLOSINGBALANCEMONTH, Funcția CLOSINGBALANCEQUARTER, Funcția CLOSINGBALANCEYEAR

• Funcția OPENINGBALANCEMONTH, Funcția OPENINGBALANCEQUARTER, Funcția OPENINGBALANCEYEAR

Compararea valorilor în timp

Următoarele subiecte conțin exemple de comparare a sumelor din perioade de timp diferite. Perioadele de timp implicite acceptate de DAX sunt luni, trimestre și ani.

• Funcția PREVIOUSMONTH, PREVIOUSQUARTER, Funcția PREVIOUSYEAR

• Funcția TOTALMTD, Funcția TOTALQTD, Funcția TOTALYTD

• Funcția PARALLELPERIOD

Calcularea unei valori peste un interval de date particularizat

Consultați următoarele subiecte pentru exemple despre cum să regăsiți intervale de date particularizate, cum ar fi primele 15 zile de la începutul unei promoții de vânzări.

• Funcția DATESINPERIOD

• Funcția DATESBETWEEN

• Funcția DATEADD

• Funcția FIRSTDATE

• Funcția LASTDATE

Dacă utilizați funcțiile time intelligence pentru a regăsi un set particularizat de date, puteți utiliza acel set de date ca intrare pentru o funcție care efectuează calcule, pentru a crea agregate particularizate în perioade de timp. Consultați următorul subiect pentru un exemplu despre cum să faceți acest lucru:

• Funcția PARALLELPERIOD

  Notă: Dacă nu trebuie să specificați un interval de date particularizat, dar lucrați cu unități de contabilitate standard, cum ar fi luni, trimestre sau ani, vă recomandăm să efectuați calcule utilizând funcțiile time intelligence proiectate în acest scop, cum ar fi TOTALQTD, TOTALMTD, TOTALQTD etc.

Afișarea numai a celor mai importante zece elemente într-un raport PivotTable

Ordonarea dinamică a elementelor utilizând o formulă

Următorul subiect conține un exemplu despre cum să utilizați DAX pentru a crea o ierarhizare stocată într-o coloană calculată. Deoarece formulele DAX sunt calculate dinamic, puteți fi întotdeauna sigur că ierarhizarea este corectă, chiar dacă datele subiacente s-au modificat. De asemenea, deoarece formula este utilizată într-o coloană calculată, puteți utiliza clasificarea într-un slicer, apoi puteți selecta primele 5, primele 10 sau chiar primele 100 de valori.

• Funcția EARLIER.