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

Opis formuł języka DAX

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

Formuły języka DAX różnią się jednak na następujące ważne sposoby:

Jeśli chcesz dostosować obliczenia w poszczególnych wierszach, język DAX zawiera funkcje umożliwiające wykonywanie obliczeń, które różnią się w zależności od kontekstu przy użyciu bieżącej wartości wiersza lub pokrewnej wartości.
Język DAX zawiera typ funkcji, który 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ą obliczanie przy użyciu zakresów dat i porównywanie wyników w okresach równoległych.

Gdzie używać formuł języka DAX

Formuły można tworzyć w Power Pivot w obliczeniowycholumnach c lub w obliczonych wartościach ieldsf.

Kolumny obliczeniowe

Kolumna obliczeniowa to kolumna dodana 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 kolumn. Jeśli uwzględnisz tabelę Power Pivot w tabeli przestawnej (lub na wykresie przestawnym), kolumna obliczeniowa może być używana tak samo, jak w przypadku każdej innej kolumny danych.

Formuły w kolumnach obliczeniowych są bardzo podobne do formuł tworzonych w programie Excel. Jednak inaczej niż w programie 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 zostaną obliczone dla kolumny zaraz po utworzeniu formuły. Wartości kolumn są obliczane ponownie tylko w przypadku odświeżenia danych źródłowych lub użycia ręcznego ponownego obliczania.

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

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

Środki

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

Po zdefiniowaniu formuły dla miary jawnej nic się nie dzieje, dopóki miara nie zostanie dodana do tabeli przestawnej. Po dodaniu miary formuła jest obliczana 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 tworzonej miary 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ł przy użyciu paska formuły

Power Pivot, podobnie jak program Excel, udostępnia pasek formuły ułatwiający tworzenie i edytowanie formuł oraz funkcje autouzupełniania w celu zminimalizowania błędów podczas pisania i składni.

Aby wprowadzić nazwę tabeli Zacznij wpisywać nazwę tabeli. Funkcja Autouzupełnianie formuł udostępnia listę rozwijaną zawierającą prawidłowe nazwy zaczynające 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 przewodnik dotyczący tworzenia 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ć w środku 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 z listy rozwijanej, a cały tekst po punkcie wstawiania pozostaje niezmieniony.

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

Power Pivot nie powoduje dodania nawiasu zamykającego funkcji ani automatycznego dopasowania nawiasów. Należy się upewnić, że każda funkcja jest poprawna syntaktycznie lub że nie można zapisać formuły ani jej użyć.

Używanie wielu funkcji w formule

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

Wiele funkcji języka DAX jest zaprojektowanych tak, aby były używane wyłącznie jako funkcje zagnieżdżone. Te funkcje zwracają tabelę, której nie można bezpośrednio zapisać. powinna być podana jako dane wejściowe do funkcji tabeli. Na przykład funkcje SUMA.X, ŚREDNIA.X i MIN.X wymagają tabeli jako pierwszego argumentu.

Uwaga: Niektóre ograniczenia dotyczące zagnieżdżania funkcji istnieją w ramach miar, aby zapewnić, że wydajność nie będzie miała wpływu na wiele obliczeń wymaganych przez zależności między kolumnami.

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

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

Wiele funkcji języka DAX ma taką samą nazwę i takie samo zachowanie ogólne jak funkcje programu Excel, ale zostały zmodyfikowane w celu podjęcia różnych typów danych wejściowych, a w niektórych przypadkach mogą zwracać inny typ danych. Ogólnie rzecz biorąc, nie można używać funkcji języka DAX w formule programu Excel ani używać formuł programu Excel w Power Pivot bez jakichkolwiek modyfikacji.
Funkcje języka DAX nigdy nie przyjmują odwołania do komórki ani zakresu jako odwołania, ale funkcje języka DAX przyjmują kolumnę lub tabelę jako odwołanie.
Funkcje daty i godziny języka DAX zwracają typ danych datetime. Z kolei funkcje daty i godziny programu Excel zwracają liczbę całkowitą reprezentującą datę jako liczbę kolejną.
Wiele nowych funkcji języka DAX zwraca tabelę wartości lub wykonywać obliczenia na podstawie tabeli wartości jako wartości wejściowych. Z kolei program Excel nie ma funkcji zwracających tabelę, ale niektóre funkcje mogą pracować z tablicami. Możliwość łatwego odwoływania się do kompletnych tabel i kolumn to nowa funkcja w Power Pivot.
Język DAX udostępnia nowe funkcje odnośników podobne do funkcji wyszukiwania tablicowego i wektorowego 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, w języku DAX cały typ danych zostanie zmieniony na typ danych, który najlepiej uwzględnia wszystkie wartości.

Typy danych języka DAX

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

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

Formuły i model relacyjny

Okno Power Pivot to obszar, w którym można pracować z wieloma tabelami danych i łączyć tabele w modelu relacyjnym. W ramach tego 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 sumowania wartości dla powiązanej tabeli, a następnie zapisać tę wartość w jednej komórce. Aby sterować wierszami z powiązanej tabeli, można też zastosować filtry do tabel i kolumn. Aby uzyskać więcej informacji, zobacz Relacje między tabelami w modelu danych.

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

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

Ogólnie rzecz biorąc, formuła języka DAX w kolumnie jest zawsze stosowana do całego zestawu wartości w kolumnie (nigdy tylko do kilku wierszy lub komórek).
Tabele w Power Pivot muszą zawsze mieć taką samą liczbę kolumn w każdym wierszu, a wszystkie wiersze w kolumnie muszą zawierać ten sam typ danych.
Jeśli tabele są połączone relacją, należy się upewnić, że w obu kolumnach używanych jako klucze są w większości zgodne wartości. Ponieważ Power Pivot nie wymusza więzów integralności, w kolumnie klucza mogą występować niezgodne wartości i nadal można utworzyć relację. Obecność pustych lub niezgodnych 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.
Podczas łączenia tabel przy użyciu relacji można powiększyć zakres lub tekst c, w którym są obliczane formuły. 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 manipulują kontekstem, ale kontekst może również powodować zmiany wyników w sposób, którego nie można przewidzieć. Aby uzyskać więcej informacji, zobacz Kontekst w formułach języka DAX.

Aktualizowanie wyników formuł

Data r efresh i recalculation to dwie 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 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 interwałach. Jeśli skoroszyt został opublikowany w witrynie programu SharePoint, możesz też zaplanować automatyczne odświeżanie ze źródeł zewnętrznych.

Ponowne obliczanie to proces aktualizowania wyników formuł w celu odzwierciedlenia wszelkich zmian wprowadzonych w formułach i odzwierciedlenia tych zmian w danych źródłowych. Ponowne obliczanie może wpłynąć na wydajność w następujący sposób:

W przypadku kolumny obliczeniowej zawsze należy ponownie obliczyć wynik formuły dla całej kolumny za każdym razem, gdy formuła zostanie zmieniona.
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ż ponownie obliczona po zmianie nagłówka wiersza lub kolumny, który wpływa na filtry danych lub podczas ręcznego odświeżania tabeli przestawnej.

Rozwiązywanie problemów z formułami

Błędy podczas pisania formuł

Jeśli podczas definiowania formuły zostanie wyświetlony błąd, formuła może zawierać błąd składniowy, błąd semantyczny lub błąd obliczeń.

Najłatwiejszym rozwiązaniem są błędy składniowe. Zazwyczaj zawierają brakujący nawias lub przecinek. Aby uzyskać pomoc dotyczącą składni poszczególnych funkcji, zobacz Dokumentacja funkcji języka DAX.

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

Formuła odwołuje się do nieistniejącej kolumny, tabeli lub funkcji.
Formuła wydaje się poprawna, ale gdy aparat danych pobiera dane, znajduje niezgodność typu i zgłasza błąd.
Formuła przekazuje do funkcji nieprawidłową liczbę lub typ parametrów.
Formuła odwołuje się do innej kolumny, która zawiera błąd, dlatego 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 ma rzeczywistych danych do użycia w obliczeniach.

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

Nieprawidłowe lub nietypowe wyniki podczas klasyfikowania lub zamawiania wartości kolumn

W przypadku klasyfikowania lub zamawiania kolumny zawierającej wartość NaN (nie liczba) mogą wystąpić błędne lub nieoczekiwane wyniki. Jeśli na przykład obliczenie dzieli 0 przez 0, zwracany jest wynik NaN.

Jest to spowodowane tym, że aparat formuł wykonuje porządkowanie i klasyfikowanie przez porównanie wartości liczbowych. Nie można jednak porównać nazwy NaN z innymi liczbami w kolumnie.

Aby zapewnić poprawne wyniki, można użyć instrukcji warunkowych przy użyciu funkcji JEŻELI w celu przetestowania wartości NaN i zwrócenia wartości liczbowej 0.

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

Ogólnie rzecz biorąc, formuły języka DAX, które są budowane w Power Pivot, są całkowicie zgodne z modelami tabelarycznymi 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ć różne 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 tabelaryczną dokumentację modelowania usług Analysis Services w SQL Server 2012 BooksOnline.