W tej sekcji znajdują się linki do przykładów przedstawiających użycie formuł języka DAX w poniższych 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 , gdzie można znaleźć wszelkiego rodzaju informacje na temat języka DAX, w tym blogi, przykłady, oficjalne dokumenty i filmy przygotowane przez czołowych specjalistów w branży i firmę Microsoft.
Scenariusze: wykonywanie złożonych obliczeń
Formuły języka DAX mogą wykonywać złożone obliczenia obejmujące agregacje niestandardowe, filtrowanie i stosowanie wartości warunkowych. W tej sekcji znajdują się przykłady rozpoczynania pracy z obliczeniami niestandardowymi.
Tworzenie obliczeń niestandardowych dla tabeli przestawnej
CALCULATE i CALCULATETABLE to zaawansowane i elastyczne funkcje przydatne do definiowania pól obliczeniowych. Te funkcje umożliwiają zmianę kontekstu, w którym będą wykonywane obliczenia. Można również dostosować typ agregacji lub operacji matematycznej, która ma być wykonywana. Przykłady można znaleźć w poniższych tematach.
Stosowanie filtru do formuły
W większości przypadków, w których funkcja języka DAX przyjmuje tabelę jako argument, można zamiast tego przekazać tabelę filtrowaną, używając funkcji FILTER 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 oraz wpływ filtrów na wyniki formuł. Aby uzyskać więcej informacji, zobacz temat Filtrowanie danych w formułach języka DAX.
Funkcja FILTRUJ umożliwia określanie kryteriów filtrowania przy użyciu wyrażeń, natomiast pozostałe funkcje są przeznaczone specjalnie do filtrowania pustych wartości.
Selektywne usuwanie filtrów w celu utworzenia dynamicznego współczynnika
Tworząc filtry dynamiczne w formułach, można łatwo odpowiedzieć na takie pytania, jak:
- Jaki był udział bieżącej sprzedaży produktu w całkowitej sprzedaży w danym roku?
- W jakim stopniu ten dział przyczynił się do całkowitych zysków za wszystkie lata działalności w porównaniu z innymi działami?
Kontekst tabeli przestawnej może mieć wpływ na formuły używane w tabeli przestawnej, ale można selektywnie zmieniać kontekst, dodając lub usuwając filtry. Przykład w temacie ALL pokazuje, jak to zrobić. Aby znaleźć stosunek sprzedaży konkretnego sprzedawcy do sprzedaży wszystkich sprzedawców, należy utworzyć miarę, która oblicza wartość dla bieżącego kontekstu podzieloną przez wartość kontekstu ALL.
Temat ALLEXCEPT zawiera przykład selektywnego czyszczenia filtrów w formule. W obu przykładach zobaczysz, jak zmieniają się wyniki w zależności od projektu tabeli przestawnej.
Aby zapoznać się z innymi przykładami obliczania współczynników i wartości procentowych, zobacz następujące tematy:
Używanie wartości z pętli zewnętrznej
Oprócz używania w obliczeniach wartości z bieżącego kontekstu, język DAX może używać wartości z poprzedniej pętli podczas tworzenia zestawu powiązanych obliczeń. W poniższym temacie pokazano, jak skonstruować formułę odwołującą się do wartości z pętli zewnętrznej. Funkcja EARLIER obsługuje maksymalnie dwa poziomy pętli zagnieżdżonych.
Aby dowiedzieć się więcej o kontekście wiersza i powiązanych tabelach oraz o używaniu tego pojęcia w formułach, zobacz Kontekst w formułach języka DAX.
Scenariusze: praca z tekstem i datami
W tej sekcji znajdują się linki do tematów referencyjnych języka DAX zawierających przykłady typowych scenariuszy dotyczących pracy z tekstem, wyodrębniania i redagowania wartości dat i godzin oraz tworzenia wartości na podstawie warunku.
Tworzenie kolumny klucza przez łączenie
Dodatek Power Pivot nie zezwala na klucze złożone; Dlatego też, jeśli w źródle danych znajdują się klucze złożone, może być konieczne połączenie ich w jedną kolumnę klucza. W poniższym temacie przedstawiono przykład tworzenia kolumny obliczeniowej na podstawie klucza złożonego.
Compose daty na podstawie części dat wyodrębnionych z daty tekstowej
Dodatek Power Pivot korzysta z typu danych Data/godzina programu SQL Server do pracy z datami, dlatego jeśli dane zewnętrzne zawierają daty sformatowane inaczej — na przykład jeśli daty są zapisane w regionalnym formacie daty, który nie jest rozpoznawany przez aparat danych dodatku Power Pivot, lub jeśli w danych są używane zastępcze klucze liczb całkowitych — może być konieczne wyodrębnienie części daty, a następnie złożenie tych części w prawidłową datę za pomocą formuły języka DAX. reprezentacja czasu.
Na przykład jeśli masz kolumnę dat, które zostały przedstawione jako liczba całkowita, a następnie zaimportowane 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.TEKSTU([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 tworzenia dat.
Definiowanie niestandardowego formatu daty lub liczb
Jeśli dane zawierają daty lub liczby, które nie są reprezentowane przez jeden ze standardowych formatów tekstowych systemu Windows, można zdefiniować format niestandardowy, aby zapewnić poprawną obsługę wartości. Te formaty są używane podczas konwertowania wartości na ciągi lub z ciągów. Poniższe tematy zawierają też szczegółową listę wstępnie zdefiniowanych formatów, które są dostępne do pracy z datami i liczbami.
- Wstępnie zdefiniowane formaty liczbowe dla funkcji FORMAT
- Niestandardowe formaty liczbowe dla funkcji FORMAT
- Wstępnie zdefiniowane formaty daty i godziny dla funkcji FORMAT
- Niestandardowe formaty daty i godziny dla funkcji FORMAT
Zmienianie typów danych za pomocą formuły
W dodatku 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 dodatek Power Pivot. Do manipulowania typem danych wyjściowych można jednak użyć niejawnych konwersji typów danych wykonywanych przez dodatek Power Pivot.
- Aby przekonwertować datę lub ciąg liczbowy na liczbę, pomnóż ją przez liczbę 1,0. Na przykład następująca 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 walutę na ciąg, połącz tę wartość z pustym ciągiem. Na przykład poniższa formuła zwraca dzisiejszą datę w postaci ciągu.
=""& DZIŚ()
W celu zwrócenia określonego typu danych można również użyć następujących funkcji:
Konwertowanie liczb rzeczywistych na liczby całkowite
- ROUND, funkcja
- ZAOKR.W.GÓRĘ
-
ZAOKR.W.DÓŁ
Konwertowanie liczb rzeczywistych, liczb całkowitych lub dat na ciągi znaków - ZAOKR.DO.TEKST
-
FORMAT, funkcja
Konwertowanie ciągów na liczby rzeczywiste lub daty - WARTOŚĆ, funkcja
- DATA.WARTOŚĆ
- CZAS.WARTOŚĆ, funkcja
Scenariusz: Wartości warunkowe i testowanie błędów
Podobnie jak program Excel, język DAX zawiera funkcje umożliwiające testowanie wartości w danych i zwracanie innej wartości na podstawie warunku. Można na przykład utworzyć kolumnę obliczeniową, która będzie oznaczać odsprzedawców etykietami jako Preferowani lub Wartościowi w zależności od rocznej wielkości sprzedaży. Funkcje sprawdzające wartości są również przydatne do sprawdzania zakresu lub typu wartości, aby zapobiec przerywaniu obliczeń przez nieoczekiwane błędy danych.
Tworzenie wartości na podstawie warunku
Zagnieżdżonych warunków JEŻELI można używać do testowania wartości i warunkowego generowania nowych wartości. Poniższe tematy zawierają proste przykłady przetwarzania warunkowego i wartości warunkowych:
Sprawdzanie błędów w formule
Inaczej niż w programie Excel nie można mieć prawidłowych wartości w jednym wierszu kolumny obliczeniowej, a nieprawidłowych wartości w innym. Oznacza to, że jeśli w jakiejkolwiek części kolumny dodatku Power Pivot występuje błąd, cała kolumna jest oflagowywana, dlatego należy zawsze poprawiać błędy formuł, których wynikiem są nieprawidłowe wartości.
Na przykład jeśli utworzysz formułę dzielenia przez zero, możesz otrzymać wynik nieskończoności lub błąd. Niektóre formuły również nie będą działać, jeśli funkcja napotka pustą wartość, mimo że oczekuje wartości liczbowej. Podczas opracowywania modelu danych najlepiej dopuścić do wyświetlenia błędów, co pozwoli kliknąć komunikat i rozwiązać problem. Jednak podczas publikowania skoroszytów należy uwzględnić obsługę błędów, aby zapobiec niepowodzeniu obliczeń przy nieoczekiwanych wartościach.
Aby uniknąć zwracania błędów w kolumnie obliczeniowej, należy użyć kombinacji funkcji logicznych i informacyjnych do testowania błędów, zawsze zwracając prawidłowe 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 obejmują funkcje ułatwiające pobieranie dat lub zakresów dat z danych. Na podstawie tych dat lub zakresów dat można następnie obliczyć wartości dla podobnych okresów. Funkcje analizy czasowej obejmują również funkcje, które pracują ze standardowymi interwałami dat, umożliwiając porównywanie wartości na przestrzeni miesięcy, lat lub kwartałów. Można też utworzyć formułę porównującą wartości dla pierwszej i ostatniej daty określonego okresu.
Aby uzyskać listę wszystkich funkcji analizy czasowej, zobacz Funkcje analizy czasowej (język DAX). Aby uzyskać porady dotyczące efektywnego używania dat i godzin w analizie dodatku Power Pivot, zobacz temat Daty w dodatku Power Pivot.
Obliczanie łącznej sprzedaży
Poniższe tematy zawierają przykłady obliczania sald zamknięcia i otwarcia. W przykładach pokazano sposób tworzenia sald bieżących dla różnych interwałów, takich jak dni, miesiące, kwartały lub lata.
- Funkcja CLOSINGBALANCEMONTH, funkcja CLOSINGBALANCEQUARTER, funkcja CLOSINGBALANCEYEAR
- Funkcja OPENINGBALANCEMONTH, funkcja OPENINGBALANCEQUARTER, funkcja OPENINGBALANCEYEAR, funkcja OPENINGBALANCEYEAR
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.
- PREVIOUSMONTH, funkcja, PREVIOUSQUARTER, PREVIOUSYEAR, funkcja
- Funkcja TOTALMTD, funkcja TOTALQTD, funkcja TOTALYTD
- Funkcja PARALLELPERIOD
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 po rozpoczęciu promocji sprzedaży.
Jeśli korzystasz z funkcji analizy czasowej w celu pobrania niestandardowego zestawu dat, możesz użyć tego zestawu dat jako danych wejściowych dla funkcji wykonującej obliczenia w celu utworzenia niestandardowych agregacji dla różnych okresów. W następującym temacie podano przykład wykonania tej czynności:
-
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 wykonywanie obliczeń przy użyciu zaprojektowanych do tego celu funkcji analizy czasowej, takich jak TOTALQTD, TOTALMTD, TOTALQTD itp.
Scenariusze: klasyfikowanie i porównywanie wartości
Aby wyświetlić tylko pierwsze n elementów w kolumnie lub tabeli przestawnej, masz kilka opcji:
- Możesz użyć funkcji programu Excel, aby utworzyć filtr najlepszy. Możesz również wybrać kilka najwyższych lub najniższych wartości w tabeli przestawnej. W pierwszej części tej sekcji opisano, jak filtrować 10 pierwszych elementów w tabeli przestawnej. Aby uzyskać więcej informacji, zobacz dokumentację programu Excel.
- Można utworzyć formułę dynamicznie klasyfikującą wartości, a następnie przefiltrować wartości według klasyfikacji lub użyć wartości klasyfikacji jako fragmentatora. W drugiej części tej sekcji opisano, jak utworzyć tę formułę i użyć tej klasyfikacji we fragmentatorze.
Każda metoda ma zalety i wady.
- Górny filtr programu Excel jest łatwy w użyciu, ale filtr jest przeznaczony wyłącznie do celów wyświetlania. Jeśli dane źródłowe tabeli przestawnej ulegną zmianie, należy ręcznie odświeżyć tabelę przestawną, aby zobaczyć te zmiany. Jeśli chcesz dynamicznie pracować z klasyfikacjami, możesz użyć języka DAX do utworzenia formuły, która porównuje wartości z innymi wartościami w kolumnie.
- Formuła DAX jest bardziej zaawansowana; ponadto, 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 obliczeniowo i ta metoda może być nieodpowiednia w przypadku tabel z wieloma wierszami.
Wyświetlanie tylko dziesięciu pierwszych elementów w tabeli przestawnej
Aby wyświetlić najwyższe lub najniższe wartości w tabeli przestawnej
|
|---|
Dynamiczne porządkowanie elementów przy użyciu formuły
Poniższy temat zawiera przykład użycia języka DAX do utworzenia klasyfikacji przechowywanej w kolumnie obliczeniowej. Formuły języka DAX są obliczane dynamicznie, więc zawsze możesz mieć pewność, że klasyfikacja jest poprawna, nawet jeśli dane źródłowe uległy zmianie. Ponieważ ta formuła jest używana w kolumnie obliczeniowej, można użyć klasyfikacji we fragmentatorze, a następnie wybrać 5, 10 pierwszych lub nawet 100 pierwszych wartości.