DAX

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

Ngày đăng: 14/03/2022

Trong bài thực hành hôm nay, chúng ta sẽ tạo ra các calculated tables (các bảng được tính), calculated columns (các cột được tính) và các measures (phép tính) đơn giản bằng cách sử dụng DAX (Data Analysis Expression).

Đây là bài thực hành nằm trong chuỗi bài luyện tập Power BI của Microsoft. Xuyên suốt chuỗi bài này, chúng ta sẽ đi từ khâu đầu tiên trong quá trình phân tích dữ liệu bằng Power BI – chuẩn bị dữ liệu, cho đến những bước cuối cùng – xuất bản báo cáo dưới dạng report hoặc dashboard. Mục tiêu cuối cùng của series này là xây dựng một giải pháp Power BI cho công ty Adventure Works – công ty sản xuất và kinh doanh đồ thể thao mạo hiểm đa quốc gia.

Xem toàn bộ các bài hướng dẫn luyện tập của Datapot tại đây: https://datapot.vn/category/power-bi/huong-dan-thuc-hanh-power-bi/

Xem học liệu gốc của Microsoft tại đây: https://microsoftlearning.github.io/DA-100-Analyzing-Data-with-Power-BI/ 

Bạn có thể thực hiện từng bài thực hành theo thứ tự bất kỳ mà bạn mong muốn, tuy nhiên, để thuận lợi nhất cho quá trình luyện tập, chúng mình đề xuất thứ tự luyện tập như sau:

  1. Prepare Data in Power BI Desktop
  2. Load Data in Power BI Desktop
  3. Model Data in Power BI Desktop, Part 1
  4. Model Data in Power BI Desktop, Part 2
  5. Create DAX Calculations in Power BI Desktop, Part 1
  6. Create DAX Calculations in Power BI Desktop, Part 2
  7. Design a Report in Power BI Desktop, Part 1
  8. Design a Report in Power BI Desktop, Part 2
  9. Create a Power BI Dashboard
  10. Create a Power BI Paginated Report
  11. Perform Data Analysis in Power BI Desktop
  12. Enforce Row-Level Security

Hướng dẫn

Exercise 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).

  1. Trong Power BI Desktop, trong thẻ Modeling trên thanh ribbon, Report view, trong nhóm Calculations, click chọn New Table.Picture 1
  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.Picture 4
    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)
    Picture 10
  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)Sales tables, và chọn Delete.
    Picture 2
    Picture 3
  8. Trong bảng Salesperson chọn các cột dưới đây và ẩn chúng (đặt chế độ Is HiddenYes):
    • 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

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

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

    Picture 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
    Picture 7
  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.
    Picture 9

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.Picture 11
  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
    Picture 12
  4. Tạo tiếp các cột trong bảng Date:
    • Quarter
    • Month

    Picture 14

  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
    Picture 15
  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 đồ đó.
    Picture 51
  7. Trong ngăn Fields trong bảng Date kéo trường Year vào Rows.
    Picture 17
  8. Kéo thêm trường Month vào Rows, ngay dưới trường Year vừa kéo.
    Picture 18
  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).
    Picture 19
  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.
    Picture 20
  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..).
    Picture 21
  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.
    Picture 22
  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.
    Picture 23

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

In this task you will complete the design of the Date table by hiding a column and creating a hierarchy. You will then create relationships to the Sales and Targets tables.

  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

    Picture 24

  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

In this task you will mark the Date table as a 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.Picture 8
  4. In the Mark as Date Table window, in the Date Column dropdown list, select Date.Picture 37
  5. Click OK.
    Picture 26
  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.
    Picture 27
  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ó
    Picture 30
  3. Để tạo measure, trong ngăn Fields nhấn chuột phải vào bảng Sales và chọn New Measure.
    Picture 31
  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.Picture 32
  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 PriceMax PriceMedian 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

    Picture 33

  10. Ẩn cột Unit Price.
  11. Chọn hai measure Order Lines 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

    Picture 36

  12. Trong Report view, bỏ trường Unit Price.
    Picture 38
  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.
    Picture 39

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

  1. Trong Report view, chọn Page 1.
    Picture 40
  2. Xem lại bảng vừa tạo và hãy chú ý đến cột Target.
    Picture 41Hã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.
    Picture 42
  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.
    Picture 43
  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 VarianceVariance Margin vào biểu đồ dạng bảng.
    Picture 44
  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
    Picture 46

Task 3: Kết thúc

Lưu file Power BI Desktop

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

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *