Power BI Lab 4: Create DAX Calculations in Power BI Desktop Part 1

Cập nhật theo giáo trình mới nhất của Microsoft, ngày 14/09/2023

Bài hướng dẫn Lab 4: Create DAX Calculations in Power BI Desktop (Tạo các phép tính DAX trong Power BI Desktop) là một bài thực hành nằm trong chuỗi bài luyện tập Power BI của Microsoft. Mục tiêu của chuỗi bài này là giúp học viên hiểu về các bước xử lý dữ liệu và từ đó thiết kế báo cáo trong Power BI.  

Xuyên suốt chuỗi bài này, chúng ta sẽ sử dụng bộ dữ liệu của công ty Adventure Works – một công ty sản xuất và kinh doanh đồ thể thao mạo hiểm đa quốc gia – để xây dựng một giải pháp Power BI cho công ty này.  

Datapot khuyến khích học viên thực hành các bài lab theo thứ tự sau để củng cố các kĩ năng cơ bản một cách tốt nhất: 

• Lab 1: Prepare Data in Power BI Desktop (Chuẩn bị dữ liệu trong Power BI Desktop) 
• Lab 2: Load Transformed Data in Power BI Desktop (Làm sạch và chuyển đổi dữ liệu trong Power BI Desktop) 
• Lab 3: Design a model in Power BI – Part 1 (Xây dựng Model trong Power BI – Phần 1) 
• Lab 3 nâng cao:  Design a model in Power BI – Part 2 (Xây dựng Model trong Power BI – Phần 2) 
• Lab 4: Create DAX Calculations in Power BI Desktop (Tạo các phép tính DAX trong Power BI Desktop) 
• Lab 5: Create Advanced DAX Calculations in Power BI Desktop (Tạo các phép tính DAX nâng cao trong Power BI Desktop) 
• Lab 6: Design a report in Power BI Desktop – Part 1 (Thiết kế báo cáo trong Power BI Desktop – Phần 1) 
• Lab 7: Design a report in Power BI Desktop – Part 2 (Thiết kế báo cáo trong Power BI Desktop – Phần 2)
• Lab 8: Perform Advanced Analytics with AI Visuals (Phân tích nâng cao với biểu đồ AI)
• Lab 9: Create a Power BI Dashboard (Tạo dashboard trên Power BI) 
• Lab 10: Enforce Row-Level Security (Cài đặt Row-Level Security) 

Chuẩn bị trước khi thực hành 

Để bắt đầu thực hành chuỗi bài Lab này, chúng ta cần chuẩn bị:  

• Cài đặt Power BI Desktop – Hướng dẫn cài đặt Power BI Desktop – YouTube 
• Database AdventureWorksDW2020 (Kết nối với SQL Server) 
• 2 file ColorFormats.csv và ResellerSalesTargets.csv 

Đối với học viên của Datapot, các bạn đã được cung cấp thông tin để kết nối đến SQL Server có chứa dataset AdventureWorksDW2020 và link download file. Các bạn đã đủ công cụ để bắt đầu thực hành 11 bài Lab.

Trong trường hợp tự thực hành, các bạn cần: 

• Cài đặt SQL Server và SQL Server Management Studio 
• Tải file dữ liệu (đuôi .bak) và 2 file ColorFormats.csv và ResellerSalesTargets.csv tại https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/raw/Main/AllfilesDownload.zip 
• Restore file .bak – Xem hướng dẫn tại đây.

Sau khi hoàn thành, các bạn sẽ sử dụng server name để kết nối với Power BI Desktop: 

Mục tiêu của bài Lab 4

Sau bài thực hành này, bạn sẽ có thể:

• Tạo ra calculated tables
• Tạo ra calculated columns
• Tạo ra measures

Hướng dẫn thực hành

Hướng dẫn bằng Video:

Hướng dẫn từng bước:

Bài tập 1: Tạo Calculated Tables 

Task 1: Chuẩn bị 

Mở file Power BI Desktop đã lưu ở bài lab trước đó.

Task 2: Tạo bảng Salesperson 

Trong task này chúng ta sẽ tạo ra bảng mới có tên là Salesperson (có quan hệ trực tiếp tới bảng Sales). 

2.1. Trong Power BI Desktop, trong thẻ Modeling trên thanh ribbon, Report view, trong nhóm Calculations, click chọn New Table 

2.2. Trong thanh viết công thức (xuất hiện ở dưới thanh ribbon khi bạn tạo hoặc sửa công thức), nhập vào công thức DAX sau: 

(xuống dòng bằng cách nhấn Shift+Enter) 
Sau đó nhấn Enter.

Một calculated table sẽ được tạo theo cấu trúc: Tên bảng = công thức DAX để tạo bảng. 

Chú ý:  

