Filtrarea datelor în formulele DAX

Se aplică la
Excel pentru Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Această secțiune descrie cum să creați filtre în cadrul formulelor Data Analysis Expressions (DAX). Puteți crea filtre în cadrul formulelor, pentru a restricționa valorile din datele sursă care sunt utilizate în calcule. Faceți acest lucru specificând un tabel ca intrare pentru formulă, apoi definind o expresie de filtru. Expresia filtru pe care o furnizați este utilizată pentru a interoga datele și a returna doar un subset de date sursă. Filtrul se aplică dinamic de fiecare dată când actualizați rezultatele formulei, în funcție de contextul curent al datelor.

În acest articol

Crearea unui filtru într-un tabel utilizat într-o formulă

Puteți aplica filtre în formule care preiau un tabel ca intrare. În loc să introduceți un nume de tabel, utilizați funcția FILTER pentru a defini un subset de rânduri din tabelul specificat. Acest subset este transmis apoi la o altă funcție, pentru operațiuni cum ar fi agregările particularizate.

De exemplu, să presupunem că aveți un tabel de date care conține informații despre comenzi despre reselleri și doriți să calculați cât de mult a vândut fiecare reseller. Cu toate acestea, doriți să afișați volumul vânzărilor doar pentru acei reselleri care au vândut mai multe unități din produsele dvs. cu valoare mai mare. Următoarea formulă, bazată pe registrul de lucru eșantion DAX, arată un exemplu de cum puteți crea acest calcul utilizând un filtru:

=SUMX(
     FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Cantitate] > 5 &&
     "ResellerSales_USD"[ProductStandardCost_USD] > 100),
     'ResellerSales_USD'[SalesAmt]
     )

  • Prima parte a formulei specifică una dintre funcțiile de agregare Power Pivot, care preia un tabel ca argument. SUMX calculează o sumă pentru un tabel.

  • A doua parte a formulei FILTER(table, expression),arată SUMX ce date se vor utiliza. SUMX Necesită un tabel sau o expresie care are ca rezultat un tabel. Aici, în loc să utilizați toate datele dintr-un FILTER tabel, utilizați funcția pentru a specifica care dintre rândurile din tabel sunt utilizate.
    Expresia filtru are două părți: prima parte denumește tabelul la care se aplică filtrul. A doua parte definește o expresie de utilizat ca condiție de filtrare. În acest caz, filtrați după resellerii care au vândut mai mult de 5 unități și produsele care au costat mai mult de 100 lei. Operatorul, &&, este un operator AND logic care indică faptul că ambele părți ale condiției trebuie să fie adevărate pentru ca rândul să aparțină subsetului filtrat.

  • A treia SUMX parte a formulei îi spune funcției ce valori trebuie adunate. În acest caz, utilizați doar cantitatea vânzărilor.
    Rețineți că funcții cum ar fi FILTER, care returnează un tabel, nu returnează niciodată tabelul sau rândurile în mod direct, ci sunt încorporate întotdeauna în altă funcție. Pentru mai multe informații despre FILTER și alte funcții utilizate pentru filtrare, inclusiv mai multe exemple, consultați Funcțiile de filtrare (DAX).

    Notă

    Expresia filtru este afectată de contextul în care este utilizată. De exemplu, dacă utilizați un filtru într-o măsură, iar măsura este utilizată într-un raport PivotTable sau PivotChart, subsetul de date returnat poate fi afectat de filtre sau slicere suplimentare pe care utilizatorul le-a aplicat în raportul PivotTable. Pentru mai multe informații despre context, consultați Contextul în formulele DAX.

Filtre care elimină dublurile

Pe lângă filtrarea pentru valori specifice, puteți returna un set unic de valori din alt tabel sau altă coloană. Acest lucru poate fi util atunci când doriți să contorizați numărul de valori unice dintr-o coloană sau să utilizați o listă de valori unice pentru alte operațiuni. DAX oferă două funcții pentru returnarea valorilor distincte: funcția DISTINCT și funcția VALUES.

  • Funcția DISTINCT examinează o singură coloană pe care o specificați ca argument pentru funcție și returnează o coloană nouă care conține doar valorile distincte.
  • Funcția VALUES returnează, de asemenea, o listă de valori unice, dar returnează și membrul necunoscut. Acest lucru este util atunci când utilizați valori din două tabele care sunt asociate printr-o relație și o valoare lipsește dintr-un tabel și este prezentă în celălalt. Pentru mai multe informații despre membrul necunoscut, consultați Contextul în formulele DAX.

