Rozszerzone tabele w LuckyTemplates

W tym samouczku przyjrzymy się bardzo konkretnemu scenariuszowi, z którym na pewno każdy z Was spotkał się podczas pracy z językiem DAX — rozwiniętą tabelą . Rozwinięte tabele mogą zepsuć obliczenia, jeśli nie wiesz, jak one faktycznie działają. Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

W tym przykładzie używamy bazy danych firmy Contoso, która zawiera tabelę sprzedaży , tabelę klientów , tabelę dat , tabelę produktów , tabelę kategorii produktów i tabelę podkategorii produktów .

Po lewej stronie końcowej mamy relację jeden do wielu między produktami, kategorią, podkategorią i tabelami sprzedaży. Mamy również relację jeden-do-wielu między klientem a sprzedażą, a także daty i sprzedaż.

Spis treści

• Przykład 1
• Przykład nr 2
• Czerwona sprzedaż nr 1
• Czerwona sprzedaż nr 2
• Czerwona sprzedaż nr 3
• Weryfikacja rozszerzonych tabel
• Definiowanie rozszerzonych tabel
• Krojenie kolumny kategorii i używanie rozszerzonych tabel
• Wyjaśnienie czerwonej sprzedaży 2
• Wyjaśnienie czerwonej sprzedaży 3
• Używanie DAX Studio do weryfikacji rozszerzonych tabel
• Wniosek

Przykład 1

Załóżmy, że chcemy utworzyć raport z tej kolumny kategorii w tabeli kategorii i użyć miary, która oblicza liczbę klientów w tabeli klientów. Chcemy podzielić numer na stół klienta według kategorii.

Jeśli ustawię filtr na tabeli kategorii, filtr ten trafi do tabeli podkategorii, następnie dotrze do tabeli produktów, a na końcu dotrze i przefiltruje tabelę sprzedaży. Ale ten filtr nie będzie mógł przefiltrować tabeli klienta, chyba że włączymy filtrowanie dwukierunkowe.

Wróćmy do raportu, aby naprawić to obliczenie. Możemy umieścić to obliczenie w funkcji CALCULATE, a następnie napisać Sales. Widzimy, że tym razem nie powtarzamy tej samej liczby dla każdej komórki wizualizacji.

Aby zweryfikować, czy to obliczenie jest poprawne, możemy przenieść klucz klienta z tabeli sprzedaży do agregacji, a następnie wykonać na nim Count (Distinct).

Możesz zobaczyć, że zwracamy tę samą wartość dla każdego wiersza. Korzystając z tabeli sprzedaży wewnątrz funkcji CALCULATE, byliśmy w stanie naprawić obliczenia.

Przykład nr 2

Przejdźmy do następnego przykładu. W tym przypadku chcemy określić wielkość sprzedaży produktów czerwonych w 2007, 2008 lub 2009 roku. W zależności od wyboru krajalnicy, chcę również cofnąć się o rok.

Na przykład, jeśli wybiorę rok 2009, chcę zgłosić sprzedaż w kolorze czerwonym za rok 2008. Jeśli wybiorę rok 2008, chcę również zgłosić sprzedaż w kolorze czerwonym za rok 2007.

Czerwona sprzedaż nr 1

Obliczenie naszej całkowitej sprzedaży jest w zasadzie sumą danych dotyczących sprzedaży. W kontekście wiersza pomnożymy ilość przez cenę netto.

Utwórzmy nową miarę i nazwijmy ją Red Sales. Napiszemy CALCULATE, a następnie Total Sales. Zamierzamy tabelę Sales, aby pokazać, że kolor produktów jest równy czerwieni.

Następnie nad datami w kolumnie daty napiszemy

Kiedy wprowadzamy te obliczenia do wizualizacji karty, otrzymujemy puste miejsce.

Czerwona sprzedaż nr 2

Co tu się dzieje? Spróbujmy przepisać to obliczenie i zobaczyć, czy uda nam się uzyskać wynik. Stworzymy nową miarę i nazwiemy ją Red Sales 2 i użyjemy pierwszej części obliczeń dla Red Sales 1.

Zamierzamy zainicjować kolejny CALCULATE nad pierwszym CALCULATE.