• Tên bảng không được trùng với tên bảng nào đã tồn tại trong data model.  
• Thanh công thức sẽ hỗ trợ việc nhập vào công thức DAX đúng định dạng và có nghĩa: các tính năng sẽ giúp bạn tự động hoàn thành câu lệnh, tô màu các thành phần trong công thức giúp bạn dễ dàng nhận biết và kiểm soát công thức của mình một cách chính xác.  
• Bảng mới được tạo này là một bảng được copy hoàn toàn từ bảng Salesperson (Performance). Nó chỉ copy các dữ liệu từ bảng gốc mà không sao chép các thuộc tính của bảng như tính ẩn/hiện, định dạng.  

Tip: Bạn nên xuống dòng trong công thức của bạn bằng cách nhấn Shift+Enter để công thức của bạn trở nên dễ nhìn hơn. 

2.3. Sau khi nhấn Enter cho công thức ở trên, trong ngăn Fields, bạn sẽ thấy có một bảng mới xuất hiện tên là Salesperson là bảng bạn vừa tạo (ký hiệu là bảng có biểu tượng máy tính ở phía trước, thể hiện đây không phải bảng gốc mà là một bảng được tính ra) 

Chú ý: Các bảng calculated tables cũng làm gia tăng kích cỡ model bởi vì chúng cũng chứa dữ liệu, do đó sẽ tiêu tốn tài nguyên để lưu trữ. Khác với các bảng được load từ Power Query, các bảng được tính ra sẽ không thể sử dụng dữ liệu được tải từ các nguồn dữ liệu bên ngoài và chỉ có thể dựa và dữ liệu đã được load vào data model. 

2.4. Tiếp theo, hãy chuyển sang Model view. Hãy chú ý rằng bây giờ bạn có thể sử dụng bảng Salesperson trong model. Tạo một quan hệ từ cột Salesperson | EmployeeKey tới cột Sales | EmployeeKey. 

2.5. Click chuột phải vào mối quan hệ đang hiện là inactive – không hoạt động (đường nối là đường nét đứt) giữa hai bảng Salesperson (Performance) và Sales tables, và chọn Delete. 

2.6. Trong bảng Salesperson chọn các cột dưới đây và ẩn chúng (đặt chế độ Is Hidden là Yes): 

• EmployeeID 

• EmployeeKey 
• UPN 

2.7. Trong sơ đồ model, chọn bảng Salesperson. 

Trong ngăn Properties, hộp Description nhập vào: Salesperson related to Sales. 

Đối với bảng Salesperson (Performance), đặt phần mô tả thành Salesperson related to region(s).  

Khi đó, mô hình dữ liệu của bạn sẽ có hai cách để phân tích những người bán hàng: bảng Salesperson để phân tích doanh thu theo từng người bán hàng và bảng Salesperson (Performance) để phân tích doanh thu trong từng khu vực bán hàng được phân chia cho từng người bán hàng. 

Task 3: Tạo bảng Date 

Trong task này chúng ta sẽ tạo ra bảng Date. 

3.1. Chuyển sang Data view. 

3.2. Trên thẻ Home trên thanh ribbon, trong nhóm Calculations, click chọn New Table.

3.3. Trong thanh công thức, nhập vào công thức DAX sau:  

(Giải thích: Hàm CALENDARAUTO() trả về một bảng chỉ chứa 1 cột gồm các giá trị ngày. Tính “auto” (tự động) của hàm này thể hiện ở chỗ nó sẽ quét qua tất cả các cột chứa ngày trong mô hình, sau đó tạo ra một cột trong đó mỗi hàng là một ngày nằm trong phạm vi ngày đó.) 

Trong hàm này có một đối số (argument) tùy chọn (nghĩa là có thể có hoặc không). Đối số này cho biết tháng cuối cùng của năm là tháng nào. Nếu không điền đối số, giá trị mặc định sẽ là 12, nghĩa là tháng 12 là tháng cuối cùng của năm. Trong trường hợp này, đối số là 6, nghĩa là tháng 6 là tháng cuối cùng của năm. 

Hãy chú ý các giá trị ngày: chúng đang được định dạng theo định dạng của Mỹ: mm/dd/yyyy

Ở góc dưới cùng bên trái, trong thanh trạng thái, bạn sẽ thấy bảng mới có 1826 dòng, tương đương với 5 năm.

Task 4: Tạo các calculated columns 

4.1. Trong thanh ribbon, nhóm menu Table Tools, trong nhóm Calculations click chọn New Column.

4.2. Trong thanh công thức, nhập vào công thức DAX sau và nhấn Enter:  

