Filtrowanie danych w formułach
W formułach można tworzyć filtry w celu ograniczenia wartości z danych źródłowych używanych w obliczeniach. Należy najpierw określić tabelę jako dane wejściowe formuły, a następnie zdefiniować wyrażenie filtru. Wyrażenie filtru wykonuje zapytanie dotyczące danych i zwraca tylko pewien podzestaw danych źródłowych. Filtr jest stosowany dynamicznie przy każdej aktualizacji wyników formuły, zależnie od bieżącego kontekstu danych. W tej sekcji opisano sposób tworzenia filtrów w formułach języka Data Analysis Expressions (DAX).
Tworzenie filtru dla tabeli używanej w formule
Filtry można stosować w formułach, które jako danych wejściowych używają tabeli. Zamiast wprowadzać nazwę tabeli, można użyć funkcji FILTER w celu wskazania podzestawu wierszy z określonej tabeli. Następnie ten podzestaw jest przekazywany do innej funkcji w celu wykonania operacji, takich jak agregacje niestandardowe.
Na przykład tabela danych zawiera informacje o zamówieniach odsprzedawców i trzeba obliczyć wartość sprzedaży każdego odsprzedawcy. Jednak trzeba wyświetlić kwoty sprzedaży tylko dla tych odsprzedawców, którzy sprzedali wiele sztuk produktów o wysokiej wartości. Poniższa formuła, oparta na przykładowym skoroszycie języka DAX, przedstawia przykład sposobu przeprowadzenia tego obliczenia przy użyciu filtru:
=SUMX(
FILTER ('ResellerSales_USD', 'ResellerSales_USD'[Quantity] > 5 &&
'ResellerSales_USD'[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
Pierwsza część formuły określa jedną z funkcji agregacji programu PowerPivot, która przyjmuje tabelę jako argument. Funkcja SUMX oblicza sumę dla tabeli.
Druga część formuły, FILTER(table, expression), informuje funkcję SUMX, których danych ma użyć. Funkcja SUMX wymaga tabeli lub wyrażenia dającego w wyniku tabelę. Tutaj, zamiast użycia wszystkich danych w tabeli, jest używana funkcja FILTER w celu określenia wierszy z tabeli, które mają zostać użyte.
Wyrażenie filtru składa się z dwóch części: pierwsza część określa tabelę, do której zostanie zastosowany filtr. Druga część definiuje wyrażenie, które ma zostać użyte jako warunek filtru. W tym przypadku filtrowanie jest wykonywane wg odsprzedawców, którzy sprzedali ponad 5 sztuk produktów o cenie wyższej niż 100 USD. Operator && to operator logiczny AND wskazujący, że wiersz musi być zgodny z obiema częściami warunku, aby został dołączony do przefiltrowanego podzestawu.
Trzecia część formuły informuje funkcję SUMX, które wartości mają zostać zsumowane. W tym przypadku jest używana kwota sprzedaży.
Należy zauważyć, że funkcje, takie jak FILTER, które zwracają tabelę, nigdy nie zwracają tabeli ani wierszy bezpośrednio do skoroszytu programu PowerPivot, lecz są zawsze osadzone w innej funkcji. Aby uzyskać więcej informacji dotyczących funkcji FILTER i innych funkcji używanych do filtrowania oraz zapoznać się z większą liczbą przykładów, zobacz temat Funkcje filtru (język DAX).
[!UWAGA]
Na wyrażenie filtru wpływa kontekst, w którym jest ono używane. Na przykład jeśli filtr jest używany w mierze i ta miara jest używana w tabeli przestawnej lub na wykresie przestawnym, podzestaw zwracanych danych może zależeć od dodatkowych filtrów lub fragmentatorów zastosowanych w tabeli przestawnej. Aby uzyskać więcej informacji dotyczących kontekstu, zobacz temat Kontekst w formułach języka DAX.
Filtry usuwające duplikaty
Oprócz stosowania filtrów w celu uzyskania określonych wartości można również zwracać unikatowy zestaw wartości z innej tabeli lub kolumny. Może to być pomocne, jeśli trzeba zliczyć unikatowe wartości w kolumnie lub użyć listy unikatowych wartości w innych operacjach. W języku DAX są dostępne dwie funkcje zwracające unikatowe wartości: Funkcja DISTINCT i Funkcja VALUES.
Funkcja DISTINCT analizuje pojedynczą kolumnę określoną jako argument i zwraca nową kolumnę zawierającą tylko odmienne wartości.
Funkcja VALUES również zwraca listę unikatowych wartości, ale zwraca także nieznany element członkowski. Jest to przydatne w przypadku używania wartości z dwóch tabel, które są powiązane relacją, i pewna wartość istnieje w jednej tabeli, ale nie ma jej w drugiej. Aby uzyskać więcej informacji dotyczących nieznanego elementu członkowskiego, zobacz temat Kontekst w formułach języka DAX.
Obie te funkcje zwracają całą kolumnę wartości i dlatego tych funkcji można używać w celu uzyskania listy wartości, które są przekazywane do innej funkcji. Na przykład za pomocą poniższej formuły, używając unikatowego klucza produktu, można pobrać listę różnych produktów sprzedanych przez określonego odsprzedawcę, a następnie zliczyć produkty na liście, używając funkcji COUNTROWS:
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
Jak kontekst wpływa na filtry
Na wyniki formuły języka DAX dodanej do tabeli przestawnej lub wykresu przestawnego może wpływać kontekst. W przypadku pracy z tabelą programu PowerPivot kontekst to bieżący wiersz i jego wartości. W przypadku pracy z tabelą przestawną lub wykresem przestawnym kontekst oznacza zestaw lub podzestaw danych zdefiniowany przez operacje, takie jak fragmentowanie lub filtrowanie. Ponadto projekt tabeli przestawnej lub wykresu przestawnego wymusza swój własny kontekst. Na przykład w przypadku utworzenia tabeli przestawnej grupującej wielkość sprzedaży według regionów i lat w tabeli przestawnej pojawiają się tylko dane dotyczące tych regionów i lat. Dlatego wszelkie miary dodawane do tabeli przestawnej są obliczane w kontekście nagłówków kolumn i wierszy oraz wszystkich filtrów w formule miary.
Aby uzyskać więcej informacji, zobacz temat Kontekst w formułach języka DAX.
Usuwanie filtrów
Podczas pracy ze złożonymi formułami warto wiedzieć dokładnie, jakie są bieżące filtry, i modyfikować kontekst filtru w ramach formuły. Język DAX oferuje kilka funkcji umożliwiających usuwanie filtrów i określanie, które kolumny mają pozostawać częścią kontekstu bieżącego filtru. W tej sekcji opisano, w jaki sposób te funkcje wpływają na wyniki formuły.
Usuwanie wszystkich filtrów za pomocą funkcji ALL
Funkcja ALL umożliwia usunięcie wszelkich uprzednio zastosowanych filtrów i zwraca wszystkie wiersze w tabeli do funkcji, która wykonuje agregację lub inną operację. W przypadku użycia co najmniej jednej kolumny (zamiast tabeli) jako argumentów funkcji ALL, funkcja ALL zwraca wszystkie wiersze, ignorując wszelkie filtry kontekstowe.
[!UWAGA]
Użytkownicy zaznajomieni z terminologią relacyjnych baz danych mogą porównać działanie funkcji ALL do generowania naturalnego lewego sprzężenia zewnętrznego wszystkich tabel.
Na przykład istnieją dwie tabele, Sales oraz Products, i trzeba utworzyć formułę obliczającą sumę wielkości sprzedaży bieżącego produktu podzieloną przez wielkość sprzedaży dla wszystkich produktów. Należy wziąć pod uwagę fakt, że jeśli formuła jest używana w mierze, użytkownik tabeli przestawnej może używać fragmentatora do filtrowania danych według określonych produktów, używając nazw produktów w wierszach. Dlatego, aby uzyskać prawdziwą wartość mianownika niezależnie od wszelkich filtrów lub fragmentatorów, trzeba dodać funkcję ALL w celu usunięcia wszystkich filtrów. Następująca formuła to przykład sposobu użycia funkcji ALL do usunięcia efektów wcześniej zastosowanych filtrów:
=SUM (Sales[Amount])/SUMX(Sales[Amount], FILTER(Sales, ALL(Products)))
Pierwsza część formuły, SUM (Sales[Amount]), oblicza licznik.
Przy sumowaniu jest uwzględniany bieżący kontekst, co oznacza, że w przypadku dodania formuły do kolumny obliczeniowej stosowany jest kontekst wiersza, a w przypadku dodania formuły jako miary do tabeli przestawnej stosowane są wszelkie filtry zastosowane do tej tabeli (kontekst filtru).
Druga część formuły oblicza mianownik. Funkcja ALL usuwa wszelkie filtry, które mogły zostać zastosowane do tabeli Products.
Aby uzyskać więcej informacji i zapoznać się ze szczegółowymi przykładami, zobacz temat Funkcja ALL.
Usuwanie określonych filtrów za pomocą funkcji ALLEXCEPT
Funkcja ALLEXCEPT również usuwa istniejące filtry, ale można określić, że niektóre z nich mają zostać zachowane. Nazwy kolumn podawane jako argumenty funkcji ALLEXCEPT określają, które kolumny mają być nadal filtrowane. Jeśli planowane jest usunięcie filtrów z większości kolumn, ale nie ze wszystkich, wygodniej jest użyć funkcji ALLEXCEPT niż ALL. Funkcja ALLEXCEPT jest szczególnie przydatna w przypadku tworzenia tabel przestawnych, które mogą być filtrowane według wielu różnych kolumn, gdy użytkownik chce sterować wartościami używanymi w formule. Aby uzyskać więcej informacji i zapoznać się ze szczegółowym przykładem sposobu użycia funkcji ALLEXCEPT w tabeli przestawnej, zobacz temat Funkcja ALLEXCEPT.