Język DAX (Data Analysis Expressions) w dodatku Power Pivot

Język DAX (Data Analysis Expressions) na początku brzmi nieco przytłaczająco, ale nie daj się zwieść nazwie. Podstawy języka DAX są naprawdę łatwe do zrozumienia. Po pierwsze — język DAX NIE jest językiem programowania. Język DAX jest językiem formuł. Język DAX umożliwia definiowanie niestandardowych obliczeń dla kolumn obliczeniowych i miar (nazywanych również polami obliczeniowymi). Język DAX zawiera niektóre funkcje używane w formułach programu Excel oraz dodatkowe funkcje przeznaczone do pracy z danymi relacyjnymi i wykonywania agregacji dynamicznych.

Opis formuł języka DAX

Formuły języka DAX są bardzo podobne do formuł programu Excel. Aby utworzyć funkcję, wpisz znak równości, a następnie nazwę funkcji lub wyrażenie i wszelkie wymagane wartości i argumenty. Podobnie jak program Excel, język DAX udostępnia różne funkcje, których można używać do pracy z ciągami, wykonywania obliczeń z użyciem dat i godzin oraz tworzenia wartości warunkowych.

Formuły języka DAX różnią się jednak pod następującymi istotnymi względami:

• Jeśli chcesz dostosować obliczenia wiersz po wierszu, język DAX zawiera funkcje umożliwiające używanie bieżącej lub powiązanej wartości wiersza do wykonywania obliczeń różniących się w zależności od kontekstu.
• Język DAX zawiera typ funkcji, która zwraca tabelę jako wynik, a nie pojedynczą wartość. Tych funkcji można używać do wprowadzania danych dla innych funkcji.
• Funkcje analizy czasowejw języku DAX umożliwiają wykonywanie obliczeń na podstawie zakresów dat i porównywanie wyników dla równoległych okresów.

Gdzie używać formuł języka DAX

Dodatek Power Pivot umożliwia tworzenie formuł w kolumnach lub polach obliczeniowych.

Kolumny obliczeniowe

Kolumna obliczeniowa to kolumna dodawana do istniejącej tabeli dodatku Power Pivot. Zamiast wklejać lub importować wartości w kolumnie, należy utworzyć formułę języka DAX definiującą wartości w kolumnie. Jeśli dołączysz tabelę dodatku Power Pivot do tabeli przestawnej (lub wykresu przestawnego), kolumna obliczeniowa będzie mogła być używana tak samo jak każda inna kolumna danych.

Formuły w kolumnach obliczeniowych są bardzo podobne do formuł tworzonych w programie Excel. Jednak w przeciwieństwie do programu Excel nie można utworzyć innej formuły dla różnych wierszy w tabeli. Zamiast tego formuła języka DAX jest automatycznie stosowana do całej kolumny.

Jeśli kolumna zawiera formułę, wartość jest obliczana dla każdego wiersza. Wyniki są obliczane dla kolumny zaraz po utworzeniu formuły. Wartości kolumn są ponownie obliczane tylko w przypadku odświeżenia danych źródłowych lub użycia ręcznego obliczenia ponownego.

Kolumny obliczeniowe można tworzyć na podstawie miar i innych kolumn obliczeniowych. Należy jednak unikać używania tej samej nazwy dla kolumny obliczeniowej i miary, ponieważ może to spowodować mylące wyniki. Podczas odwoływania się do kolumny najlepiej używać w pełni kwalifikowanego odwołania do kolumny, aby uniknąć przypadkowego wywołania miary.

Aby uzyskać bardziej szczegółowe informacje, zobacz temat Kolumny obliczeniowe w dodatku Power Pivot.

Miary

Miara to formuła utworzona specjalnie na potrzeby użycia w tabeli przestawnej (lub na wykresie przestawnym), w której są używane dane dodatku Power Pivot. Miary mogą być oparte na standardowych funkcjach agregacji, takich jak COUNT lub SUM, lub można zdefiniować własną formułę przy użyciu języka DAX. Miara jest używana w obszarze Wartości tabeli przestawnej. Jeśli chcesz umieścić obliczone wyniki w innym obszarze tabeli przestawnej, użyj zamiast niego kolumny obliczeniowej.

Gdy definiujesz formułę dla miary jawnej, nic się nie dzieje, dopóki nie dodasz miary do tabeli przestawnej. Podczas dodawania miary formuła jest obliczana dla każdej komórki w obszarze Wartości tabeli przestawnej. Ponieważ wynik jest tworzony dla każdej kombinacji nagłówków wierszy i kolumn, wynik miary może być inny w każdej komórce.

