Kontekst w formułach języka DAX

Artykuł
12/05/2014

Kontekst umożliwia wykonywanie analiz dynamicznych, w których wyniki formuły mogą zmieniać się w celu odzwierciedlenia bieżącego wyboru wierszy i kolumn oraz dowolnych powiązanych danych. Zrozumienie, czym jest kontekst, oraz efektywne używanie kontekstu stanowi kluczowy element tworzenia wysoko wydajnych dynamicznych analiz i rozwiązywania problemów z formułami.

W tej sekcji zdefiniowano różne typy kontekstu: kontekst wiersza, kontekst zapytania i kontekst filtru. Wyjaśniono, w jaki sposób kontekst jest określany dla formuł znajdujących się w kolumnach obliczeniowych i tabelach przestawnych.

W ostatniej części tej sekcji znajdują się łącza do szczegółowych przykładów pokazujących, jak wyniki formuł zmieniają się w zależności od kontekstu.

Wprowadzenie do kontekstu

Na formuły w programie PowerPivot wpływ mają filtry zastosowane w tabeli przestawnej, relacje między tabelami oraz filtry użyte w formułach. Kontekst jest tym, co umożliwia wykonywanie analizy dynamicznej. Zrozumienie istoty kontekstu znacząco ułatwi tworzenie formuł i rozwiązywanie związanych z nimi problemów.

Istnieją różne typy kontekstu: kontekst wiersza, kontekst zapytania i kontekst filtru.

Kontekst wiersza można uważać za „wiersz bieżący”. Jeśli utworzono kolumnę obliczeniową, kontekst wiersza składa się z wartości w każdym pojedynczym wierszu oraz wartości w kolumnach powiązanych z bieżącym wierszem. Istnieją też funkcje (EARLIER i EARLIEST), które pobierają wartość z bieżącego wiersza, a następnie używają jej podczas wykonywania operacji na całej tabeli.

Kontekst zapytania dotyczy podzestawu danych, który jest niejawnie tworzony dla każdej komórki w tabeli przestawnej, w zależności od nagłówków wierszy i kolumn.

Kontekst filtru to zestaw wartości dozwolonych w każdej kolumnie, określony na podstawie ograniczeń filtru, które zostały zastosowane do wiersza lub zostały zdefiniowanie przez wyrażenia filtrów w formule.

W następującym temacie opisano bardziej szczegółowo różne typy kontekstu: Kontekst w formułach języka DAX.

Powrót do początku

Kontekst wiersza

W przypadku utworzenia formuły w kolumnie obliczeniowej kontekst wiersza dla tej formuły obejmuje wartości ze wszystkich kolumn w bieżącym wierszu. Jeśli tabela jest powiązana z inną tabelą, kontekst obejmuje też wszystkie wartości z tej innej tabeli, które są powiązane z bieżącym wierszem.

Na przykład utworzono kolumnę obliczeniową =[Freight] + [Tax], która dodaje dwie kolumny z jednej tabeli. Ta formuła zachowuje się jak formuły w tabeli programu Excel, które automatycznie odwołują się do wartości z tego samego wiersza. Należy zauważyć, że tabele to coś innego niż zakresy: nie można odwoływać się do wartości z wiersza znajdującego się przed bieżącym wierszem, używając notacji zakresu, i nie można odwoływać się do dowolnie wybranej pojedynczej wartości w tabeli lub komórce. Zawsze trzeba pracować z tabelami i kolumnami.

W kontekście wiersza automatycznie są uwzględniane relacje między tabelami w celu ustalenia, które wiersze w powiązanych tabelach są skojarzone z bieżącym wierszem.

Na przykład w poniższej formule funkcja RELATED jest używana w celu pobrania wartości podatku z powiązanej tabeli na podstawie regionu dostawy dla zamówienia. Wartość podatku jest ustalana dzięki użyciu wartości regionu w bieżącej tabeli, wyszukaniu regionu w powiązanej tabeli i pobraniu z powiązanej tabeli stawki podatku dla regionu.

= [Freight] + RELATED('Region'[TaxRate])

