Power BI

Ngày tạo 10/06/2022

- 927 Lượt xem

Khi làm việc với PowerBI thì việc tạo ra 1 Date Table là cực kỳ quan trọng.

Giả sử khi bạn muốn tính Sales Amount và Receipts Amount theo năm, theo quý, theo tháng, theo ngày thì sao?

Lúc đó nếu bạn không có 1 bảng chung Date Table giữa 2 bảng Sales và bảng Receipts thì bạn sẽ không thể nào thu được kết quả mà bạn muốn.

Trong bài viết này chúng ta sẽ coi như chưa có bảng Date Table và ta sẽ tiến hành build bảng Date Table từ chưa có gì nhé.

Trên đây là hình ảnh về Data Model của chúng ta đang có.

Giờ ta sẽ kéo trường Order Date bên bảng Sales vào Report của chúng ta.

Bạn có thể thấy mặc dù không có bảng Date Table nhưng khi ta kéo trường Order Date vào thì ta vẫn thấy dữ liệu được phân ra lần lượt theo năm, quý, và tháng.

Tuy nhiên bạn có thể thấy là quý đang hiển thị là Qtr 1 hay tháng thì sẽ hiển thị là January, February. Mình tin là nhiều bạn khi làm Report không hề muốn kết quả xuất ra là như vậy. Vậy nếu không có bảng Date Table thì bạn có thể thay đổi được các chỉ số ở trên không? Câu trả lời đương nhiên là không.

Bạn có thể thấy là cột SalesAmount vẫn tính khá ok nhưng nếu bạn nhìn sang cột Receipts Amount thì không ok như vậy.

Bạn có thể thấy là Receipts Amount sẽ luôn hiện cùng 1 kết quả.

Dưới đây là công thức DAX được dùng để tạo nên 2 Measure đó.

Sales Amount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

Receipts Amount = SUMX ( Receipts, Receipts[Quantity] * Receipts[Net Price] )

Bạn có thể dễ dàng thấy được Sales Amount được lấy hoàn toàn dữ liệu từ bảng Sales nên khi bạn kéo trường Order Date vào phần Rows thì Sales Amount vẫn có thể chạy Filter Context và cho ra kết quả theo từng năm, tháng, ngày theo Order Date. Tuy nhiên Receipts Amount thì dùng dữ liệu từ bảng Receipts nên không làm được điều đó vì nếu bạn để ý thì bảng Sales và bảng Receipts không hề có bất kỳ Relationship nào cả.

Chính vì thế Receipts Amount không thể Filter theo từng năm, tháng được và vì thế kết quả trả về ở đây chính là tổng của Quantity * Net Price bên Table Receipts.

Vậy giờ để giải quyết vấn đề này chúng ta cần làm thế nào?

Trước khi bạn tiến hành tạo ra 1 Date Table thì bạn cần phải tắt đi chế độ Auto Date/time của Power BI.

Để làm điều đó thì bạn cần vào File > Chọn Options and Settings > Options. Sau đó vào phần Data Load và chọn như hình vẽ dưới đây.

Giờ sau khi ta đã tắt tính năng Auto Date Time thì khi bạn nhìn vào phần Report ta sẽ thấy như này.

Bạn có thể thấy là Order Date sẽ không phân ra theo năm, quý, tháng nữa mà sẽ chỉ có từng ngày như thế này thôi.

Giờ ta sẽ phải đi tạo ra 1 Date Table chung cho Data Model này.

Trước khi tạo ra Date Table bạn cần chú ý là Date Table ngày bắt đầu sẽ luôn phải là ngày 1/1 của năm bé nhất trong data và ngày kết thúc sẽ là ngày 31/12 của năm lớn nhất trong Data của bạn.

Để tạo ra Date Table thì bạn có 2 cách.

1 là bạn sử dụng hàm CALENDARAUTO hoặc hàm CALENDAR.

Tuy nhiên trong phạm vi bài viết này chúng ta sẽ sử dụng hàm CALENDARAUTO vì đây là cách đơn giản nhất và dễ nhất.

Hàm CALENDARAUTO hoạt động theo cách như sau.

PowerBI sẽ đi scan tất cả các cột dạng ngày tháng trong Data Model. Sau đó tự động lấy ra năm bé nhất và năm lớn nhất. Sau đó tự động tạo ra Date Table.

Giờ ta sẽ chạy thử hàm CALENDARAUTO xem kết quả sẽ như thế nào nhé.

Date = CALENDARAUTO()

Bạn có thể thấy kết quả trả về lại bắt đầu từ ngày 01/01/1910. Tại sao lại như vậy?

Như mình đã nói là CALENDARAUTO sẽ scan qua tất cả các cột dạng ngày tháng trong Data Model. Vì thế nó sẽ scan cả các cột khác như ngày sinh chẳng hạn.

Bạn có thể check cột Birth Date trong bảng Customer và thấy ngày bé nhất là 13/08/1910.

Chính vì lý do này nên CALENDARAUTO sẽ trả về ngày bé nhất là ngày đầu năm 1910. Chính là ngày 01/01/1910.

Vậy giờ làm thế nào để xử lý việc này?

Đơn giản là giờ bảng Date của chúng ta sẽ liên quan chủ yếu tới cột Order Date. Vì vậy ta sẽ tiến hành tìm ra năm bé nhất trong cột Order Date của bảng Sales. Sau đó ta tiến hành Filter là được. Công thức DAX của chúng ta sẽ như sau.

Date =

VAR FirstSalesDate = MIN(Sales[Order Date])

VAR YearFirstOrder = YEAR(FirstSalesDate)

VAR Dates = CALENDARAUTO()

RETURN