Definicja utworzonej miary jest zapisywana wraz z tabelą danych źródłowych. Jest on wyświetlany na liście pól tabeli przestawnej i dostępny dla wszystkich użytkowników skoroszytu.

Aby uzyskać bardziej szczegółowe informacje, zobacz Miary w dodatku Power Pivot.

Tworzenie formuł za pomocą paska formuły

Dodatek Power Pivot, podobnie jak program Excel, zawiera pasek formuły ułatwiający tworzenie i edytowanie formuł oraz funkcję Autouzupełnianie, która minimalizuje ryzyko występowania błędów pisowni i składni.

Aby wprowadzić nazwę tabeli Zacznij wpisywać nazwę tabeli. Funkcja autouzupełniania formuł udostępnia listę rozwijaną zawierającą prawidłowe nazwy rozpoczynające się tymi literami.

Aby wprowadzić nazwę kolumny Wpisz nawias kwadratowy, a następnie wybierz kolumnę z listy kolumn bieżącej tabeli. W przypadku kolumny z innej tabeli zacznij wpisywać pierwsze litery nazwy tabeli, a następnie wybierz kolumnę z listy rozwijanej Autouzupełnianie.

Aby uzyskać więcej szczegółowych informacji oraz przewodnik po konstruowaniu formuł, zobacz Tworzenie formuł do obliczeń w dodatku Power Pivot.

Porady dotyczące korzystania z funkcji Autouzupełnianie

Funkcji Autouzupełnianie formuł można używać wewnątrz istniejącej formuły z funkcjami zagnieżdżonymi. Tekst znajdujący się bezpośrednio przed punktem wstawiania jest używany do wyświetlania wartości na liście rozwijanej, a cały tekst za punktem wstawiania pozostaje bez zmian.

Zdefiniowane nazwy stałych utworzone dla stałych nie są wyświetlane na liście rozwijanej autouzupełniania, ale można je wpisywać.

W dodatku Power Pivot nawiasy zamykające funkcji nie są dodawane ani automatycznie dopasowywane. Należy się upewnić, że każda funkcja jest poprawna składniowo — w przeciwnym razie nie będzie można zapisać lub użyć formuły. 

Używanie wielu funkcji w formule

Funkcje można zagnieżdżać, co oznacza, że wyniki jednej funkcji są używane jako argumenty innej funkcji. W kolumnach obliczeniowych można zagnieździć maksymalnie 64 poziomy funkcji. Jednak zagnieżdżanie może utrudnić tworzenie formuł lub rozwiązywanie problemów.

Wiele funkcji języka DAX jest przeznaczonych do używania wyłącznie jako funkcji zagnieżdżonych. Funkcje te zwracają tabelę, która w rezultacie nie może być bezpośrednio zapisana; Powinien być podawany jako dane wejściowe do funkcji tabeli. Na przykład funkcje SUMX, ŚREDNIAX i MINX wymagają tabeli jako pierwszego argumentu.

Porównanie funkcji języka DAX i funkcji programu Excel

Biblioteka funkcji języka DAX jest oparta na bibliotece funkcji programu Excel, ale między bibliotekami występuje wiele różnic. W tej sekcji podsumowano różnice i podobieństwa między funkcjami programu Excel i funkcjami języka DAX.

• Wiele funkcji języka DAX ma takie same nazwy i takie samo ogólne działanie, jak funkcje programu Excel, ale zostały zmodyfikowane, aby przyjmować inne typy danych wejściowych, a w niektórych przypadkach mogą zwracać inny typ danych. Zasadniczo nie można używać funkcji języka DAX w formułach programu Excel ani używać formuł programu Excel w dodatku Power Pivot bez pewnych modyfikacji.
• Funkcje języka DAX nigdy nie przyjmują odwołania do komórki lub zakresu jako odwołania, lecz przyjmują jako odwołanie kolumnę lub tabelę.
• Funkcje daty i godziny języka DAX w języku DAX zwracają typ danych Data/godzina. Z kolei funkcje daty i godziny w programie Excel zwracają liczbę całkowitą, która reprezentuje datę w postaci liczby kolejnej.
• Wiele nowych funkcji języka DAX zwraca tabelę wartości lub wykonuje obliczenia na podstawie tabeli wartości jako danych wejściowych. Z kolei w programie Excel nie ma funkcji zwracających tabelę, ale niektóre funkcje mogą pracować z tablicami. Nowa funkcja dodatku Power Pivot to możliwość łatwego odwoływania się do całych tabel i kolumn.
• Język DAX udostępnia nowe funkcje wyszukiwania, które są podobne do tablicowych i wektorowych funkcji wyszukiwania w programie Excel. Funkcje języka DAX wymagają jednak ustanowienia relacji między tabelami.
• Oczekuje się, że dane w kolumnie będą zawsze tego samego typu. Jeśli dane nie są tego samego typu, język DAX zmienia całą kolumnę na typ danych, który najlepiej pomieści wszystkie wartości.

