W tej sekcji opisano, jak tworzyć filtry w formułach języka DAX (Data Analysis Expressions). W formułach można tworzyć filtry, aby ograniczyć wartości z danych źródłowych używane w obliczeniach. W tym celu należy określić tabelę jako dane wejściowe formuły, a następnie zdefiniować wyrażenie filtru. Podane wyrażenie filtru służy do wykonywania zapytań dotyczących danych i zwraca tylko podzbiór danych źródłowych. Filtr jest stosowany dynamicznie po każdej aktualizacji wyników formuły, w zależności od bieżącego kontekstu danych.
W tym artykule
Tworzenie filtru w tabeli używanej w formule
Filtry można stosować w formułach, które przyjmują tabelę jako dane wejściowe. Zamiast wprowadzać nazwę tabeli, możesz użyć funkcji FILTRUJ, aby zdefiniować podzbiór wierszy z określonej tabeli. Ten podzbiór jest następnie przekazywany do innej funkcji na potrzeby operacji, takich jak agregacje niestandardowe.
Załóżmy na przykład, że masz tabelę danych zawierającą informacje o zamówieniach odsprzedawców i chcesz obliczyć, ile sprzedali poszczególni sprzedawcy. Trzeba jednak wyświetlić kwoty sprzedaży tylko tych odsprzedawców, którzy sprzedali wiele sztuk produktów o wyższej wartości. Poniższa formuła, oparta na przykładowym skoroszycie języka DAX, przedstawia przykład sposobu tworzenia tego obliczenia przy użyciu filtru:
=SUMA.TEKSTU(
FILTRUJ ('ResellerSales_USD'; 'ResellerSales_USD'[Ilość] > 5 &&
"ResellerSales_USD"[ProductStandardCost_USD] > 100),
'ResellerSales_USD'[SalesAmt]
)
W pierwszej części formuły jest określona jedna z funkcji agregujących dodatku Power Pivot, która przyjmuje tabelę jako argument. Funkcja SUMX oblicza sumę dla tabeli.
Druga część formuły określa
SUMX,FILTER(table, expression),których danych należy użyć.SUMXWymaga tabeli lub wyrażenia, którego wynikiem jest tabela. W tym miejscu zamiast używać wszystkich danych w tabeli, należy użyćFILTERfunkcji w celu określenia, które z wierszy tabeli mają zostać użyte.
Wyrażenie filtru składa się z dwóch części: pierwsza część to nazwa tabeli, do której ma zastosowanie filtr. W drugiej części zdefiniowano wyrażenie używane jako warunek filtru. W tym przypadku filtrujesz odsprzedawców, którzy sprzedali więcej niż 5 sztuk i produktów, które kosztują więcej niż 100 USD. Operator && jest operatorem logicznym AND, który wskazuje, że obie części warunku muszą być spełnione, aby wiersz należał do filtrowanego podzbioru.Trzecia część formuły wskazuje
SUMXfunkcji, które wartości powinny zostać zsumowane. W tym przypadku używana jest tylko kwota sprzedaży.
Należy pamiętać, że funkcje takie jak FILTR, które zwracają tabelę, nigdy nie zwracają bezpośrednio tabeli lub wierszy, ale zawsze są osadzone w innej funkcji. Aby uzyskać więcej informacji o funkcji FILTRUJ i innych funkcjach używanych do filtrowania, wraz z innymi przykładami, zobacz Funkcje filtru (język DAX).Uwaga
Wyrażenie filtru zależy od kontekstu, w którym jest używane. Jeśli na przykład używasz filtru w mierze, a miara jest używana w tabeli przestawnej lub na wykresie przestawnym, podzbiór zwróconych danych może być zależny od dodatkowych filtrów lub fragmentatorów zastosowanych przez użytkownika w tabeli przestawnej. Aby uzyskać więcej informacji na temat kontekstu, zobacz Kontekst w formułach języka DAX.
Filtry usuwające duplikaty
Oprócz filtrowania w poszukiwaniu określonych wartości można zwrócić unikatowy zestaw wartości z innej tabeli lub kolumny. Może to być przydatne, gdy chcesz zliczyć unikatowe wartości w kolumnie lub użyć listy unikatowych wartości do innych operacji. Język DAX udostępnia dwie funkcje zwracające unikatowe wartości: funkcję DISTINCT i funkcję VALUES.
- Funkcja UNIKATOWE sprawdza pojedynczą kolumnę określoną jako argument funkcji i zwraca nową kolumnę zawierającą tylko unikatowe wartości.
- Funkcja VALUES również zwraca listę unikatowych wartości, ale zwraca również nieznany element członkowski. Jest to przydatne, gdy używasz wartości z dwóch tabel połączonych relacją i w jednej tabeli brakuje wartości, a w drugiej nie ma wartości. Aby uzyskać więcej informacji o nieznanym elemencie członkowskim, zobacz Kontekst w formułach języka DAX.
Obie te funkcje zwracają całą kolumnę wartości; W związku z tym za pomocą tych funkcji można uzyskać listę wartości, która jest następnie przekazywana do innej funkcji. Na przykład możesz użyć następującej formuły, aby uzyskać listę różnych produktów sprzedawanych przez określonego odsprzedawcę przy użyciu unikatowego klucza produktu, a następnie zliczyć produkty na tej liście przy użyciu funkcji ILE.WIERSZY:
=COUNTROWS(DISTINCT('ResellerSales_USD'[ProductKey]))
Jak kontekst wpływa na filtry
Gdy formuła języka DAX jest dodawana do tabeli przestawnej lub wykresu przestawnego, kontekst może mieć wpływ na wyniki formuły. Jeśli pracujesz z tabelą dodatku Power Pivot, kontekstem jest bieżący wiersz i jego wartości. W przypadku pracy z tabelą przestawną lub wykresem przestawnym kontekst oznacza zbiór lub podzbiór danych zdefiniowany przez operacje, takie jak fragmentowanie lub filtrowanie. Projekt tabeli przestawnej lub wykresu przestawnego narzuca także własny kontekst. Jeśli na przykład utworzysz tabelę przestawną grupującą sprzedaż według regionów i lat, zostaną w niej wyświetlone tylko dane dotyczące tych regionów i lat. Dlatego wszystkie miary dodawane do tabeli przestawnej są obliczane w kontekście nagłówków kolumn i wierszy oraz wszelkich filtrów w formule miary.
Aby uzyskać więcej informacji, zobacz Kontekst w formułach języka DAX.
Usuwanie filtrów
Podczas pracy ze złożonymi formułami warto znać dokładnie, jakie filtry aktualnie działają, lub można zmodyfikować część filtru formuły. Język DAX udostępnia kilka funkcji umożliwiających usuwanie filtrów i określanie, które kolumny są zachowywane w ramach kontekstu bieżącego filtru. W tej sekcji omówiono wpływ tych funkcji na wyniki w formule.
Zastępowanie wszystkich filtrów za pomocą funkcji ALL
Za pomocą tej ALL funkcji można zastąpić wszelkie wcześniej zastosowane filtry i zwrócić wszystkie wiersze tabeli do funkcji wykonującej agregację lub inną operację. Jeśli zamiast tabeli jest używana przynajmniej jedna kolumna, funkcja ALLALL zwraca wszystkie wiersze, ignorując wszelkie filtry kontekstowe.
Uwaga
Jeśli znasz terminologię relacyjnych baz danych, możesz pomyśleć o ALL generowaniu naturalnego lewego sprzężenia zewnętrznego wszystkich tabel.
Załóżmy na przykład, że użytkownik ma tabele Sprzedaż i Produkty i chce utworzyć formułę, która obliczy sumę sprzedaży bieżącego produktu podzieloną przez sprzedaż wszystkich produktów. Należy wziąć pod uwagę fakt, że w przypadku użycia formuły w miarze użytkownik tabeli przestawnej może używać fragmentatora do filtrowania pod kątem określonego produktu, którego nazwa produktu znajduje się w wierszach. Dlatego, aby uzyskać rzeczywistą wartość mianownika niezależnie od jakichkolwiek filtrów i fragmentatorów, należy dodać funkcję ALL zastępującą wszystkie filtry. Poniższa formuła jest przykładem użycia funkcji ALL w celu zastąpienia efektów poprzednich filtrów:
=SUMA(Sprzedaż[Kwota])/SUMX(Sprzedaż[Kwota]; FILTRUJ(Sprzedaż; ALL(Produkty)))
- Pierwsza część formuły, SUMA (Sprzedaż[Liczba]), oblicza licznik.
- Suma uwzględnia bieżący kontekst, co oznacza, że jeśli dodasz formułę do kolumny obliczeniowej, kontekst wiersza zostanie zastosowany, a jeśli dodasz formułę do tabeli przestawnej jako miarę, zostaną zastosowane wszystkie filtry zastosowane w tabeli przestawnej (kontekst filtru).
- Druga część formuły oblicza mianownik. Funkcja ALL zastępuje wszelkie filtry, które mogą zostać zastosowane do tabeli
Products.
Aby uzyskać więcej informacji, w tym szczegółowe przykłady, zobacz opis funkcji ALL.
Zastępowanie określonych filtrów przy użyciu funkcji ALLEXCEPT
Funkcja ALLEXCEPT zastępuje również istniejące filtry, ale można określić, że niektóre z istniejących filtrów mają zostać zachowane. Kolumny nazwane jako argumenty funkcji ALLEXCEPT określają, które kolumny będą nadal filtrowane. Jeśli chcesz zastąpić filtry z większości kolumn, ale nie ze wszystkich, funkcja ALLEXCEPT jest wygodniejsza niż ALL. Funkcja ALLEXCEPT jest szczególnie przydatna podczas tworzenia tabel przestawnych, które mogą być filtrowane według wielu różnych kolumn, gdy chcesz kontrolować wartości używane w formule. Aby uzyskać więcej informacji, w tym szczegółowy przykład użycia funkcji ALLEXCEPT w tabeli przestawnej, zobacz opis funkcji ALLEXCEPT.