Różnice między ALL, ALLEXCEPT i ALLSELECTED na przykładach w Excelu

Kontekst filtrowania w DAX – krótkie przypomnienie podstaw

Różnica między kontekstem wiersza a kontekstem filtrowania w Power Pivot

DAX liczy miary zawsze w pewnym kontekście. W praktyce chodzi o dwa główne rodzaje: kontekst wiersza i kontekst filtrowania.

Kontekst wiersza pojawia się przede wszystkim w obliczanych kolumnach. Dla każdego wiersza tabeli formuła widzi tylko dane z tego jednego rekordu. Przykładowo, w tabeli Sprzedaż obliczona kolumna =Sprzedaż[Qty] * Sprzedaż[Price] działa w kontekście aktualnego wiersza i nie „wie” nic o innych wierszach.

Kontekst filtrowania działa inaczej. Określa, które wiersze są brane pod uwagę w agregacjach (SUM, COUNT, AVERAGE itd.). Pochodzi z filtrów na modelu danych: z tabel przestawnych, fragmentatorów (slicerów), pól w wierszach/kolumnach, a także z funkcji DAX takich jak CALCULATE, FILTER czy właśnie ALL, ALLEXCEPT, ALLSELECTED.

Miary są liczone zawsze w kontekście filtrowania. Właśnie manipulacja tym kontekstem decyduje, czy wyjdzie „% od całości”, „% w kategorii”, czy „% w wybranym zakresie”. Dlatego przy ALL, ALLEXCEPT i ALLSELECTED kluczowe jest zrozumienie, jakie filtry są w danej chwili aktywne i które z nich funkcja usuwa lub zachowuje.

Jak filtry z tabel przestawnych, fragmentatorów i kolumn wpływają na miary

W Excelu najczyściej kontekst filtrowania powstaje z:

• ustawionych pól w wierszach/kolumnach tabeli przestawnej,
• pól przeciągniętych do obszaru filtrów,
• fragmentatorów (slicerów) połączonych z tabelą przestawną,
• filtrów na poziomie poszczególnych pól (strzałki filtrów przy nagłówkach w tabeli przestawnej).

Jeśli w wierszach masz np. DimProdukt[Kategoria], to dla każdego wiersza tabeli kontekst filtrowania obejmuje tylko wiersze tabeli faktów (Sprzedaż) powiązane z daną kategorią. Miara [Suma Sprzedaży] liczy wtedy SUM tylko dla tej jednej kategorii.

Gdy dodasz fragmentator z DimData[Rok], każdy wybór roku dodatkowo zawęża kontekst filtrowania. Miara widzi tylko te sprzedaże, które spełniają oba warunki: wybraną kategorię i wybrany rok. Tak powstaje to, co w raporcie użytkownik widzi jako „aktualny widok danych”.

Rola funkcji CALCULATE w modyfikowaniu kontekstu

CALCULATE jest centralną funkcją DAX do zmiany kontekstu filtrowania. Jej uproszczone działanie można opisać tak: weź bieżący kontekst, zmodyfikuj go zgodnie z dodatkowymi filtrami i policz wyrażenie.

Przykład:

[Sprzedaż 2024] := CALCULATE( [Suma Sprzedaży]; DimData[Rok] = 2024 )

Jeśli raport jest już przefiltrowany na lata 2023–2025, CALCULATE „zastępuje” filtr na kolumnie DimData[Rok] na wartość 2024. Pozostałe filtry (np. region, kategoria) pozostają bez zmian. Tak samo działają ALL, ALLEXCEPT, ALLSELECTED – ale zamiast ustawiać konkretną wartość, zmieniają zakres filtrów (usuwają część, zachowują część lub biorą pod uwagę wybrane przez użytkownika fragmenty danych).

Z praktycznego punktu widzenia prawie wszystkie sensowne użycia ALL/ALLEXCEPT/ALLSELECTED są wewnątrz CALCULATE. To tam decydujesz, który kontekst jest „przekręcony” na potrzeby liczenia procentów, rankingów lub wartości całkowitych.

Prosty przykład: suma sprzedaży z i bez filtru roku

Załóżmy model:

• tabela faktów Sprzedaż z kolumnami: [Kwota], [IdProduktu], [IdDaty],
• wymiar DimData z kolumną [Rok], połączony z Sprzedaż[IdDaty].

Miara bazowa:

[Suma Sprzedaży] := SUM( Sprzedaż[Kwota] )

Jeśli w tabeli przestawnej w wierszach ustawisz DimData[Rok], dla każdego roku zobaczysz sumę sprzedaży tylko za ten rok. Dla całej tabeli (Total) – sumę za wszystkie lata widoczne w raporcie.

Teraz druga miara:

[Suma Sprzedaży Wszystkie Lata] := CALCULATE( [Suma Sprzedaży]; ALL( DimData[Rok] ) )

Ta druga miara ignoruje filtr na kolumnie Rok. Jeśli w tabeli przestawnej masz rozbicie po latach, to dla każdego roku otrzymasz ten sam wynik – sumę sprzedaży za wszystkie lata (w ramach innych aktywnych filtrów). Różnica między miarami pokazuje mechanizm: ALL czyści określony filtr z kontekstu filtrowania.

