Kontekst umożliwia przeprowadzenie analizy dynamicznej, w której wyniki formuły mogą zostać zmienione w celu odzwierciedlenia bieżącego zaznaczenia wiersza lub komórki, a także wszelkich powiązanych danych. Zrozumienie kontekstu i efektywne wykorzystanie kontekstu jest bardzo ważne w przypadku 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 ocenia się kontekst formuł w kolumnach obliczeniowych i tabelach przestawnych.
Ostatnia część tego artykułu zawiera linki do szczegółowych przykładów, które ilustrują, jak wyniki formuł zmieniają się w zależności od kontekstu.
Opis kontekstu
Na formuły w Power Pivot mogą mieć wpływ filtry zastosowane w tabeli przestawnej, relacje między tabelami i filtry używane w formułach. Kontekst umożliwia wykonywanie analiz dynamicznych. Opis kontekstu jest istotny podczas tworzenia formuł i rozwiązywania problemów.
Istnieją różne typy kontekstu: kontekst wiersza, kontekst zapytania i kontekst filtru.
Kontekst wiersza można uznać za "bieżący wiersz". Jeśli utworzono kolumnę obliczeniową, kontekst wiersza składa się z wartości w każdym wierszu i wartości w kolumnach powiązanych z bieżącym wierszem. Niektóre funkcje (EARLIER i EARLIEST) pobierają wartość z bieżącego wiersza, a następnie używają tej wartości podczas wykonywania operacji na całej tabeli.
Kontekst zapytania odwołuje się do podzestawu danych, który niejawnie jest 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 wiersza lub zdefiniowanych przez wyrażenia filtru w formule.
Kontekst wiersza
Jeśli formuła zostanie utworzona w kolumnie obliczeniowej, kontekst wiersza tej formuły będzie uwzględniał wartości ze wszystkich kolumn w bieżącym wierszu. Jeśli tabela jest powiązana z inną tabelą, zawartość zawiera również wszystkie wartości z tej innej 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 dodaje dwie kolumny z tej samej tabeli. Ta formuła działa jak formuły w tabeli programu Excel, które automatycznie odwołują się do wartości z tego samego wiersza. Należy pamiętać, że tabele różnią się od zakresów: nie można odwoływać się do wartości z wiersza przed bieżącym wierszem przy użyciu notacji zakresu i nie można odwoływać się do żadnej dowolnej pojedynczej wartości w tabeli lub komórce. Zawsze musisz pracować z tabelami i kolumnami.
Kontekst wierszy jest automatycznie zgodny z relacjami 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 korzysta z funkcji RELATED w celu pobrania wartości podatkowej z powiązanej tabeli na podstawie regionu, do który wysłano zamówienie. Wartość podatku jest określana na podstawie wartości regionu w bieżącej tabeli, wyszukiwania regionu w powiązanej tabeli, a następnie uzyskiwania stawki podatkowej dla tego regionu z powiązanej tabeli.
= [Fracht] + RELATED('Region'[Stawka Podatkowa])
Ta formuła pobiera po prostu stawkę podatku dla bieżącego regionu z tabeli Region. Nie musisz znać ani określić klucza łączącego tabele.
Kontekst wielu wierszy
Ponadto język DAX zawiera funkcje iteracji obliczeń na tabeli. Te funkcje mogą mieć wiele bieżących wierszy i konteksty bieżących wierszy. W terminach programowania można tworzyć formuły powtarzające się w pętli wewnętrznej i zewnętrznej.
Załóżmy na przykład, że skoroszyt zawiera tabelę Produkty i tabelę Sprzedaż . Warto przejść przez całą tabelę sprzedaży, pełną transakcji obejmujących wiele produktów, i znaleźć największą zamówioną ilość dla każdego produktu w jednej transakcji.
W programie Excel to obliczenie wymaga serii podsumowań pośrednich, które musiałyby zostać odbudowane w przypadku zmiany danych. Jeśli jesteś użytkownikiem programu Excel, możesz mieć możliwość tworzenia formuł tablicowych, które będą wykonywać te zadania. Alternatywnie w relacyjnej bazie danych można pisać zagnieżdżone podzespy.
Jednak w języku DAX można utworzyć pojedynczą formułę, która zwraca prawidłową 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 opis tej formuły, zobacz funkcję EARLIER.
Krótko mówiąc, funkcja EARLIER przechowuje kontekst wiersza z operacji poprzedzającej bieżącą operację. Przez cały czas funkcja przechowuje dwa zestawy kontekstu w pamięci: jeden zestaw kontekstu reprezentuje bieżący wiersz wewnętrznej pętli formuły, a inny zestaw kontekstu reprezentuje bieżący wiersz zewnętrznej pętli formuły. Język DAX automatycznie pobiera wartości między obiema pętlami, dzięki czemu można tworzyć złożone agregaty.
Kontekst kwerendy
Kontekst zapytania odwołuje się do podzestawu danych, który niejawnie jest pobierany dla formuły. Po upuszczeniu miary lub innego pola wartości do komórki w tabeli przestawnej aparat Power Pivot sprawdza nagłówki wierszy i kolumn, fragmentatory i filtry raportów w celu określenia kontekstu. Następnie Power Pivot wykonuje obliczenia niezbędne do wypełnienia każdej komórki w tabeli przestawnej. Pobrany zestaw danych jest kontekstem zapytania dla każdej komórki.
Kontekst może się zmieniać w zależności od miejsca umieszczenia formuły, dlatego wyniki formuły zmieniają się również 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 w minimalnym kontekście.
Załóżmy na przykład, że utworzysz tę prostą formułę, która zsumuje wartości w kolumnie Zysk w tabeli Sprzedaż :
=SUMA('Sprzedaż'[Zysk])
Jeśli użyjesz tej formuły w kolumnie obliczeniowej w tabeli Sprzedaż , wyniki formuły będą takie same dla całej tabeli, ponieważ kontekst zapytania dla formuły jest zawsze całym zestawem danych tabeli Sprzedaż . Wyniki będą miały zysk dla wszystkich regionów, wszystkich produktów, wszystkich lat itd.
Jednak zazwyczaj nie chcesz wyświetlać tego samego wyniku setki razy, ale zamiast tego chcesz uzyskać zysk za określony rok, określony kraj lub region, określony produkt lub ich kombinację, 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 powyższa, w miarę, a następnie upuścić ją w tabeli przestawnej. Za każdym razem, gdy dodajesz nagłówki kolumn lub wierszy do tabeli przestawnej, zmieniasz kontekst zapytania, w którym jest obliczana miara. Operacje krojenia i filtrowania mają również wpływ na kontekst. Dlatego ta sama formuła, używana w tabeli przestawnej, jest obliczana w innym kontekście zapytania dla każdej komórki.
Kontekst filtru
Kontekst filtru jest dodawany po określeniu ograniczeń filtru dla zestawu wartości dozwolonych w kolumnie lub tabeli przy użyciu argumentów do formuły. Kontekst filtru jest stosowany do innych kontekstów, takich jak kontekst wierszy lub kontekst zapytania.
Na przykład tabela przestawna oblicza swoje wartości dla każdej komórki na podstawie nagłówków wierszy i kolumn zgodnie z opisem w poprzedniej sekcji kontekstu zapytania. Jednak w miarach lub kolumnach obliczeniowych dodanych do tabeli przestawnej można określić wyrażenia filtru w celu kontrolowania wartości używanych przez formułę. Filtry można również wyczyścić wybiórczo w określonych kolumnach.
Aby uzyskać więcej informacji na temat tworzenia filtrów w formułach, zobacz Funkcje filtru.
Aby uzyskać przykład sposobu czyszczenia filtrów w celu utworzenia sum końcowych, zobacz funkcję WSZYSTKIE.
Aby uzyskać przykłady selektywnego czyszczenia i stosowania filtrów w formułach, zobacz funkcję ALLEXCEPT.
Dlatego należy przejrzeć definicję miar lub formuł używanych w tabeli przestawnej, aby mieć świadomość kontekstu filtru podczas interpretowania wyników formuł.
Określanie kontekstu w formułach
Podczas tworzenia formuły Power Pivot dla programu Excel najpierw sprawdza ogólną składnię, a następnie sprawdza nazwy podanych kolumn i tabel pod kątem możliwych kolumn i tabel w bieżącym kontekście. Jeśli Power Pivot nie może znaleźć kolumn i tabel określonych przez formułę, zostanie wyświetlony komunikat o błędzie.
Kontekst jest ustalany zgodnie z opisem w poprzednich sekcjach przy użyciu dostępnych tabel w skoroszycie, wszelkich relacji między tabelami i wszelkich zastosowanych filtrów.
Jeśli na przykład dane zostały właśnie zaimportowane do nowej tabeli i nie zastosowano filtrów, cały zestaw kolumn w tabeli jest częścią bieżącego kontekstu. Jeśli istnieje wiele tabel połączonych relacjami i pracujesz w tabeli przestawnej, 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 zaawansowana koncepcja, która może również utrudniać rozwiązywanie problemów z formułami. Zalecamy rozpoczęcie od prostych formuł i relacji w celu sprawdzenia, jak działa kontekst, a następnie rozpoczęcie eksperymentowania z prostymi formułami w tabelach przestawnych. W poniższej sekcji przedstawiono również kilka przykładów użycia przez formuły różnych typów kontekstu do 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 odnośników. W przykładzie w tym temacie przedstawiono interakcje filtrowania i kontekstu wierszy.
-
Funkcja FILTRUJ umożliwia określenie wierszy do uwzględnienia w bieżącym kontekście. W przykładach w tym temacie pokazano również, jak osadzić filtry w ramach innych funkcji, które wykonują agregujące.
-
Funkcja WSZYSTKIE ustawia kontekst w formule. Za jego pomocą można zastąpić filtry zastosowane w kontekście zapytania.
-
Funkcja ALLEXCEPT umożliwia usunięcie wszystkich filtrów z wyjątkiem filtrów określonych przez użytkownika. Oba te tematy zawierają przykłady, które przeprowadzą Cię przez tworzenie formuł i zrozumienie złożonych kontekstów.
-
Funkcje EARLIER i EARLIEST umożliwiają przechodzenie między tabelami w pętli, wykonując obliczenia, odwołując się do wartości z pętli wewnętrznej. Jeśli znasz koncepcję rekursji oraz wewnętrzne i zewnętrzne pętle, docenisz moc, którą zapewniają funkcje EARLIER i EARLIEST. Jeśli jesteś nowym użytkownikiem tych pojęć, uważnie wykonaj czynności opisane w przykładzie, aby zobaczyć, jak w obliczeniach są używane konteksty wewnętrzne i zewnętrzne.
Więzy integralności
W tej sekcji omówiono niektóre zaawansowane pojęcia związane z brakującymi wartościami w Power Pivot tabelach 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 jesteś nowym użytkownikiem pojęć związanych z danymi relacyjnymi, zalecamy zapoznanie się z tematem wprowadzającym Omówienie relacji.
Więzy integralności i relacje Power Pivot
Power Pivot nie wymaga wymuszania więzów integralności między dwiema tabelami w celu zdefiniowania prawidłowej relacji. Zamiast tego jest tworzony pusty wiersz na końcu "jeden" każdej relacji jeden-do-wielu i służy do obsługi wszystkich niezgodnych wierszy z powiązanej tabeli. Skutecznie działa jako sprzężenie zewnętrzne SQL.
Jeśli w tabelach przestawnych dane zostaną pogrupowane według jednej strony relacji, wszystkie niepasowane dane po stronie "wiele" relacji zostaną zgrupowane i zostaną uwzględnione w sumach z nagłówkiem pustego wiersza. Pusty nagłówek jest mniej więcej równoważny "nieznanemu elementowi".
Opis nieznanego członka
Pojęcie nieznanego elementu członkowskiego jest prawdopodobnie znane, jeśli pracowałeś z wielowymiarowymi systemami baz danych, takimi jak SQL Server Analysis Services. Jeśli dany termin jest dla Ciebie nowym elementem, w poniższym przykładzie wyjaśniono, co to jest nieznany element członkowski i jaki ma on wpływ na obliczenia.
Załóżmy, że tworzysz obliczenie sumujące miesięczną sprzedaż dla każdego sklepu, ale w kolumnie w tabeli Sprzedaż brakuje wartości dla nazwy sklepu. Biorąc pod uwagę, że tabele Sklepu i Sprzedaży są połączone nazwą sklepu, czego można oczekiwać w formule? W jaki sposób tabela przestawna powinna grupować lub wyświetlać dane sprzedaży, które nie są powiązane z istniejącym sklepem?
Ten problem jest typowy w magazynach danych, gdzie duże tabele danych faktów muszą być logicznie powiązane z tabelami wymiarów zawierającymi informacje o sklepach, regionach i innych atrybutach używanych do kategoryzowania i obliczania faktów. Aby rozwiązać ten problem, wszelkie nowe fakty niezwiązane z istniejącą jednostką są tymczasowo przypisywane do nieznanego członka. Dlatego niepowiązane fakty będą wyświetlane pogrupowane w tabeli przestawnej pod pustym nagłówkiem.
Traktowanie wartości pustych w porównaniu z pustym wierszem
Wartości puste różnią się od pustych wierszy dodanych w celu uwzględnienia nieznanego elementu członkowskiego. Pusta wartość 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 na temat wartości pustej, a także innych typów danych języka DAX, zobacz Typy danych w modelach danych.
