Power BI DAX – Detekcja poziomu hierarchii z ISINSCOPE

DAX ciągle się rozwija co do tego nie ma wątpliwości. Na moment pisania niniejszego artykułu najnowszym ucieleśnieniem tych słów jest ISINSCOPE. Funkcja ta została dodana do Power BI w aktualizacji November 2018 i wprowadza niemałą rewolucję. Ci którzy pracowali lub nadal pracują z kostkami analitycznymi i językiem MDX z całą pewnością kojarzą funkcję SCOPE i wiecie co? ISINSCOPE w niektórych przypadkach może pomóc nam podobne problemy! Jak ona działa, w czym może być użyteczna i czy różni się od takich funkcji jak ISFILTERED i ISCROSSFILTERED? Sprawdźmy to!

Wspomniana aktualizacja Power BI wprowadziła jedną z najbardziej oczekiwanych funkcjonalności jaką były “przyciski” umożliwiające poruszanie się po hierarchii w obiekcie typu matrix w analogiczny do znanej z Excela tabeli przestawnej. Oczywiście są one kolejną opcją i nie zastępują już istniejących możliwości zwijania i rozwijania. Po pierwszych testach z mojej strony naprawdę wygląda to dobrze:

Powyższy “tryb” działania matrixa to po prostu wyłączona właściwość Stepped Layout:

Po włączeniu tejże opcji otrzymamy efekt nieco podobny do kompaktowej wersji tabeli przestawnej w Excel:

Jak włączyć możliwości zwijania i rozwijania w ten sposób? Wystarczy włączyć niebudzącą zastrzeżeń co do zastosowania opcję, która może być włączona zarówno dla nagłówków wierszy jak i nagłówków kolumn:

Wszystko łatwo i przyjemnie czyli dokładnie tak jak byśmy tego oczekiwali. Zwijanie tego typu jak dobrze wiemy wiąże się z ułatwieniem nawigowania po hierarchii atrybutów. Jednym z częstych problemów z jakim się spotykam jest sprawdzenie czy też detekcja na z poziomu DAX na jakim poziomie hierarchii się znajduje. Do tej pory było kilka sposobów aby osiągnąć ten cel jak na przykład użycie wspomnianej we wprowadzeniu funkcji ISFILTERED, jednakże wiązało się to (i wiąże nadal) z kilkoma problemami, które postaram się przedstawić w dalszej części artykułu.

Spróbujmy zatem przedstawić głównego bohatera dzisiejszego show czyli ISINSCOPE. Aby ją przetestować stworzymy sobie miarę, która sprawdzi czy jesteśmy na poziomie Roku, kwartału czy może miesiąca. Sama funkcja jest bardzo prosta i przyjmuje tylko jeden parametr, którym jest kolumna – stworzyłem zatem trzy miary z użyciem tej funkcji aby sprawdzić jej działanie w matrixie:

YearIsInScope = ISINSCOPE(DimDate[CalendarYear])
QuarterIsInScope = ISINSCOPE(DimDate[CalendarQuarter])
MonthIsInScope = ISINSCOPE(DimDate[EnglishMonthName])

Po dodaniu do matrixa wszystkich pożądanych pól otrzymałem następujący rezultat :

Na pierwszy rzut oka możemy wysnuć następujące wnioski:

• Miara dla roku zwróciła TRUE na każdym z trzech poziomów,
• Miara dla kwartału zwróciła TRUE na poziomie kwartału i miesiąca,
• Miara dla miesiąca zwróciła TRUE dla poziomu miesiąca.

Widoczna jest tutaj bardzo prosta zależność jaką rządzi się ISINSCOPE – zwraca ona TRUE dla poziomu na którym znajduje się kolumna podana jako parametr oraz dla wszystkich poziomów znajdujących się poniżej. Bardzo ważne jest to aby to zapamiętać! Oczywiście nic nie stoi na przeszkodzie aby stworzoną przeze mnie miarę nieco stuningować i zmodyfikować ją w taki sposób, aby zwróciła ona TRUE tylko na określonym poziomie, a nie na danym poziomie i na podległych. Osiągniemy to w bardzo prosty sposób używając kilku operatorów logicznych – i tak dla przykładu dla roku miara wygląda następująco:

YearIsInScope =
ISINSCOPE ( DimDate[CalendarYear] )
    && NOT ISINSCOPE ( DimDate[CalendarQuarter] )
    && NOT ISINSCOPE ( DimDate[EnglishMonthName] )

Fajnie! Jednakże ktoś może powiedzieć, że nic w tym odkrywczego bo podobne rzeczy można było osiągnąć przy pomocy ISFILTERED i ISCROSSFILTERED. Porównajmy zatem te konstrukcje – poniżej możecie zauważyć zestawienie miar, które wykorzystują poszczególne funkcje. Każda z nich przyjęła jako parametr atrybut CalendarYear. Miara YearInScopeAdjusted to kod, który sprawdza poziom CalendarYear i dwóch podległych atrybutów (tak jak to zrobiliśmy wyżej), YearIsInScope to czysta implementacja ISINSCOPE:

Wygląda tak samo prawda? Różnica pojawi się w momencie gdy analogicznie sprawdzimy poziom kwartału (dla każdej miary podmieniłem analogicznie Rok na Kwartał):

Różnica pojawia się przy funkcji ISCROSSFILTERED – mamy tutaj wartość TRUE zarówno na roku jak i na kwartale. Dlaczego? Rok i kwartał należą do tej samej tabeli (DimDate), a więc filtracja jednego atrybutu filtruje inne atrybuty z tej samej tabeli. Mamy zatem różnicę między ISINSCOPE i ISCROSSFILTERED. Jaka jest różnica w stosunku do ISFILTERED? Aby to zobrazować dodajmy slicer i załóżmy filtr na kwartał:

Po przefiltrowaniu po atrybucie będącym argumentem funkcji ISFILTERED i ISCROSSFILTERED pokazują już całkowicie inne rezultaty niż ISINSCOPE. Dlaczego? ISFILTERED zwraca TRUE bo kolumna rzeczywiście została przefiltrowana więc funkcja działa zgodnie z oczekiwaniami, skoro ISFILTERED zwraca TRUE to ISCROSSFILTERED tym bardziej (filtracja bezpośrednia zawsze będzie odbierana przez ISCROSSFILTERED, odwrotna sytuacja tj. filtracja “krzyżowa” nie będzie odbierana przez ISFILTERED). Slicery i filtracja z ich pomocą skutecznie utrudniała detekcję poziomu hierarchii do tej pory – na szczęście jak widzicie pojawiła się nowa alternatywa.Mam nadzieję, że na podstawie tego prostego przykładu zrozumieliście różnicę pomiędzy tymi funkcjami.

Zmierzając powoli ku końcowi możemy zadać sobie pytanie czy ISINSCOPE jest pozbawiona wad? Oczywiście, że nie – głównym problemem jest to, że nie jest ona dostatecznie dynamiczna ze względu na fakt, że jeśli chcemy osiągnąć efekt taki jak w mierze IsInScopeAdjusted to musimy sprawdzić każdy niżej położony poziom hierarchii. Czyli w gruncie rzeczy musimy znać konkretny scenariusz raportowy co czasem jest możliwe, a czasem nie. Bo co się stanie jeżeli w przedstawionym przeze mnie przykładzie niespodziewanie zamiast miesiąca w matrixie ktoś użyje innego atrybutu? Oczywiście ISINSCOPE zwróci TRUE również na poziomie produktu ponieważ nie uwzględniliśmy go w naszej kalkulacji. Mimo wszystko jest to bardzo dobre ulepszenie, które sprawdzi się idealnie w sytuacjach gdzie znana jest hierarchia i znany jest sposób nawigowania po poziomach (przydatne jest tworzenie predefiniowanych hierarchii zamiast dawania użytkownikom całkowitej dowolności w przeciąganiu atrybutów na matrixa). Na moment pisania tego artykułu możemy użyć ISINSCOPE w Azure Analysis Services oraz w Power BI, być może kiedyś zobaczymy tą funkcję w Power Pivot oraz w on-premise Analysis Services, przynajmniej mam taką nadzieję. Póki co należy się cieszyć z tego, że pojawiła się funkcjonalność pozwalająca na detekcję poziomu hierarchii w zdecydowanie lepszy sposób niż dotąd.