DAX’ta Çelişkili Filtreleri Birleştirme Yöntemleri.

Giriş

Son makalemde, çağırılan ölçüyle aynı filtri değiştiren iç içe geçmiş ölçülerin nasıl ele alınacağından bahsetmiştim.

Eğer okumadıysanız, işte o makale:

How to Solve Issues When You Nest Measures While Overwriting the Same FilterIn DAX, we reuse existing measures all the time when writing new measures. What happens when we write a measure based on another measure and try to change a filter already set in the nested measure?Salvatore Cagliari · 7 min read

Ancak kısa bir süre sonra, bu yaklaşımın işe yaramayacağı benzer bir durumla karşılaştım.

Bu durumda ne oldu?

Durum ve veri

Siparişları alan ve bir siparişin her durumda ne kadar kaldığını izlemek isteyen bir işletmeyi düşünün.

1. Açık

2. Beklemede

3. Sevk hazırlığında

4. Kargolandı

5. Faturalama

6. Kapalı

Sorun şu: Kapalı her sipariş her durumda ne kadar kaldı?

Bu nedenle siparişleri sayarken, kapalı siparişler için bir filtre ekliyorum.

Ancak durumlar arasındaki değişimi nasıl izleyebilirim?

Sorun nedir?

Bir sipariş tablonuz olduğunda, bu tabloda siparişin güncel durumunu işaretleyen bir durum sütunu bulunabilir.

Ancak yalnızca kapalı siparişleri göz önünde bulundurursam, diğer durumlar için değişimi nasıl izleyebilirim?

Yalnızca kapalı siparişlere bakarsam, başka herhangi bir durumda olan siparişleri görmem.

Bu çelişki, veri modelini değiştiren bir çözüm gerektiriyor.

Kolay çözüm

Bazı ERP sistemleri şöyle bir sipariş tablosuna sahip olabilir:

Şekil 1 – Durum geçişlerinin tarihlerini içeren basit bir sipariş tablosu (Görsel Yazar’a ait)

Bu şekilde, örneğin Power Query’deki #duration() fonksiyonunu kullanarak durumlar arasındaki gün sayısını hesaplamak kolaydır.

Durumlar arasındaki süreleri içeren sütunları toplayan birden fazla ölçü oluşturabilirsiniz.

Ancak durum böyle değilse ne olur?

Siparişler herhangi bir izleme olmadan durumlar arasında geçiş yaptığında.

Ya da yalnızca bir ölçü oluşturmak ve durumlar arasında ayrım yapmak istediğinizde?
Çözüm bunu izin veriyorsa, sipariş girişinden sipariş kapatılışına kadar olan toplam süreyi hesaplayabilirsiniz.

Kaynaktaki değişiklikleri izleyin

Şimdi bu, mümkün olanlarla ve kaynağın çalıştığı veri platformuyla ilgili.

Kapalı bir sistemse, örneğin altındaki veriye erişemiyorsanız, aynı güne ait değişiklikleri izlemek için günde birden fazla veri çıkarma işlemi yapmanız gerekir.

Bu, kaynak sisteme yük ekleyebilir. Bu nedenle, gereksinimleri karşılamaya yönelik yardım veya öneri için uygulama sağlayıcısıyla iletişime geçin.

Kaynak sisteme yükü azaltmaya yönelik olası bir çözüm, yalnızca gerekli tabloyu bir Power BI Dataflow kullanarak çıkarmaktır. Ardından verileri Dataflow’dan Power BI’a yükleyin.

Uygulamanın veri sistemi erişiminiz varsa, bir CDC (Change Data Capture / Veri Değişikliği Yakalama) çözümü ekleyebilirsiniz.

Örneğin SQL Server, CDC işlevselliğini içerir.

CDC, bir tablodaki tüm verilere yapılan tüm değişiklikleri kaydetmenizi sağlar.

Bu şekilde, değişikliklerin kesin tarih ve saatini de dahil ederek tüm değişiklikleri izleyebilirsiniz.

Bu yaklaşım, her sipariş durumu değişikliğinin tarih ve saatini içeren sipariş tablosunun tarihlendirilmiş bir sürümünü almanızı sağlar.

SQL Server’daki CDC’yi açıklamak bu makalenin kapsamı dışında.

Daha fazla bilgi için yukarıdaki belgeleri okuyun.

Bu özelliği açıklayan çevrimiçi olarak da yeterli bilgi var.

Tarihlendirmeyi ekleyin

Böyle bir uygulamaya erişemediğim için verileri simüle etmek zorundayım. Bunun için yukarıdaki tabloyu alıp istenen sonuca ulaşmak için üzerinde değişiklikler yapıyorum.

Hedef, sipariş tablosunun tarihlendirilmiş sürümünü doğrudan elde etmek.

Yukarıdaki tablodan bir tarihlendirilmiş tabloyu nasıl hesapladığım işte.

Sonuçları iyileştirmek için her tarihe bir zaman bileşeni ekledim. Ardından verileri bir SQL Server veri sistemine yükledim.

Tablodaki veri şudur:

Şekil 2 – Her tarihe bir zaman bileşeni eklenerek SQL Server’a yüklenen sipariş tablosu (Görsel: Yazarın)

Ardından, her durum için saat cinsinden zaman farkını ve sipariş ile kapatma tarih/saat’i arasındaki toplam süreyi hesaplarım:

sql

SELECT

[OrderNo]

        ,[Customer No]

        ,[Order Date]

DATEDIFF

(mi, [Order Date], [Start preparation])

/

     AS

        ,[Start preparation]

DATEDIFF

(mi, [Start preparation], [Shipping Date])

/

  AS

,[Shipping Date]

DATEDIFF

(mi, [Shipping Date], [Billing Date])

/

AS

DATEDIFF

(mi, [Order Date], [Shipping Date])

/

AS

,[Billing Date]

DATEDIFF

(mi, [Billing Date], [Closing Date])

/

AS

,[Closing Date]

DATEDIFF

(mi, [Order Date], [Closing Date])

/

AS

FROM

[dbo].[OrdersWithStates];

Gördüğünüz gibi, farkı dakika cinsinden hesaplamak için DATEDIFF() fonksiyonunu kullanıyor, ardından sonuca 60 ile bölerek saat’in kesirli kısmını alıyorum.

Bu sorgunun sonucu aşağıdaki gibidir:

Şekil 3 – Saat cinsinden sürelerle birlikte sipariş tablosunun sonuçları (Resim: Yazarın)

Bu sonuçlara dayanarak, bu verileri unpivot ederek her sipariş ve durum için bir satıra sahip bir tablo elde edebilirim.

Bunun SQL kodu şöyledir:

sql

WITH

[Prep]

AS

(

SELECT

[OrderNo]

,[Customer No]

,[Order Date]

DATEDIFF

(mi, [Order Date], [Start preparation])

/

AS

        ,[Start preparation]

DATEDIFF

(mi, [Start preparation], [Shipping Date])

/

  AS

        ,[Shipping Date]

DATEDIFF

(mi, [Shipping Date], [Billing Date])

/

       AS

DATEDIFF

(mi, [Order Date], [Shipping Date])

/

AS

,[Billing Date]

DATEDIFF

(mi, [Billing Date], [Closing Date])

/

AS

,[Closing Date]

DATEDIFF

(mi, [Order Date], [Closing Date])

/

AS

FROM

)

SELECT

[OrderNo]

,[Customer No]

,[Order Date]

,[Start preparation]

,[Shipping Date]

,[Billing Date]

,[Closing Date]

,[DatesAndTimes]

,[Duration]

FROM

(

SELECT

[OrderNo]

,[Customer No]

,[Order Date]

,[TimeInOrderInHrs]

,[Start preparation]

,[TimeInPrepInHrs]

,[Shipping Date]

,[TimeInShippingInHrs]

,[TimeOrderToShippinhgInHrs]

,[Billing Date]

,[TimeInBillingInHrs]

,[Closing Date]

                ,[TimeForFullOrderInHrs]

            FROM

[Prep])

AS

[p]

        UNPIVOT

        (

            [Duration]

FOR

[DatesAndTimes]

                        IN

                        ([TimeInOrderInHrs]

                        ,[TimeInPrepInHrs]

                        ,[TimeInShippingInHrs]

                        ,[TimeOrderToShippinhgInHrs]

                        ,[TimeInBillingInHrs]

                        ,[TimeForFullOrderInHrs])

                    )

                    AS

[unpvt];

 Bu sorgunun sonucu aşağıdaki gibidir:

Şekil 4 – Durum başına sürelerle birlikte açılmış sipariş tablosu. Bu tablo, sipariş tarihi ile sipariş tarihi ile sevkiyat tarihi arasındaki toplam süre arasındaki süreyi içerir (Görsel: Yazarın)