Rodzina funkcji ALL – wspólny mianownik

Co łączy ALL, ALLEXCEPT, ALLSELECTED: „czyszczenie” filtrów

ALL, ALLEXCEPT i ALLSELECTED należą do tej samej rodziny funkcji: pracują na kontekście filtrowania i zwracają zestaw wierszy, który jest potem używany przez CALCULATE. Ich wspólna idea jest prosta: zmienić filtry aktywne na tabeli lub kolumnie.

Różnica tkwi w tym, jak konkretnie traktują istniejące filtry:

• ALL – usuwa wszystkie filtry z podanej tabeli lub kolumny.
• ALLEXCEPT – usuwa wszystkie filtry z tabeli, ale zostawia te na wskazanych kolumnach.
• ALLSELECTED – usuwa filtry pojedynczych komórek wynikających z układu raportu, lecz zachowuje filtrację wynikającą z wyboru użytkownika (fragmentatory, filtry raportu, czasem wybrane pola w tabeli przestawnej).

Wszystkie trzy zwracają „specjalny” typ – table expression. Najczęściej nie widać tego w formule, bo są używane wewnątrz CALCULATE, który potrafi taki wynik potraktować jak zestaw filtrów.

Na jakich obiektach działają: kolumny, tabele, wyrażenia

Podstawowe zastosowania:

• ALL(Tabela) – usuwa filtry z całej tabeli (wszystkie kolumny).
• ALL(Kolumna) – usuwa filtry tylko z tej jednej kolumny.
• ALLEXCEPT(Tabela; Kolumna1; Kolumna2; …) – czyści filtry z tabeli, lecz zostawia je na wskazanych kolumnach tej tabeli.
• ALLSELECTED(Tabela) lub ALLSELECTED(Kolumna) – czyści filtry pochodzące z bieżącego układu komórek, ale respektuje filtr globalny z raportu.

ALL można też stosować w wersji ALL( WyrażenieTablicowe ), ale przy klasycznym modelu Power Pivot w Excelu na początek w zupełności wystarcza myślenie o tabelach i kolumnach.

Typowe scenariusze: procent całości, porównania między grupami, rankingi

Najczęstsze zastosowania rodziny ALL w raportach:

• procent całości – udział w całkowitej sprzedaży, % marży całej firmy, udział w liczbie zamówień,
• procent w grupie – udział produktu w kategorii, sprzedawcy w regionie, klienta w swoim segmencie,
• porównania między grupami – sprzedaż klienta vs sprzedaż całej kategorii, kanału, kraju,
• rankingi – pozycja produktu w kategorii, ranking regionów, top N klientów względem całkowitej sprzedaży,
• analiza udziału w rynku – udział firmy w rynku w ramach wybranego zakresu (lat, krajów) vs udział w pełnym okresie.

Wszystkie te zadania sprowadzają się do kontrolowanego sterowania filtrami. Jeśli kontekst filtrowania jest ustawiony poprawnie, DAX sam policzy wartości. Problem pojawia się dopiero wtedy, gdy funkcje ALL/ALLEXCEPT/ALLSELECTED usuwają „za dużo” albo „za mało” filtrów – wtedy wyniki wyglądają na losowe.

Krótkie porównanie tekstowe ALL, ALLEXCEPT i ALLSELECTED

Najbardziej praktyczne jest myślenie o nich jak o trzech poziomach „resetu” filtrów:

• ALL – twardy reset na wybranym obszarze modelu („udawaj, że nie ma żadnych filtrów na tej tabeli/kolumnie”).
• ALLEXCEPT – miękki reset („czyść wszystko, ale zachowaj klucz grupowania, np. kategorię lub region”).
• ALLSELECTED – reset kontekstu bieżącej komórki, ale w ramach tego, co wybrał użytkownik („licz % w obrębie zaznaczonego fragmentu danych”).

Świadomy wybór jednej z tych funkcji jest kluczowy. To ona decyduje, czy Twoja miara „patrzy” na pełne dane, czy tylko na wybraną część, i jakie filtry dalej obowiązują.

ALL – usuń wszystkie filtry z wybranego zakresu

Składnia ALL dla tabeli i dla kolumn

Podstawowe warianty składni ALL w DAX:

• ALL( Tabela )
• ALL( Tabela[Kolumna] )
• ALL( WyrażenieTablicowe ) – rzadziej spotykane w prostych modelach.

ALL zwraca tabelę zawierającą wszystkie wiersze (lub unikalne wartości kolumny), ignorując aktualne filtry na tym obiekcie. Gdy taka tabela trafia do CALCULATE, działa jak polecenie „przestań stosować filtr na tej tabeli/kolumnie”.

Przykład składni w miarze:

[Total Sprzedaż Bez Filtrów Daty] := CALCULATE( [Suma Sprzedaży]; ALL( DimData ) )

Miara usuwa wszystkie filtry na tabeli DimData (rok, miesiąc, kwartał), ale zachowuje inne filtry, np. na klienta czy produkt.