FILTER(

Dates,

YEAR([Date]) >= YearFirstOrder

)

Tại sao ta lại có [Date] trong hàm YEAR. Phải chăng [Date] là 1 Measure. Đương nhiên không phải.

[Date] là 1 cột được tạo ra tự động từ hàm CALENDARAUTO. Vì thế nó không có tên bảng, cũng không phải Measure. Bạn phải nhớ nó là cột [Date] chứa tất cả các ngày được tạo ra từ hàm CALENDARAUTO là được.

Kết quả của hàm trên sẽ như hình dưới đây.

Bạn có thể thấy kết quả trả về giá trị nhỏ nhất là ngày 01/01/2007. Điều này hoàn toàn phù hợp vì ngày có Order đầu tiên là ngày 02/01/2007.

Tuy nhiên như bạn thấy nếu bảng DateTable chỉ có 1 cột như vậy thì cũng không được. Đó chính là lý do chúng ta cần Add thêm các cột khác nữa như Quarter, Year, Month, …

Để làm điều này thì chúng ta sẽ sử dụng hàm ADDCOLUMN là được.

Date =

VAR FirstSalesDate = MIN(Sales[Order Date])

VAR YearFirstOrder = YEAR(FirstSalesDate)

VAR Dates =

FILTER(

CALENDARAUTO(),

YEAR([Date]) >= YearFirstOrder

)

RETURN

ADDCOLUMNS(

Dates,

"Year", YEAR([Date]),

"Month", FORMAT([Date], "mmm"),

"Month Number", MONTH([Date]),

"Quarter", FORMAT([Date], "\QQ"),

"Day of Week", FORMAT([Date], "ddd"),

"WeekDayNumber", WEEKDAY([Date])

)

Đây là công thức DAX mà chúng ta sẽ sử dụng để tạo ra các cột cho bảng Date Table.

Lời khuyên của mình là khi tạo ra bảng này thì các cột như Month Number hay WeekDayNumber sẽ không show trên Report View. Vì thế mình sẽ tiến hành Hide đi các cột này. Bằng cách là trong phần Data View thì ta chuột phải vào cột muốn Hide và chọn Hide In Report View.

Giờ ta sẽ quay lại Sheet Report và kéo cột Date Table vào bảng và xem kết quả nhé.

Bạn có thể thấy là ta thu được 1 kết quả không chính xác. Ở đây số được xuất hiện là tổng Sales amount và Tổng Receipts Amount chứ không phải là số Sales và số Receipts cho từng ngày.

Tại sao lại như vậy?

Đơn giản vì bảng Date Table chúng ta tạo ra chưa có mối quan hệ Relationship với bảng sales và bảng Receipts.

Vậy trong trường hợp này ta sẽ cần tạo ra Relationship cho các bảng này.

Ta sẽ lần lượt kéo Order date của bảng Sales vào cột Date trên Date Table. Điều tương tự ta sẽ làm với cột Delivery Date trên bảng Sales và Sale Date trên bảng Receipts.

Kết quả chúng ta thu được sẽ như hình dưới đây.

Bạn có thể thấy là trong cùng 1 thời điểm thì 2 bảng chỉ có 1 quan hệ được active mà thôi. Đó chính là lý do ta thấy có 1 đường nét liền (mối quan hệ active) và 1 đường nét đứt (mối quan hệ inactive).

Sau khi làm xong việc này thì chúng ta sẽ Mark Date Table as Calendar bằng cách như sau.

Ở trong giao diện Data View. Ta sẽ chọn Table Tools > Sau đó chọn tới Mark as Date Table.

Tại đây ta chọn tới cột Date. Bạn cần lưu ý là cột mà bạn lựa chọn phải có kiểu dữ liệu là Date. Hơn thế nữa nó chỉ chứa các giá trị không trùng lặp. Bạn có thể yên tâm là khi bạn tạo ra cột Date bằng hàm CALENDARAUTO thì sẽ chỉ bao gồm các giá trị Unique mà thôi.

Giờ ta sẽ thử kéo Year, Month, Date bên bảng Date Table vào Report xem kết quả như thế nào. Dưới đây là kết quả.

Bạn có thể thấy kết quả đã được Filter theo từng năm chính xác theo cả Sales Amount và Receipts Amount rồi đúng không nào.

Giờ ta sẽ ấn mở rộng phần Year ra nhé.

Bạn có thể thấy là cột tháng đang được sắp xếp theo thứ tự A B C của bảng chữ cái và đây là điều không chính xác. Vì thế ta sẽ vào Data View. Sau đó ta sử dụng tính năng Sort By Column.

Đầu tiên ta chọn cột Month. Sau đó trong mục Sort By Column thì ta chọn tới cột Month Number.

Ta cũng sẽ làm điều tương tự với cột Day of Week. Cột này ta sẽ tiến hành Sort theo cột WeekDayNumber.
Ta sẽ xem lại kết quả trong bảng dưới đây.

Ta cũng sẽ thử với Day Of Week xem thế nào.

Cũng rất chính xác vì ta coi chủ nhật là ngày đầu tuần, ngày kết thúc tuần sẽ là thứ bảy.

Đến đây bảng Date Table của chúng ta cũng đã hoàn thành rồi. Trong trường hợp bạn muốn Add thêm các cột nữa cho Bảng Date thì chỉ cần bổ sung ở trong hàm ADDCOLUMNS là được.

Date =

VAR FirstSalesDate = MIN(Sales[Order Date])

VAR YearFirstOrder = YEAR(FirstSalesDate)

VAR Dates =

FILTER(

CALENDARAUTO(),

YEAR([Date]) >= YearFirstOrder

)

RETURN

ADDCOLUMNS(

Dates,

"Year", YEAR([Date]),