CALCULATETABLE vs FILTER

Power BI ‘da birbirine benzeyen -fakat semantiği, yani çalışma tarzları-mantıkları tamamen farklı olan- tablo fonksiyonlarına çok belirgin bir örnek var: CALCULATETABLE ile FILTER.  Her ikisi de tablo fonksiyonu. Yani döndürdükleri şey bir tablo.  Bazı durumlarda her ikisi de benzer işlevi görebilir, hatta aynı sonuçları üretebilir.

Daha önce hem CALCULATETABLE hem de FILTER ile ilgili giriş yazıları yazdım. Öncesinde göz atmak isteyebilirsiniz zira bu yazı biraz karmaşık gelebilir.

Önce her iki fonksiyonun da benzer sonuçlar ürettiği bir örnek yapalım.

Aşağıdaki iki ifadeyi yeni birer tablo yaratmak için yazıyorum.

Kırmızı Ürünler CALCULATETABLE = 
CALCULATETABLE( 'Ürünler' ; 'Ürünler'[Renk] = "Kırmızı")

Kırmızı Ürünler FILTER =
FILTER( 'Ürünler' ; 'Ürünler'[Renk] = "Kırmızı")

Her iki ifadenin de oluşturduğu tablolar aynı, her ikisinde de aynı sayıda kayıt var.

Oluşan tablolar aynı olsa da, tabloların oluşma şekli tamamen farklı. FILTER, önce tüm ürünler tablosunu gördü, sonra bu tablonun her bir satırı için “ürün rengi kırmızı mı” testini uyguladı, testi geçen satırları tablo olarak döndürdü. Ürünler tablosunda toplamda 2517 satır var, 2517 kez aynı tablodaki her bir satırı iterate etti, testi geçen 99 satırı döndürdü.

CALCULATETABLE ise önce rengi kırmızı olan ürünleri buldu, bunu yaparken de satırlarda iterasyon yapmadı, doğrudan renk sütunundaki değerler üzerinden çalıştı, sonra bu ürünlerin olduğu tabloyu döndürdü.

Yukarıdaki örneklere göre iki fonksiyon arasındaki ilk fark, FILTER’ın bir iterator olması. CALCULATETABLE ise bir iterator değil.

İkinci fark ise “execution” sırası. FILTER önce ilk parametreyi okuyor (tabloyu) sonra bu tablodan condition’lara uyan satırları döndürüyor.

CALCULATETABLE ise önce condition’ı uyguluyor, sonra buna göre ilk parametreyi çalıştırıyor.

Bu ikinci farkı açmak için bir örnek  daha yapalım: Aşağıdakileri gene bir tablo ifadesi olarak ekliyorum.

Kırmızı Ürün Sayısı CALCULATETABLE =
CALCULATETABLE(
    ADDCOLUMNS(
        VALUES( 'Ürünler'[Renk] ) ;
        "Ürün Sayısı" ; COUNTROWS( 'Ürünler' )
    ) ;
    'Ürünler'[Renk] = "Kırmızı"
)

99 döndürdü ve dönen rakam doğru.

Bu sefer aynı ifadeyi başına FILTER ekleyerek yazıyorum.

Kırmızı Ürün Sayısı FILTER =
FILTER(
    ADDCOLUMNS(
        VALUES( 'Ürünler'[Renk] ) ;
        "Ürün Sayısı" ; COUNTROWS( 'Ürünler' )
    ) ;
    'Ürünler'[Renk] = "Kırmızı"
)

2517 döndürdü ve dönen rakam yanlış. Bu rakam kırmızı renkli ürün sayısı değil, tüm ürünlerin sayısı!

