Power Pivot #16 – DAX-ın fundamental elementi – FILTER funksiyası

Power Analytics

DAX-ı və onun 3 əsas tətbiq sahəsindən ikisi olan Power Pivot və Power BI-ı öyrənmək üçün aşağıdakı 5 təməl komponentini anlamaq fundamental əhəmiyyət daşıyır:

  • Filter və Row kontekstləri
  • Cədvəllər (Tables)
  • Cədvəllərarası əlaqələr (Relationships)
  • DAX funksiyaları
  • Alqoritmik mexanizmlər

Hazırki yazıda, cədvəllərə istədiyimiz “formanı” verməyə yarayan FILTER funksiyasından bəhs edəcəyik.

Mövzuya bir sual ilə başlayaq – Cədvəlləri filterləmək niyə bu qədər əhəmiyyətlidir?

Bunun səbəbi – DAX-dakı əksər hesablamaların, o və ya bu şəkildə cədvəllər formalaşdıqdan sonra aparılmasındadır. Yəni, hər hansı bir hesablama aparılmadan öncə, DAX, cədvəldəki hansı sətirlərin aktiv olub-olmadığına baxacaqdır. Əgər cədvələ heç bir filterləmə tətbiq edilməyibsə, bu halda, cədvəldəki bütün sətirlər hesablamaya daxil ediləcək, əks halda ancaq aktiv sətirlər nəzərə alınacaqdır.

Anbardakı mal qalıqlarını göstərən WH adlı bəsit bir cədvəl düşünün:

Şəkil 1. WH adlı cədvəl

Biz anbarlardakı ümumi mal qalığını öyrənmək istəsək, SUM ilə sadə bir measure yaza bilərik:

DAX formulu 1. WH adlı anbardakı bütün məhsulların cəmini hesablayan düstur

Bu measure-ı Pivot cədvəlimizə atsaq, 2 fərqli filter konteksti avtomatik olaraq işə düşəcək – məhsullar (Product) sətirlərdə və məkan (City & Region) sütunlarda. DAX formulumuzu yazarkən heç bir filterləmə əməliyyatı aparmadığımız üçün, cədvəldəki bütün sətirlər aktiv olaraq qaldı və nəticə etibarilə, 8 sətrin hər biri nəzərə alınaraq, 85 məhsulun hər birini hesabata daxil etdi.

Şəkil 2. Bütün anbarlarda yerləşən mal qalıqları.

FILTER funksiyası

Gəlin indi məsələyə başqa yerdən baxaq. Ancaq şəhərdə (City) yerləşən anbarlardakı məhsulların sayını hesablamaq istəsək nə etməliyik? Necə bir formul yazmalıyıq? FILTER funksiyası bu vəziyyətdə köməyimizə çatır və City olmayan bütün sətirləri deaktiv etməyə yardım edir.

DAX formulu 2. FILTER funksiyasının sintaksı.

Funksiya 2 parametrdən ibarətdir:

  • Table: Filterlənəcək cədvəlin adı.
  • Filter: Filterləmə kriteriyası.

Bir başqa deyişlə, bizə lazım olan cədvəli seçib, onun hansı sətirlərinin aktiv, hansılarının deaktiv olacağını müəyyən edirik. Bu əməliyyatın Excel-dəki analogiyalarından biri SUMIF funksiyasıdır. Orada da müəyyən kriteriyaya cavab verən dəyərləri cəmləyirdik, burada da eyni işi görür, lakin məsələyə fərqli perspektivdən yanaşırıq.

DAX formulu 3. Ancaq City-dəki anbarlardakı mal qalıqlarını hesablayan DAX düsturu.

Öncəliklə, FILTER funksiyası ilə, WH cədvəlindəki [Warehouse] adlı sütunun bütün sətirlərinə baxıldı və dəyəri City olan bütün sətirlər aktivləşdirilərək, digər bütün sətirlər iqnor edildi. Daha sonra iterator funksiyamız olan SUMX funksiyası aktiv olan bütün bu sətirləri bir-bir cəmlədi. Formulun ən sadə dillə açıqlaması belədir.

Şəkil 3. DAX formulu 3 ilə filterdən xaric edilən sətirlər və müvafiq Pivot cədvəl

