Lembar contekan fungsi power bi

Power BI adalah salah satu alat intelijen bisnis yang paling tangguh dan andal di luar sana. Salah satu fitur Power BI yang paling kuat adalah DAX [Data Analysis Expressions], yang merupakan ekspresi formula yang dirancang untuk analisis data tingkat lanjut. Rumus DAX berisi fungsi, operator, pernyataan, dan lainnya. Lembar contekan ini dirancang untuk menjadi pendamping praktis Anda saat bekerja dengan DAX di Power BI

Klik untuk mengunduh lembar contekan

Sumber. datacamp. com

Ini adalah kelanjutan dari artikel saya sebelumnya DAX Cheat sheet — Bagian 1. Dalam artikel ini, saya telah mendalami beberapa fungsi intelijen waktu DAX yang penting. Dokumentasi Microsoft dapat ditemukan di sini. Saya telah membahas ekspresi DAX berikut

1. KALENDER
2. TAHUN
3. BULAN
4. HARI
5. SEPEREMPAT
6. WEEKNUM
7. HARI KERJA
8. KENCAN PERTAMA
9. TANGGAL TERAKHIR
10. TANGGAL ANTARA
11. DATESMTD
12. TOTALMTD
13. SEMUA DIPILIH
14. DATEADD
15. TERKAIT
16. MULAI BULAN
17. AKHIR BULAN
18. MULAI TAHUN
19. AKHIR TAHUN
20. PERIODE PARALEL
21. HARI BERIKUTNYA
22. HARI SEBELUMNYA

Saya telah menggunakan campuran Power BI dan DAX Studio untuk mendemonstrasikan ukuran DAX khusus

Contoh data

Saya akan menggunakan model sampel Adventure Works DW 2020 yang dapat diunduh dari situs Microsoft. Saya telah menempatkan salinan PBIX di repo Github saya, tautannya ada di sini

Skema Basis Data

Data

Untuk merasakan seperti apa datanya, saya telah menyajikan 5 baris teratas dari masing-masing tabel dalam database ini

Pelanggan

Wilayah penjualan

Pengecer

Order penjualan

Penjualan

Tanggal

Sebagian besar pengukuran DAX kustom yang disajikan di bawah ini ditulis di sekitar kolom Penjualan[Jumlah Penjualan]. Saya juga menambahkan tabel tanggal baru MyCalendar

Tabel tanggal khusus di Power BI

Untuk memahami cara kerja fungsi DAX intelijen Tanggal dan Waktu, kita akan mulai dengan membuat tabel Tanggal kustom. Model AdventureWorks juga dilengkapi dengan tabel tanggal. Ini adalah tabel bernama Tanggal dan ditautkan ke tabel Penjualan melalui kolom DateKey

Apa itu tabel Tanggal dan mengapa kita membutuhkannya?

Tabel tanggal sama seperti tabel lainnya di Power BI tetapi dengan beberapa persyaratan wajib seperti yang ditentukan oleh Power BI di sini

• Itu harus memiliki kolom tipe data tanggal [atau tanggal/waktu] — dikenal sebagai kolom tanggal
• Kolom tanggal harus berisi nilai unik
• Kolom tanggal tidak boleh berisi KOSONG
• Kolom tanggal tidak boleh ada tanggal yang hilang
• Kolom tanggal harus mencakup tahun penuh. Setahun belum tentu tahun kalender [Januari-Desember]
• Tabel tanggal harus ditandai sebagai tabel tanggal

Mengapa tidak menggunakan tabel Tanggal yang sudah disertakan dengan AdventureWorks?

2 alasan. Kami ingin mengeksplorasi kesempatan belajar ini untuk membuat tabel tanggal yang cukup canggih. Dan, dengan tidak bergantung pada hubungan, kita dapat mempelajari cara menikah dengan tujuan umum Tabel tanggal untuk melaporkan data berdasarkan tanggal waktu [Jumlah Penjualan dalam contoh ini]

Bagaimana cara membuat tabel tanggal menggunakan DAX?

Ada dua fungsi Power BI yang berguna untuk membuat tabel tanggal. Ini adalah CALENDAR dan CALENDARAUTO Saya telah mengambil inspirasi dari tutorial yang luar biasa ini untuk membuat tabel tanggal khusus dan menamainya sebagai MyCalendar

MyCalendar = VAR myExtendedCalendar = ADDCOLUMNS[
            CALENDAR[DATE[2017,7,1],DATE[2021,6,30]], 
            "Year", YEAR[[Date]],
            "MonthNumber", MONTH[[Date]],
            "DayNumber", DAY[[Date]],
            "Month", FORMAT[[Date],"mmmm"],
            "DayOfWeek", FORMAT[[Date],"ddd"],
            "DayOfWeekNumber", WEEKDAY[[Date]],
            "QuarterNumber", QUARTER[[Date]],
            "Quarter", FORMAT[[Date],"\QQ"],
            "WeekNumber",WEEKNUM[[Date]]
            ]