Bu tablo artık hesaplamalara başlamak için Power BI içine yüklenebilir.

Bunu yapmadan önce, kapanış tarihi olmayan bazı siparişler ekleyeceğim.

Ayrıca, datetime değerleri içeren sütunlara dayanarak tarih sütunları ve siparişleri duruma göre işaretleyen ek sütunlar ekledim:

Şekil 5 – Tarih sütunlarını ve siparişlerin belirli durumlarda olup olmadığını işaretleyen sütunları içeren tam sipariş tablosu (Görsel: Yazarın)

Maviyle işaretlenen sütunlar tarih sütunlarıdır. Bunlara ihtiyaç duyarım, çünkü tarih tablosuna ilişkiler ekleyebilmek için. Bu, zaman içeren orijinal sütunlarla çalışmaz, çünkü tarih tablosundaki sütun zaman içermez; dolayısıyla ilişki eşleşen satırları bulamaz.

Kırmızıyla işaretlenen sütunlar, siparişleri belirli durumlarda işaretleyen bayrak sütunlarıdır.

DAX kodu ekle

Gerek tek ölçü, Duration sütununun özetlenmesidir.

Ancak Total Duration’u hariç tutmam gerekir:

sql

Duration

=

        CALCULATE(

            SUM

(

‘OrdersStateDuration’

[Duration])

‘OrdersStateDuration’

[DatesAndTimes]

<>

“TimeOrderToShippinhgInHrs”

            )

 Sonrasında bu ifadeyi, Satır için Takvim Hiyerarşisini kullanarak bir Matris görseline ekliyorum:

Şekil 6 – “TotalOrderToShippingInHrs” için hariç tutulan Sürenin hariç tutulduğu Toplam Süre zamanları (Yazarın Resmi)

Sonrasında, OrdersStateDuration tablosundan [DateAndTimes] sütununu Matris sütunlarına ekliyorum.

Son olarak, yalnızca kapalı siparişleri dahil edecek bir filtre ekliyorum:

Şekil 7 – Durumlar bazında Süre ve yalnızca kapalı siparişleri dahil eden filtre ile son sonuçlar (Yazarın Resmi)

Şimdi, her durum için gereken zamanı gösteren gerekli sonuca ulaştım.

Başka bir yararlı metrik de her durum için ortalama zaman olurdu.

Ancak bu, yalnızca sürenin toplamını hesaplayan mevcut ölçünün bir kopyası.

DAX kodu ekle

Bu zorluğun çözümü iki faktöre bağlıdır:

1.      Verinin kaynak sistemden nasıl çıkarılabileceği

2.      Bir siparişin yeterli hassasiyetle takibinin nasıl yapılacağı

Bunlar, veri kaynağına göre değişen klasik veri mühendisliği zorluklarıdır.
Standart bir tarif yoktur, çünkü bu kaynak sisteme ve veri biçimine bağlıdır.

Veri tablosunu değişim geçmişiyle teslim eden sistemler gördüm, ancak çoğu sistem böyle yapmıyor.

Bir ilgili projede Jira’dan veri çıkardım ve bu sistem, saniye hassasiyetine kadar tüm alanlara yapılan tüm değişimleri kaydeden bir geçmiş tablosuna sahiptir. Orada veriyi gerekli biçime dönüştürmek kolaydı.

Jira’nın, her sorunu her durumda geçirdiği zamanla kaydeden bir tablosu bile var. Bu tür bir senaryo için bir ölçü oluşturulması gerektiğinde bu tablo son derece yardımcı olur.

Veri, yukarıda gösterilen veriye benzer bir biçime geldiğinde, Power BI karmaşık bir mantığa ihtiyaç duymaz ve sonuçları son derece verimli bir şekilde sağlayabilir.

Tecrübe edinimim, zekice veri modellemenin her zaman karmaşık DAX mantığından üstün olduğunu gösteriyor.

Bu yaklaşımın işinize yaradığını umuyor, size yeni bir şey göstermekten de mutlu oldum.

Sürelerle Power Query içinde çalışma konusundaki açıklama:

SQL Server’da Değişiklik Verisi Yakalama (Change Data Capture) için Microsoft dokümantasyonu:

Burada kullanılan veri manuel olarak oluşturuldu. Gerçek veriye dair herhangi bir referans yoktur.