Power BI ‘da ya da DAX’ta diyelim bazı fonksiyonların işlevi gayet belli ve tek: Diyelim USERELATIONSHIP, amacı belli, kullanımı açık! Ama bazı fonksiyonlar var ki işlevi itibariyle çok yaratıcı formüller yazmak için kullanılabilir: VALUES () bunlardan biri. Çalışma mantığını iyi anlamak kaydıyla birçok farklı durum için kullanılabilir.
VALUES(), aynı ALL() gibi bir tablo fonksiyonu: Yani döndürdüğü şey bir tablo. Dolayısıyla bizden parametre olarak bir tablo isteyen diğer fonksiyonlarla birlikte kullanılabilir.
VALUES ( ) fonksiyonuna parametre olarak ya bir sütun adı veririz ya da bir tablo adı.
VALUES ( Tablo Adı veya Sütun Adı )
En yaygın kullanımı sütun adı vermek şeklinde olduğu için örnekleri bunun üzerinden ilerleteceğim ama notumuzu da düşelim: Parametre olarak bir tablo verdiğimizde döndürdüğü şey context’te gördüğü tablonun tüm sütunlarıdır ve varsa duplike satırları da içerir. (Varsa “blank” satırı da içerir, yazının ileriki bölümlerinde değineceğim.)
…
Hem ALL( Sütun Adı ) hem de VALUES( Sütun Adı ), bize, verdiğimiz sütun neyse bu sütundaki tekil değerleri içeren, tek sütunluk bir tablo döndürür. Aradaki en büyük fark ise -bir metrik içerisinde kullanıldığında- ALL ‘un context gözetmeksizin verdiğimiz sütundaki tüm tekil değerleri döndürmesi, VALUES ‘un ise filter context’te gördüğü tekil değerleri döndürmesidir.
Aşağıdaki metrikleri matrise düşürüyorum:
Tüm Alt Kategorilerin Sayısı :=
COUNTROWS( ALL( 'Ürün Alt Kategorileri'[Alt Kategori] ) )
Alt Kategori Sayısı :=
COUNTROWS( VALUES('Ürün Alt Kategorileri'[Alt Kategori] ) )
ALL( ‘Ürün Alt Kategorileri'[Alt Kategori] ), alt kategori üzerindeki tüm filtreleri kaldırdığından mevcut tüm alt kategorilerin tekil değerlerini içeren tek bir sütun döndürüyor, dıştaki COUNTROWS’da bu dönen tek sütunluk tablonun satır sayısını hesaplıyor.
VALUES(‘Ürün Alt Kategorileri'[Alt Kategori] ) ise mevcut filter context’e gördüğü alt kategorilerin tekil listesini içeren gene tek sütunluk bir tablo döndürüyor.
“Bilgisayarlar” kategorisinin altında 8 farklı, “Cep Telefonları”‘nın altında 4 farklı alt kategori var, vs.
VALUES ( ) , bir metrik altında kullanıldığında mevcut filter context’e riayet eder. ALL () ise etmez!
VALUES (…) ‘kısmında neyi gördüğünü bulabiliriz:
Filter Contextte Görünen Alt Kategoriler :=
CONCATENATEX(
VALUES( 'Ürün Alt Kategorileri'[Alt Kategori] );
'Ürün Alt Kategorileri'[Alt Kategori];
", "
)
Yukarıda -bir metrik altında kullanıldığında- ibaresini özellikle işaretledim, çünkü metriklerin hepsinde -istisnasız hepsinde- “=” ‘den sonrasını çalıştırmaya başlamadan önce herkes ilgili filter context’i bulmak zorundadır. Ve her zaman bir filter context vardır. ( “=” den sonra yazdıklarınızla mevcut ilk filter context’i değiştirebilirsiniz elbette! )
ALL() ve VALUES() fonksiyonlarını bir hesaplanmış tablo veya hesaplanmış sütun formüllerinde kullanırsak, bu kavramlarda aktif filter context olmadığından her ikisi de aynı sonucu döndürecektir.
Aşağıdaki formülleri hesaplanmış tablo olarak eklediğimizde her iki fonksiyonun da aynı sonuçları döndürdüğünü görebiliriz.
Hesaplanmış Tablo_ALL = ALL( 'Ürünler'[Renk] )
Hesaplanmış Tablo_VALUES = VALUES( 'Ürünler'[Renk] )
…
VALUES gibi mevcut filter context’e riayet eden çok benzer bir başka fonksiyon daha var: DISTINCT ! Her ikisi de benzer mantıkla çalışıyor, tek farkla: VALUES, ilgili sütunda blank, boş bir değer varsa bunu da bir değer olarak dikkate alır. DISTINCT ise almaz! İlgili sütundaki boş satır verinin kendisinde olabilir, yani bildiğiniz boş bir satır vardır, sütun değerlerini açıp baktığımızda gözümüzle görürüz!
Bazen bu “blank” satırı fiktif de olabilir! Yani gerçekte veride öyle bir satır yoktur ama tablolar arası ilişki kurarken –tamamen eşleşmeyen verilerden kaynaklanan– bir uyuşmazlık olabilir! Power BI, tablolar arası ilişki kurarken -eğer gerekli teknik şartlar tamamsa- many tarafındaki tabloda görüp de one tarafındaki tabloda karşılığını bulamadığı tüm kayıtlar için -referential integrity gereği- one tarafındaki tabloda fiktif bir “blank” satırı oluşturur. Sütun değerlerini açıp baktığımızda bu “blank” satırı göremeyiz ama fiktif olarak bu satır mevcuttur. Ve VALUES bu “blank” satırı bir değer olarak dikkate alır. ( DISTINCT almaz! )
** Konuyla ilgili bu yazıya göz atmak isteyebilirsiniz.
Kullandığım örnek modelin veri setinden, Alt Kategoriler tablosundan bilinçli olarak “Çamaşır Makineleri” alt kategorisini filtreliyorum, yani data’dan uçuruyorum. Satışlar tablosunda alt kategorisi “çamaşır makinesi” olan ürünlerin satışları duruyor. Bu durumda alt kategorisi bulunamayan, eşleşmeyen kayıtlar olmuş oluyor.
Alt kategori sayısını bu sefer DISTINCT ile sayalım:
Alt Kategori Sayısı_DISTINCT :=
COUNTROWS( DISTINCT( 'Ürün Alt Kategorileri'[Alt Kategori] ) )
Kayıt eşleşmemesi sebebiyle ortaya çıkan fiktif boş satırı DISTINC bir değer olarak saymıyor!
** Aynı benzer durum -yani blank satırı sayıp saymama durumu- ALL için de geçerli. ALL bu blank satırı bir değer olarak dikkate alıyor. Ekürilerinden ALLNOBLANKROW ise dikkate almayacaktır.
Tüm Alt Kategorilerin Sayısı_ALLNOBLANKROW :=
COUNTROWS( ALLNOBLANKROW( 'Ürün Alt Kategorileri'[Alt Kategori] ) )
** Veri eşleşmemesinden dolayı ortaya çıkan bu fiktif “blank” satırını ortadan kaldırmaya çalışıyoruz bazen “görüntüyü bozduğu” için ama tam aksine gözümünüzün önünde durması -en azından başlangıçta- daha iyi. Çünkü veriyle ilgili modelimizde bir tutarsızlık olduğunu anlıyoruz.
Ama nihai, doğru, düzeltilmiş bir veri modelinde bu tür eşleşmeyen kayıtların -normalde- olmaması gerekir!
Ara not :
Bazen modelde, context’te gördüğümüz birden fazla sütunun tekil değer listesinin kombinasyonunu bulmaya ihtiyacımız olur. Bunu yapmak için VALUES malesef işimize yaramıyor. Misal context’teki tekil Kategori-Alt Kategori kombinasyonunu bulmak için VALUES ( ‘Ürün Kategorileri'[Kategori], ‘Ürün Alt Kategorileri'[Alt Kategori] gibi bir syntax yok! VALUES da DISTINCT de sadece tek bir sütun referansı kabul ediyor.
Böyle bir ihtiyaç varsa SUMMARIZE fonksiyonunu kullanmak gerekir.
SUMMARIZE( 'Ürünler' ;
'Ürün Kategorileri'[Kategori] ;
'Ürün Alt Kategorileri'[Alt Kategori]
)
gibi. Kapat ara not.
VALUES () fonksiyonunun bize lazım olduğu durumlardan biri, context’te gördüğümüz tekil değer listesindeki her bir değer için bir işlem yapmak istediğimiz durumlar. Bu tür durumlarda genelde VALUES ( Sütun Adı) ‘nı genelde bizden tablo isteyen başka bir fonksiyona -ki genelde bu bir iterator oluyor- veriyoruz. En belirgin durumlardan biri için bu yazıya göz atmak isteyebilirsiniz. “Toplam Satırı Toplamı Göstermiyor“
…
Tekil değer listesindeki her bir değer için bir işlem yapmanın yanısıra bu fonksiyonu bazen tek bir değeri yakalamak için kullanırız. Diyelim ki aşağıdaki gibi bir matris elde etmek istiyoruz.
Bize satırdaki kategoriyi “Satırdaki kategori …” text’iyle verecek. Aşağıdaki metriği matrise düşürüyorum:
Satırdaki Kategori :=
"Satırdaki kategori " & VALUES( 'Ürün Kategorileri'[Kategori] )
VALUES fonksiyonunu kullanırken aldığımız en önemli hata mesajı: “A table of multiple values….” . Yani, tek bir değer beklerden birden fazla değerin olduğu bir tablo var elimde ve bunlarla ne yapacağımı bilmiyorum!
Formülü yazarken hiç itiraz etmezken matrise düşürdüğümüzde yukarıdaki hata mesajını verip patladı! Bütün sorun toplam satırından kaynaklanıyor aslında! Çünkü toplam satırına baktığında buradaki filter context’te VALUES( ‘Ürün Kategorileri'[Kategori] ) formülünün döndürdüğü listede birden fazla değer var ve bunlarla ne yapacağını bilemiyor! Bu tür durumlarda HASONEVALUE, ISFILTERED gibi fonksiyonlarla VALUES() ‘un döndürdüğü tabloda tek bir değer mi var kontrol edebilir ve formülümüzü buna göre düzenleyebiliriz.
Ben benzer mantıkta başka bir örnek vereyim:
Satırdaki Kategori :=
IF(
COUNTROWS( VALUES( 'Ürün Kategorileri'[Kategori] ) ) = 1;
"Satırdaki kategori " & VALUES( 'Ürün Kategorileri'[Kategori] );
CONCATENATEX(
VALUES( 'Ürün Kategorileri'[Kategori] );
'Ürün Kategorileri'[Kategori];
"-"
)
)
COUNTROWS( VALUES( ‘Ürün Kategorileri'[Kategori] ) ) = 1 ile benzer bir etkiyi yaratmak mümkün: Sadece tek bir değer mi görüyorsun? Eğer birden fazla değer görüyorsan da, bu değerleri araya “-” koyarak birleştir!
Burada dikkat çeken durumlardan biri şu: VALUES’un döndürdüğü tabloda tek bir değer olması durumunda bu tablo yapısını otomatik olarak skalar bir değere dönüştürüyor! “Bilgisayarlar” satırı için döndürdüğü şey tek sütun tek satırdan oluşan ve değer olarak “Bilgisayarlar” değerini içeren bir tablo! Bunu sabit bir değere dönüştürüyor.
…
Gelelim VALUES ‘un yaratıcılığı arttıran bir başka örneğine!
Tüm tarihlerdeki satışları hesaplayan aşağıdaki gibi bir metriğimiz olsun:
Tüm Satışlar := CALCULATE( [Satışlar] ; ALL('Tarih') )
Eğer bu metriğin yıl değişimlerine tepki vermesini istiyorsak VALUES ile modifiye edebiliriz!
Tüm Satışlar_VALUES :=
CALCULATE( [Satışlar] ; ALL('Tarih') ; VALUES( 'Tarih'[Yıl] ) )
ALL ( ‘Tarih’) ile tüm tarihlere bakacaksın derken, VALUES (‘Tarih'[Yıl]) ile gördüğün context’teki yıl değerine riayet edeceksin demiş olduk. CALCULATE’e verdiğimiz filtrelerin AND ile birleştiğini hatırlayalım! “2007” yılına ait satırlar için cümleyi tekrarlamak gerekirse: Tüm tarihlere bak VE yıl da 2007 olacak. Dolayısıyla hesaplanan şey 2007 yılına ait tüm satışlar!
Biraz daha faydalı bir örnek yapalım: Tarih bazlı kümülatif hesaplamaları için DATESYTD, DATESQTD ve DATESMTD gibi fonksiyonlar var doğrudan kullanabileceğimiz! Ama haftalık kümülatif yok! Çünkü haftanın standart bir tanımı yok! Yılın ilk haftası 1 Ocak ile mi başlıyor, ilk Pazartesi ile mi başlıyor vs!
Satışların aylık kümülatifini bulmak DATESMTD ile çok kolay, haftalık kümülatifi nasıl bulacağız? Aynı mantıkla yazacağımız formüle, Hafta No değişirse (Tarih tablosunda ilgili tarihin hangi haftaya ait olduğunu gösteren bir sütunun bulunması lazım!) buna riayet et dememiz lazım!
Satışlar Aylık Kümülatif :=
CALCULATE( [Satışlar] ; DATESMTD( 'Tarih'[Tarih] ) )
Satışlar Haftalık Kümülatif :=
CALCULATE( [Satışlar] ;
DATESMTD( 'Tarih'[Tarih] ) ;
VALUES( 'Tarih'[Hafta No] )
)
VALUES , DAX’taki tabiri caizse en sevdiğim fonksiyonlarından biri. Bununla finansal raporlardaki P&L formülleri de yazılır, yukarıdaki örnekte olduğu gibi DAX’ın standardında olmayan haftalık kümülatif formülü de yazılabilir. Yaratıcılığınıza kalmış!
Bu fonksiyonla ilgili aslında son bir durum daha var yazmam gereken: ALLEXCEPT ile benzerliği ve farklılığı. Yazı okunabilir uzunlukta olmaktan çıkacağından bunu da sonraki yazıya bırakayım.
Yazıdaki modeli indirebilirsiniz.
Sadece üyeler görebilir. Hızlı üyelik için sosyal medya hesabınızla giriş yapabilirsiniz!