Typy danych języka DAX

Do modelu danych dodatku Power Pivot można importować dane z wielu różnych źródeł danych, które mogą obsługiwać różne typy danych. Gdy importujesz lub ładujesz dane, a następnie używasz ich w obliczeniach lub w tabelach przestawnych, dane są konwertowane na jeden z typów danych dodatku Power Pivot. Aby uzyskać listę typów danych, zobacz Typy danych w modelach danych.

Typ danych Table to nowy typ danych w języku DAX, który jest używany jako dane wejściowe i wyjściowe w wielu nowych funkcjach. Na przykład funkcja FILTRUJ przyjmuje tabelę jako dane wejściowe i wyprowadza inną tabelę zawierającą tylko wiersze spełniające warunki filtrowania. Łącząc funkcje tabelaryczne z funkcjami agregującymi, można wykonywać złożone obliczenia na dynamicznie zdefiniowanych zestawach danych. Aby uzyskać więcej informacji, zobacz temat Agregacje w dodatku Power Pivot.

Formuły i model relacyjny

Okno programu Power Pivot to obszar, w którym można pracować z wieloma tabelami danych i łączyć je w model relacyjny. W tym modelu danych tabele są połączone relacjami, które pozwalają na tworzenie korelacji z kolumnami w innych tabelach i tworzenie bardziej interesujących obliczeń. Można na przykład utworzyć formuły sumujące wartości powiązanej tabeli, a następnie zapisać te wartości w jednej komórce. Możesz też sterować wierszami z powiązanej tabeli, stosując filtry do tabel i kolumn. Aby uzyskać więcej informacji, zobacz Relacje między tabelami w modelu danych.

Ponieważ tabele można łączyć za pomocą relacji, tabele przestawne mogą również zawierać dane z wielu kolumn pochodzących z różnych tabel.

Ponieważ jednak formuły mogą działać z całymi tabelami i kolumnami, obliczenia trzeba projektować inaczej niż w programie Excel.

• Na ogół formuła języka DAX w kolumnie jest zawsze stosowana do całego zestawu wartości w kolumnie (nigdy nie jest stosowana tylko do kilku wierszy lub komórek).
• Tabele w dodatku Power Pivot muszą zawsze zawierać taką samą liczbę kolumn w każdym wierszu i wszystkie wiersze w kolumnie muszą zawierać ten sam typ danych.
• Gdy tabele są połączone relacją, należy upewnić się, że dwie kolumny używane jako klucze mają w większości zgodne wartości. Ponieważ dodatek Power Pivot nie wymusza więzów integralności, możliwe jest, że kolumna klucza zawiera niezgodne wartości, a mimo to zostanie utworzona relacja. Obecność wartości pustych lub niezgodnych może jednak wpływać na wyniki formuł i wygląd tabel przestawnych. Aby uzyskać więcej informacji, zobacz temat Odnośniki w formułach dodatku Power Pivot.
• Łączenie tabel za pomocą relacji polega na zwiększeniu zakresu lub kontekstu, w którym formuły są obliczane. Na przykład na formuły w tabeli przestawnej mogą wpływać wszelkie filtry lub nagłówki kolumn i wierszy w tabeli przestawnej. Można pisać formuły, które zmieniają kontekst, ale kontekst może również powodować zmiany wyników w sposób nieprzewidziany. Aby uzyskać więcej informacji, zobacz Kontekst w formułach języka DAX.

Aktualizowanie wyników formuł

Odświeżanie i ponowne obliczanie danych to dwie odrębne, ale powiązane ze sobą operacje, które należy poznać podczas projektowania modelu danych zawierającego złożone formuły, duże ilości danych lub dane uzyskane z zewnętrznych źródeł danych.

