Funkcja CALCULATE języka DAX: problemy i rozwiązania

W dzisiejszym blogu zbadamy, dlaczego nie należy używać funkcji DAX w celu uzyskania średniej dla kategorii i przedstawimy kilka alternatywnych rozwiązań pozwalających uzyskać takie same wyniki, jakich oczekujesz. Możesz obejrzeć pełny film tego samouczka na dole tego bloga .

Uzyskanie średniej dla kategorii w języku DAX jest dziwnie skomplikowane. Używając funkcji CALCULATE do rozwiązywania tych obliczeń, nowi użytkownicy języka DAX mogą uznać kod DAX za trudniejszy do zrozumienia niż powinien. Dobra wiadomość jest taka, że ​​istnieją prostsze rozwiązania tego problemu. 

Spis treści

• Omówienie funkcji CALCULATE języka DAX
• OBLICZ Formułę DAX
• Kod DAX dla średniej dla kategorii
• W czym problem?
• Łatwiejsze rozwiązanie
  • Korzystanie z funkcji PODSUMOWANIE
  • Korzystanie z funkcji GROUPBY
• Porównywanie wyników: funkcje języka DAX SUMMARIZE vs GROUPBY vs CALCULATE
• Wniosek

Omówienie funkcji CALCULATE języka DAX

Kilka miesięcy temu prezentowałem na dla LuckyTemplates. Nazywało się to Kontrkulturą DAX, gdzie przedstawiłem kilka alternatywnych poglądów na temat DAX. 

Jeden z głównych dotyczy użycia funkcji OBLICZ. Mówiłem o tym, dlaczego nie powinieneś używać CALCULATE, zwłaszcza jeśli jesteś nowy w DAX. 

OBLICZ jest naprawdę złożoną funkcją do zrozumienia i użycia. Przyjmuje pewne założenia dotyczące twojego modelu danych i może wpędzić cię w wiele kłopotów. 

Zagłębiłem się również w szybką miarę Microsoftu dla Running Total, ponieważ nie działa ona w sytuacjach z jedną tabelą i potrzebuje schematu gwiaździstego, aby działała poprawnie.

Pokazałem więc łatwiejszą alternatywę, która działa niezależnie od tego, czy jest to schemat gwiazdy, czy model z pojedynczą tabelą bez potrzeby CALCULATE. 

OBLICZ Formułę DAX

Ten blog zajmie się szybką miarą o nazwie Średnia na kategorię . Jeśli korzystasz z programu LuckyTemplates Desktop, zacznij od przejścia do okienka Pola i utworzenia nowej szybkiej miary .

W oknie Quick Measure, które się pojawi, wybierz Średnia dla kategorii z listy rozwijanej Obliczenia . Następnie przeciągnij ? Wartość w polu tekstowym Wartość podstawowa i Miesiąc w polu Kategoria .

Następnie kliknij OK , a otrzymasz wizualizację, która wygląda tak. 

Na osi x znajdują się ćwiartki — kwartał 1, 2, 3 i 4. 

A to, co chcesz zrobić, to zsumować wartości dla każdego miesiąca pokazanego poniżej, a następnie wziąć średnią z tych wartości. W skrócie Średnia na kategorię, która jest tematem naszego bloga. 

Nasz model danych jest bardzo prosty. Mamy tabelę z datami , wartościami generowanymi losowo oraz miesiącami , sortowaniem miesięcy i kwartałami w osobnych kolumnach. 

I mamy to samo w naszej tabeli dat z tabelą kalendarza, miesiącem, sortowaniem miesięcy i kwartałem. Oznacza to, że możemy wybrać kwartał lub miesiąc z tabeli dat lub z pierwszej tabeli. 

Dobrą wiadomością dotyczącą średniej dla kategorii jest to, że faktycznie działa niezależnie od tego, czy masz model pojedynczej tabeli, czy schemat gwiaździsty.

W tych wizualizacjach używamy kwartału z pierwszej tabeli…

 …podczas gdy ten używa kwartału z naszej tabeli dat. To działa w obie strony. 

Ale problem ze średnią na kategorię to po prostu kod DAX. 

Ten kod DAX jest dziwny, ponieważ nawet jeśli jesteś ekspertem od języka DAX, prawdopodobnie na pierwszy rzut oka wprawi Cię to w zakłopotanie. Jest dziwnie skonstruowany z instrukcją CALCULATE bez klauzuli filter i po prostu zawija instrukcję sum. 

W rzeczywistości jest to jedna z tych moich ulubionych irytacji, kiedy widzę ludzi na forach, którzy używają funkcji OBLICZ, która po prostu zawija sumę bez powodu. Ale w tym przypadku rzeczywiście ma to swój powód.

Za każdym razem, gdy je widzę, wprawia mnie to w zakłopotanie, ponieważ jest to po prostu natychmiastowa wskazówka, że ​​nie mają pojęcia, co robią z funkcją OBLICZ. Więc zamiast tego radzę trzymać się od tego z daleka. 

Kod DAX dla średniej dla kategorii

Pokazany powyżej kod DAX używa . Ta funkcja nie znajduje się w segmencie CALCULATE, co znowu czyni ją dziwną. Podobno ta funkcja jest zbudowana do użycia w instrukcjach CALCULATE na podstawie jej dokumentacji.

Kod używa również , z czym się nie zgadzam. Nigdy nie powinni używać WARTOŚCI, ponieważ różne wartości mają tendencję do zwracania pustego wiersza, jeśli występuje niedopasowany wiersz. To może wpędzić cię w wiele kłopotów, ale to temat na inny film.