Ambele funcții returnează o coloană întreagă de valori; Așadar, utilizați funcțiile pentru a obține o listă de valori care este transmisă apoi altei funcții. De exemplu, puteți utiliza următoarea formulă pentru a obține o listă cu produsele distincte vândute de un anumit reseller, utilizând cheia de produs unică, apoi pentru a contoriza produsele din acea listă utilizând funcția COUNTROWS:

=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))

Începutul paginii

Cum afectează contextul filtrele

Atunci când adăugați o formulă DAX la un raport PivotTable sau PivotChart, rezultatele formulei pot fi afectate de context. Dacă lucrați într-un tabel Power Pivot, contextul este rândul curent și valorile sale. Dacă lucrați într-un raport PivotTable sau PivotChart, contextul înseamnă setul sau subsetul de date definite de operațiuni precum slicarea sau filtrarea. Designul unui raport PivotTable sau PivotChart impune, de asemenea, propriul context. De exemplu, dacă creați un raport PivotTable care grupează vânzările după regiune și an, doar datele care se aplică acelor regiuni și ani apar în raportul PivotTable. Prin urmare, orice măsuri adăugate în raportul PivotTable sunt calculate în contextul titlurilor de coloană și de rând, plus orice filtre din formula de măsură.

Pentru mai multe informații, consultați Contextul în formulele DAX.

Începutul paginii

Eliminarea filtrelor

Când lucrați cu formule complexe, poate că doriți să știți exact care sunt filtrele curente sau poate doriți să modificați partea de filtrare a formulei. DAX oferă mai multe funcții care vă permit să eliminați filtre și să controlați ce coloane sunt reținute ca parte a contextului filtrului curent. Această secțiune oferă o prezentare generală a modului în care aceste funcții afectează rezultatele dintr-o formulă.

Înlocuirea tuturor filtrelor cu funcția ALL

Puteți utiliza funcția ALL pentru a înlocui orice filtre care au fost aplicate anterior și a returna toate rândurile din tabel la funcția care efectuează operațiunea de agregare sau de altă natură. Dacă utilizați una sau mai multe coloane, în loc de un tabel, ca argumente pentru ALL, funcția ALL returnează toate rândurile, ignorând filtrele de context.

Notă

Dacă sunteți familiarizat cu terminologia bazelor de date relaționale, vă puteți gândi ca ALL la aceasta să genereze uniunea externă naturală la stânga a tuturor tabelelor.

De exemplu, să presupunem că aveți tabelele Vânzări și Produse și doriți să creați o formulă care va calcula suma vânzărilor produsului curent împărțită la vânzările tuturor produselor. Trebuie să luați în considerare faptul că, dacă formula este utilizată într-o anumită măsură, utilizatorul raportului PivotTable poate utiliza un slicer pentru a filtra după un anumit produs, cu numele produsului pe rânduri. Prin urmare, pentru a obține valoarea adevărată a numitorului indiferent de filtre sau slicere, trebuie să adăugați funcția ALL pentru a înlocui orice filtre. Formula următoare este un exemplu de utilizare a ALL pentru a înlocui efectele filtrelor anterioare:

=SUM (Vânzări[Cantitate])/SUMX(Vânzări[Cantitate], FILTER(Vânzări, ALL(Produse)))

  • Prima parte a formulei, SUM (Vânzări[Cantitate]), calculează numărătorul.
  • Suma ia în considerare contextul curent, însemnând că, dacă adăugați formula într-o coloană calculată, se aplică contextul rândului, iar dacă adăugați formula într-un raport PivotTable ca măsură, se aplică orice filtre aplicate în PivotTable (contextul de filtrare).
  • A doua parte a formulei, calculează numitorul. Funcția ALL înlocuiește orice filtre care pot fi aplicate în Products tabel.

Pentru mai multe informații, inclusiv exemple detaliate, consultați funcția ALL.

Înlocuirea anumitor filtre cu funcția ALLEXCEPT

Funcția ALLEXCEPT înlocuiește și filtrele existente, dar puteți să specificați ca unele dintre filtrele existente să fie păstrate. Coloanele pe care le denumiți ca argumente ale funcției ALLEXCEPT specifică ce coloane vor continua să fie filtrate. Dacă doriți să înlocuiți filtrele din majoritatea coloanelor, dar nu din toate, ALLEXCEPT este mai convenabil decât ALL. Funcția ALLEXCEPT este utilă mai ales atunci când creați rapoarte PivotTable care pot fi filtrate pe mai multe coloane diferite și doriți să controlați valorile care sunt utilizate în formulă. Pentru mai multe informații, inclusiv un exemplu detaliat de utilizare a funcției ALLEXCEPT într-un raport PivotTable, consultați Funcția ALLEXCEPT.

Începutul paginii