(Giải thích: Giống với calculated table, một calculated column cũng được tạo ra theo cấu trúc: Tên cột = Công thức DAX. Tên cột mới được tạo không được trùng với bất kỳ tên cột nào đã tồn tại trong bảng. Trong công thức này, nếu tháng > 6, cộng 1 vào năm (ví dụ, nếu cột Date đang ở tháng 7 năm 2017 thì Year sẽ là 2018)).

4.3. Kiểm tra kết quả: cột Year đã được tạo mới  

4.4. Tạo tiếp các cột trong bảng Date: 

• Quarter 
• Month 

4.5. Quay lại Report view. Để tạo trang báo cáo mới, ở góc dưới, nhấn vào biểu tượng dấu cộng.

4.6. Để thêm một ma trận vào báo cáo, trong ngăn Visualizations chọn biểu đồ dạng ma trận. 
Tip: Bạn có thể di chuột đến từng biểu đồ để xem mô tả loại biểu đồ đó.

4.7. Trong ngăn Fields trong bảng Date kéo trường Year vào Rows.

Kéo thêm trường Month vào Rows, ngay dưới trường Year vừa kéo.

4.8. Ở góc trên bên trái của biểu đồ (hoặc ở dưới bên phải tùy vào vị trí bạn đặt ma trận này trong trang báo cáo), click chọn biểu tượng hai mũi tên rẽ nhánh (để mở rộng tất cả các năm xuống thêm 1 level nữa: từ năm thành tháng).

Hãy chú ý rằng từ năm sẽ mở rộng đến các tháng, tuy nhiên các tháng đang được sắp xếp theo thứ tự bảng chữ cái thay vì theo thời gian.

Để chỉnh lại, bạn chuyển sang Data view, chúng ta sẽ tạo cột tên MonthKey vào bảng Date để làm cơ sở sắp xếp. 

Công thức này sẽ trả về các giá trị dạng số được viết theo thứ tự YYYYMM.

Để kiểm tra lại, trong Data view, hãy chắc chắn rằng cột mới tạo chứa các giá trị dạng số (ví dụ: 201707 cho July 2017, v..v..).

4.9. Quay lại Report view. 

Trong ngăn Fields chọn cột Month (khi một trường được chọn, bạn sẽ thấy cột đó có màu nền màu xám) 

Trên thanh ribbon, trong nhóm menu Column Tools, trong nhóm Sort, chọn Sort by Column, sau đó chọn MonthKey.

Quay lại ma trận bạn đã tạo ở trước, bạn sẽ thấy các tháng đang được sắp xếp theo thứ tự thời gian.

Task 5: Hoàn thiện bảng Date

Tiếp theo chúng ta sẽ thực hiện một số thao tác điều chỉnh trên bảng Date và tạo relationship với bảng Sales và Targets.

5.1. Chọn Model View.

5.2. Trong bảng Date, chúng ta chọn cột MonthKey và ẩn nó đi (thiết lập mục Is Hidden là Yes).

5.3. Trong cửa sổ Fields bên phải giao diện, chọn bảng Date -> click chuột phải vào cột Year sau đó chọn Create hierachy.

5.4. Đổi tên hierachy vừa tạo thành Fiscal bằng cách click chuột phải chọn Rename.

5.5. Tiếp theo chúng ta có thể thêm các trường vào hierarchy như đã được hướng dẫn trong lab trước, hoặc có thể chuột phải vào trường Quarter và chọn Add to hierarchy -> Fiscal. Tương tự với trường Month.

5.6. Tạo các quan hệ sau:

• Date | Date to Sales | OrderDate
• Date | Date to Targets | TargetMonth

5.7. Ẩn đi hai cột sau

• Sales | OrderDate
• Targets | TargetMonth

Task 6: Mark the Date table

Tiếp theo chúng ta sẽ đánh dấu bảng Date này là bảng date chính được sử dụng trong mô hình. Thao tác này rất quan trọng vì trong tương lai khi sử dụng các hàm DAX thuộc bộ hàm time intelligence, chúng ta sẽ cần 1 bảng date chuẩn chỉnh. 

6.1 Chuyển giao diện hiển thị sang Report view.

6.2. Trong Fields, chọn bảng Date

6.3. Trên thanh công cụ, chọn thẻ Table Tools, chọn Mark as date table.

6.4. Màn hình sẽ hiển thị cửa sổ Mark as date table, trong danh sách Date column, chọn Date, và nhấn OK.

6.5. Lưu file Power BI Desktop.

Bài tập 2: Tạo các Measures

Task 1: Tạo các measure đơn giản

1.1. Trong Report view, Page 2, trong ngăn Fields kéo vào ma trận trường Sales | Unit Price.

1.2. Click vào mũi tên thả xuống của trường Unit Price, và quan sát các tùy chọn sẵn có. 

1.3. Để tạo measure, trong ngăn Fields nhấn chuột phải vào bảng Sales và chọn New Measure.