Wprowadźmy tę miarę do macierzy i zobaczmy wyniki. Jeśli wybierzemy rok 2008, otrzymamy 51 947. Jeśli wybierzemy rok 2009, otrzymamy 24 343. Wreszcie, jeśli wybierzemy rok 2010, otrzymamy 39 724.

Czerwona sprzedaż nr 3

Istnieje inny sposób zapisania tego obliczenia. Napiszemy nową miarę i nazwiemy ją Red Sales 3, a następnie użyjemy funkcji CALCULATE.

Zamierzamy obliczyć całkowitą sprzedaż i zapisać, że dla produktów kolor jest równy czerwieni. Następnie użyj funkcji SAMEPERIODLASTYEAR na datach.

Jeśli przeciągniemy miarę nr 3 do wizualizacji karty, zobaczysz, że te dwie wartości karty zwracają tę samą wartość, co jest poprawne.

Ale coś jest nie tak z naszym pierwszym obliczeniem, ponieważ zwracamy puste miejsce, a nie poprawną wartość.

Zobaczmy, co tak naprawdę się tutaj dzieje. Teraz, gdy widzieliśmy już kilka obliczeń na rozwiniętych tabelach, poznajmy teorię, która za nimi stoi.

Zanim zrozumiesz, czym jest tabela rozszerzona, musisz zrozumieć, że wszystkie tabele, które tu mamy, nazywane są tabelami podstawowymi .

Kiedy więc te tabele stają się tabelami rozwiniętymi? Po utworzeniu relacji wiele do jednego między jedną a drugą tabelą tabela podstawowa staje się tabelą rozwiniętą.

Weryfikacja rozszerzonych tabel

Ale jak możemy zweryfikować, czy rozszerzenie tabeli rzeczywiście ma miejsce? Cóż, możesz użyć powiązanego słowa kluczowego w dowolnej tabeli. Jeśli masz dostęp do kolumny z jednej strony, będziesz wiedział, że może nastąpić rozszerzenie tabeli.

Przejdźmy do tabeli sprzedaży i utwórzmy nową kolumnę obliczeniową.

Powiedzmy, że chcemy pobrać kolor produktu z tabeli produktów dla tego konkretnego klucza produktu. Użyjemy RELATED, które poda tylko listę kolumn w IntelliSense, które faktycznie można rozwinąć z tabeli sprzedaży.

Widzimy, że tabele klientów i tabele sprzedaży mają relację wiele do jednego. Możemy również zobaczyć listę kolumn tabeli klientów, kolumn tabeli dat i kolumn produktów.

Wybierając Produkty[Kolor], możemy utworzyć nową kolumnę w tabeli sprzedaży za pomocą słowa kluczowego RELATED. RELATED daje nam dostęp tylko do tych kolumn tabeli, do których tabela bazowa faktycznie może się rozwinąć.

Jeśli zmienimy charakter tej relacji z wielu do jednego na wiele do wielu, to obliczenie przestanie działać.

Zmieńmy charakter tej relacji na wiele-do-wielu. Na dole widzimy, że otrzymujemy symbol ostrzegawczy.

Gdy wracamy do tabeli sprzedaży, pojawia się komunikat o błędzie informujący, że kolumna Products[Color] albo nie istnieje, albo nie ma związku z żadną tabelą.

W obecnym kontekście ostrzeżenie, które otrzymujemy, nie jest zbyt przyjazne dla użytkownika. Zasadniczo oznacza to, że tabeli sprzedaży nie można rozwinąć do tabeli produktów, ponieważ nie jesteśmy w stanie uzyskać dostępu tylko do jednej wartości dla tego konkretnego wiersza.

A ponieważ używamy relacji wiele do wielu, rozwinięcie tabeli nie ma miejsca, a RELATED nie działa.

Wróćmy do widoku diagramu i poprawmy to obliczenie. Zmienimy charakter relacji na wiele do jednego i aktywujemy tę relację, aby obliczenia działały.

Definiowanie rozszerzonych tabel

Zanim zaczniemy przeglądać obliczenia, które już wykonaliśmy w raporcie, powtórzmy definicję rozszerzonej tabeli.