//Add the Financial year columns        
VAR myExtendedCalendarWithFinancialColuns = ADDCOLUMNS[
            myExtendedCalendar, 
            "FinancialYear",IF[ [MonthNumber]>=7, [Year]+1, [Year] ],
            "FinancialQuarterNumber",IF[ [MonthNumber]>=7, [QuarterNumber]-2, [QuarterNumber]+2 ]
            ]
//Add the Financial Quarter columns            
VAR myExtendedCalenderWithQuarterDisplayColumn = ADDCOLUMNS[
            myExtendedCalendarWithFinancialColuns, 
            "FinancialQuarter",CONCATENATE["Q",[FinancialQuarterNumber]]
            ]return myExtendedCalenderWithQuarterDisplayColumn

Struktur tabel MyCalendar

Mengatur properti pengurutan untuk kolom Teks

Kami hampir sampai. Tapi, ada satu langkah penting yang harus dilakukan. Kolom teks seperti Bulan, Hari Minggu, dan Kuartal perlu mengetahui urutan pengurutan. Perhatikan kolom Bulan. Saat kolom ini dipilih sebagai Kolom dalam visual Matriks, urutan pengurutan secara default akan berdasarkan abjad, yaitu. e. Desember datang sebelum Januari. Ini tidak benar. Kami ingin order Jan, Feb, March, April ,. Desember. Untuk mencapai ini, kita perlu menentukan MonthNumber sebagai kolom pengurutan untuk kolom Bulan

Kami telah menyelesaikan pekerjaan dasar kami. Kami sekarang siap untuk memulai eksplorasi kami untuk memahami perilaku yang mendasari ekspresi DAX

Hitung jumlah hari Minggu dalam periode waktu apa pun [FIRSTTDATE, LASTDATE, DATESBETWEEN]

Objektif

Ini adalah skenario mainan. Diberikan potongan waktu apa pun, bagaimana kita menemukan jumlah hari Minggu dalam periode itu?

visual matriks

ukuran DAX

NumberOfSundays = 
VAR first = FIRSTDATE[MyCalendar[Date]]
VAR last = LASTDATE[MyCalendar[Date]]
VAR daysInBetween = DATESBETWEEN[MyCalendar[Date] , first, last]
VAR datesWithSundays=FILTER[daysInBetween, WEEKDAY[MyCalendar[Date]]==1] 
VAR countOfSundays = COUNTROWS[datesWithSundays]
return countOfSundays

_

Untuk kedua contoh yang disajikan di atas, kami menggunakan ukuran tunggal

NumberOfFriday13th = 
VAR first = FIRSTDATE[MyCalendar[Date]]
VAR last = LASTDATE[MyCalendar[Date]]
VAR between = DATESBETWEEN[MyCalendar[Date],first, last]
VAR days = COUNTROWS
    [
        FILTER[
            between, 
            WEEKDAY[MyCalendar[Date]]==6 && DAY[MyCalendar[Date]]==13
            ] 
    ]
return days

2. Ukurannya agnostik dari panjang periode waktu. Filter ROW di setiap visual memiliki semua informasi tentang baris dari tabel

NumberOfFriday13th = 
VAR first = FIRSTDATE[MyCalendar[Date]]
VAR last = LASTDATE[MyCalendar[Date]]
VAR between = DATESBETWEEN[MyCalendar[Date],first, last]
VAR days = COUNTROWS
    [
        FILTER[
            between, 
            WEEKDAY[MyCalendar[Date]]==6 && DAY[MyCalendar[Date]]==13
            ] 
    ]
return days

3. Kami menggunakan ekspresi

NumberOfFriday13th = 
VAR first = FIRSTDATE[MyCalendar[Date]]
VAR last = LASTDATE[MyCalendar[Date]]
VAR between = DATESBETWEEN[MyCalendar[Date],first, last]
VAR days = COUNTROWS
    [
        FILTER[
            between, 
            WEEKDAY[MyCalendar[Date]]==6 && DAY[MyCalendar[Date]]==13
            ] 
    ]
return days

4 dan

NumberOfFriday13th = 
VAR first = FIRSTDATE[MyCalendar[Date]]
VAR last = LASTDATE[MyCalendar[Date]]
VAR between = DATESBETWEEN[MyCalendar[Date],first, last]
VAR days = COUNTROWS
    [
        FILTER[
            between, 
            WEEKDAY[MyCalendar[Date]]==6 && DAY[MyCalendar[Date]]==13
            ] 
    ]
return days

5 untuk menemukan batas periode waktu di setiap sel. Ekspresi

NumberOfFriday13th = 
VAR first = FIRSTDATE[MyCalendar[Date]]
VAR last = LASTDATE[MyCalendar[Date]]
VAR between = DATESBETWEEN[MyCalendar[Date],first, last]
VAR days = COUNTROWS
    [
        FILTER[
            between, 
            WEEKDAY[MyCalendar[Date]]==6 && DAY[MyCalendar[Date]]==13
            ] 
    ]
return days

_6 memberi kita tabel dari semua