CALCULATETABLE ile yazdığımız ilk ifadede, önce condition çalışıyor! Dolayısıyla hem ADDCOLUMNS hem de VALUES ‘un gördüğü tabloda rengi sadece kırmızı ürünler var. Hatta -bu örneğe göre- VALUES ( ‘Ürünler'[Renk] ) tek satırlık “Kırmızı” tablosunu görüyor. Bu yüzden context transition’a gerek kalmadan da 99 hesaplıyor.

FILTER ile yazdığımız versiyonda ise önce ADDCOLUMNS ve VALUES grubu çalışıyor. VALUES ‘un gördüğü renk sütununda kırmızı da dahil olmak üzere tüm renkler var. Tüm renklerin altındaki ürünleri saydığında 2517 buluyor. FILTER’ın condition’ı ancak bu hesap yapıldıktan sonra devreye giriyor. Renk olarak elinde kırmızı kalıyor. Fakat yazdığımız formülde “context transition” yapmadığımız için tüm ürünleri saymaya devam ediyor. Bu ifadenin doğru çalışabilmesi için COUNTROWS’u CALCULATE ile birlikte çağırmamız lazım.

Kırmızı Ürün Sayısı FILTER Doğru =
FILTER(
    ADDCOLUMNS(
        VALUES( 'Ürünler'[Renk] ) ;
        "Ürün Sayısı" ; CALCULATE( COUNTROWS( 'Ürünler' ) )
    ) ;
    'Ürünler'[Renk] = "Kırmızı"
)

** ADDCOLUMNS ve context transition için buradan!

…

FILTER’ın bir iterator -dolayısıyla row context yaratan- bir fonksiyon olması, CALCULATETABLE ‘ın ise bir iterator olmamasına ilişkin bir örnek daha yapacağım: Örnekleri de bilinçli olarak biraz absürt vereceğim müsadenizle! Ürünler tablosuna her bir renkten kaç tane ürün olduğunu gösteren bir hesaplanmış sütunu aşağıdaki formülle ekliyorum.

Her Bir Renkten Kaç Ürün Var FILTER =
COUNTROWS(
    FILTER( 'Ürünler' ;
        'Ürünler'[Renk] = EARLIER( 'Ürünler'[Renk] )
    )
)

Doğru bir şekilde çalışıyor.

Aynı ifadeyi bu sefer FILTER yerine CALCULATETABLE ile yazıyorum.

Her Bir Renkten Kaç Ürün Var CALCULATETABLE =
COUNTROWS(
    CALCULATETABLE( 'Ürünler' ;
        'Ürünler'[Renk] = EARLIER( 'Ürünler'[Renk] )
    )
)

İfade bir şey hesaplıyor : Her satırda 1 gösteriyor!

FILTER’lı versiyonda iki tane row context var alt alta çalışan: İlkini hesaplanmış sütun yaratıyor, diğerini FILTER. EARLIER bu durumda bir anlam ifade ediyor! FILTER ile ürünler tablosunun tüm satırlarını tararken, dıştaki hesaplanmış sütunun yarattığı row context’teki satırın bilgisine erişmemizi sağlıyor. EARLIER yazısı için buradan.

Ama CALCULATETABLE ‘lı versiyonda EARLIER bir şey ifade etmiyor, daha doğrusu hesaplanmış sütunun oluşturduğu row context’in iterate ettiği satır neyse onu ifade ediyor, o da her zaman için tek bir satır. Elinde hep tek bir satır kaldığı için 1 hesaplıyor!

Bu -nispeten beyin yakan- örnekleri konseptleri anlamak adına açıyorum, üzerinde düşünmeden DAX’ı öğrenmek çok kolay değil çünkü. Tablo fonksiyonları da DAX’ın belki de en önemli fonksiyon grubu. Yoksa normal bir modelde ürün sayısını DISTINCTCOUNT’la sayarız, matrise -ya da herhangi bir görsele- renk sütunuyla birlikte düşürdüğümüzde işimiz biter.

…

Buraya kadar verdiğim tüm örneklerde ya hesaplanmış sütun oluşturduk ya da hesaplanmış tablo yarattık. Bunu söyleme ihtiyacı duydum çünkü bu konseptlerin ikisinde de , formülümüze ne yazdıysak yazdık, filter context kavramı yok! Ne eklediysek refere ettiğimiz tabloların/sütunların tamamı üzerinden çalışacak. Ve oluştuktan sonra bunların orijinal veri kaynağından okuduğumuz tablo veya sütunlardan hiçbir farkı kalmayacak. Tek farkları DAX ile hesaplanmış olmaları.

Metrik yazdığımızda ise –her zaman ve de her zaman için– eşittir öncesinde mutlaka bir filter context vardır!

…

CALCULATETABLE, CALCULATE ‘in tablo döndüren versiyonu. CALCULATE, -nümerik veya text- tek sabit bir değer döndürürken CALCULATETABLE bir tablo döndürüyor. Bunun haricince CALCULATE için geçerli olan her şey CALCULATETABLE için de geçerli. Dönen şeyin farklılığı haricinde her ikisinin de çalışma mantıkları birebir aynı.

CALCULATETABLE -aynen CALCULATE ‘te olduğu gibi- filtre ifadelerine yazdığımız condition’lara göre önce mevcut filter context’i modifiye ederek çalışır.  Aynen yukarıdaki örneklerde olduğu gibi, önce condition’lar sonra ilk parametredeki ifade çalışacak. 2008 yılındaki kırmızı renkli ürünlerin satışlarını hesaplamak için aşağıdaki bir ifadeyi metrik olarak yazabiliriz.

2008 Kırmızı Satışları CALCULATETABLE :=
SUMX(
    CALCULATETABLE(
        'Satışlar';
        'Tarih'[Yıl] = 2008;
        'Ürünler'[Renk] = "Kırmızı"
    );
    'Satışlar'[Tutar]
)

Her yerde 172.. rakamı var, çünkü mevcut filter context’i modifiye ediyoruz: Gördüğün filter context’te yıl ve renk olarak ne varsa var, umursamıyorum, Yıl 2008 olacak, ürün rengi kırmızı olacak. Dıştaki SUMX ‘te bu tabloyu iterate edecek.

FILTER ‘ın ise kendi başına filter context’i değiştirebilme gibi bir kabiliyeti yok. Mevcut filter context’te gördükleri üzerinden çalışacak. Aynen önceki örneklerde olduğu önce ilk parametrede verdiğimiz tabloyu bulacak, sonra bunun üzerinde verdiğimiz condition’ı test edecek.

2008 Kırmızı Satışları_FILTER =
SUMX(
    FILTER(
        'Satışlar',
        RELATED( 'Tarih'[Yıl] ) = 2008
            && RELATED( 'Ürünler'[Renk] ) = "Kırmızı"
    ),
    'Satışlar'[Tutar]
)

Bir farkı daha not edebiliriz artık: CALCULATETABLE filter context’i modifiye ederek, değiştirerek çalışır, FILTER’ın ise böyle bir kabiliyeti yoktur, ancak ALL ve ekürisi ile birlikte kullanılırsa olabilir. Bu durumda da filter context’i değiştiren FILTER’ın kendisi değil, ALL veya hangi ekürüsini kullandıysak odur.

Aşağıda aynı sonucu döndüren iki farklı metriği ekliyorum.

Tutarı 1000 den Yüksek Satışlar CALCULATETABLE = 
SUMX( 
    CALCULATETABLE( 'Satışlar' , 'Satışlar'[Tutar] > 1000 ) ,
    'Satışlar'[Tutar]
)

Tutarı 1000 den Yüksek Satışlar FILTER = 
SUMX( 
    FILTER( 'Satışlar' , 'Satışlar'[Tutar] > 1000 ) ,
    'Satışlar'[Tutar]
)

Her iki metrikte de condition’da refere ettiğimiz sütun Satışlar tablosundaki Fiyat sütunu. Matriste -görselde- bu sütunu kullanmadığımız sürece her ikisi de aynı sonuçları gösterecektir.

O zaman soru şu oluyor: Ne zaman CALCULATETABLE’ı ne zaman FILTER’ı kullanalım?

Eğer ihtiyaç yukarıdakine benzer bir şeyse CALCULATETABLE performans olarak daha çok tercih edilmeli. Yukarıdaki dediğim örneğin en bariz özelliği şu : Sütun refere ettiğimiz Boolean tipinde bir condition var! Falanca sütun şuna eşit mi bundan büyük mü! CALCULATETABLE ‘ın condition’larına yazabileceğimiz filtrelerin tipleri bununla sınırlı. Artı bu condition’larda metrik de çağıramıyoruz! FILTER’a verebileceğimiz condition’larda bu kadar kısıtlayıcı bir durum yok!

Örneğin satışı 100.000’in üzerinde olan kategori sayısını CALCULATETABLE ile bulamayız. Çünkü condition’da metrik refere edemiyoruz!

Satışı 100.000 den Yüksek Kategori Sayısı CALCULATETABLE = 
COUNTROWS( 
    CALCULATETABLE( 'Kategoriler' ,
        [Satışlar] > 100000
    )
) -- Hata mesajı verip çalışmayacak!

Ama FILTER ile bulabiliriz. Condition’da metrik refere etmeyle ilgili bir sorunu yok FILTER’in.

Satışı 100.000 den Yüksek Kategori Sayısı FILTER = 
COUNTROWS( 
    FILTER( 'Kategoriler' ,
        [Satışlar] > 100000
    )
)

Benzer şekilde fiyatı maliyetinin 2 katından yüksek ürün sayısını bulmaya çalışsak -adına da karlı ürünler desek- bunu da CALCULATETABLE ile yapamayız!

Karlı Ürün Sayısı CALCULATETABLE = 
COUNTROWS( 
    CALCULATETABLE( 'Ürünler' ,
         'Ürünler'[Birim Fiyat] > 2 * 'Ürünler'[Birim Maliyet]
    )
) -- Hata mesajı verip çalışmayacak!

Condition Boolean tipinde değil!

Ama FILTER ile yazarız!

Karlı Ürün Sayısı FILTER = 
COUNTROWS( 
    FILTER( 'Ürünler' ,
         'Ürünler'[Birim Fiyat] > 2 * 'Ürünler'[Birim Maliyet]
    )
)

CALCULATETABLE ile yapamadıklarımıza! örnekler verdikten sonra bir de -benim de neredeyse her modelde kullandığım-  bir “kullanışlı” örneğini yapalım.

Time Intelligence grubundaki fonksiyonlardan bir çoğu kozmetik bir durumdan muzdarip! Örneğin aşağıdaki gibi basit bir yıllık kümülatif metriğini ele alalım.

Satışlar YTD :=
CALCULATE( [Satışlar] ; DATESYTD( 'Tarih'[Tarih] ))

Satışlar 2009 Ağustos’ta kayıt olarak bitmesine rağmen YTD metriği satış olmayan Eylül-Aralık ayları için de hesap yapmaya devam ediyor! Gayet de normal bu haliyle! Çünkü tarih tablomuzda 2009’un tüm ayları var. Fakat istiyoruz ki satışın hiç olmadığı bu aylarda YTD hesaplamasın. Çözme yöntemlerinden biri metriği IF kalıbına sokup IF [Satışlar] > 0 ise hesapla demek olabilir.

Ben ise -genelde- aşağıdaki yöntemi kullanıyorum.

Tarih tablosuna hesaplanmış sütun olarak, satışlar tablosundaki en son satışın gerçekleştiği tarihi bulup getiriyorum ve satırdaki tarih eğer bundan küçükse (ya da büyükse) “Evet-Hayır” ya da “True-False” değerini veriyorum. Devamında da tüm time intelligence metriklerine bu sütunu CALCULATETABLE ile birlikte bir condition olarak ekliyorum!

Son Satış Tarihinden Önce = 'Tarih'[Tarih] <= MAX( 'Satışlar'[Tarih] )

Satışlar YTD CALCULATETABLE =
CALCULATE( [Satışlar] ;
    CALCULATETABLE(
            DATESYTD('Tarih'[Tarih]) ;
            'Tarih'[Son Satış Tarihinden Önce] = TRUE()
    )
)

Bunu daha önce PowerPlatform Türkiye Meetup grubunda yaptığımız webinar’da da göstermiştim. İzlemek isterseniz buradan.

Son olarak -bu sefer hakikaten son- DATESYTD, DATESQTD gibi fonksiyonlar olmasaydı!, bunları DAX ile bulabilir miydik örneğini eklemek istiyorum! Doğrudan yazı başlığıyla ilgisi olmasa da FILTER gibi tablo fonksiyonları için güzel bir örnek olur sanıyorum.

Yıllık kümülatifin cümlesini kuralım: Matriste gözüken yıla ait tüm tarihleri bul, bu yılın tüm tarihlerindeki satışları toplayarak gel! Matrisin satırında gözüken MAX tarihin yılına da dikkate al!

Satışlar DAX YTD = 
CALCULATE( [Satışlar] ,
    FILTER( ALL( 'Tarih'[Tarih] ) , 
        'Tarih'[Tarih] <= MAX( 'Tarih'[Tarih] ) &&
         YEAR( 'Tarih'[Tarih] ) = YEAR( MAX( 'Tarih'[Tarih] ) )
    )
)

Değişken kullanarak yazmak daha da kolay olurdu!

Satışlar DAX YTD VAR = 
VAR _MatristekiSonTarih = MAX( 'Tarih'[Tarih] )
RETURN
CALCULATE( [Satışlar] ,
    FILTER( ALL( 'Tarih'[Tarih] ) , 
        'Tarih'[Tarih] <= _MatristekiSonTarih &&
         YEAR( 'Tarih'[Tarih] ) = YEAR( _MatristekiSonTarih)
    )
)

DATESQTD hesaplamak istersek buradaki condition’lara && deyip çeyrekler de eşit olacak demek yeterli!

Satışlar DAX QTD VAR = 
VAR _MatristekiSonTarih = MAX( 'Tarih'[Tarih] )
RETURN
CALCULATE( [Satışlar] ,
    FILTER( ALL( 'Tarih'[Tarih] ) , 
        'Tarih'[Tarih] <= _MatristekiSonTarih &&
         YEAR( 'Tarih'[Tarih] ) = YEAR( _MatristekiSonTarih) &&
         QUARTER( 'Tarih'[Tarih] ) = QUARTER( _MatristekiSonTarih )
    )
)

Yazıyı buraya kadar tek seferde kesintisiz okuyabilen arkadaşları tebrik ederim!

Yazıdaki modeli -bloga üyeyseniz- indirebilirsiniz.

Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!