Hiyerarşiler, PATH, PATHITEM

Tabular engine, yani Power BI ‘ın analitik motoru, parent-child hiyerarşi yapısını doğrudan desteklemiyor. MDX’teki gibi doğrudan bir destek yok, bunun yerine hiyerarşileri sütun bazlı yapıya çevirmemizi sağlayacak fonksiyonlar var: PATH, PATHITEM vs gibi.

Hiyerarşiden neyi kastettiğimizi açalım, aşağıdaki gibi bir veri setimiz var:

Tüm ERP’lerdeki hesap planları üç aşağı beş yukarı bu yapıya benziyor, tüm hesap kodları tek bir tabloda, her bir hesabın ait olduğu bir üst grubu (Parent) gösteren bir başka sütun daha var.

Örneğin 1140 no’lu hesabın bir üst grubu 1120 no’lu hesap grubu, o da 110 numaralı bir başka grubun üyesi, bu grubun üstünde 10, onun üzerinde de en tepede 1 nolu hesap grubu var. Her bir ara-üst grubun (Parent) altında bir veya daha fazla alt hesap olabilir.

Eğer 10 numaralı hesap grubu için bir hesaplama yapacaksak, bunun altındaki tüm doğrudan veya dolaylı hesap-hesap gruplarını dahil etmemiz lazım. Hiyerarşi kabaca bu şekilde.

Bu tabloyu, her bir seviyeyi bir sütunda gösterecek şekilde çevirmemiz lazım.

Her bir hesabın tüm hiyerarşi yapısını bulmamızı sağlayacak ilk fonksiyon PATH. Hesap kodu ve parent sütunlarını vererek her bir hesabın full hiyerarşisini bulmak mümkün.

Hesaplanmış sütun olarak hesaplar tablosuna “Hiyerarşi” sütunu ekliyorum.

Hiyerarşi = PATH( Hesaplar[Hesap Kodu] ; Hesaplar[Parent Hesap Kodu] )

En üst seviyedeki 1 hesabının altında bir çok hesap ve-veya hesap grubu var. Hiyerarşideki seviye sayısı da değişken. 110 no’lu hesap için 3 seviye varken (1-10-110), 1130 no’lu hesap için 5 seviye var (1-10-110-1120-1130) .

Her bir hesap için hiyerarşide kaç seviye olduğunu PATHLENGHT ile bulabiliriz.

Seviye Sayısı = PATHLENGTH( 'Hesaplar'[Hiyerarşi] )

“Hiyerarşi” sütunu tek başına anlamlı bir sütun değil, fakat birazdan oluşturacağımız her bir seviyeyi gösteren hesaplanmış sütunları yaratırken referansımız olacak!

Hiyerarşideki her bir seviyeyi ayırmamızı sağlayacak fonksiyon ise PATHITEM.

Her bir seviye için ayrı ayrı hesaplanmış sütunlar ekliyorum.

1. Seviye = PATHITEM( 'Hesaplar'[Hiyerarşi] ; 1 ; INTEGER )

2. Seviye = PATHITEM( 'Hesaplar'[Hiyerarşi] ; 2 ; INTEGER )

3. Seviye = PATHITEM( 'Hesaplar'[Hiyerarşi] ; 3 ; INTEGER )

Bu şekilde tüm seviyeleri yazdırmak mümkün. PATHITEM’ın ikinci parametresindeki rakam göstermek istediğimiz seviye numarası. Son parametreye ise oluşan sütunun tam sayı olmasını istiyorsak INTEGER demek gerekiyor, aksi taktirde oluşan sütun Text tipinde olacaktır.

Her bir seviye için kod numarası göstermek yerine, ilgili kodun -yani hesabın- adını getirmek görsel açıdan daha uygun olacak. Bunun için kullanabileceğimiz fonksiyon ise LOOKUPVALUE. Adı üstünde, ilgili kodun karşılığını hesap tanımı sütununda arayıp bulmamızı sağlayacak.

Hesaplanmış sütun olarak yazdığım formülleri aşağıdaki gibi modifiye ediyorum:

1. Seviye =
LOOKUPVALUE (
    'Hesaplar'[Hesap Tanımı] ; -- Bu değeri getir
    'Hesaplar'[Hesap Kodu] ; -- Bu sütunda ara
    PATHITEM ( 'Hesaplar'[Hiyerarşi] ; 1 ; INTEGER ) -- Bu değeri ara
)

Son parametrede dönen seviye değerini, Hesap Kodu sütununda  bul, bulduğun hesap kodunun tanımını getir!

** PATHITEM fonksiyonunun son parametresine -bu örneğe göre- INTEGER yazmamızın sebebi, modeldeki “Hesap Kodu” sütununun da INTEGER olması. LOOKUPVALUE fonksiyonu arama yaparken text bir sütünu tam sayı bir sütunla karşılaştıramaz ve hata mesajı verirdi. Eğer PATHITEM formülüne INTEGER parametresini vermezsek, LOOKUPVALUE fonksiyonunda hata mesajı almamak için formülün PATHITEM kısmını VALUE() parantezine almak gerekirdi. VALUE(), rakam içeren text bir değeri tam sayıya dönüştüren bir fonksiyon.

Benzer mantıkla tüm seviyeleri yazabiliriz artık.

2. Seviye =
LOOKUPVALUE (
    'Hesaplar'[Hesap Tanımı] ;
    'Hesaplar'[Hesap Kodu] ;
    PATHITEM ( 'Hesaplar'[Hiyerarşi] ; 2 ; INTEGER )
)

3. Seviye =
LOOKUPVALUE (
    'Hesaplar'[Hesap Tanımı] ;
    'Hesaplar'[Hesap Kodu] ;
    PATHITEM ( 'Hesaplar'[Hiyerarşi] ; 3 ; INTEGER )
)

…

Seviyeler üzerinden bir matris oluşturduğumuzda kozmetik olarak bir durum var yalnız!

Bazı orta-üst hesap gruplarının alt seviye detayları olmadığı için “blank” değerler çıkıyor.

Eğer seviye yoksa, bir önceki seviyeyi al diyerek, 2. seviyeden başlamak üzere (çünkü 1. seviye örneğe göre her zaman dolu) formülleri modifiye etmemiz lazım.

2. Seviye =
IF( PATHITEM ( 'Hesaplar'[Hiyerarşi] ; 2 ; INTEGER ) = BLANK() ;
    'Hesaplar'[1. Seviye] ;
    LOOKUPVALUE (
        'Hesaplar'[Hesap Tanımı] ;
        'Hesaplar'[Hesap Kodu] ;
        PATHITEM ( 'Hesaplar'[Hiyerarşi] ; 2 ; INTEGER )
    )
)

3. Seviye =
IF( PATHITEM ( 'Hesaplar'[Hiyerarşi] ; 3 ; INTEGER ) = BLANK() ;
    'Hesaplar'[2. Seviye] ;
    LOOKUPVALUE (
        'Hesaplar'[Hesap Tanımı] ;
        'Hesaplar'[Hesap Kodu] ;
        PATHITEM ( 'Hesaplar'[Hiyerarşi] ; 3 ; INTEGER )
    )
)

…

Bu şekilde matristeki kozmetik durumu da düzeltmiş oluruz.

** Formülde if testine BLANK() yerine ISBLANK() varyasyonu da yazılabilir.

Veride kaç seviye sayısı olduğunu yukarıda bulmuştuk. Bazen bu tür hiyerarşik yapılarda matriste (görselde) kaçıncı seviyeye baktığımızı görmek isteyebiliriz.  Eğer çok alt seviye detaya bakıyorsak “şunu yap bunu yap” demek için lazım olabilir. Bunu da daha önce kullandığımız ISINSCOPE fonksiyonu ile yazabiliriz.

Hangi Seviyedeyiz :=
ISINSCOPE( 'Hesaplar'[1. Seviye] ) +
ISINSCOPE( 'Hesaplar'[2. Seviye] ) +
ISINSCOPE( 'Hesaplar'[3. Seviye] )

Yazıdaki modeli indirebilirsiniz.

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