W tej sekcji zamieszczono linki do przykładów przedstawiających używanie formuł języka DAX w następujących scenariuszach.
-
Wykonywanie złożonych obliczeń
-
Praca z tekstem i datami
-
Wartości warunkowe i testowanie pod kątem błędów
-
Korzystanie z analizy czasowej
-
Klasyfikowanie i porównywanie wartości
W tym artykule
Wprowadzenie
Odwiedź witrynę typu wiki centrum zasobów języka DAX , w której znajdziesz różnego rodzaju informacje na temat języka DAX, w tym blogi, przykłady, pliki do oficjalnych informacji i klipy wideo udostępnione przez czołowych specjalistów z branży i firmę Microsoft.
Scenariusze: wykonywanie złożonych obliczeń
Formuły języka DAX mogą wykonywać złożone obliczenia, które obejmują agregacje niestandardowe, filtrowanie i używanie wartości warunkowych. W tej sekcji przedstawiono przykłady rozpoczynania pracy z obliczeniami niestandardowymi.
Tworzenie niestandardowych obliczeń dla tabeli przestawnej
CALCULATE i CALCULATETABLE to zaawansowane i elastyczne funkcje, które przydają się do definiowania pól obliczeniowych. Te funkcje umożliwiają zmianę kontekstu wykonywania obliczeń. Można również dostosować typ agregacji lub operacji matematycznych do wykonania. Zobacz poniższe tematy, aby uzyskać przykłady.
Stosowanie filtru do formuły
W większości miejsc, w których funkcja języka DAX przyjmuje tabelę jako argument, zazwyczaj można przejść do odfiltrowanej tabeli, używając funkcji FILTRUJ zamiast nazwy tabeli lub określając wyrażenie filtru jako jeden z argumentów funkcji. W poniższych tematach przedstawiono przykłady tworzenia filtrów i wpływu filtrów na wyniki formuł. Aby uzyskać więcej informacji, zobacz Filtrowanie danych w formułach języka DAX.
Funkcja FILTRUJ umożliwia określenie kryteriów filtrowania przy użyciu wyrażenia, natomiast pozostałe funkcje są przeznaczone specjalnie do filtrowania wartości pustych.
Selektywne usuwanie filtrów w celu utworzenia współczynnika dynamicznego
Tworząc filtry dynamiczne w formułach, możesz łatwo odpowiedzieć na następujące pytania:
-
Jaki był wkład bieżącej sprzedaży produktu w łączną sprzedaż w danym roku?
-
Ile ten podział przyczynił się do całkowitych zysków za wszystkie lata operacyjne, w porównaniu do innych działów?
Kontekst tabeli przestawnej może wpływać na formuły używane w tabeli przestawnej, ale można selektywnie zmieniać kontekst, dodając lub usuwając filtry. W przykładzie w temacie WSZYSTKIE pokazano, jak to zrobić. Aby ustalić stosunek sprzedaży określonego odsprzedawcy do wartości sprzedaży dla wszystkich odsprzedawców, należy utworzyć miarę obliczaną dla bieżącego kontekstu wartość podzieloną przez wartość kontekstu ALL.
W temacie ALLEXCEPT przedstawiono przykład selektywnego czyszczenia filtrów formuły. W obu przykładach przedstawiono sposób zmieniania się wyników w zależności od projektu tabeli przestawnej.
Aby poznać inne przykłady obliczania współczynników i procentów, zobacz następujące tematy:
Używanie wartości z zewnętrznej pętli
Oprócz używania wartości z bieżącego kontekstu w obliczeniach język DAX może używać wartości z poprzedniej pętli do tworzenia zestawu powiązanych obliczeń. W poniższym temacie przedstawiono sposób tworzenia formuły odwołujcej się do wartości z zewnętrznej pętli. Funkcja EARLIER obsługuje maksymalnie dwa poziomy pętli zagnieżdżonych.
Aby dowiedzieć się więcej o kontekście wierszy i powiązanych tabelach oraz jak używać tej koncepcji w formułach, zobacz Kontekst w formułach języka DAX.
Scenariusze: praca z tekstem i datami
Ta sekcja zawiera linki do tematów referencyjnych języka DAX, które zawierają przykłady typowych scenariuszy związanych z pracą z tekstem, wyodrębnianiem i redagowaniem wartości daty i godziny lub tworzeniem wartości na podstawie warunku.
Tworzenie kolumny klucza przez łączenie
Power Pivot nie zezwala na klucze złożone; dlatego jeśli w źródle danych są klucze złożone, może być konieczne połączenie ich w jedną kolumnę kluczy. W poniższym temacie przedstawiono przykład tworzenia kolumny obliczeniowej na podstawie klucza złożonego.
Redagowanie daty na podstawie części daty wyodrębnionych z daty tekstowej
Power Pivot używa typu danych data/godzina programu SQL Server do pracy z datami. Dlatego jeśli dane zewnętrzne zawierają daty sformatowane w inny sposób — na przykład jeśli daty są zapisane w regionalnym formacie daty, który nie jest rozpoznawany przez aparat danych Power Pivot lub jeśli w danych są używane klucze zastępcze typu integer — może być konieczne wyodrębnienie części daty za pomocą formuły języka DAX, a następnie utworzenie ich w prawidłowej reprezentacji daty/godziny.
Jeśli na przykład masz kolumnę dat reprezentowaną jako liczba całkowita, a następnie zaimportowana jako ciąg tekstowy, możesz przekonwertować ten ciąg na wartość daty/godziny, używając następującej formuły:
=DATA(PRAWY([Wartość1];4);LEWY([Wartość1];2);FRAGMENT.DATY([Wartość1];2))
|
Wartość1 |
Wynik |
|---|---|
|
01032009 |
1/3/2009 |
|
12132008 |
12/13/2008 |
|
06252007 |
6/25/2007 |
Poniższe tematy zawierają więcej informacji na temat funkcji używanych do wyodrębniania i redagowania dat.
Definiowanie niestandardowego formatu daty lub liczby
Jeśli dane zawierają daty lub liczby, które nie są reprezentowane w jednym ze standardowych formatów tekstowych systemu Windows, możesz zdefiniować format niestandardowy, aby upewnić się, że wartości są obsługiwane poprawnie. Te formaty są używane podczas konwertowania wartości na ciągi lub ciągi. W poniższych tematach przedstawiono również szczegółową listę wstępnie zdefiniowanych formatów dostępnych do pracy z datami i liczbami.
Zmienianie typów danych za pomocą formuły
W Power Pivot typ danych wyjściowych jest określany na podstawie kolumn źródłowych i nie można jawnie określić typu danych wyniku, ponieważ optymalny typ danych jest określany przez Power Pivot. Do manipulowania typem danych wyjściowych można jednak używać niejawnych konwersji typów danych wykonywanych przez Power Pivot.
-
Aby przekonwertować datę lub ciąg liczbowy na liczbę, pomnóż ją przez 1,0. Na przykład poniższa formuła oblicza bieżącą datę minus 3 dni, a następnie zwraca odpowiadającą jej wartość całkowitą.
=(DZIŚ()-3)*1,0
-
Aby przekonwertować datę, liczbę lub wartość waluty na ciąg, połącz wartość z pustym ciągiem. Na przykład następująca formuła zwraca dzisiejszą datę jako ciąg znaków.
=""& DZIŚ()
Następujące funkcje mogą być również używane w celu zapewnienia zwrócenia określonego typu danych:
Konwertowanie liczb rzeczywistych na liczby całkowite
-
Konwertowanie liczb rzeczywistych, liczb całkowitych lub dat na ciągi
-
Konwertowanie ciągów na liczby rzeczywiste lub daty
Scenariusz: Wartości warunkowe i testowanie pod kątem błędów
Podobnie jak w programie Excel, język DAX zawiera funkcje, które umożliwiają testowanie wartości w danych i zwracanie innej wartości na podstawie warunku. Można na przykład utworzyć kolumnę obliczeniową, która oznacza odsprzedawców jako preferowanych lub wartości w zależności od rocznie kwoty sprzedaży. Funkcje, które testują wartości, są również przydatne do sprawdzania zakresu lub typu wartości, aby zapobiec łamaniu obliczeń przez nieoczekiwane błędy danych.
Tworzenie wartości na podstawie warunku
Zagnieżdżone warunki JEŻELI umożliwiają testowanie wartości i generowanie nowych wartości warunkowo. Poniższe tematy zawierają kilka prostych przykładów przetwarzania warunkowego i wartości warunkowych:
Testowanie błędów w formule
W przeciwieństwie do programu Excel nie można mieć prawidłowych wartości w jednym wierszu kolumny obliczeniowej i nieprawidłowych wartości w innym wierszu. Oznacza to, że w przypadku wystąpienia błędu w dowolnej części kolumny Power Pivot cała kolumna jest oflagowana z błędem, więc zawsze należy poprawiać błędy formuł, które powodują nieprawidłowe wartości.
Jeśli na przykład utworzysz formułę dzielącą przez zero, może zostać wyświetlony wynik nieskończoności lub błąd. Niektóre formuły również będą kończyć się niepowodzeniem, jeśli funkcja napotka pustą wartość, gdy oczekuje wartości liczbowej. Podczas opracowywania modelu danych najlepiej jest zezwolić na wyświetlanie błędów, aby można było kliknąć komunikat i rozwiązać problem. Jednak podczas publikowania skoroszytów należy uwzględnić obsługę błędów, aby zapobiec nieoczekiwanym wartościom, powodując niepowodzenie obliczeń.
Aby uniknąć zwracania błędów w kolumnie obliczeniowej, należy użyć kombinacji funkcji logicznych i informacyjnych w celu przetestowania błędów i zawsze zwrócenia prawidłowych wartości. W poniższych tematach przedstawiono kilka prostych przykładów tego, jak to zrobić w języku DAX:
Scenariusze: korzystanie z analizy czasowej
Funkcje analizy czasowej języka DAX zawierają funkcje ułatwiające pobieranie dat lub zakresów dat z danych. Następnie możesz użyć tych dat lub zakresów dat, aby obliczyć wartości w podobnych okresach. Funkcje analizy czasowej obejmują również funkcje, które współpracują ze standardowymi interwałami dat, aby umożliwić porównywanie wartości z różnych miesięcy, lat lub kwartałów. Można również utworzyć formułę, która porównuje wartości dla pierwszej i ostatniej daty określonego okresu.
Aby uzyskać listę funkcji analizy czasowej, zobacz Funkcje analizy czasowej (język DAX). Aby uzyskać porady dotyczące efektywnego używania dat i godzin w analizie Power Pivot, zobacz Daty w dodatku Power Pivot.
Obliczanie sprzedaży skumulowanej
Poniższe tematy zawierają przykłady obliczania sald zamknięcia i otwierania. Przykłady umożliwiają tworzenie sald bieżących w różnych interwałach, takich jak dni, miesiące, kwartały lub lata.
Porównywanie wartości w czasie
Poniższe tematy zawierają przykłady porównywania sum w różnych okresach. Domyślne okresy obsługiwane przez język DAX to miesiące, kwartały i lata.
Obliczanie wartości w niestandardowym zakresie dat
W poniższych tematach przedstawiono przykłady pobierania niestandardowych zakresów dat, na przykład pierwszych 15 dni od rozpoczęcia promocji sprzedaży.
Jeśli pobierasz niestandardowy zestaw dat za pomocą funkcji analizy czasowej, możesz użyć tego zestawu dat jako danych wejściowych do funkcji wykonującej obliczenia, aby utworzyć niestandardowe agregaty w różnych okresach. Zobacz poniższy temat, aby dowiedzieć się, jak to zrobić:
-
Uwaga: Jeśli nie musisz określać niestandardowego zakresu dat, ale pracujesz ze standardowymi jednostkami księgowymi, takimi jak miesiące, kwartały lub lata, zalecamy wykonanie obliczeń przy użyciu funkcji analizy czasowej zaprojektowanych do tego celu, takich jak TOTALQTD, TOTALMTD, TOTALQTD itp.
Scenariusze: Klasyfikowanie i porównywanie wartości
Aby wyświetlić tylko n najwyższych elementów w kolumnie lub tabeli przestawnej, masz kilka opcji:
-
Za pomocą funkcji w programie Excel można utworzyć filtr najpopularniejszych elementów. Możesz również wybrać liczbę najwyższych lub najniższych wartości w tabeli przestawnej. W pierwszej części tej sekcji opisano sposób filtrowania 10 pierwszych elementów w tabeli przestawnej. Aby uzyskać więcej informacji, zobacz dokumentację programu Excel.
-
Możesz utworzyć formułę, która dynamicznie porządkuje wartości, a następnie filtrować według wartości klasyfikowania lub używać wartości klasyfikowania jako fragmentatora. W drugiej części tej sekcji opisano, jak utworzyć tę formułę, a następnie użyć tej klasyfikacji we fragmentatorze.
Istnieją zalety i wady każdej metody.
-
Górny filtr programu Excel jest łatwy w użyciu, ale filtr jest przeznaczony wyłącznie do wyświetlania. Jeśli zmienią się dane źródłowe tabeli przestawnej, musisz ręcznie odświeżyć tabelę przestawną, aby zobaczyć zmiany. Jeśli musisz dynamicznie pracować z rankingami, możesz użyć języka DAX, aby utworzyć formułę, która porównuje wartości z innymi wartościami w kolumnie.
-
Formuła języka DAX jest bardziej zaawansowana; co więcej, dodając wartość rankingu do fragmentatora, możesz po prostu kliknąć fragmentator, aby zmienić liczbę wyświetlanych najwyższych wartości. Obliczenia są jednak kosztowne w obliczeniach i ta metoda może nie być odpowiednia dla tabel z wieloma wierszami.
Pokazywanie tylko dziesięciu pierwszych elementów w tabeli przestawnej
|
Aby wyświetlić najwyższe lub najniższe wartości w tabeli przestawnej
|
Dynamiczne zamawianie elementów za pomocą formuły
W poniższym temacie przedstawiono przykład użycia języka DAX do utworzenia rankingu przechowywanego w kolumnie obliczeniowej. Formuły języka DAX są obliczane dynamicznie, dlatego zawsze można mieć pewność, że klasyfikacja jest poprawna, nawet jeśli dane źródłowe uległy zmianie. Ponadto, ponieważ formuła jest używana w kolumnie obliczeniowej, można użyć rankingu we fragmentatorze, a następnie wybrać 5 pierwszych, 10 pierwszych, a nawet 100 najwyższych wartości.
