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

Opis formuł języka DAX

Formuły języka DAX są bardzo podobne Excel formuł. Aby go utworzyć, wpisz znak równości, nazwę funkcji lub wyrażenie oraz wszelkie wymagane wartości i argumenty. Podobnie Excel język DAX oferuje wiele funkcji, których można używać do pracy z ciągami, wykonywania obliczeń przy użyciu dat i godzin oraz tworzenia wartości warunkowych.

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

Jeśli chcesz dostosować obliczenia dla poszczególnych wierszy, język DAX zawiera funkcje, które umożliwiają wykonywanie obliczeń poszczególnych kontekstowych przy użyciu bieżącej wartości wiersza lub powiązanej wartości.
Język DAX zawiera typ funkcji, która zwraca tabelę jako wynik, a nie jedną wartość. Te funkcje mogą służyć do wprowadzania danych do innych funkcji.
Funkcje analizy czasowej w języku DAX umożliwiają obliczenia z zastosowaniem zakresów dat i porównywanie wyników dla okresów równoległych.

Gdzie używać formuł języka DAX

Formuły można tworzyć w Power Pivot lub w obliczonych wzorach.

Kolumny obliczeniowe

Kolumna obliczeniowa to kolumna, która jest dodawania do istniejącej Power Pivot tabeli. Zamiast wklejać lub importować wartości w kolumnie, należy utworzyć formułę języka DAX definiującą wartości kolumny. Jeśli dołączysz tabelę Power Pivot przestawnej (lub tabeli wykres przestawny), kolumny obliczeniowej będzie można używać tak samo, jak każdej innej kolumny danych.

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

Gdy 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 obliczanie tylko w przypadku odświeżenia danych źródłowych lub ręcznego ponownego obliczania.

Kolumny obliczeniowe można tworzyć na podstawie miar i innych kolumn obliczeniowych. Nie należy jednak używać tej samej nazwy kolumny obliczeniowej i miary, ponieważ może to prowadzić do mylących wyników. Odwołując się do kolumny, najlepiej użyć w pełni kwalifikowanego odwołania do kolumny, aby uniknąć przypadkowego wywołania miary.

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

Miary

Miara to formuła utworzona specjalnie do użycia w tabeli przestawnej (lub tabeli wykres przestawny) która używa Power Pivot danych. Miary mogą być oparte na standardowych funkcjach agregacji, takich jak ILE.NIELICZBowych lub SUMA, 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, zamiast tego użyj kolumny obliczeniowej.

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

Definicja tworzyć miarę jest zapisywana wraz z tabelą danych źródłowych. Jest on wyświetlany na liście pól tabeli przestawnej i jest 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

Power Pivot, na przykład Excel, udostępnia pasek formuły ułatwiający tworzenie i edytowanie formuł, a także funkcję Autouzupełniania, co pozwala zminimalizować błędy pisowni i składni.

Aby wprowadzić nazwę tabeli Zacznij wpisywać nazwę tabeli. Funkcja Autouzupełnianie formuł udostępnia listę rozwijaną zawierającą prawidłowe nazwy, których nazwy zaczynają się od tych liter.

Aby wprowadzić nazwę kolumny Wpisz nawias kwadratowy, a następnie wybierz kolumnę z listy kolumn w 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 i dowiedzieć się, jak tworzyć formuły, zobacz Tworzenie formuł do obliczeń w dodatku Power Pivot.

Wskazówki korzystania z funkcji Autouzupełnianie

Funkcji Autouzupełnianie formuł można używać w środku istniejącej formuły z funkcjami zagnieżdżonych. Tekst bezpośrednio przed punktem wstawiania służy do wyświetlania wartości na liście rozwijanej, a cały tekst po punkcie wstawiania pozostaje bez zmian.

Nazwy zdefiniowane tworzyć stałe nie są wyświetlane na liście rozwijanej autouzupełniania, ale nadal można je wpisywać.

Power Pivot nie dodaje nawiasów zamykających funkcji ani automatycznie do siebie dodawaj nawiasów. Należy się upewnić, że każda funkcja jest prawidłowo zsynchronizowana, że nie można zapisać formuły ani jej użyć.

Używanie wielu funkcji w formule

Funkcje można zagnieżdżać, czyli używać wyników jednej funkcji jako argumentu innej funkcji. W kolumnach obliczeniowych można zagnieździć maksymalnie 64 poziomy funkcji. Jednak zagnieżdżanie może utrudniać tworzenie formuł lub rozwiązywanie problemów.