Co dokładnie usuwa ALL: filtry wierszy, segmenty, filtry na poziomie modelu

ALL usuwa każdy filtr działający na wskazany obiekt:

• filtry pochodzące z pól w wierszach/kolumnach tabeli przestawnej,
• filtry z fragmentatorów (slicerów),
• filtry z obszaru „Filtry” w tabeli przestawnej,
• filtry nałożone przez inne funkcje DAX (np. wcześniejsze CALCULATE, FILTER),
• filtry ukryte w relacjach, jeśli przechodzą przez daną tabelę lub kolumnę.

Ważna rzecz: ALL nie dotyka filtrów na innych tabelach, chyba że te filtry są przekazywane poprzez relacje na tabelę, którą czyścimy. Na przykład ALL(DimData) nie usuwa filtrów na DimProdukt, ale jeśli filtr na DimProdukt powoduje filtr na Sprzedaż, to ten filtr nadal obowiązuje.

W praktyce ALL jest narzędziem „brutalnym” – usuwa wszystko na danej tabeli. Używając go na tabeli faktów, w wielu przypadkach otrzymasz wynik liczony na absolutnie wszystkich wierszach faktów, niezależnie od tego, co użytkownik kliknął w raporcie (z wyjątkiem filtrów na tabele niepołączone lub filtrów bezpieczeństwa na poziomie wiersza, jeśli takie zostaną wdrożone).

Prosty przykład: procent udziału w całkowitej sprzedaży z ALL(Tabela)

Klasyczny scenariusz „ALL w miarach procentowych”:

Miara bazowa:

[Suma Sprzedaży] := SUM( Sprzedaż[Kwota] )

Miara z użyciem ALL:

[Suma Sprzedaży Wszystkie Filtry] := CALCULATE( [Suma Sprzedaży]; ALL( Sprzedaż ) )

Miara procentowa:

[% Udział w Całkowitej Sprzedaży] := DIVIDE( [Suma Sprzedaży]; [Suma Sprzedaży Wszystkie Filtry] )

Jeżeli w tabeli przestawnej w wierszach masz kategorię, w kolumnach rok, a użytkownik używa też fragmentatora regionu, to:

• [Suma Sprzedaży] – liczy sprzedaż przy aktualnych filtrach (konkretna komórka: konkretna kategoria, konkretny rok, wybrany region).
• [Suma Sprzedaży Wszystkie Filtry] – liczy sprzedaż na całej tabeli faktów Sprzedaż, ignorując wszystkie filtry na tej tabeli (rok, region, produkt, klient), ale pozostawiając filtry na innych, niepowiązanych tabelach, jeśli istnieją.
• [% Udział w Całkowitej Sprzedaży] – pokazuje udział wartości z danej komórki w globalnej sprzedaży.

Ten wzorzec jest dobry, jeśli interesuje Cię udział w całej sprzedaży firmy, niezależnie od wybranego fragmentu. Gdy jednak użytkownik ogranicza raport np. tylko do segmentu „B2B”, takie ALL(Sprzedaż) może okazać się zbyt agresywne i lepiej sprawdzi się ALLSELECTED.

Różnica między ALL(Tabela) a ALL(JednaKolumna) w tym samym raporcie

ALL na całej tabeli i na pojedynczej kolumnie działają podobnie, ale ich efekt na wynik miary może być zupełnie inny.

Przykład:

Porównanie ALL na tabeli faktów i na kolumnie wymiaru

Rozważ prosty raport: w wierszach kategorie produktów (DimProdukt[Kategoria]), w kolumnach lata (DimData[Rok]), w wartości sprzedaż z tabeli faktów Sprzedaż.

Dwie miary procentowe:

[% Udział w Całkowitej Sprzedaży Tabela] :=
DIVIDE(
  [Suma Sprzedaży];
  CALCULATE( [Suma Sprzedaży]; ALL( Sprzedaż ) )
)

[% Udział w Całkowitej Sprzedaży Kolumna] :=
DIVIDE(
  [Suma Sprzedaży];
  CALCULATE( [Suma Sprzedaży]; ALL( DimProdukt[Kategoria] ) )
)

Pierwsza miara czyści filtry na całej tabeli faktów, druga tylko na kolumnie Kategoria. Efekt:

• ALL(Sprzedaż) – licznik z danej komórki, mianownik z całej sprzedaży bez względu na rok, kategorię, region.
• ALL(DimProdukt[Kategoria]) – licznik z danej komórki, mianownik z całej sprzedaży w obrębie bieżących filtrów (rok, region itp.), ale z pominięciem podziału na kategorię.

Na tej samej tabeli przestawnej przy slicerze „Region = Zachód” druga miara pokaże udział kategorii w całej sprzedaży regionu, a pierwsza udział w sprzedaży globalnej.

ALL na wymiarze dat vs ALL na tabeli faktów

Kolejna częsta decyzja: czyścić filtry z tabeli dat, czy z tabeli faktów.

Miara 1 – ignoruje filtry dat, ale zostawia resztę:

[Sprzedaż Bez Filtru Daty] :=
CALCULATE( [Suma Sprzedaży]; ALL( DimData ) )

Miara 2 – ignoruje wszystkie filtry na tabeli faktów:

[Sprzedaż Całkowita Fakty] :=
CALCULATE( [Suma Sprzedaży]; ALL( Sprzedaż ) )

W raportach typu „Sprzedaż YTD vs sprzedaż w całym okresie raportowania” wystarczy zwykle czyścić DimData. Gdy zamiast tego wyczyścisz Sprzedaż, dostaniesz wynik kompletnie niezależny od wyboru regionu, kanału, klienta, co często mija się z intencją.

ALL w połączeniu z innymi filtrami w CALCULATE

ALL jest jednym z wielu argumentów FILTRUJĄCYCH w CALCULATE. Kolejność logiczna ma znaczenie.

Przykład:

[Sprzedaż Kategoria vs Cała Marka] :=
CALCULATE(
  [Suma Sprzedaży];
  ALL( DimProdukt[Kategoria] );
  DimProdukt[Marka] = SELECTEDVALUE( DimProdukt[Marka] )
)

ALL usuwa filtr kategorii, następnie CALCULATE nakłada nowy filtr: Marka = bieżąca marka z kontekstu. Efekt: sprzedaż całej marki (wszystkie kategorie) w bieżącym roku/regionie/slicerach.

Jeśli odwrócisz logikę i spróbujesz filtrować kategorię po użyciu ALL(Tabela), filtr kategorii przepadnie, bo ALL(Tabela) czyści również ją. W takim scenariuszu lepszy jest ALLEXCEPT lub ALL tylko na kolumnach, które chcesz wyzerować.

ALL a relacje jednostronne i dwustronne

ALL oddziałuje też przez relacje. W prostym modelu (Dim → Fakty, jednokierunkowo) wygląda to intuicyjnie, ale w modelach z relacjami dwukierunkowymi bywa mylące.

• Przy relacjach jednokierunkowych filtr z wymiaru propaguje się do faktów, ALL na wymiarze czyści filtr i tym samym „odcina” też wpływ na fakty.
• Przy relacjach dwukierunkowych ALL na faktach może wpłynąć z powrotem na wymiary, jeśli zezwolono na propagację w obu kierunkach.

Dlatego w klasycznym modelu Power Pivot z raportami w Excelu bezpieczniej jest utrzymywać relacje jednokierunkowe i czyścić głównie wymiary (daty, produkt, klient) zamiast tabeli faktów.

ALL w praktyce raportów Excel – klasyczne zastosowania

Udział w sprzedaży w obrębie widoku raportu

Przy prostym raporcie Excel + Power Pivot najczęstszy przypadek to liczenie udziału procentowego w obrębie aktualnego widoku (np. „udział regionu w sprzedaży wszystkich regionów w wybranym roku”).

Miara bazowa:

[Sprzedaż] := SUM( Sprzedaż[Kwota] )

Miara „całkowita” dla bieżących filtrów poza regionem:

[Sprzedaż Wszystkie Regiony] :=
CALCULATE(
  [Sprzedaż];
  ALL( DimRegion[Region] )
)

Miara procentowa:

[% Udział Regionu] :=
DIVIDE( [Sprzedaż]; [Sprzedaż Wszystkie Regiony] )

W tabeli przestawnej z wierszami = Region, kolumnami = Rok i slicerem „Kanał sprzedaży” wynik pokaże udział regionu w sprzedaży wszystkich regionów w ramach wybranych lat i kanałów.

Porównanie do wartości docelowych lub budżetu

Często budżet jest w innej tabeli niż fakty sprzedażowe. ALL pozwala policzyć wskaźnik w odniesieniu do pełnego budżetu lub tylko jego części.

Miara:

[Realizacja Budżetu %] :=
DIVIDE(
  [Suma Sprzedaży];
  CALCULATE( [Budżet]; ALL( DimData ) )
)

Tu budżet liczony jest po całym okresie (ALL na DimData), ale nadal w ramach bieżących filtrów na produkt, klienta czy region. Dzięki temu można porównywać np. sprzedaż w jednym kwartale do rocznego budżetu, nie tracąc kontekstu produktowego.

Rankingi w tabelach przestawnych z użyciem ALL

Do klasycznych rankingów (pozycja klienta, produktu, regionu) ALL czyści wymiar, po którym sortujemy.

Przykład: ranking klientów po sprzedaży w bieżącym roku i regionie.

[Sprzedaż Klienta] := [Suma Sprzedaży]

[Ranking Klienta] :=
RANKX(
  ALL( DimKlient[Klient] );
  [Sprzedaż Klienta];
  ; DESC;
  Dense
)

ALL na kolumnie DimKlient[Klient] tworzy listę wszystkich klientów w bieżących filtrach (rok, region, produkt), a RANKX ustawia bieżącego klienta na odpowiednim miejscu tej listy.

Porównanie rok do roku z pełnym okresem historii

Zestawienia rok do roku często wymagają policzenia sprzedaży w tym samym okresie ubiegłego roku, ale także odniesienia do całego okresu w bazie.

