Power BI Lab 5: Create DAX Calculations in PBI Desktop Part 1

Hướng dẫn

Exercise 1: Tạo Calculated Tables

Task 1: Chuẩn bị

Task 2: Tạo bảng Salesperson

1. Trong Power BI Desktop, trong thẻ Modeling trên thanh ribbon, Report view, trong nhóm Calculations, click chọn New Table.
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:Salesperson = Salesperson (Performance)’ — xuống dòng bằng cách nhấn Shift+EnterSau đó 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ú ý rằng 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.
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)
4. Hãy chú ý rằng 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.
5. Chuyển sang Model view.
6. 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.
7. 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.
8. 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
9. Trong sơ đồ model, chọn bảng Salesperson.
10. Trong ngăn Properties, hộp Description nhập vào: Salesperson related to Sales.
11. Đố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, trong khi 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

1. Chuyển sang Data view.
2. Trên thẻ Home trên thanh ribbon, trong nhóm Calculations, click chọn New Table.
3. Trong thanh công thức, nhập vào công thức DAX sau:

    Date =  
    CALENDARAUTO(6)
   

   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 (optional, 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.
4. 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
5. Ở 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

1. Trong thanh ribbon, nhóm menu Table Tools, trong nhóm Calculations click chọn New Column.
2. Trong thanh công thức, nhập vào công thức DAX sau và nhấn Enter:

    Year =
    "FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1)
   

   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.Giải thích công thức: 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).
3. Kiểm tra kết quả: cột Year đã được tạo mới
4. Tạo tiếp các cột trong bảng Date:
   • Quarter
   • Month
   
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
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 đồ đó.
7. Trong ngăn Fields trong bảng Date kéo trường Year vào Rows.
8. Kéo thêm trường Month vào Rows, ngay dưới trường Year vừa kéo.
9. Ở 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).
10. 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.
11. Để 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.

    MonthKey =
    (YEAR('Date'[Date]) * 100) + MONTH('Date'[Date])
    

    Công thức này sẽ trả về các giá trị dạng số được viết theo thứ tự YYYYMM
12. Để 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..).
13. Quay lại Report view.
14. 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)
15. Trên thanh ribbon, nhóm menu Column Tools, trong nhóm Sort, chọn Sort by Column, sau đó chọn MonthKey.
16. 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

1. Switch to Model view.
2. In the Date table, hide the MonthKey column (set Is Hidden to Yes).
3. On the Fields right side pane, select the Date table, right click on the Year column, and select create hierarchy.
4. Rename newly created hierarchy to Fiscal by right click and Rename.
5. Add the follow two remaining fields to the Fiscal hierachy by selecting them in the fields pane, right clicking, selecting Add to hierarchy -> Fiscal.
   • Quarter
   • Month
   
6. Tạo các quan hệ sau:
   • Date | Date to Sales | OrderDate
   • Date | Date to Targets | TargetMonth
7. Ẩn đi hai cột sau
   • Sales | OrderDate
   • Targets | TargetMonth

Task 6: Mark the Date table

1. Switch to Report view.
2. In the Fields pane, select the Date table (not the Date field).
3. On the Table Tools contextual ribbon, from inside the Calendars group, click Mark as Date Table, and then select Mark as Date Table.
4. In the Mark as Date Table window, in the Date Column dropdown list, select Date.
5. Click OK.
6. Save the Power BI Desktop file.Power BI Desktop now understands that this table defines date (time). It’s important when relying on time intelligence calculations. You’ll work with time intelligence calculations in the Create DAX Calculations in Power BI Desktop, Part 2 lab.Note that this design approach for a date table is suitable when you don’t have a date table in your data source. If you have a data warehouse, it would be appropriate to load date data from its date dimension table rather than “redefining” date logic in your data model.

Exercise 2: Tạo các Measures

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

1. Trong Report view, Page 2, trong ngăn Fields kéo vào ma trận trường Sales | Unit Price.
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ó
3. Để tạo measure, trong ngăn Fields nhấn chuột phải vào bảng Sales và chọn New Measure.
4. Trong thanh công thức, nhập vào công thức DAX sau:

   Avg Price =  
    ‎AVERAGE(Sales[Unit Price])
   

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.
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.
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)
8. Chuyển sang Model view, và chọn 4 measure sau: Avg Price, Max Price, Median Price, and Min Price.
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
   
10. Ẩn cột Unit Price.
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
    
12. Trong Report view, bỏ trường Unit Price.
13. Thêm vào biểu đồ ma trận 5 trường sau:
    • Median Price
    • Min Price
    • Max Price
    • Orders
    • Order Lines
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

1. Trong Report view, chọn Page 1.
2. Xem lại bảng vừa tạo và hãy chú ý đến cột Target. Hãy nhớ lại rằng trong buổi lab trước, giữa hai bảng salespeople và regions có tồn tại quan hệ nhiều – nhiều. Đ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 đó.
3. Chọn biểu đồ dạng bảng, trong ngăn, Visualizations bỏ trường Target.
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.
5. Tạo measure trong bảng Targets theo công thức DAX sau:

    Target =
    IF(
       HASONEVALUE('Salesperson (Performance)'[Salesperson]),
       SUM(Targets[TargetAmount])
    )
   

   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.
6. Định dạng số cho cột Target thành không có chữ số thập phân.
7. Ẩn cột TargetAmount
8. Thêm measure Target vào biểu đồ.
9. Hãy chú ý rằng Total của cột  Target là BLANK.
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
11. Đặt định dạng cho measure Variance có 0 chữ số sau dấu thập phân.
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.
13. Thêm hai measure Variance và Variance Margin vào biểu đồ dạng bảng.
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

Video giới thiệu về DAX trong Power BI: