Scenariile DAX în PowerPivot

Create calculelor particularizate pentru un raport PivotTable

CALCULATE și CALCULATETABLE sunt funcții puternice, flexibile, care sunt 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 operațiune matematică de efectuat. Consultați următoarele subiecte, de exemplu.

• CALCULA

• CALCULATETABLE

Aplicarea unui filtru la o formulă

În majoritatea locurilor în care o funcție DAX ia un tabel ca argument, puteți trece, de obicei, într-un tabel filtrat, fie utilizând funcția FILTER în locul numelui tabelului, fie specificând o expresie filtru ca unul dintre argumentele funcției. Următoarele subiecte oferă exemple de creare a filtrelor și a modului î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 criteriile de filtrare utilizând o expresie, în timp ce celelalte funcții sunt proiectate special pentru a filtra valorile necompletate.

• FILTRU

• ALLNOBLANKROW

• PRIMABLANKĂ

• ULTIMABLANKĂ

Eliminarea selectivă a filtrelor pentru a crea un raport dinamic

Prin crearea filtrelor dinamice în formule, puteți răspunde cu ușurință la întrebări precum următoarele:

• Care a fost contribuția vânzărilor produsului curent la vânzările totale din an?

• Cât a contribuit această împărțire la profiturile totale pe toți anii de funcționare, 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 adăugând sau eliminând filtre. Exemplul din subiectul TOATE vă arată cum să faceți acest lucru. Pentru a găsi raportul dintre vânzările unui anumit reseller și vânzările pentru toți resellerii, creați o măsură care calculează valoarea pentru contextul curent împărțit la valoarea pentru contextul ALL.

Subiectul ALLEXCEPT oferă un exemplu de golire selectivă a filtrelor dintr-o formulă. Ambele exemple vă ajută să parcurgeți modul în care rezultatele se modifică în funcție de proiectarea raportului PivotTable.

• TOATE

• ALLEXCEPT

Pentru alte exemple de calcul al ratelor și procentelor, consultați următoarele subiecte:

• ISBLANK

• ISERROR

Utilizarea unei valori dintr-o buclă exterioară

În plus față de 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 vă arată cum să construiți o formulă care face referire la o valoare dintr-o buclă externă. Funcția EARLIER acceptă până la două niveluri de bucle imbricate.

• Funcția EARLIER

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

Create o coloană 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 de creare a unei coloane calculate pe baza unei chei compuse.

• Funcția CONCATENATE

Compun o dată pe baza părților de dată extrase dintr-o dată text

Power Pivot utilizează un tip de date dată/oră SQL Server 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 dvs. utilizează chei de surogat întregi - poate fi necesar să utilizați o formulă DAX pentru a extrage părțile de dată și a compune părțile într-o reprezentare validă a datei/orei.

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 oferă 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 Windows standard, puteți defini 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 disponibile pentru lucrul cu date și numere.

• Formate numerice predefinite pentru funcția FORMAT

• Formate numerice particularizate pentru funcția FORMAT

• Formate 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 optim de date 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 efectua conversia unei date sau a unui șir numeric într-un număr, înmulțiți cu 1,0. De exemplu, următoarea formulă calculează data curentă minus 3 zile, apoi afișează valoarea întreagă corespunzătoare.

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

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

  =""& TODAY()

Următoarele funcții pot fi utilizate, de asemenea, pentru a vă asigura că se returnează un anumit tip de date:

Conversia numerelor reale în 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 în numere sau date reale

• Funcția VALUE

• Funcția DATEVALUE

• Funcția TIMEVALUE

Create unei valori bazate pe o condiție

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

• DACĂ

• Funcția OR

• Funcția CONCATENATE

Testarea erorilor dintr-o formulă

Spre deosebire de 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 a unei coloane Power Pivot, întreaga coloană este semnalizată cu o eroare, astfel încât 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 și dacă funcția întâlnește o valoare necompletată atunci când se așteaptă la o valoare numerică. În timp ce dezvoltați modelul de date, cel mai bine este să permiteți afișarea erorilor, astfel încât să puteți face 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șită 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 erorile și a returna întotdeauna valori valide. Următoarele subiecte 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 de calcul al soldurilor de închidere și de deschidere. Exemplele vă permit să creați solduri parțiale în 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 în perioade de timp diferite. Perioadele implicite de timp 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 de regăsire a intervalelor 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ții 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ă procedați:

• 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 primelor zece elemente dintr-un raport PivotTable

Ordonarea dinamică a elementelor utilizând o formulă

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

• Funcția EARLIER.