Samouczek usługi LuckyTemplates w języku DAX dotyczący odwrotnego TOPN

W tym poście na blogu przyjrzymy się pytaniu zadanemu na forum LuckyTemplates i użyjemy techniki LuckyTemplates języka DAX, aby je rozwiązać. Jeśli chcesz śledzić i pobrać pliki, po prostu odwiedź forum LuckyTemplates i przejdź do . Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

Aby wyjaśnić scenariusz, który zamierzamy omówić, mamy jako przykład prostą tabelę. W pierwszej kolumnie mamy Poziom finansowy 1 . W drugiej kolumnie mamy datę o nazwie Ukończono. W ostatniej kolumnie mamy kombinację poziomu finansowego i daty zakończenia, którą nazwiemy Przychód .

Następnie mamy krajalnicę dla daty i nazwy klienta .

To, co chcemy zrobić dla określonego okresu, to zidentyfikowanie trzech najważniejszych dat, które nie są zerowe. A następnie w tych trzech najlepszych datach chcemy określić poziom finansowy.

W pierwszej wersji miary wymieniłem daty, które są mniejsze niż 29.04.2021, w których mamy pewne przychody.

W tych dniach chcemy określić poziomy finansowe, które przyniosły pewne przychody. Wyjaśnię ci, jak możemy osiągnąć to obliczenie.

Spis treści

• Modelowanie danych
• Wersja 1
• Przygotowanie kontekstu filtra
• Weryfikacja wyniku obliczenia usługi LuckyTemplates języka DAX
• Inne obliczenia usługi LuckyTemplates w języku DAX
• Wersja 2
• Korzystanie z funkcji KEEPFILTERS
• Wersja 3
• Wniosek

Modelowanie danych

Przed napisaniem rzeczywistego języka DAX przyjrzyjmy się modelowi danych, który zawiera wiele tabel. Interesuje nas tylko tabela Data , Tabela danych , Tabela kategorii zadań i Tabela klientów .

Zauważ, że tabela Customer nie jest tak naprawdę ważna, ponieważ jest używana tylko wewnątrz krajalnicy.

Miara przychodów jest obliczana w tabeli Dane. Kategoria Praca zawiera poziom finansowy, który mamy na matrycy. Następnie używamy kolumny Date z tabeli Date w naszym slicerze.

Wersja 1

Pozbądźmy się wszystkiego z matrixa i zacznijmy od nowa. Zamierzam stworzyć nową miarę i nazwać ją V1, ponieważ to będzie pierwsza wersja. Przyjrzymy się również dwóm innym odmianom.

Przygotowanie kontekstu filtra

Pierwszą rzeczą, którą zamierzamy zrobić, jest pobranie wszystkich wartości poziomu finansowego z kontekstu filtra. Utworzymy i nazwiemy ją FinancialLevelInFilterContext .

Aby pobrać wartości z kontekstu filtru, użyjemy w tabeli kategorii stanowisk, która zawiera kolumnę poziomu finansowego. Następnie musimy utworzyć kolejną zmienną i nazwać ją Result . Następnie napisz i również zamknij.

W pierwszym argumencie mamy wybór nad fragmentatorem dat. W tych datach chcemy zidentyfikować daty, które nie mają 0 i mają przynajmniej pewien przychód.

Jeśli chcesz uzyskać dostęp do wszystkich dat wybranych w tym krajalniku, musimy użyć nad tabelą dat.

A ponieważ mamy poziom finansowy na matrycy, musimy również upewnić się, że usuwamy tę konkretną wartość z kontekstu filtra. Możemy użyć nad kategorią Job, a następnie napisać Financial Level 1.

Teraz, gdy nasz kontekst filtra jest przygotowany, możemy napisać najgłębsze obliczenie dla CALCULATE. Najpierw zadeklarujemy zmienną, która będzie kombinacją poziomu finansowego i kolumny daty, jak widzieliśmy w pierwszej tabeli.

Następnie użyjemy i użyjemy tabeli Dane do podsumowania tych dwóch tabel: Kategoria stanowiska i Tabela dat.

Zapewni to kombinację poziomu finansowego i kolumny daty, która istnieje w tabeli Dane. Następnie utworzymy kolejną zmienną, w której będziemy przechowywać wartość wiersza przychodów dla pierwszej utworzonej przez nas zmiennej.