Przykładowe miary:

[Sprzedaż Poprzedni Rok] :=
CALCULATE(
  [Suma Sprzedaży];
  SAMEPERIODLASTYEAR( DimData[Data] )
)

[Sprzedaż Poprzedni Rok Pełen Okres] :=
CALCULATE(
  [Suma Sprzedaży];
  ALL( DimData );
  SAMEPERIODLASTYEAR( DimData[Data] )
)

Druga miara ignoruje bieżące zawężenie daty i liczy sprzedaż za poprzedni rok po całej osi czasu (np. pełny rok), zachowując jednocześnie filtry na produkt czy region. To podejście użyteczne przy analizie trendów, gdy raport prezentuje krótszy wycinek okresu.

ALL a fragmentatory i filtry raportu w Excelu

Fragmentatory (slicery) działają jak każdy inny filtr. ALL je ignoruje, jeśli dotyczą tej samej tabeli/kolumny, którą czyścisz.

Przykład: fragmentator dla DimData[Rok] oraz miara:

[Sprzedaż % vs Wszystkie Lata] :=
DIVIDE(
  [Suma Sprzedaży];
  CALCULATE( [Suma Sprzedaży]; ALL( DimData[Rok] ) )
)

Niezależnie od wybranego roku w fragmentatorze mianownik zawsze uwzględnia wszystkie lata. W praktyce użytkownik często tego się nie spodziewa, dlatego takie miary lepiej opisywać bardzo jednoznacznymi etykietami (np. „% vs wszystkie lata (ignoruje wybór roku)”).

ALLEXCEPT – zostaw część filtrów, resztę wyczyść

Składnia ALLEXCEPT i główna idea

ALLEXCEPT czyści filtry z całej tabeli, ale pozostawia je na wskazanych kolumnach.

Składnia:

• ALLEXCEPT( Tabela; Tabela[Kolumna1]; Tabela[Kolumna2]; ... )

Działa to tak, jakbyś zastosował ALL(Tabela), a potem „przywrócił” filtry na podanych kolumnach. W praktyce najczęściej zachowuje się klucz grupowania: kategorię, region, segment klienta.

Procent w obrębie grupy: klasyczny scenariusz ALLEXCEPT

Typowy przykład: udział produktu w kategorii.

Wymiary: DimProdukt[Kategoria], DimProdukt[Produkt]. Fakty: Sprzedaż.

Miara:

[Sprzedaż Produktu] := [Suma Sprzedaży]

[Sprzedaż Kategorii] :=
CALCULATE(
  [Suma Sprzedaży];
  ALLEXCEPT( DimProdukt; DimProdukt[Kategoria] )
)

[% Udział Produktu w Kategorii] :=
DIVIDE( [Sprzedaż Produktu]; [Sprzedaż Kategorii] )

ALLEXCEPT czyści wszystkie filtry na DimProdukt poza kategorią. Dzięki temu mianownik zawiera sumę sprzedaży całej kategorii, ale nadal ograniczoną przez inne filtry: rok, region, kanał sprzedaży, slicery.

ALLEXCEPT a wielopoziomowe hierarchie

Przy hierarchiach typu Kontynent → Kraj → Region miejski ALLEXCEPT pozwala zachować wybrany poziom, a wyczyścić niższe.

Załóżmy, że w tabeli DimGeografia masz kolumny: Kontynent, Kraj, Miasto. W tabeli przestawnej w wierszach znajduje się Miasto.

Miara:

[Sprzedaż Kraju] :=
CALCULATE(
  [Suma Sprzedaży];
  ALLEXCEPT( DimGeografia; DimGeografia[Kraj] )
)

Dla każdej komórki (konkretne miasto) ALLEXCEPT czyści filtr na Miasto, ale zachowuje filtr Kraj. Wynik to sprzedaż całego kraju w bieżącym okresie i przy pozostałych filtrach.

ALLEXCEPT na tabeli faktów – kiedy ma sens

Rzadziej, ale bywa, że ALLEXCEPT stosowany jest bezpośrednio na tabeli faktów, gdy ta zawiera wszystkie potrzebne kolumny grupujące.

Przykład:

[Sprzedaż w Grupie Klienta] :=
CALCULATE(
  [Suma Sprzedaży];
  ALLEXCEPT( Sprzedaż; Sprzedaż[GrupaKlienta] )
)

W prostych modelach, gdzie nie ma oddzielnej DimKlient, takie podejście działa, ale w rozbudowanych modelach lepiej czyścić wymiary niż fakty – jest to bardziej przewidywalne przy dalszej rozbudowie modelu.

ALLEXCEPT a połączenie z innymi filtrami DAX

Tak jak przy ALL, ALLEXCEPT często współpracuje z dodatkowymi filtrami.

Przykład: udział produktu w kategorii w bieżącym roku, ale ignorując slicer kanału sprzedaży.

[Sprzedaż Kategorii Rok] :=
CALCULATE(
  [Suma Sprzedaży];
  ALLEXCEPT( DimProdukt; DimProdukt[Kategoria] );
  VALUES( DimData[Rok] );
  ALL( DimKanał )
)