Jeśli masz model danych ze schematem gwiaździstym , tabela faktów zostanie rozwinięta do wszystkich tabel w modelu danych, jeśli istnieje relacja wiele do jednego między wymiarami a tabelą faktów.

Jeśli masz schemat płatka śniegu , wówczas tabela podkategorii produktów i tabela kategorii zostaną rozwinięte do tabeli podstawowej, która w tym przypadku jest tabelą produktów. Tabela sprzedaży jest tabelą bazową, która rozszerza się na wszystkie inne tabele.

Za sceną tabela sprzedaży będzie zawierała wszystkie kolumny w jednej tabeli. Zauważ, że rozszerzenie tabeli jest tylko logiczną koncepcją, więc nie spowoduje rozszerzenia i zwiększenia rozmiaru twojego modelu danych.

Rozwinięta tabela pojawia się tylko wtedy, gdy odwołujesz się do tabeli (która jest tabelą podstawową) i gdy masz relację wiele do jednego z innymi tabelami.

Krojenie kolumny kategorii i używanie rozszerzonych tabel

Spróbujmy naprawić obliczenia, które już wykonaliśmy w widoku raportu. W tym przykładzie wycinamy według kolumny kategorii z tabeli kategorii produktów i próbujemy policzyć, ilu jest klientów.

Więc co my tu właściwie robimy? Używamy rozszerzonej tabeli sprzedaży jako odniesienia. Gdy kontekst filtra zawiera wartość z tabeli kategorii, filtr ten trafi do tabeli sprzedaży z produktów podkategorii i bezpośrednio do sprzedaży.

Ponieważ sales jest tabelą rozszerzoną i używamy tego odwołania w funkcji CALCULATE, tabela sales będzie również zawierać kolumnę tabeli klientów. Kiedy stosujemy filtr w tabeli sprzedaży, pośrednio filtrujemy również tabelę klientów.

Wróćmy do naszych czerwonych kalkulacji sprzedaży i spróbujmy zrozumieć, co tak naprawdę się dzieje. Zaczniemy od pustych obliczeń. Jeśli wybierzemy tę miarę, zobaczysz, że piszemy zagnieżdżony kod. Mamy CALCULATE, następnie Total Sales, FILTER ponad sprzedażą i SAMEPERIODLASTYEAR.

Rozbijmy tę kalkulację krok po kroku. Najpierw musimy zidentyfikować zewnętrzny kontekst filtra, który istnieje poza obliczeniami.

Wybraliśmy rok 2008 w krajalnicy numeru roku kalendarzowego.

Z tego kontekstu filtra zostanie oceniona tabela sprzedaży. Tabela sprzedaży będzie zawierała tylko wiersz dla numeru roku kalendarzowego 2008, a kolor produktu będzie czerwony. Mamy tutaj dwa filtry: jeden tworzony przez kontekst filtra i drugi tworzony przez filtr .

SAMEPERIODLASTYEAR jest oceniany w kontekście filtra, w którym rok to 2008. Otrzyma listę dat w 2008 i przesunie te daty w 2008 na 2007. Tabela, która zostanie zwrócona przez this, będzie zawierała tylko terminy na 2007r.

Po zakończeniu tych dwóch operacji funkcja CALCULATE przygotowuje kontekst filtru i stosuje te dwa filtry w kontekście filtru. Kiedy to zastosujemy, mamy kontekst filtra, w którym kolor produktu jest równy czerwieni, aw kolumnie rok mamy filtr obejmujący lata 2007 i 2008.

Tak więc dla tego modelu danych nie ma jednej transakcji, która istnieje w dwóch różnych latach. Kiedy CALCULATE spróbuje połączyć te dwa filtry w warunku i , powie, że rok powinien być 2008 i 2007, a kolor produktu powinien być czerwony.

Kiedy filtrujemy tabelę sprzedaży z kontekstem filtra roku 2008, pośrednio filtrujemy również tabelę dat. Stabilna sprzedaż ma relację wiele do jednego do stabilnej daty.

Utwórzmy nowe obliczenie, aby określić, ile wierszy znajduje się w tabeli dat dla powiązanych dat. Zamierzamy napisać CALCULATE, a następnie tabeli dat. Następnie przefiltrujemy WSZYSTKO z tabeli sprzedaży i powiemy, że POWIĄZANE daty w numerze roku kalendarzowego powinny być równe 2008.