Zasadniczo chodzi o to, że używają funkcji WARTOŚCI, aby pobrać wszystkie nasze kategorie. Na przykład w pierwszym kwartale są to styczeń, luty i marzec. VALUES również zwraca tabelę, ale używa instrukcji KEEPFILTER, aby była ważna jako pierwszy parametr w . 

Zachowują również funkcję CALCULATE do wykonania w kontekście KEEPFILTERS. Ze względu na sposób działania AVERAGEX bierze drugie wyrażenie i wykonuje je w kontekście pierwszego wyrażenia. 

W czym problem?

Ogólnie rzecz biorąc, działa dobrze, ale nie dla kogoś nowego w DAX. Uważam, że Microsoft naprawdę nie trafił w sedno, kiedy tworzył ten szybki środek, ponieważ szybkie miary są podobno przeznaczone dla osób, które nie znają języka DAX.

Szybkie miary to świetny pomysł, na przykład „Napiszmy dla ciebie kilka typowych miar z różnymi obliczeniami, ponieważ dopiero zaczynasz korzystać z języka DAX i nie wiesz wszystkiego o języku DAX”. Ale dlaczego mieliby je konstruować w tak dziwny, skomplikowany sposób? 

Jak ktoś nowy w DAX ma na to spojrzeć i dowiedzieć się, co się dzieje, skoro nawet prawdopodobnie profesjonaliści DAX trochę drapią się po głowach nad tym? 

Więc dla mnie przegapili doskonałą okazję, aby pomóc ludziom nauczyć się języka DAX za pomocą ich szybkich środków, nalegając, aby zawierały CALCULATE i musiały przeskakiwać przez obręcze, aby uzyskać tam CALCULATE. 

Łatwiejsze rozwiązanie

Korzystanie z funkcji PODSUMOWANIE

Jak wspomniałem wcześniej, jest na to lepszy i prostszy sposób.

Najpierw utwórz zmienną tabeli za pomocą VAR_Table . Następnie użyjemy polecenia , aby pobrać tabelę, podsumować ją według miesiąca, utworzyć kolumnę Wartość i zsumować nasze wartości.

Na koniec weźmiemy średnią naszych wartości za pomocą funkcji ŚREDNIA . 

Ten kod jest prostszy i o wiele bardziej logiczny. Nie zawiera funkcji CALCULATE, której i tak nie potrzebujesz. 

Możesz natknąć się na artykuł na blogu krytykujący SUMMARIZE. Blog mówi, że sposób, w jaki SUMMARIZE działa wewnętrznie, jest dość skomplikowany.

Sugeruje również, że możesz wpaść w kłopoty w jednym konkretnym przypadku, ale nigdy na to nie wpadniesz. Stałoby się tak tylko wtedy, gdyby twoje obliczenia obejmowały bardzo złożone obliczenia i bardzo dużą tabelę. Tylko wtedy SUMMARIZE da Ci niepewne rezultaty. 

Przynajmniej tak twierdzi artykuł na blogu. I to jest w porządku, jeśli chcesz unikać SUMMARIZE. W takim przypadku użyj zamiast tego funkcji .

Korzystanie z funkcji GROUPBY

Nikt nie ma problemu z używaniem funkcji GROUPBY, więc nauczmy się też jej używać. Ponownie utwórz zmienną tabeli za pomocą VAR_Table. Następnie GROUPBY Month i tworzymy kolumnę Value.

Następnie stosujemy przy użyciu CURRENTGROUP , tak działa GROUPBY. Podsumujmy naszą Wartość i ponownie przeprowadźmy przez nią ŚREDNI . 

Nasz kod powinien wyglądać tak.

Porównywanie wyników: funkcje języka DAX SUMMARIZE vs GROUPBY vs CALCULATE

Zobaczmy teraz, czy skróty, które zrobiliśmy, zwracają te same wyniki. 

Jak widać na poniższych obrazkach, zarówno SUMMARIZE oznaczone jako Lepsza średnia na kategorię, jak i GROUPBY oznaczone jako Lepsza średnia na kategorię 2 zwracają te same liczby. 

Obie nasze formuły zwracają 3,4 tys. dla pierwszego kwartału, 3,6 tys. dla drugiego kwartału, 3,4 tys. dla trzeciego kwartału i 3,5 tys. dla czwartego kwartału. I znowu pracują w modelu danych z jedną tabelą, używając Quarters dla naszej tabeli.

Działają również w schemacie gwiaździstym, w którym używamy kwartałów w naszej tabeli dat. 

Szybkie środki — jak ich używać w modelach LuckyTemplates
Korzystanie z funkcji Calculate w LuckyTemplates
Skuteczne sposoby korzystania z funkcji GROUPBY w LuckyTemplates

Wniosek

Nie musimy zmuszać się do stosowania pokręconej logiki kontekstu, aby wprowadzić funkcję CALCULATE do naszych formuł. Wystarczy użyć prostych standardowych funkcji języka DAX, aby osiągnąć te same cele. 

W rzeczywistości prawdopodobnie w 80% – 90% przypadków nie ma absolutnie żadnego powodu, aby zawracać sobie głowę używaniem funkcji CALCULATE do czegokolwiek. Zamiast tego możesz użyć funkcji SUMMARIZE i GROUPBY, które są prostsze i bardziej logiczne.

Jeśli chcesz zapoznać się z tym plikiem PBIX, już go opublikowałem w galerii Quick Measures i nazwałem plik Better Average Per Category . Po prostu przewiń stronę w dół, aby znaleźć plik PBIX, który możesz pobrać i grać ze sobą. 

Wszystkiego najlepszego,

Grega Decklera