ALLEXCEPT steruje zakresem kategorii, VALUES blokuje rok na bieżącej wartości, ALL(DimKanał) ignoruje kanał sprzedaży. Tak złożone miary mają sens tylko wtedy, gdy dokładnie wiadomo, które filtry mają pozostać, a które zostać skasowane.

ALLEXCEPT w modelu z relacjami – subtelne konsekwencje

ALLEXCEPT tylko w obrębie jednej tabeli

ALLEXCEPT działa wyłącznie na tabeli podanej jako pierwszy argument. Nie czyści filtrów w innych tabelach bezpośrednio, ale może wpływać na nie przez relacje.

Jeśli użyjesz ALLEXCEPT(DimProdukt; DimProdukt[Kategoria]), filtry na DimData, DimKlient czy innych wymiarach nadal obowiązują. Wpływ na tabelę faktów zależy od kierunku i istnienia relacji.

ALLEXCEPT a relacje wielu-do-wielu

Przy relacjach wielu-do-wielu ALLEXCEPT bywa źródłem zaskoczeń. Oczyszczenie filtrów na jednym wymiarze nie oznacza pełnego „uwolnienia” od powiązanej tabeli pośredniczącej.

Jeżeli model korzysta z tabeli mostowej (bridge table), np. Klient–Segment, to ALLEXCEPT na DimKlient nie czyści filtrów przechodzących przez tę tabelę, o ile te filtry nie są bezpośrednio na kolumnach DimKlient.

ALLEXCEPT a kierunek filtrowania i relacje dwukierunkowe

Przy relacjach jednokierunkowych efekt ALLEXCEPT jest dość przewidywalny: czyścisz filtry na wymiarze, które następnie przepływają w stronę tabeli faktów. Przy relacjach dwukierunkowych lub włączonym „kierunku filtrowania obu stron” sytuacja staje się bardziej złożona.

Jeżeli DimProdukt i DimKlient są połączone z faktem dwukierunkowo, ALLEXCEPT na DimProdukt może być „korygowane” przez filtry na DimKlient, które wracają do DimProdukt. W rezultacie część produktów, której spodziewasz się w mianowniku, znika, bo filtr klienta zawęża zbiór możliwych produktów.

Przy podejrzeniu takich efektów najlepiej mieć pomocniczą miarę typu:

[Liczba Produktów ALLEXCEPT] :=
COUNTROWS(
  CALCULATETABLE(
    VALUES( DimProdukt[Produkt] );
    ALLEXCEPT( DimProdukt; DimProdukt[Kategoria] )
  )
)

Prosta tabela przestawna z kategorią i tą miarą szybko pokaże, czy filtracja z innych wymiarów przecina zakres oczyszczany przez ALLEXCEPT.

ALLEXCEPT a filtrowanie z poziomu faktów

Jeśli raport używa filtrów bezpośrednio na tabeli faktów (np. fragmentator na Sprzedaż[TypDokumentu]), ALLEXCEPT na wymiarze nie dotknie tych filtrów. W dalszym ciągu zawężają one zbiór wierszy faktów.

Przykład:

[Sprzedaż Kategorii (Dokumenty)] :=
CALCULATE(
  [Suma Sprzedaży];
  ALLEXCEPT( DimProdukt; DimProdukt[Kategoria] )
)

Jeśli na fragmentatorze ustawiono tylko faktury, miara nie uwzględni paragonów ani korekt, bo filtr z tabeli Sprzedaż pozostaje aktywny. To częste źródło różnic między oczekiwanym „pełnym” mianownikiem a realnym wynikiem.

ALLEXCEPT i kolumny obliczeniowe vs. miary

ALLEXCEPT (podobnie jak ALL) działa sensownie wyłącznie w miarach. W kolumnach obliczeniowych kontekst wiersza jest stały i nie reaguje na filtry zewnętrzne, więc ALLEXCEPT nie zachowa się tak, jak w tabeli przestawnej.

Jeżeli ktoś próbuje przepisać logikę „udziału w kategorii” do kolumny obliczeniowej, wyniki będą inne od tych z miary, bo nie ma tam kontekstu filtrowania pochodzącego z raportu.

ALLSELECTED – współpraca z użytkownikiem raportu

Składnia ALLSELECTED i ogólne zachowanie

ALLSELECTED usuwa część filtrów, ale respektuje to, co zostało wybrane przez użytkownika na poziomie raportu. Zachowuje „zewnętrzny” kontekst wyboru, jednocześnie czyści część filtrów „wewnętrznych”.

Składnia:

• ALLSELECTED( Tabela )
• ALLSELECTED( Tabela[Kolumna] )

Działa podobnie do ALL, lecz nie ignoruje filtrów ustawionych „nad” bieżącą wizualizacją, np. filtrów raportu czy fragmentatorów ograniczających cały arkusz, o ile te wybory są elementem bieżącego kontekstu selekcji.

ALLSELECTED jako podstawa udziałów procentowych w tabeli

Typowy scenariusz: udział produktu w widocznym podzbiorze danych, a nie w całej bazie.

Miara:

[Sprzedaż Produktu] := [Suma Sprzedaży]

[Sprzedaż Widoczna (ALLSELECTED)] :=
CALCULATE(
  [Suma Sprzedaży];
  ALLSELECTED( DimProdukt )
)

[% Udział Produktu w Widocznych] :=
DIVIDE( [Sprzedaż Produktu]; [Sprzedaż Widoczna (ALLSELECTED)] )

W tabeli przestawnej zawierającej tylko część kategorii (bo użytkownik przefiltrował fragmentatorem) mianownik obejmuje wyłącznie produkty w wybranych kategoriach, nie wszystkie produkty z modelu. To zachowanie jest często bliższe intuicji użytkownika niż „twarde” ALL.

ALLSELECTED a drill-down i poziomy szczegółowości

Przy przechodzeniu z poziomu kategorii do produktu ALLSELECTED pozwala liczyć udział w obrębie aktualnego poziomu, ale tylko dla wybranego wcześniej zakresu.

Przykład:

[Sprzedaż Poziom Widoczny] :=
CALCULATE(
  [Suma Sprzedaży];
  ALLSELECTED( DimProdukt[Kategoria]; DimProdukt[Produkt] )
)

Jeśli użytkownik zawęzi raport do dwóch kategorii, a następnie przejdzie w dół do produktów, to ALLSELECTED „widzi” tylko te dwie kategorie i ich produkty. Udziały produktów nie odnoszą się wtedy do całego asortymentu, ale do aktualnie analizowanego wycinka.

ALLSELECTED i filtry na poziomie arkusza w Excelu

W Excelu filtr raportu (Report Filter) w tabeli przestawnej jest traktowany podobnie jak fragmentator. ALLSELECTED respektuje taki filtr, o ile jest częścią kontekstu selekcji.

Jeżeli w filtrze raportu wybierzesz tylko jeden region, ALLSELECTED(DimRegion) będzie pracować w obrębie tego regionu. ALL(DimRegion) zignoruje ten wybór i pokaże wyniki względem wszystkich regionów.

Różnicę widać w dwóch miarach:

[% Region vs Wszystkie Wybrane] :=
DIVIDE(
  [Suma Sprzedaży];
  CALCULATE( [Suma Sprzedaży]; ALLSELECTED( DimRegion ) )
)

[% Region vs Wszystkie w Modelu] :=
DIVIDE(
  [Suma Sprzedaży];
  CALCULATE( [Suma Sprzedaży]; ALL( DimRegion ) )
)

W pierwszym przypadku mianownik zmienia się przy zmianie filtra raportu, w drugim pozostaje stały.

ALLSELECTED a sortowanie i ranking w kontekście użytkownika

Przy rankingach, które mają działać tylko w obrębie wybranej części danych, ALLSELECTED jest często lepszym wyborem niż ALL.

Miara:

[Ranking Produktu (Wybrane)] :=
RANKX(
  ALLSELECTED( DimProdukt[Produkt] );
  [Suma Sprzedaży];
  ; DESC;
  Dense
)

Jeżeli użytkownik przefiltruje kategorię w fragmentatorze, ranking obejmie tylko produkty z tej kategorii, a nie cały asortyment. Dla wielu analiz handlowych takie „lokalne” rankingi są bardziej użyteczne.

ALLSELECTED i wpływ innych filtrów wymiarów

ALLSELECTED nie czyści filtrów na innych tabelach, podobnie jak ALL i ALLEXCEPT, ale respektuje ich wybory, jeśli są częścią kontekstu selekcji. To istotne, gdy tabela przestawna ma wiersze z jednego wymiaru, a slicery z innych.

Przykład: tabela z produktami, fragmentatory na Rok i Region, miara:

[Sprzedaż Widoczna Produktów] :=
CALCULATE(
  [Suma Sprzedaży];
  ALLSELECTED( DimProdukt )
)

Zmiana roku lub regionu zawęża zakres produktów branych pod uwagę, bo ALLSELECTED nie wychodzi poza to, co zostało wybrane w szerszym kontekście raportu. Odwrotnie niż ALL(DimProdukt), które pominęłoby część tych filtrów.

ALLSELECTED a tabele i wykresy przestawne w Excelu

Różnica między filtrem raportu a polami w wierszach

W tabeli przestawnej część filtrów pochodzi z pól w wierszach/kolumnach, a część z filtrów raportu i fragmentatorów. ALLSELECTED reaguje na ten układ.

Jeśli w wierszach jest DimProdukt[Kategoria], a w filtrze raportu DimRegion[Region], to ALLSELECTED(DimProdukt) „widzi” wybór regionu, bo jest on elementem kontekstu selekcji. Natomiast wybór konkretnej kategorii z listy wierszy w tabeli przestawnej nie jest wprost interpretowany jako „selekcja” dla ALLSELECTED – to filtr na poziomie wizualizacji.

Dlatego dwie tabele przestawne oparte na tym samym modelu mogą dawać inne wyniki ALLSELECTED przy tym samym układzie fragmentatorów, jeżeli różnią się polami w sekcji Wiersze i Kolumny.