NumberOfFriday13th = 
VAR first = FIRSTDATE[MyCalendar[Date]]
VAR last = LASTDATE[MyCalendar[Date]]
VAR between = DATESBETWEEN[MyCalendar[Date],first, last]
VAR days = COUNTROWS
    [
        FILTER[
            between, 
            WEEKDAY[MyCalendar[Date]]==6 && DAY[MyCalendar[Date]]==13
            ] 
    ]
return days

7 dalam batas-batas ini. Hitungan hari Minggu diekstrak dengan menggunakan ekspresi

NumberOfFriday13th = 
VAR first = FIRSTDATE[MyCalendar[Date]]
VAR last = LASTDATE[MyCalendar[Date]]
VAR between = DATESBETWEEN[MyCalendar[Date],first, last]
VAR days = COUNTROWS
    [
        FILTER[
            between, 
            WEEKDAY[MyCalendar[Date]]==6 && DAY[MyCalendar[Date]]==13
            ] 
    ]
return days

8

Hitung jumlah kejadian hari Jumat tanggal 13 dalam periode waktu apa pun [FIRSTTDATE, LASTDATE, DATESBETWEEN]

Objektif

Ini adalah skenario mainan. Dalam contoh ini, kami menghitung kemunculan semua hari yang merupakan hari Jumat dan merupakan hari ke-13 setiap bulan. Ukuran DAX secara semantik identik dengan yang kami tulis untuk menghitung hari Minggu dalam periode waktu mana pun

visual matriks

ukuran DAX

NumberOfFriday13th = 
VAR first = FIRSTDATE[MyCalendar[Date]]
VAR last = LASTDATE[MyCalendar[Date]]
VAR between = DATESBETWEEN[MyCalendar[Date],first, last]
VAR days = COUNTROWS
    [
        FILTER[
            between, 
            WEEKDAY[MyCalendar[Date]]==6 && DAY[MyCalendar[Date]]==13
            ] 
    ]
return days

Menggunakan Pemotong untuk memilih jangka waktu

Ukuran DAX yang sama sekarang dapat digunakan untuk menampilkan hitungan "Friday the 13th" untuk jangka waktu yang dipilih secara acak

Total penjualan dalam periode apa pun [FIRSTTDATE, LASTDATE,RELATED, FILTER]

Objektif

Mengingat waktu apa pun — hitung total penjualan dalam periode ini. Kami akan mencapai ini menggunakan 3 pendekatan berbeda

• Opsi 1 — Seret dan lepas di luar kotak [tanpa ukuran]
• Opsi 2 — Mengukur kueri mana yang menggunakan tabel Tanggal Pekerjaan Petualangan di luar kotak
• Opsi 3 — Mengukur kueri yang menggunakan tabel MyCalendar khusus

Opsi-1 Seret dan lepas di luar kotak [tanpa ukuran]

Ini menjadi perhitungan yang cukup sederhana, kita dapat menarik dan melepaskan kolom Jumlah Penjualan dari Total Penjualan

dan kemudian memilih opsi Sum dari menu konteks

Opsi-2 Mengukur kueri mana yang menggunakan tabel Tanggal Pekerjaan Petualangan

Ekspresi FIRSTDATE dan LASTDATE pada tabel Tanggal di luar kotak memberi kita batasan periode waktu dalam konteks saat ini. DATESBETWEEN memberi kita semua tanggal dari tabel tanggal dan kita menggunakan ini untuk menyediakan filter dalam ekspresi CALCULATE

TotalSalesInPeriodUsingAdWorksDate = 
    
    var first = FIRSTDATE['Date'[Date]]
    var last =  LASTDATE['Date'[Date]]    VAR between = DATESBETWEEN['Date'[Date], first , last]
    VAR filteredDates = FILTER['Date', 'Date'[Date] in between]
    VAR totalSales  = CALCULATE
    [ 
        SUMX[Sales, Sales[Sales Amount]],
        filteredDates
    ]
    return totalSales

Opsi-3 Mengukur kueri mana yang menggunakan tabel kustom MyCalendar

Visual Matrix menggunakan tabel tanggal MyCalendar untuk merender hierarki. Oleh karena itu, dalam ukuran ini kami menggunakan FIRSTDATE dan LASTDATE untuk mendapatkan batas-batasnya. Ekspresi CALCULATE mengubah konteksnya dengan memfilter tabel tanggal Adventure Works

TotalSalesInPeriodUsingMyCalendar = 
    
    var first = FIRSTDATE[MyCalendar[Date]]
    var last =  LASTDATE[MyCalendar[Date]]    /*
    In this measure, we are assuming that there is no direct relationship between MyCalendar and Sales
    Hence the need for explicit filtering
    */
    VAR totalSales  = CALCULATE
    [ 
        SUMX[Sales, Sales[Sales Amount]],
        FILTER[
            Sales,
            RELATED['Date'[Date]] >= first && RELATED['Date'[Date]] = firstDayOfCurrentFinancialYear && RELATED['Date'[Date]] = first && RELATED['Date'[Date]]