Ta formuła po prostu pobiera z tabeli Region stawkę podatku dla bieżącego regionu. Nie trzeba znać ani określać klucza łączącego te tabele.

Kontekst wielu wierszy

Język DAX zawiera także funkcje umożliwiające iteracyjne wykonywanie obliczeń w tabeli. Te funkcje mogą mieć wiele bieżących wierszy i wiele kontekstów bieżącego wiersza. Z programistycznego punktu widzenia można powiedzieć, że jest możliwe utworzenie formuły wykonywanej cyklicznie w pętli wewnętrznej i zewnętrznej.

Na przykład skoroszyt zawiera tabele Products i Sales. Należy przejrzeć całą tabelę Sales, która zawiera transakcje obejmujące wiele produktów, i znaleźć największą zamówioną ilość każdego produktu w pojedynczej transakcji.

Wykonanie tego obliczenia w programie Excel wymaga wykonania serii pośrednich sumowań, które w przypadku zmiany danych trzeba będzie również zmienić. Zaawansowani użytkownicy programu Excel mogą potrafić utworzyć formuły tablicowe, które wykonają to zadanie. Z kolei w relacyjnej bazie danych można napisać zagnieżdżone instrukcje podwyboru.

Jednak za pomocą języka DAX można utworzyć pojedynczą formułę, która zwraca poprawną wartość i której wyniki są automatycznie aktualizowane w chwili dodawania danych do tabel.

=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])

Aby skorzystać ze szczegółowego przewodnika dotyczącego tej formuły, zobacz temat Funkcja EARLIER

W skrócie funkcja EARLIER przechowuje kontekst wiersza z operacji poprzedzającej bieżącą operację. Ta funkcja zawsze przechowuje w pamięci dla zestawy kontekstu: jeden zestaw kontekstu reprezentuje bieżący wiersz dla wewnętrznej pętli formuły, a drugi zestaw kontekstu reprezentuje bieżący wiersz dla zewnętrznej pętli formuły. Język DAX automatycznie przenosi wartości między tymi dwiema pętlami, dzięki czemu można tworzyć złożone agregacje.

Kontekst zapytania

Kontekst zapytania odnosi się do podzestawu danych, które są w sposób niejawny pobierane dla formuły. Po upuszczeniu miary lub innego pola wartości w komórce tabeli przestawnej aparat programu PowerPivot sprawdza nagłówki wierszy i kolumn, fragmentatory i filtry raportu w celu ustalenia kontekstu. Następnie program PowerPivot przeprowadza niezbędne obliczenia w celu wypełnienia wszystkich komórek tabeli przestawnej. Pobierany zestaw danych jest kontekstem zapytania dla każdej komórki.

Kontekst może zmieniać się w zależności od miejsca użycia formuły, więc wyniki formuły również będą zmieniać się w zależności od tego, czy formuła zostanie użyta w tabeli przestawnej, która zawiera wiele grupowań i filtrów, czy w kolumnie obliczeniowej bez filtrów i z minimalnym kontekstem.

Na przykład następująca prosta formuła służy do sumowania wartości w kolumnie Profit tabeli Sales: =SUM('Sales'[Profit]). W przypadku użycia tej formuły w kolumnie obliczeniowej znajdującej się w tabeli Sales wyniki formuły będą takie same w całej tabeli, ponieważ kontekstem zapytania dla formuły jest zawsze cały zestaw danych tabeli Sales. Wyniki obejmują zyski dla wszystkich regionów, wszystkich produktów, wszystkich lat itd.

Zazwyczaj jednak nie ma potrzeby oglądania setek razy tych samych wyników, ale warto zobaczyć zysk za dany rok, w określonym kraju lub dla określonego produktu albo kombinację tych parametrów i sumy końcowe.

W tabeli przestawnej łatwo można zmienić kontekst, dodając lub usuwając nagłówki kolumn i wierszy oraz dodając i usuwając fragmentatory. Następnie w mierze można utworzyć formułę podobną do powyższej i upuścić ją w tabeli przestawnej. Każde dodanie nagłówków kolumn lub wierszy do tabeli przestawnej powoduje zmianę kontekstu zapytania, w jakim jest obliczana miara. Operacje fragmentowania i filtrowania także mają wpływ na kontekst. Dlatego jedna formuła używana w tabeli przestawnej jest obliczania w innym kontekście zapytania dla każdej komórki.