ALLSELECTED z wieloma tabelami w tabeli przestawnej

Gdy tabela przestawna ma wiersze z kilku wymiarów (np. Region i Kategoria), ALLSELECTED stosowane na jednym z nich będzie zachowywać się zgodnie z jego zakresem, ale nadal podlega ograniczeniom narzuconym przez drugi.

Przykład:

[Sprzedaż Widoczna Kategorii] :=
CALCULATE(
  [Suma Sprzedaży];
  ALLSELECTED( DimProdukt[Kategoria] )
)

Jeśli w Wierszach są DimRegion[Region] i DimProdukt[Kategoria], to dla danej komórki (konkretny region + kategoria) mianownik obejmuje wszystkie kategorie w tym regionie, ale tylko w zakresie aktualnych filtrów raportu i fragmentatorów. Nie obejmuje innych regionów, bo kontekst regionu pochodzi z wiersza.

ALLSELECTED a „Pokaż tylko top N” w tabeli przestawnej

Funkcja „pokaż tylko elementy najwyższe/ostatnie” w tabeli przestawnej nie jest klasycznym filtrem DAX. ALLSELECTED nie widzi tego ograniczenia jako selekcji.

Jeżeli użytkownik ograniczy widok do top 10 produktów, a miara udziału używa ALLSELECTED(DimProdukt), mianownik nadal będzie bazował na wszystkich produktach pozostających w kontekście selekcji, nie tylko na top 10. To częste źródło pytań „dlaczego suma udziałów przekracza 100%” – bo top 10 liczone jest względem pełnego zbioru, a nie względem części pokazanej w tabeli.

ALLSELECTED na wykresach przestawnych

Wykres przestawny oparty na tej samej tabeli przestawnej dzieli z nią kontekst filtrowania i selekcji. ALLSELECTED będzie reagował tak samo jak w tabeli, o ile oba obiekty korzystają z tego samego zestawu pól i filtrów.

Jeśli jednak wykres jest oparty na innej tabeli przestawnej (kopii lub nowej), może mieć inny układ wierszy/kolumn i inne filtry raportu. To z kolei prowadzi do różnic w interpretacji ALLSELECTED, mimo że fragmentatory są wspólne.

Porównanie ALL, ALLEXCEPT, ALLSELECTED na jednym modelu

Model referencyjny do porównań

Przykładowy model:

• DimData (Data, Rok, Miesiąc)
• DimProdukt (Kategoria, Produkt)
• DimRegion (Region)
• FaktSprzedaż (DataKey, ProduktKey, RegionKey, Sprzedaż)

Fragmentatory: Rok, Kategoria, Region. Wiersze tabeli przestawnej: Produkt.

Trzy miary udziału produktu

Miary bazowe:

[Sprzedaż] := SUM( FaktSprzedaż[Sprzedaż] )

Miary udziału:

[% ALL Produkt] :=
DIVIDE(
  [Sprzedaż];
  CALCULATE( [Sprzedaż]; ALL( DimProdukt ) )
)

[% ALLEXCEPT Kategoria] :=
DIVIDE(
  [Sprzedaż];
  CALCULATE(
    [Sprzedaż];
    ALLEXCEPT( DimProdukt; DimProdukt[Kategoria] )
  )
)

[% ALLSELECTED Produkt] :=
DIVIDE(
  [Sprzedaż];
  CALCULATE( [Sprzedaż]; ALLSELECTED( DimProdukt ) )
)

Scenariusz 1: brak fragmentatorów, pełny widok

Bez filtrów:

• % ALL Produkt – udział produktu w całej bazie (wszystkie produkty, wszystkie kategorie).
• % ALLEXCEPT Kategoria – udział produktu w swojej kategorii w całej bazie.
• % ALLSELECTED Produkt – identyczny z % ALL Produkt, ponieważ „wybór użytkownika” = pełny zakres.

Scenariusz 2: filtr Kategoria na fragmentatorze

Użytkownik wybiera jedną kategorię. Wiersze tabeli: produkty z tej kategorii.

• % ALL Produkt – liczy udział produktu względem wszystkich produktów we wszystkich kategoriach, również tych niewybranych.
• % ALLEXCEPT Kategoria – ponieważ filtr kategorii pozostaje, mianownik to suma sprzedaży tylko tej wybranej kategorii; pozostałe kategorie są poza kontekstem.
• % ALLSELECTED Produkt – mianownik to suma sprzedaży wszystkich produktów w wybranej kategorii (czyli jak % ALLEXCEPT Kategoria), bo selekcja ogranicza model do jednej kategorii.

Scenariusz 3: filtr Rok i Region, bez filtra Kategoria

Wybór: Rok = 2024, Region = „Północ”. Kategoria nieograniczona.

• % ALL Produkt – mianownik: sprzedaż wszystkich produktów (wszystkie kategorie) w roku 2024 w regionie „Północ”. Filtry na Rok i Region pozostają.
• % ALLEXCEPT Kategoria – mianownik: sprzedaż wszystkich produktów w bieżącej kategorii, nadal tylko w roku 2024 i regionie „Północ”.