Nazwą tej zmiennej będzie FinancialLevelAndDatesWithRevenue . Kodem tej zmiennej będzie nad poprzednią zmienną. Następnie utworzymy wirtualną kolumnę, która będzie Przychodem, aby zainicjować przejście kontekstu. Zatem dla każdego wiersza tej zmiennej przypisaliśmy przychód.

Ta tabela zawiera przychód, który również ma wartość zero. Musimy odfiltrować te wiersze z zerem. W tym celu możemy utworzyć kolejną zmienną i nazwać ją RemoveZeroes .

Użyjemy dla poprzedniej zmiennej, a następnie upewnimy się, że przychód nie będzie równy zeru. Może być większa od zera lub mniejsza od zera, ale nie powinna być ściśle równa zeru.

Następnie pobierzemy datę ze zmiennej RemoveZeroes . W tym celu możemy utworzyć kolejną zmienną i nazwać ją KeepOnlyDates .

Mogę użyć , aby pozbyć się zduplikowanych dat, które są zwracane przez .

Następnie zidentyfikujemy trzy najlepsze daty w kolejności malejącej. Użyjemy innej zmiennej i nazwiemy ją Last3Dates , a następnie użyjemy .

Następnie użyjemy w ciągu ostatnich trzech dat w kolumnie daty, a następnie użyjemy ogranicznika.

Przeciągnijmy i upuśćmy naszą nowo utworzoną miarę wewnątrz tej macierzy. Wynik, który otrzymujemy to 31.03.2021, 07.04.2021 i 02.04.2021. Oznacza to, że nasz środek działa.

Wróćmy do edytora i pozbądźmy się części RETURN CONCATENATEX. Aby pobrać te trzy ostatnie daty, możemy sprawdzić, które wiersze ze zmiennej RemoveZeroes są faktycznie częścią tych trzech ostatnich dat.

W tym celu utworzymy kolejną zmienną DatesInLast3Dates i użyjemy funkcji FILTER na RemoveZeroes . Spowoduje to zwrócenie tabeli zawierającej kategorię pracy i datę.

Podsumujemy kolumnę przychodów, którą utworzyliśmy w tej tabeli. Zwróćmy SUMX po DatesInLast3Dates, a następnie podsumujmy kolumnę przychodów.

Dzięki temu obliczeniu otrzymujemy tylko jedną wartość dla każdego wiersza, ponieważ usunęliśmy kategorię Stanowisko z kontekstu filtra.

Użyjemy zmiennej, którą stworzyliśmy na początku, aby sprawdzić, czy każdy wiersz zmiennej DatesInLast3Dates jest dostępny w kontekście filtru. Możemy napisać inną zmienną i nazwać ją IsInFilterContext .

Spowoduje to odfiltrowanie dat w zmiennej DatesInLast3Dates . Następnie zwrócę SUMX w kontekście IsInFilter i podsumuję kolumnę przychodów.

Gdy naciśniemy Enter , zobaczysz, że otrzymamy ten sam wynik, co na początku.

Weryfikacja wyniku obliczenia usługi LuckyTemplates języka DAX

Aby sprawdzić, czy faktycznie otrzymujemy poprawny wynik, możemy utworzyć nową tabelę CALCULATE, która pomoże nam w debugowaniu, identyfikowaniu i sprawdzaniu, czy kod, który napisaliśmy do tej pory, faktycznie zwraca poprawny wynik.

Stwórzmy kopię kodu, który napisaliśmy do tej pory. Po prostu skopiuję fragment podświetlony poniżej, wrócę, aby utworzyć nową tabelę i wkleję zaznaczony kod. Nie będziemy zawracać sobie głowy nazywaniem tej tabeli, ponieważ nie jest to dla nas interesujące.

I możemy po prostu napisać RETURN DatesInLast3Dates . Jeśli kliknę Potwierdź , otrzymamy tabelę, ale wiemy, że tak naprawdę nie zwraca ona jeszcze poprawnego wyniku, ponieważ tak naprawdę musimy naśladować zachowanie fragmentatora.

Możemy zawinąć ten kod w CALCULATETABLE i wciąć wszystko. W ostatniej części napiszę, że data powinna być większa lub równa 2021-03-15, a nazwa klienta powinna być równa DHL Supply Chain .

Tabela, którą utworzyliśmy wewnątrz tego środka, praktycznie zwraca poprawny wynik. Możemy użyć CALCULATETABLE do zweryfikowania wirtualnej tabeli, którą tworzysz w swoim obliczeniu.