Kontekst filtru

Kontekst filtru jest dodawany podczas określania przy użyciu argumentów formuły ograniczeń filtru dla zestawu wartości, które są dozwolone w kolumnie lub tabeli. Kontekst filtru jest stosowany po zastosowaniu innych kontekstów, takich jak kontekst wiersza lub kontekst zapytania.

Na przykład tabela przestawna oblicza wartości dla poszczególnych komórek na podstawie nagłówków wierszy i kolumn, tak jak opisano w poprzedniej sekcji dotyczącej kontekstu zapytania. Jednak w obrębie miar lub kolumn obliczeniowych dodanych do tabeli przestawnej można określić wyrażenia filtru, aby kontrolować wartości używane przez formułę. Można także selektywnie czyścić filtry w wybranych kolumnach.

Aby uzyskać więcej informacji dotyczących sposobu tworzenia filtrów w formułach, zobacz temat Funkcja FILTER.

Aby zapoznać się z przykładem sposobu czyszczenia filtrów w celu tworzenia sum końcowych, zobacz temat Funkcja ALL.

Aby zapoznać się z przykładami sposobów selektywnego czyszczenia i stosowania filtrów w formułach, zobacz temat Funkcja ALLEXCEPT.

Dlatego konieczne jest sprawdzenie definicji miar lub formuł używanych w tabeli przestawnej, ponieważ dzięki temu podczas interpretowania wyników formuł użytkownik będzie świadomy kontekstu filtru.

Ustalanie kontekstu w formułach

Podczas tworzenia formuły program PowerPivot for Excel najpierw sprawdza składnię ogólną, a następnie sprawdza podane nazwy kolumn i tabel w odniesieniu do możliwych kolumn i tabel w bieżącym kontekście. Jeśli program PowerPivot nie może odnaleźć kolumn i tabel określonych w formule, zwraca błąd.

Kontekst jest ustalany w sposób opisany w poprzednich sekcjach na podstawie tabel dostępnych w skoroszycie, relacji między tabelami i zastosowanych filtrów.

Na przykład jeśli zaimportowano dane do nowej tabeli i nie zastosowano żadnych filtrów, cały zestaw kolumn w tabeli jest częścią bieżącego kontekstu. W przypadku wielu tabel połączonych relacjami, gdy użytkownik pracuje w tabeli przestawnej, która została przefiltrowana przez dodanie nagłówków kolumn i użycie fragmentatorów, kontekst zawiera powiązane tabele oraz dowolne filtry danych.

Koncepcja kontekstu daje duże możliwości, ale może też utrudniać rozwiązywanie problemów z formułami. Zaleca się, aby rozpocząć pracę od prostych formuł i relacji, co umożliwi poznanie zasady działania kontekstu, a następnie przejść do pracy z prostymi formułami w tabelach przestawnych. W poniższej sekcji podano kilka przykładów pokazujących, jak w formułach są używane różne typy kontekstu w celu dynamicznego zwracania wyników.

Przykłady kontekstu w formułach

Funkcja RELATED rozwija kontekst bieżącego wiersza tak, aby zawierał wartości z powiązanej kolumny. Umożliwia to wykonywanie wyszukiwań. Przykład w tym temacie pokazuje interakcję między filtrowaniem a kontekstem wiersza.
Funkcja FILTER umożliwia określenie wierszy, które mają być uwzględnione w bieżącym kontekście. Przykłady w tym temacie pokazują także sposób osadzania filtrów w innych funkcjach, które wykonują agregacje.
Funkcja ALL ustawia kontekst w obrębie formuły. Można jej użyć, aby zastąpić filtry stosowane jako wynik kontekstu zapytania.
Funkcja ALLEXCEPT umożliwia usunięcie wszystkich filtrów z wyjątkiem tych określonych przez użytkownika. Oba tematy zawierają przykłady pokazujące tworzenie formuł i złożone konteksty.
Funkcje EARLIER i EARLIEST umożliwiają tworzenie pętli w tabelach, ponieważ wykonują obliczenia, a jednocześnie odwołują się do wartości z pętli wewnętrznej. Jeśli użytkownik zna koncepcję rekursji oraz ma wiedzę dotyczącą pętli wewnętrznych i zewnętrznych, z pewnością doceni możliwości oferowane przez funkcje EARLIER i EARLIEST. Jeśli użytkownik nie zna tych koncepcji, powinien uważnie wykonać kroki opisane w tym przykładzie, aby dowiedzieć się, jak konteksty wewnętrzny i zewnętrzny są używane podczas wykonywania obliczeń.