Odświeżanie danych to proces aktualizowania danych w skoroszycie przy użyciu nowych danych z zewnętrznego źródła danych. Dane można odświeżać ręcznie w określonych odstępach czasu. Jeśli skoroszyt został opublikowany w witrynie programu SharePoint, można również zaplanować automatyczne odświeżanie z zewnętrznych źródeł.

Ponowne obliczanie to proces aktualizowania wyników formuł w celu odzwierciedlenia wszelkich zmian w samych formułach i odzwierciedlenia tych zmian w danych podstawowych. Ponowne obliczanie może mieć następujący wpływ na wydajność:

• W przypadku kolumny obliczeniowej wynik formuły powinien być zawsze obliczany ponownie dla całej kolumny po każdej zmianie formuły.
• W przypadku miary wyniki formuły nie są obliczane, dopóki miara nie zostanie umieszczona w kontekście tabeli przestawnej lub wykresu przestawnego. Formuła zostanie również obliczona ponownie po zmianie nagłówka wiersza lub kolumny, która ma wpływ na filtry danych, lub w przypadku ręcznego odświeżenia tabeli przestawnej.

Rozwiązywanie problemów z formułami

Błędy podczas pisania formuł

Jeśli podczas definiowania formuły wystąpi błąd, może ona zawierać błąd składniowy, semantyczny lub błąd obliczeń.

Błędy składniowe są najłatwiejsze do usunięcia. Zazwyczaj dotyczą one brakującego nawiasu okrągłego lub przecinka. Aby uzyskać pomoc dotyczącą składni poszczególnych funkcji, zobacz Dokumentacja funkcji języka DAX.

Drugi typ błędu występuje, gdy składnia jest poprawna, ale wartość lub kolumna, do której prowadzi odwołanie, nie ma sensu w kontekście formuły. Takie błędy semantyczne i błędy w obliczeniach mogą być powodowane przez dowolny z następujących problemów:

• Formuła odwołuje się do nieistniejącej kolumny, tabeli lub funkcji.
• Formuła wydaje się być poprawna, ale kiedy aparat danych pobiera dane, znajduje niezgodność typów i zgłasza błąd.
• Formuła przekazuje do funkcji nieprawidłową liczbę lub nieprawidłowy typ parametru.
• Formuła odwołuje się do innej kolumny, w której wystąpił błąd, przez co jej wartości są nieprawidłowe.
• Formuła odwołuje się do kolumny, która nie została przetworzona, co oznacza, że zawiera metadane, ale nie zawiera rzeczywistych danych, których można użyć do obliczeń.

W pierwszych czterech przypadkach język DAX oflagowuje całą kolumnę zawierającą nieprawidłową formułę. W ostatnim przypadku język DAX wyszarza kolumnę, aby wskazać, że kolumna jest w stanie nieprzetworzonym.

Niepoprawne lub nietypowe wyniki podczas klasyfikowania lub porządkowania wartości kolumn

Podczas klasyfikowania lub porządkowania kolumny zawierającej wartość NaN (Not a Number) można uzyskać błędne lub nieoczekiwane wyniki. Na przykład gdy obliczenie dzieli 0 przez 0, zwracany jest wynik NaN.

Dzieje się tak, ponieważ aparat formuł porządkuje i klasyfikuje, porównując wartości liczbowe. NaN nie może być jednak porównywane z innymi liczbami w kolumnie.

Aby uzyskać poprawne wyniki, można użyć instrukcji warunkowych z funkcją JEŻELI, aby sprawdzić wartości NaN i zwrócić liczbową wartość 0.

Zgodność z modelami tabelarycznymi usług Analysis Services i trybem DirectQuery

Formuły języka DAX tworzone w dodatku Power Pivot są na ogół w pełni zgodne z modelami tabelarycznymi usług Analysis Services. Jeśli jednak model dodatku Power Pivot zostanie przeniesiony do wystąpienia usług Analysis Services, a następnie wdrożony w trybie DirectQuery, istnieją pewne ograniczenia.

• Niektóre formuły języka DAX mogą zwracać inne wyniki po wdrożeniu modelu w trybie DirectQuery.
• Niektóre formuły mogą powodować błędy sprawdzania poprawności podczas wdrażania modelu w trybie DirectQuery, ponieważ formuła zawiera funkcję języka DAX, która nie jest obsługiwana w relacyjnym źródle danych.

Aby uzyskać więcej informacji, zobacz dokumentację modelowania tabelarycznego usług Analysis Services w witrynie BooksOnline programu SQL Server 2012.