Power BI modellerinde zaman zaman sütun değerlerini veya tablodaki satır sayılarını sayma ihtiyacı duyarız; bunu yapmamızı sağlayacak sayma fonksiyonları DAX’ta mevcut.
Yazıdaki örneklerin tamamında aşağıda resmini verdiğim modeli kullanacağım: ‘Ürünler’ ve ‘Satışlar’ tablosu ‘ÜrünID’ sütunu üzerinden ilişkili, ‘Ürünler’ tablosunda üç farklı Ürün ID var, ‘Satışlar’ tablosunda ise dört farklı ID ve bir de ID değeri “boş” (blank) olan bir satır var. ‘Ürün ID’ 5, satışlar tablosunda var, ama ürünler tablosunda yok! [Toplam Satışlar] metriği ise ‘Satışlar'[Tutar] sütununun SUM’ını alıyor.
Önce “Referential Integrity” ne demek onu açıklayalım: Tabular engine, ‘Ürünler’ ve ‘Satışlar’ tablosunu one-to-many şeklinde ilişkilendirebilmek için, ‘Satışlar’ tablosunda gördüğü her bir ID değeri için ‘Ürünler’ tablosunda bir karşılık bulmak zorundadır.
Ürünler tablosunda karşılığını bulamadığı tüm ‘Satışlar’ satırları için, ‘Ürünler’ tablosuna görünmeyen bir BLANK satırı ekler ve karşılığını bulamadığı tüm kayıtları bu “Blank” satırıyla eşleştirir.
Böylelikle “fact” tablosundaki istisnasız her bir satır için master tabloda bir karşılık bulmayı garanti etmiş olur.
** Eğer multidimensional (MDX) bir model kuruyor olsaydık yukarıdaki veri yapısıyla model hiç çalışmazdı, veriyi önce düzeltmek-temizlemek gerekirdi. Tabular modellemenin + farklı tasarım özelliklerinden biri.
Şimdi sayma ile ilgili bazı fonksiyonlara geçebiliriz:
COUNTROWS (Tablo) : Verilen tabloda gördüğü satır sayısını döndürür.
COUNT: Nümerik ve nümerik değerlere çevrilebilen değerler içeren sütunlar üzerinde çalışır. Sütunda gördüğü “boş” olmayan değerleri sayar.
COUNTA: Tüm sütun tipleri üzerinde çalışır. Sütunda gördüğü “boş” olmayan değerleri sayar.
COUNTBLANK: Sütunda gördüğü “boş” değerleri sayar.
DISTINCTCOUNT: Sütunda gördüğü tekil (unik) değerleri sayar. “Blank” ‘i (“boş”) bir değer olarak görür ve sayar.
Bu tanımlar üzerinden aşağıdaki metrikleri yazıp hepsini bir matrise düşürüyorum.
(1) CountRows ÜRÜNLER := COUNTROWS('Ürünler')
(2) CountRows SATIŞLAR := COUNTROWS('Satışlar')
(3) CountBlank ÜRÜNLER := COUNTBLANK('Ürünler'[Ürün ID])
(4) CountBlank SATIŞLAR := COUNTBLANK('Satışlar'[Ürün ID])
(5) Count := COUNT('Satışlar'[Ürün ID])
(6) CountA := COUNTA('Satışlar'[Ürün ID])
(7) DistinctCount ÜRÜNLER := DISTINCTCOUNT('Ürünler'[Ürün ID])
(8) DistinctCount SATIŞLAR := DISTINCTCOUNT('Satışlar'[Ürün ID])
Matristeki “Ürün ID” alanı “Ürünler” tablosundaki Ürün ID alanı.
Açıklamalara geçmeden önce daha önce yazdığım önemli bazı cümleleri tekrarlayayım:
- Her bir görselin her bir hücresi ayrı ayrı hesaplanır.
- One tarafındaki tablo filtrelendiğinde, tabular engine otomatik olarak many tarafındaki tabloyu da filtreler.
Matrise düşürülen “Ürün ID” ‘Ürünler’ tablosundan geldiği ve ‘Satışlar’ tablosuyla arada one-to-many ilişki olduğu için, ‘Satışlar’ tablosu üzerinde hesap yapan tüm metrikler, ‘Satışlar’ tablosunun “Ürün ID” bazında filtrelenmiş halini görüyorlar. Aynı şekilde ‘Ürünler’ tablosu da “Ürün ID” bazında filtrelenmiş durumda.
1: İlk formül ‘Ürünler’ tablosunu sayıyor. Tabloda “boş” bir satır göremediği için (eklenen BLANK satırı, tabular engine’nin arada ilişki kurabilmek için oluşturduğu fiktif bir satır) boş gözüken “Ürün ID” satırının karşılığında bir şey döndürmüyor. Çünkü öyle bir satır gerçekte yok.
Ürün ID=1 için ‘Ürünler’ tablosunu filtrelediğinde gördüğü tek bir satır var: Ürün ID’si 1 olan satır. Dolayısıyla 1 sayısını döndürüyor. Aynı şeyi ID’leri 2 ve 3 olan satırlar için de ayrı ayrı hesaplıyor.
“Total” satırına gelince; formülün -bu hücreyi hesaplarken- gördüğü tablo tüm ‘Ürünler’ tablosu. Dolayısıyla varolan 3 satırı da görüyor ve 3 sayısını döndürüyor.
2: İkinci formül, ‘Satışlar’ tablosunda görebildiği satırları sayıyor. Matristeki Ürün ID, ‘Satışlar’ tablosunu filtrelediği için her bir hücreyi hesaplarken, aslında ilgili Ürün ID’den ‘Satışlar’ tablosunda kaç satır varsa onları görüyor.
“Boş” olan “Ürün ID” ‘si için, ‘Satışlar’ tablosunda 2 tane satır görüyor. (Ürün ID’si 5 olan ve boş olan satırlar). Aynı şekilde Ürün ID’si 3 olan satır için de ‘Satışlar’ tablosunda 2 satır sayıyor. Diğerleri içinse her bir ID için birer tane satır olduğundan 1 sayısını döndürüyor. “Total” satırı için, tüm ‘Satışlar’ tablosunu gördüğünden, ID’si boş olan satır dahil 6 sayısını döndürüyor.
3: ‘Ürünler’ tablosundaki [Ürün ID] sütunundaki değerlere bakıyor, hiç boş değer göremiyor, dolayısıyla “Total” satırı dahil bir şey döndürmüyor.
4: . ‘Satışlar’ tablosunda [Ürün ID]’si boş olan satırları sayıyor. Sayabileceği tek satır, ‘Satışlar’ tablosundaki Ürün ID ‘si boş ve Tutar değeri 20 olan satır.
5 ve 6: . ‘Satışlar’ tablosundaki “Ürün ID” nümerik bir sütun olduğu için her ikisi de aynı hesaplamaları yapıyor. ‘Ürünler’ tablosundaki fiktif “blank” satırı için, ‘Satışlar’ tablosunda aslında gördüğü iki satırdan sütun değeri “boş” olmayanları sayıyor, yani “boş” değeri saymıyor. Ürün ID 3 için ‘Satışlar’ tablosunda 2 tane satır görüyor ve bunları sayıyor. Total hücresini hesaplarken de gene “boş” Ürün ID’li satırı saymayıp 5 sayısını döndürüyor.
7: .’Ürünler’ tablosundaki “Ürün ID” sütununda gördüğü tekil değerleri sayıyor. Ürün ID’si “boş” olan satır için tabloya baktığında gerçekte öyle bir satır olmadığı için bir şey döndürmüyor. Diğer ID’leri gördüğü için her biri için 1 sayısını döndürüyor. “Total” satırı içinse tüm ‘Ürünler’ tablosuna bakıp gerçekte var olan 3 farklı ürünü görüyor.
8: ‘Satışlar’ tablosundaki “Ürün ID” sütununda gördüğü tekil değerleri sayıyor. Ürün ID’si boş olan satır için hesaplama yaparken, ‘Satışlar’ tablosuna ilişki üzerinden atladığında iki tane satır görüyor: Ürün ID=5 olan satır ve Ürün ID’si boş olan satır. “Boş”‘u da bir değer olarak saydığından 2 sayısını döndürüyor. “Total” satırı içinse “boş” dahil gördüğü tüm tekil değerleri sayıyor. (1,2,3,5 ve boş)
Bu formüller ve açıklamalar üzerinden matristeki rakamlar üzerinde biraz vakit geçirip düşünmekte fayda var.
…
Bir tablodaki herhangi bir sütun üzerinden aşağıdaki toplam her zaman ilgili tablodaki satır sayısını verir:
COUNTA ( 'Tablo 1'[Sütun A] ) + COUNTBLANK ( 'Tablo 1'[Sütun A] ) =
COUNTROWS ('Tablo 1')
…
COUNTROWS fonksiyonunun istediği tablo parametresi doğrudan bir tablo ismi olabileceği gibi, tablo döndüren bir “tablo fonksiyonu” da olabilir: DISTINCT veya VALUES gibi. Yazı okunabilir uzunlukta olmaktan çıkacağından bu fonksiyonlar için ayrı bir yazı yazacağım ama DISTINCTCOUNT fonksiyonuna değindiğim için şu cümleyi de ekleyip yazıyı tamamlayayım:
DISTINCTCOUNT ( 'Tablo 1'[Sütun A] ) =
COUNTROWS ( DISTINCT ( 'Tablo 1'[Sütun A] ) )