Yuxarıdakı DAX formulumuz ilə (Units in City), 2 ,4 və 7-ci sətirləri deaktiv etmiş olduq, çünki bu sətirlərdə Region olan dəyərlər mövcuddur və bu measure Pivot cədvəlimizə əlavə edildikdə, Region olan bütün sətirlərin iqnor edildiyini, və hesabata daxil edilmədiyini görürük. Bütün anbarlarda 85 ədəd məhsul olmasına baxmayaraq, FILTER funksiyası ilə bizə lazım olmayan sətirləri hesablamalara daxil etməyərək, 62 ədədlik nəticəni əldə etdik

Eyni şəkildə, anbardakı məhsul sayı 15-dən yuxarı olan anbarların hesabatını da görmək istəyə bilərik. Belə olan halda, yeni measure aşağıdakı kimi yazılmalıdır:

DAX formulu 4. Mal qalığı 15-dən çox olan anbarları müəyyən edən formul.

FILTER funksiyası ilə, WH cədvəlindəki [Units in store] adlı sütunun bütün sətirlərinə baxıldı və dəyəri 15-dən yuxarı olan bütün sətirlər aktivləşdirilərək, digər bütün sətirlər iqnor edildi. Daha sonra SUMX funksiyası aktiv olan bütün bu sətirləri bir-bir cəmlədi.

Gəlin, yeni measure-ı pivot cədvələ əlavə edək.

Şəkil 4. DAX formulu 4 ilə yazılan formulun davranışı

Görürük ki, FILTER funksiyası ilə nəzərdə tutulan filter kontekstini “dəlmiş” olduq, çünki sətirdə Jacket filteri (Bütün pivot cədvəllərdə sətir və sütunlar müstəqil filter vəzifəsi daşıyır) durmasına və Jacket məhsulunun anbardakı ümumi sayının 27 olmasına baxmayaraq, FILTER funksiyası bu filter kontekstini yan keçərək, bizim müəyyən etdiyimiz kriteriyaya tabe oldu.

Aşağıda hər 3 DAX formulunun eyni filter konteksti daxilində necə fərqli davranış sərgilədiyini görürük:


Şəkil 5. FILTERsiz və FILTERli formulların yanaşı müqayisəsi
  • Total Units formuluna heç bir filter kriteriyası tətbiq etmədiyimiz üçün, hesablamaya WH cədvəlindəki 8 sətrin hər biri daxil oldu.
  • Units in City formulunda kriteriya ancaq City olan sətirlər olduğu üçün, hesablamalarda 5 sətir iştirak etdi.
  • Units>15 formulunda isə 15-dən böyük dəyər daşıyan sadəcə 2 sətir aktiv rol aldı.

Yekunda, bütün filterləri ləğv edən ALL funksiyası ilə yeni bir measure yazaq və ümumi cədvələ əlavə edərək hesablamanı necə apardığına baxaq. Öncə mütləq mənada bütün satışları hesablayan və heç bir filterə tabe olmayan yeni measure:

DAX formulu 5. İstisnasız bütün sətirləri hesablamağa daxil edən ALL funksiyası.

ALL funksiyasının burdakı vəzifəsi, gələcəkdə içində olacağı heç bir filter kontekstinə məhəl qoymadan mütləq dəyəri hesablamaqdır. = ALL (WH) yazmaqla, WH cədvəlindəki istisnasız bütün sətirlərin daim aktiv olmasına nail olaraq, SUMX ilə hər zamankı işimizi görür və aktiv olan hər bir sətri tək-tək cəmləyirik.

Şəkil 6. ALL funksiyasının davranışını nümayiş etdirən Pivot cədvəl.

Filter kontekstinə tabe olmadığı üçün, sətirdə, sütunda nə varsa heç birinə fikir vermədən ümumi cəmi verdi. Təklikdə heç bir məna ifadə etməyən bir sütun kimi görünsə də, özündən əvvəl gələn hər 3 sütunun nisbi dəyərlərini ölçməyə yarayacaqdır. Məsələn, [Total units] sütunundakı dəyərlərin ümumi cəmə olan faiz nisbətlərini hesablayaq (Total Units / ALL Units) və [ALL units] sütununu silək:

Şəkil 7. Nisbi faizləri hesablayan Pivot cədvəl.

Hər şey anidən məna qazanmış oldu.

Beləliklə, FILTER funksiyası haqqında giriş xarakterli məlumatlar əldə etmiş olduq. Unutmayaq ki, pivot cədvəlin filter kontekstini yan keçmək istəyiriksə, FILTER funksiyasının davranış şəklini yaxşı anlamalıyıq.