Wewnątrz CALCULATE nie odnieśliśmy się do żadnego filtra w tabeli dat. Po prostu sprawdzamy, czy daty w numerze roku kalendarzowego powinny pochodzić z 2008 roku. Idealnie, ten filtr nie powinien umożliwiać filtrowania tabeli dat. Używamy również funkcji , która zignoruje kontekst filtra, który będzie pochodził z fragmentatora.

Utwórzmy nową kartę do tego obliczenia. widać, że zwracamy 348 wierszy.

Jak to więc możliwe, że z tabeli dat zawierającej 2500 wierszy zwracamy tylko 348 wierszy? Jeśli zamierzamy użyć rozszerzonej tabeli, będziemy pośrednio filtrować również drugą tabelę, która jest połączona relacją wiele do jednego.

Chociaż nie mamy żadnego filtra w bieżącym roku, nadal ograniczamy liczbę wierszy widocznych dla tabeli wymiarów, którą mamy po jednej stronie.

Gdy zwracamy tabelę sprzedaży, zwracamy również przefiltrowaną wersję tabeli dat, tabeli klientów, tabeli produktów, tabeli kategorii produktów i tabeli podkategorii produktów.

Wyjaśnienie czerwonej sprzedaży 2

Przejdźmy do następnego obliczenia, którym jest Red Sales 2. Zaczniemy od zewnętrznego CALCULATE, ponieważ jeśli zagnieżdżamy tę funkcję w dowolnym scenariuszu, zewnętrzny CALCULATE powinien przygotować kontekst filtra dla wewnętrznego CALCULATE.

Na krajarce wybieramy rok kalendarzowy 2008. Kiedy funkcja SAMEPERIODLASTYEAR otrzyma datę 2008, przesunie te daty w 2007 i będzie to kontekst FILTR dla obliczenia wewnętrznego. Ten wewnętrzny OBLICZ oceni rok 2007.

Funkcja FILTR przefiltruje tabelę sprzedaży, a tabela sprzedaży zostanie ograniczona tylko do wiersza, w którym jest rok 2007. Gdy mamy już wiersze dla roku 2007, sprawdzimy, czy powiązane produkty są równe czerwonym.

W przeciwieństwie do pierwszej kalkulacji, nie zwracamy dwóch różnych poziomów. Te dwa różne poziomy nie są stosowane w kontekście FILTRUJ, więc tabela zwrócona przez funkcję FILTRUJ będzie zawierać cały wiersz tabeli sprzedaży.

Po zastosowaniu tego filtra otrzymamy kwotę sprzedaży za rok 2007 oraz produkty, które są równe czerwieni, podczas gdy w pierwszym obliczeniu zwracaliśmy rok 2008 i 2007.

Tym razem przygotowaliśmy kontekst filtru dla tabeli sprzedaży, używając funkcji SAMEPERIODLASTYEAR i zagnieżdżając funkcję CALCULATE w innej funkcji CALCULATE.

Wyjaśnienie czerwonej sprzedaży 3

W trzecim przykładzie mamy bardzo prostą funkcję CALCULATE z dwoma stwierdzeniami: jedna mówi, że kolor produktu jest równy czerwieni, a następnie SAMEPERIODLASTYEAR na datach.

Funkcja SAMEPERIODLASTYEAR zostanie oceniona w kontekście filtra, który będzie zawierał tylko daty z 2007 r. Kolor produktów zastosuje czerwony kontekst filtra do tabeli produktów, który z kolei przefiltruje tabelę sprzedaży i będzie zawierał tylko wiersze dla produkty czerwone.

Gdy te dwie wartości zostaną zastosowane w kontekście filtru, będą filtrować tabelę sprzedaży, ale nie rozszerzoną tabelę sprzedaży. widać, że nigdzie w kodzie nie odwołujemy się do rozszerzonej tabeli sprzedaży.

Jest to idealny scenariusz, którego należy użyć, jeśli próbujesz odwołać się do rozwiniętej tabeli. Podczas korzystania z rozszerzonej tabeli obliczenia mogą stać się naprawdę zawiłe, a czasami nie jesteś w stanie określić, dlaczego obliczenia zwracają nieprawidłowy wynik.