Integralność referencyjna

W tej sekcji omówiono pewne zaawansowane pojęcia dotyczące braku wartości w tabelach programu PowerPivot, które są połączone relacjami. Ta sekcja może być użyteczna dla osób, które mają skoroszyty zawierające wiele tabel i złożonych formuł, potrzebujących pomocy w zrozumieniu wyników ich działania.

Użytkownicy, którzy nie znają jeszcze koncepcji dotyczących danych relacyjnych, powinni najpierw przeczytać temat wprowadzający: Omówienie relacji.

Integralność referencyjna i relacje programu PowerPivot

Program PowerPivot nie wymaga, aby integralność referencyjna między dwiema tabelami była bezwzględnie wymuszana w celu zdefiniowania prawidłowej relacji. Zamiast tego tworzony jest pusty wiersz po stronie „jeden” każdej relacji jeden-do-wielu i jest on używany do obsługi wszystkich niepasujących wierszy z powiązanej tabeli. Takie rozwiązanie skutecznie symuluje zachowanie sprzężenia zewnętrznego języka SQL.

W tabelach przestawnych w przypadku grupowania danych według strony „jeden” relacji wszelkie niepasujące dane po stronie „wiele” są grupowane razem i uwzględniane w sumach pod nagłówkiem wiersza pustego. Pusty nagłówek jest w przybliżeniu odpowiednikiem „nieznanego elementu członkowskiego”.

Opis typu Nieznany element członkowski

Pojęcie nieznanego elementu członkowskiego może być znane użytkownikom, którzy pracują z wielowymiarowymi systemami baz danych, takimi jak usługi SQL Server Analysis Services. Jeśli ten termin nie jest znany, następujący przykład wyjaśni, co to jest nieznany element członkowski i jak wpływa on na obliczenia.

Przypuśćmy, że użytkownik tworzy obliczenie sumujące miesięczną sprzedaż w każdym sklepie, ale w kolumnie w tabeli Sprzedaż brakuje wartości określającej nazwę sklepu. Zakładając, że tabele Sklep i Sprzedaż są połączone nazwą sklepu, jakiego wyniku formuły można oczekiwać? W jaki sposób tabela przestawna powinna grupować lub wyświetlać wartości, które nie są związane z istniejącym sklepem?

Ten problem jest typowy dla magazynów danych, w których bardzo duże tabele danych o faktach muszą być logicznie powiązane z tabelami wymiarów zawierającymi informacje o sklepach, regionach i innych atrybutach używanych do klasyfikowania i obliczania faktów. Aby rozwiązać ten problem, wszelkie nowe fakty, które nie są związane z istniejącą jednostką, są tymczasowo przypisywane do nieznanego elementu członkowskiego. Dlatego niepowiązane fakty będą zgrupowane w tabeli przestawnej pod pustym nagłówkiem.

Traktowanie wartości pustychw porównaniu z pustym wierszem

Wartości puste różnią się od pustych wierszy dodawanych w celu obsługi nieznanego elementu członkowskiego. Wartość pusta to specjalna wartość służąca do reprezentowania wartości null, ciągów pustych i innych braków wartości. Aby uzyskać więcej informacji dotyczących wartości pustej oraz innych typów danych języka DAX, zobacz temat Typy danych obsługiwane w skoroszytach programu PowerPivot.