Kontekst umożliwia wykonywanie dynamicznej analizy, w której wyniki formuły mogą się zmieniać w celu odzwierciedlenia bieżącego zaznaczenia wiersza lub komórki, a także wszystkich powiązanych danych. Zrozumienie kontekstu i jego efektywne wykorzystanie są bardzo ważne do tworzenia formuł o wysokiej wydajności, analiz dynamicznych i rozwiązywania problemów w formułach.
W tej sekcji zdefiniowano różne typy kontekstu: kontekst wiersza, kontekst zapytania i kontekst filtru. Wyjaśniono w nim, jak jest oceniany kontekst dla formuł w kolumnach obliczeniowych i w tabelach przestawnych.
W ostatniej części tego artykułu znajdują się linki do szczegółowych przykładów ilustrujących zmiany wyników formuł w zależności od kontekstu.
Zrozumienie kontekstu
Na formuły w dodatku Power Pivot mogą wpływać filtry zastosowane w tabeli przestawnej, relacje między tabelami oraz filtry używane w formułach. Kontekst jest tym, co umożliwia przeprowadzenie dynamicznej analizy. Zrozumienie kontekstu jest ważne dla tworzenia formuł i rozwiązywania związanych z nimi problemów.
Istnieją różne typy kontekstu: kontekst wiersza, kontekst zapytania i kontekst filtru.
Kontekst wiersza można traktować jako "bieżący wiersz". Jeśli utworzono kolumnę obliczeniową, kontekst wiersza składa się z wartości w poszczególnych wierszach oraz wartości w kolumnach powiązanych z bieżącym wierszem. Istnieją również 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 odwołuje się do podzbioru 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 na podstawie ograniczeń filtru zastosowanych do danego wiersza lub zdefiniowanych przez wyrażenia filtru w formule.
Kontekst wiersza
Jeśli utworzysz formułę w kolumnie obliczeniowej, kontekst wiersza tej formuły będzie zawierał wartości ze wszystkich kolumn w bieżącym wierszu. Jeśli tabela jest powiązana z inną tabelą, zawartość obejmuje również wszystkie wartości z tej tabeli, które są powiązane z bieżącym wierszem.
Załóżmy na przykład, że została utworzona kolumna obliczeniowa =[Fracht] + [Podatek], która sumuje dwie kolumny z tej samej 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 różnią się od zakresów: nie można odwoływać się do wartości z wiersza poprzedzającego bieżący wiersz przy użyciu notacji zakresu i nie można odwoływać się do dowolnej pojedynczej wartości w tabeli lub komórce. Zawsze musisz pracować z tabelami i kolumnami.
Kontekst wiersza automatycznie śledzi relacje między tabelami w celu określenia, które wiersze w powiązanych tabelach są skojarzone z bieżącym wierszem.
Na przykład poniższa formuła używa funkcji RELATED w celu pobrania wartości podatku z powiązanej tabeli na podstawie regionu, do którego wysłano zamówienie. Wartość podatku jest ustalana przy użyciu wartości regionu w bieżącej tabeli, wyszukiwania regionu w powiązanej tabeli, a następnie pobierania stawki podatkowej dla tego regionu z powiązanej tabeli.
= [Fracht] + RELATED('Region'[StawkaPodatkowa])
Ta formuła po prostu pobiera stawkę podatku dla bieżącego regionu z tabeli Region. Nie musisz znać ani określać klucza łączącego tabele.
Kontekst wielu wierszy
Ponadto język DAX zawiera funkcje, które iterują obliczenia w tabeli. Te funkcje mogą mieć wiele bieżących wierszy i kontekstów bieżących wierszy. W terminologii programistycznej można tworzyć formuły, które powtarzają się w pętli wewnętrznej i zewnętrznej.
Na przykład załóżmy, że skoroszyt zawiera tabelę Produkty i tabelę Sprzedaż . Warto przejrzeć całą tabelę sprzedaży, która jest pełna transakcji dotyczących wielu produktów, i znaleźć największą zamówioną ilość dla każdego produktu w ramach jednej transakcji.
W programie Excel to obliczenie wymaga serii podsumowań pośrednich, które trzeba będzie odbudować, jeśli dane ulegną zmianie. Zaawansowany użytkownik programu Excel może mieć możliwość tworzenia formuł tablicowych, które spełnią te zadania. W relacyjnej bazie danych można również zapisać zagnieżdżone podwybory.
Jednak w języku DAX można utworzyć pojedynczą formułę, która zwraca poprawną wartość, a wyniki są automatycznie aktualizowane za każdym razem, gdy dodajesz dane do tabel.
=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
Aby uzyskać szczegółowy przewodnik po tej formule, zobacz WCZEŚNIEJ, funkcja.
Krótko mówiąc, funkcja EARLIER przechowuje kontekst wiersza z operacji, która poprzedzała bieżącą operację. Przez cały czas funkcja przechowuje w pamięci dwa zestawy kontekstu: jeden zestaw kontekstu reprezentuje bieżący wiersz dla pętli wewnętrznej formuły, a drugi zestaw kontekstu reprezentuje bieżący wiersz dla pętli zewnętrznej formuły. Język DAX automatycznie przekazuje wartości między dwiema pętlami, dzięki czemu można tworzyć złożone wartości zagregowane.
Kontekst zapytania
Kontekst zapytania odwołuje się do podzbioru danych, które są niejawnie pobierane dla formuły. Po upuszczeniu miary lub innego pola wartości do komórki tabeli przestawnej aparat dodatku Power Pivot sprawdza nagłówki wierszy i kolumn, fragmentatory i filtry raportów w celu określenia kontekstu. Następnie dodatek Power Pivot wykonuje niezbędne obliczenia, aby wypełnić każdą komórkę w tabeli przestawnej. Zestaw pobieranych danych stanowi kontekst zapytania dla każdej komórki.
Ponieważ kontekst może się zmieniać w zależności od umieszczenia formuły, wyniki formuły również zmieniają się w zależności od tego, czy formuła jest używana w tabeli przestawnej z wieloma grupowaniami i filtrami, czy w kolumnie obliczeniowej bez filtrów i z minimalnym kontekstem.
Załóżmy na przykład, że utworzono prostą formułę sumującą wartości w kolumnie Zysk tabeli Sprzedaż :
=SUMA('Sprzedaż'[Zysk])
Jeśli użyjesz tej formuły w kolumnie obliczeniowej w tabeli Sales , wyniki formuły będą takie same dla całej tabeli, ponieważ kontekst zapytania formuły jest zawsze całym zestawem danych tabeli Sales . Twoje wyniki będą miały zysk dla wszystkich regionów, wszystkich produktów, wszystkich lat i tak dalej.
Zazwyczaj jednak nie chcemy widzieć setki razy tego samego wyniku, lecz chcemy uzyskać zysk dla określonego roku, określonego kraju lub regionu, określonego produktu albo ich kombinacji, a następnie uzyskać sumę końcową.
W tabeli przestawnej można łatwo zmienić kontekst, dodając lub usuwając nagłówki kolumn i wierszy oraz dodając lub usuwając fragmentatory. Możesz utworzyć formułę, taką jak pokazana powyżej, w miarze, a następnie umieścić ją w tabeli przestawnej. Ilekroć dodajesz nagłówki kolumn lub wierszy do tabeli przestawnej, zmienia się kontekst zapytania, w którym mierzona miara jest obliczana. Operacje fragmentowania i filtrowania również wpływają na kontekst. Dlatego ta sama formuła użyta w tabeli przestawnej jest obliczana w innym kontekście zapytania dla każdej komórki.
Kontekst filtru
Kontekst filtru jest dodawany, gdy określasz ograniczenia filtru dla zestawu wartości dozwolonych w kolumnie lub tabeli przy użyciu argumentów formuły. Kontekst filtru ma zastosowanie do innych kontekstów, takich jak kontekst wiersza lub kontekst zapytania.
Na przykład tabela przestawna oblicza wartości dla każdej komórki na podstawie nagłówków wierszy i kolumn, zgodnie z opisem w poprzedniej sekcji dotyczącej kontekstu zapytania. Jednakże w ramach miar lub kolumn obliczeniowych dodawanych do tabeli przestawnej można określić wyrażenia filtru w celu kontrolowania wartości używanych przez formułę. Możesz również selektywnie wyczyścić filtry w określonych kolumnach.
Aby uzyskać więcej informacji na temat tworzenia filtrów w formułach, zobacz Funkcje filtrów.
Aby zapoznać się z przykładem sposobu czyszczenia filtrów w celu obliczenia sum końcowych, zobacz opis funkcji ALL.
Aby zapoznać się z przykładami selektywnego czyszczenia i stosowania filtrów w formułach, zobacz opis funkcji ALLEXCEPT.
Dlatego należy zapoznać się z definicjami miar lub formuł używanych w tabeli przestawnej, aby podczas interpretowania wyników formuł znać kontekst filtru.
Określanie kontekstu w formułach
Podczas tworzenia formuły dodatek Power Pivot dla programu Excel najpierw sprawdza ogólną składnię, a następnie porównuje podane nazwy kolumn i tabel z kolumnami i tabelami, które są możliwe w bieżącym kontekście. Jeśli dodatek Power Pivot nie będzie mógł znaleźć kolumn i tabel określonych w formule, zostanie wyświetlony komunikat o błędzie.
Kontekst jest określany w sposób opisany w poprzednich sekcjach na podstawie tabel dostępnych w skoroszycie, relacji między tabelami oraz zastosowanych filtrów.
Jeśli na przykład właśnie zaimportowano część danych do nowej tabeli i nie zastosowano żadnych filtrów, cały zestaw kolumn w tabeli jest częścią bieżącego kontekstu. Jeśli istnieje wiele tabel połączonych relacjami i pracujesz z tabelą przestawną, która została przefiltrowana przez dodanie nagłówków kolumn i użycie fragmentatorów, kontekst obejmuje tabele pokrewne i wszelkie filtry danych.
Kontekst to zaawansowane pojęcie, które może również utrudniać rozwiązywanie problemów z formułami. Zalecamy rozpoczęcie od prostych formuł i relacji, aby zobaczyć, jak działa kontekst, a następnie eksperymentowanie z prostymi formułami w tabelach przestawnych. Poniższa sekcja zawiera również kilka przykładów zastosowania w formułach różnych typów kontekstu w celu dynamicznego zwracania wyników.
Przykłady kontekstu w formułach
- Funkcja RELATED rozszerza kontekst bieżącego wiersza, aby uwzględnić wartości w powiązanej kolumnie. Umożliwia to wykonywanie wyszukiwań. Przykład w tym temacie ilustruje interakcję filtrowania i kontekstu wiersza.
- Funkcja FILTRUJ umożliwia określenie wierszy do uwzględnienia w bieżącym kontekście. W przykładach przedstawionych w tym temacie pokazano również sposób osadzania filtrów w innych funkcjach agregujących.
- Funkcja ALL ustawia kontekst w formule. Za jej pomocą można zastępować filtry zastosowane w wyniku kontekstu zapytania.
- Funkcja ALLEXCEPT umożliwia usunięcie wszystkich filtrów oprócz jednego określonego przez użytkownika. Oba tematy zawierają przykłady pozwalające zapoznać się z tworzeniem formuł i zrozumieniem złożonych kontekstów.
- Funkcje EARLIER i EARLY umożliwiają przechodzenie w pętli między tabelami za pomocą wykonywania obliczeń z odwołaniami do wartości z wewnętrznej pętli. Użytkownicy zaznajomieni z pojęciem rekurencji oraz pętlami wewnętrznymi i zewnętrznymi z pewnością docenią możliwości, jakie zapewniają funkcje EARLIER i EARLY. Jeśli nie znasz tych pojęć, musisz uważnie wykonać kroki podane w przykładzie, aby zobaczyć, w jaki sposób kontekst wewnętrzny i zewnętrzny są używane w obliczeniach.
Więzy integralności
W tej sekcji omówiono niektóre zaawansowane pojęcia związane z brakującymi wartościami w tabelach dodatku Power Pivot połączonych relacjami. Ta sekcja może być przydatna, jeśli masz skoroszyty z wieloma tabelami i złożonymi formułami i potrzebujesz pomocy w zrozumieniu wyników.
Jeśli nie znasz pojęć związanych z danymi relacyjnymi, warto najpierw zapoznać się z tematem wprowadzającym: Omówienie relacji.
Więzy integralności i relacje dodatku Power Pivot
W dodatku Power Pivot nie jest wymagane wymuszanie więzów integralności między dwiema tabelami w celu zdefiniowania prawidłowej relacji. Zamiast tego na końcu "jeden" każdej relacji jeden-do-wielu jest tworzony pusty wiersz, który służy do obsługi wszystkich niepasujących wierszy z powiązanej tabeli. W praktyce zachowuje się jak zewnętrzne sprzężenie SQL.
Jeśli w tabelach przestawnych dane są grupowane według strony "jeden" relacji, wszystkie niepasujące dane po stronie "wielu" relacji zostaną zgrupowane razem i uwzględnione w sumach z nagłówkiem pustego wiersza. Pusty nagłówek odpowiada w przybliżeniu "nieznanemu członkowi".
Opis nieznanego członka
Pojęcie nieznanego elementu jest prawdopodobnie dobrze znane, jeśli pracujesz z systemami wielowymiarowych baz danych, takimi jak usługi SQL Server Analysis Services. Jeśli ten termin jest dla Ciebie nowy, w poniższym przykładzie wyjaśniono, czym jest nieznany element i jak wpływa na obliczenia.
Załóżmy, że tworzysz obliczenie sumujące miesięczną sprzedaż każdego sklepu, ale w kolumnie tabeli Sprzedaż brakuje wartości dla nazwy sklepu. Biorąc pod uwagę, że tabele Sklep i Sprzedaż są połączone nazwą sklepu, czego można oczekiwać w formule? Jak tabela przestawna powinna grupować lub wyświetlać dane dotyczące sprzedaży, które nie są związane z istniejącym sklepem?
Ten problem występuje często w hurtowniach danych, gdzie duże tabele danych faktów muszą być logicznie powiązane z tabelami wymiarów zawierającymi informacje o magazynach, regionach i innych atrybutach używanych do kategoryzowania i obliczania faktów. Aby rozwiązać ten problem, wszystkie nowe fakty, które nie są powiązane z istniejącą jednostką, są tymczasowo przypisywane do nieznanego elementu. Dlatego niepowiązane fakty będą wyświetlane jako zgrupowane w tabeli przestawnej pod pustym nagłówkiem.
Traktowanie wartości pustych a pustego wiersza
Wartości puste różnią się od pustych wierszy dodanych w celu umieszczenia nieznanego elementu członkowskiego. Wartość pusta to specjalna wartość używana do reprezentowania wartości null, pustych ciągów i innych brakujących wartości. Aby uzyskać więcej informacji o wartości pustej oraz innych typach danych języka DAX, zobacz Typy danych w modelach danych.