Jeśli nie rozumiesz koncepcji rozszerzonych tabel, możesz opracować ten konkretny kod jako miarę modelową, a następnie wdrożyć go w środowisku produkcyjnym. Inni użytkownicy mogą zacząć tworzyć inne miary na podstawie tej miary i mogą nie być w stanie zrozumieć, dlaczego obliczenia nie działają, ponieważ po prostu nie posiadają tego poziomu zrozumienia języka DAX.

Jeśli pracujesz z językiem DAX, zawsze staraj się umieścić filtr na pojedynczej kolumnie, ponieważ w przypadku korzystania z pojedynczej kolumny koncepcja rozszerzonych tabel nie jest stosowana. Na przykład, gdy użyjesz PRODUCTS [Color] = „Red” , filtr dociera do tabeli sprzedaży, ale ten filtr nie może dotrzeć do tabeli klienta, ponieważ nie używamy rozszerzonej tabeli sprzedaży.

Używając pojedynczej kolumny, nie odwołujemy się do koncepcji rozszerzonych tabel. Po prostu stosujemy podstawowy filtr w tabeli sprzedaży, który nie rozprzestrzenia się na inne tabele.

Używanie DAX Studio do weryfikacji rozszerzonych tabel

Zanim zakończymy, użyjemy DAX Studio do zweryfikowania naszego kodu dla Red Sales 3. Przejdźmy do zakładki Widok, kliknij Performance Analyzer, a następnie rozpocznij nagrywanie. Odświeżymy tę wizualizację i wybierzemy pustą kartę.

Z narzędzi zewnętrznych uruchomimy studio DAX

Widzimy, że na ekranie wyników na dole mamy puste miejsce.

Włączymy chronometraż serwera, aby zrozumieć zapytania generowane za kulisami. Przejdziemy do karty Czasy serwera i rozwiniemy ją, aby zobaczyć wszystko.

Wykonujemy JOIN na stole sprzedaży po lewej stronie. Wykonujemy również JOIN na kluczu produktu z tabeli produktów.

W klauzuli WHERE jest napisane, że daty w numerze roku kalendarzowego powinny być równe 2008, a kolor produktu powinien być czerwony.

Następnie mamy inny warunek, który mówi Dates[Date], a następnie zakres dat. Więc jakie są te daty? Idealnie daty te muszą należeć do 2008 r., ale pochodzą z 2007 r., kiedy zmieniamy formatowanie tych liczb w Excelu.

Dlatego zwracamy pustą wartość. Mówimy, że rokiem kalendarzowym powinien być rok 2008 i stosując warunek AND, że daty powinny przypadać na rok 2007. Ten rok 2007 wynika z funkcji SAMEPERIODLASTYEAR, której użyliśmy w obliczeniach.

Zmieńmy miarę i odwołanie do Red Sales 2. Po wykonaniu kodu widać, że pobieramy daty z tabeli dat, następnie filtrujemy kolumnę dat pod kątem numeru roku kalendarzowego 2008.

Tym razem jesteśmy w stanie zwrócić wynik, ponieważ nie stosujemy filtru w klauzuli WHERE i mówimy, że daty w numerze roku kalendarzowego powinny być równe 2008.

Gdzie znaleźć ukrytą pułapkę języka DAX
Zliczanie klientów w czasie przy użyciu DISTINCTCOUNT w usłudze LuckyTemplates
Analiza nowych klientów przy użyciu zaawansowanego języka DAX

Wniosek

Mamy nadzieję, że ten samouczek był pomocny w zrozumieniu, czym jest rozszerzona tabela i jak może zepsuć obliczenia. Rozszerzone tabele nie są tak naprawdę intuicyjne ani łatwe do zrozumienia.

Aby uzyskać więcej informacji na temat rozszerzonych tabel, nie zapomnij zasubskrybować kanału telewizyjnego LuckyTemplates. Cały czas publikujemy ogromną ilość treści ode mnie i wielu twórców treści, których celem jest ulepszenie sposobu, w jaki korzystasz z usługi LuckyTemplates i Power Platform.