Wiele funkcji języka DAX jest przeznaczonych do wyłącznie jako funkcji zagnieżdżonych. W wyniku tego te funkcje zwracają tabelę, której nie można bezpośrednio zapisać. powinien być podany jako dane wejściowe funkcji tabeli. Na przykład funkcje SUMA.X, ŚREDNIA.X i MINX wymagają, aby jako pierwszy argument był tabela.

Uwaga: Istnieją pewne ograniczenia zagnieżdżania funkcji w miarach, aby mieć pewność, że na wydajność nie wpływa wiele obliczeń wymaganych przez zależności między kolumnami.

Porównanie funkcji języka DAX i Excel

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

Wiele funkcji języka DAX ma taką samą nazwę i takie samo ogólne zachowanie jak funkcje języka Excel, ale zostały zmodyfikowane tak, aby uwzględniały różne typy danych wejściowych i w niektórych przypadkach zwracały inny typ danych. Ogólnie rzecz biorąc, nie można używać funkcji języka DAX Excel formuł ani Excel formuł w programie Power Pivot bez żadnych zmian.
Funkcje języka DAX nigdy nie biorą odwołania do komórki ani zakresu jako odwołania, ale zamiast tego funkcje języka DAX przejmą jako odwołanie kolumnę lub tabelę.
Funkcje daty i czasu języka DAX zwracają typ danych datetime. Natomiast funkcje Excel i czasu zwracają liczbę całkowitą reprezentującą datę jako liczbę kolejną.
Wiele nowych funkcji języka DAX zwraca tabelę wartości lub obliczenia na podstawie tabeli wartości jako danych wejściowych. Natomiast funkcja Excel nie ma funkcji zwracanych w tabeli, ale niektóre funkcje mogą działać z tablicami. Możliwość łatwego odwoływać się do pełnych tabel i kolumn to nowa funkcja w programie Power Pivot.
Język DAX zawiera nowe funkcje wyszukiwania, podobne do funkcji wyszukiwania tablicowego i wektorowego w programie Excel. Funkcje języka DAX wymagają jednak nawiązanej relacji między tabelami.
Dane w kolumnie powinny zawsze mieć ten sam typ danych. Jeśli dane nie są tego samego typu, język DAX zmienia całą kolumnę na typ danych, który najlepiej mieści wszystkie wartości.

Typy danych języka DAX

Do modelu danych można zaimportować Power Pivot danych z wielu różnych źródeł danych, które mogą obsługiwać różne typy danych. W przypadku importowania lub ładowania danych, a następnie używania danych w obliczeniach lub tabelach przestawnych dane są konwertowane na jeden z Power Pivot danych. Aby uzyskać listę typów danych, zobacz Typy danych w modelach danych.

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

Formuły i model relacyjnych

Okno Power Pivot to obszar, w którym można pracować z wieloma tabelami danych i łączyć tabele w modelu relacyjnej. W tym modelu danych tabele są ze sobą połączone za pomocą relacji, które umożliwiają tworzenie korelacji z kolumnami w innych tabelach i tworzenie bardziej interesujących obliczeń. Można na przykład utworzyć formuły sumowanie wartości dla powiązanej tabeli, a następnie zapisać te wartości w pojedynczej komórce. Aby kontrolować wiersze z powiązanej tabeli, możesz też zastosować filtry do tabel i kolumn. Aby uzyskać więcej informacji, zobacz Relacje między tabelami w modelu danych.

Tabele można łączyć przy użyciu relacji, dlatego tabele przestawne mogą także zawierać dane z wielu kolumn poszczególnych tabel.

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

Na ogół formuła języka DAX w kolumnie jest zawsze stosowana do całego zestawu wartości w kolumnie (nigdy nie dotyczy tylko kilku wierszy ani komórek).
Tabele w Power Pivot zawsze muszą mieć taką samą liczbę kolumn w każdym wierszu, a wszystkie wiersze w kolumnie muszą zawierać ten sam typ danych.
Podczas połączenia tabel za pomocą relacji należy upewnić się, że w większości przypadków dwie kolumny używane jako klucze mają zgodne wartości. Ponieważ Power Pivot więzów integralności, nie można mieć w kolumnie klucza wartości niedopasujących i nadal tworzyć relacje. Obecność pustych lub niedopasujących wartości może jednak mieć wpływ na wyniki formuł i wygląd tabel przestawnych. Aby uzyskać więcej informacji, zobacz Odnośniki w formułach dodatku Power Pivot.
Łączenie tabel za pomocą relacji umożliwia powiększenie zakresu lub tekstu tekstowego,w którym formuły są obliczane. Na przykład na formuły w tabeli przestawnej mogą mieć wpływ filtry lub nagłówki kolumn i wierszy w tabeli przestawnej. Możesz pisać formuły, które operują na kontekście, ale kontekst również może powodować zmiany wyników w sposób, na który nie możesz oczekiwać. Aby uzyskać więcej informacji, zobacz Kontekst w formułach języka DAX.