Inne obliczenia usługi LuckyTemplates w języku DAX

Teraz, gdy wiemy, że nasze obliczenia działają i rozumiemy, co faktycznie dzieje się za kulisami, możemy przyjrzeć się innym metodom obliczania tego samego problemu. Przede wszystkim stwórzmy duplikat miary, którą już stworzyliśmy.

Wersja 2

Tym razem nie będziemy polegać na funkcjach VALUES i FILTER, więc się ich pozbędziemy. Usuniemy zwrócony wynik, a także ostatnią zmienną, którą utworzyliśmy wewnątrz funkcji CALCULATE. Następnie przekonwertujemy CALCULATE na CALCULATETABLE.

I zamiast zwracać wartość skalarną, tym razem zwrócimy DatesInLast3Dates . Spowoduje to zwrócenie tej samej CALCULATETABLE, którą wykonaliśmy wcześniej. Zamierzamy użyć funkcji CALCULATE, aw pierwszym argumencie obliczymy miarę przychodów i wprowadzimy CALCULATETABLE jako kontekst filtru.

Gdy przeniosę to do macierzy, zobaczysz, że zwracamy tę samą łączną wartość dla każdej komórki.

Korzystanie z funkcji KEEPFILTERS

W tej chwili wiemy, że obliczenia za kulisami działają poprawnie, ale skąd możemy mieć pewność, że dla każdej komórki podajemy tylko wartość dla tego poziomu finansowego?

Z pomocą CALCULATETABLE stworzyliśmy tabelę zawierającą poziom finansowy, kolumnę daty i kolumnę przychodów. Możemy użyć funkcji filtru klucza, aby utworzyć punkt przecięcia między kontekstem filtru istniejącym poza CALCULATETABLE i kontekstem tworzonym przez CALCULATETABLE.

Po kliknięciu Potwierdź możemy zobaczyć, że zgłaszamy poprawną wartość dla każdej komórki i że zarówno wersja 1, jak i wersja 2 zwracają poprawną wartość.

Jak to działa? Tabela CALCULATE zwróci poziom Maintenance , następnie Rental , Service External i Spare parts . Funkcja utworzy punkt przecięcia między konserwacją a tą tabelą.

Wypożyczenie utworzy początkowy kontekst filtra, następnie CALCULATETABLE zwróci wszystkie wartości kategorii Job. Następnie nastąpi przecięcie między wypożyczeniem a tabelą zwróconą przez CALCULATETABLE. Zwrócimy tylko tabele lub wiersze tylko dla tej części Wypożyczenia.

I wreszcie, kiedy wstrzykniemy SUMMARIZE do kontekstu filtra, funkcja CALCULATE obliczy przychód tylko dla wynajmu. Ten sam proces dotyczy każdego rzędu.

Wersja 3

Spójrzmy na inny sposób obliczania tych samych obliczeń przy użyciu usługi LuckyTemplates języka DAX. Utwórzmy kopię miary w wersji 2 i utwórzmy inną miarę. Nazwiemy tę wersję 3.

Ideą tego obliczenia jest to, że ponieważ obliczamy przychód za pomocą funkcji OBLICZ, nie musimy używać części ADDCOLUMNS, ponieważ duplikujemy to samo wewnątrz i na zewnątrz funkcji OBLICZ. Zamiast tego możemy po prostu napisać, że Przychód nie powinien być równy 0.

Kiedy kliknę Potwierdź, musimy upewnić się, że kod działa, więc przeciągnijmy miarę do macierzy.

Możesz zobaczyć, że kod faktycznie zwraca tę samą wartość dla każdej komórki.

Jeśli spróbuję zmienić kontekst filtru, zmieniając datę we fragmentatorze dat i wybierając kilka wartości z nazwy klienta, zobaczysz, że wszystkie trzy miary faktycznie zwracają tę samą wartość dla każdego wiersza.

Wniosek

W tym samouczku nauczyliśmy się, jak używać złożonej logiki cofania się w czasie od daty końcowej w celu zidentyfikowania trzech najważniejszych dat. W oparciu o tę technikę LuckyTemplates języka DAX mogliśmy obliczyć przychód i pokazać tylko te wartości, które istnieją w kontekście filtra. To wszystko na teraz w tym samouczku.