1.4. Trong thanh công thức, nhập vào công thức DAX sau:

1.5. Thêm measure Avg Price vào biểu đồ ma trận. Bạn sẽ thấy cột Avg Price chứa các giá trị giống hệt cột Unit Price nhưng khác định dạng.

1.6. Mở menu trong của trường Avg Price, khi đó bạn sẽ thấy bạn không thể thay đổi phương thức tổng hợp (aggregate) của measure này.

1.7. Tiếp tục tạo các measure mới trong bảng Sales theo gợi ý sau:

• Median Price (sử dụng hàm MEDIAN())
• Min Price (sử dụng hàm MIN())
• Max Price (sử dụng hàm MAX())
• Orders (sử dụng hàm DISTINCTCOUNT())
• Order Lines (sử dụng hàm COUNTROWS())

Hàm DISTINCTCOUNT() trong measure Orders sẽ đếm mỗi order một lần (không đếm trùng). Hàm COUNTROWS() trong measure Order Lines đếm số dòng trong cả bảng (có tính trùng)

1.8. Chuyển sang Model view, và chọn 4 measure sau: Avg Price, Max Price, Median Price, and Min Price.

1.9. Định dạng 4 measure đã chọn như sau:

• Đặt định dạng số có 2 chữ số sau dấu thập phân
• Đưa các measure đã chọn vào folder tên là Pricing

1.10. Ẩn cột Unit Price.

1.11. Chọn hai measure Order Lines và Orders rồi đặt định dạng như sau:

• Đặt định dạng sử dụng  thousands separator (phân cách theo nghìn)
• Đưa hai measure này vào chung một folder tên Counts

1.12. Trong Report view, bỏ trường Unit Price.

1.13. Thêm vào biểu đồ ma trận 5 trường sau:

• Median Price
• Min Price
• Max Price
• Orders
• Order Lines

1.14. Hãy kiểm tra lại định dạng các cột một lần nữa. 

Task 2: Tạo các measure DAX phức tạp hơn

2.1. Trong Report view, chọn Page 1.

2.2. Xem lại bảng vừa tạo và hãy chú ý đến cột Target.

Hãy nhớ lại trong buổi lab trước, giữa hai bảng salespeople và regions có tồn tại quan hệ many to many. Điều này đồng nghĩa với việc việc cộng tổng các giá trị sales target không có nghĩa bởi vì giá trị salespeople target ở đây đang là sales target cho từng người bán dựa trên khu vực bán hàng. Để tính được chính xác, sales target phải được lọc cho từng người bán. Bây giờ chúng ta sẽ tạo ra một measure mới để làm được điều đó.

2.3. Chọn biểu đồ dạng bảng, trong ngăn, Visualizations bỏ trường Target.

2.4. Đổi tên cột Targets | Target thànhTargets | TargetAmount.Chúng ta phải đổi tên bởi vì chúng ta định tạo ra thêm một measure mới tên là Target. Bạn không thể có một cột và một measure có tên giống nhau trong cùng một bảng.

2.5. Tạo measure trong bảng Targets theo công thức DAX sau:

Hàm HASONEVALUE() sẽ kiểm tra xem mỗi giá trị trong bảng Salesperson có được filter hay không. Nếu có, hàm sẽ trả về tổng doanh số kế hoạch (cho người bán đó). Nếu không, hàm sẽ trả về giá trị BLANK.

2.6. Định dạng số cho cột Target thành không có chữ số thập phân.

2.7. Ẩn cột TargetAmount

2.8. Thêm measure Target vào biểu đồ.

2.9. Hãy chú ý rằng Total của cột  Target là BLANK.

2.10. Tạo tiếp các measure theo gợi ý:

• Variance: chênh lệch giữa sales thực tế và sales target
• Variance Margin: tính % của variance so với sales target

2.11. Đặt định dạng cho measure Variance có 0 chữ số sau dấu thập phân.

2.12. Đặt định dạng cho measure Variance Margin là phần trăm (percentage) có 2 chữ số sau dấu thập phân.

2.13. Thêm hai measure Variance và Variance Margin vào biểu đồ dạng bảng.

2.14. Bạn sẽ thấy bảng Targets xuất hiện ở trên cùng. 
Chú ý: Các bảng chỉ chứa các measure sẽ được đặt ở trên cùng.

Task 3: Kết thúc

Lưu file Power BI Desktop.

Xem ngay Lab 5: Create Advanced DAX Calculations in Power BI Desktop (Tạo các phép tính DAX nâng cao trong Power BI Desktop).

Chuỗi bài hướng dẫn thực hành Power BI PL300 Lab: https://datapot.vn/category/power-bi/power-bi-pl300-lab-video/