Aktualizowanie wyników formuł

Dane są dwa oddzielne, ale powiązane operacje, które należy zrozumieć podczas projektowania modelu danych zawierającego złożone formuły, duże ilości danych lub dane uzyskiwane z zewnętrznych źródeł danych.

Odświeżanie danych jest procesem 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ślony interwałach. Jeśli skoroszyt został opublikowany w witrynie SharePoint, możesz zaplanować automatyczne odświeżanie ze źródeł zewnętrznych.

Ponowne obliczanie jest procesem aktualizowania wyników formuł w celu odzwierciedlenia wszelkich zmian w samych formułach oraz odzwierciedlenia tych zmian w danych źródłowych. Ponowne obliczanie może mieć wpływ na wydajność w następujący sposób:

W przypadku kolumny obliczeniowej wynik formuły zawsze powinien być ponownie obliczany dla całej kolumny za każdym razem, gdy zmienisz formułę.
W przypadku miary wyniki formuły nie są obliczane, dopóki miara nie zostanie umieszczona w kontekście tabeli przestawnej lub tabeli wykres przestawny. Formuła zostanie również ponownie przeliczone po zmianie nagłówka wiersza lub kolumny, który wpływa na filtry danych lub 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 jest wyświetlany komunikat o błędzie, formuła może zawierać błąd składniowy,błąd semantyczny lub błąd obliczeń.

Błędy składniowe są najłatwiejsze do rozwiązania. Zazwyczaj oznacza to brak nawiasu lub przecinek. Aby uzyskać pomoc na temat składni poszczególnych funkcji, zobacz Informacje dotyczące funkcji języka DAX.

Inny typ błędu występuje, gdy składnia jest poprawna, ale wartość lub kolumna, do których następuje odwołanie, nie ma sensu w kontekście formuły. Przyczyną takich błędów semantycznych i obliczeniowych może być dowolny z następujących problemów:

Formuła odwołuje się do nieistniecej kolumny, tabeli lub funkcji.
Formuła wydaje się poprawna, ale gdy aparat danych pobiera dane, wyszukuje niezgodność typów i zgłasza błąd.
Formuła przekazuje nieprawidłową liczbę lub typ parametrów do funkcji.
Formuła odwołuje się do innej kolumny, w która wystąpił błąd, dlatego jej wartości są nieprawidłowe.
Formuła odwołuje się do kolumny, która nie została przetworzony, co oznacza, że ma metadane, ale nie zawiera rzeczywistych danych do użycia w obliczeniach.

W pierwszych czterech przypadkach język DAX oflaguje całą kolumnę zawierającą nieprawidłową formułę. W ostatnim przypadku język DAX wyszarzy kolumnę, aby wskazać, że kolumna znajduje się w stanie nieprzetworzonej.

Nieprawidłowe lub nietypowe wyniki podczas klasyfikacji lub kolejności wartości kolumn

Podczas klasyfikacji lub kolejności kolumny zawierającej wartość NaN (Nie jest liczbą) mogą wystąpić błędy lub nieoczekiwane wyniki. Na przykład gdy obliczenie dzieli 0 przez 0, zwracany jest wynik nan.

Wynika to z tego, że aparat formuły przeprowadza kolejność i klasyfikację przez porównanie wartości liczbowych. Nie można jednak porównać nazwy NaN z innymi liczbami w kolumnie.

Aby zapewnić prawidłowe wyniki, możesz użyć instrukcji warunkowych za pomocą funkcji JEŻELI w celu przetestowania wartości nan i zwrócenia wartości liczbowej 0.

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

Na ogół formuły języka DAX, które tworzysz w programie Power Pivot są całkowicie zgodne z modelami tabelarykimi usług Analysis Services. Jeśli jednak zmigrujesz model Power Pivot do wystąpienia usług Analysis Services, a następnie wdrożysz model w trybie DirectQuery, istnieją pewne ograniczenia.

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

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