DAX part 2

Power BI PL300 Lab 6: Create DAX Calculations in PBI Desktop Part 2

Ngày đăng: 22/03/2022
Trong bài lab này, chúng ta sẽ tạo ra các DAX measure với việc kiểm soát filter context. Chúng ta sẽ tập trung vào hai chủ đề chính là hàm CALCULATE() để kiểm soát bộ lọc, và các hàm Time Intelligence. Thời gian ước tính để hoàn thành lab này là 45 phút.

Lab story

Đâ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

Exercise 1: Làm việc với Filter Context trong DAX

Trong bài tập này, chúng ta sẽ tạo các measure bằng DAX có kiểm soát với filter context. Filter context – tạm dịch: ngữ cảnh bộ lọc – có thể được tạm hiểu là một hoặc nhiều bộ lọc được áp dụng trong một measure

Task 1: Tạo biểu đồ ma trận

Mở file Power BI Desktop Chúng ta sẽ tạo ra một biểu đồ ma trận để thể hiện kết quả của các measure, phục vụ việc tính toán và kiểm tra kết quả.

  1. Trong Power BI Desktop, Report view, tạo một trang báo cáo mới.
    Picture 1
  2. Trên Page 3, thêm một biểu đồ dạng ma trận vào trang báo cáo
    Picture 13
  3. Kéo toàn bộ cây phân cấp Region | Regions và thả vào bên trong biểu đồ ma trận.
  4. Thêm trường vào biểu đồ Sales | Sales.
  5. Để mở rộng toàn bộ cây phân cấp, nhấn 2 lần vào biểu tượng mũi tên rẽ nhánh ở góc trên bên phải biểu đồ.
    Picture 47
    Hãy nhớ lại rằng trong buổi thực hành từ trước, cây phân cấp Regions có 3 cấp bậc là Group, Country và Region. 
  6. Để đặt định dạng, trong ngăn Visualizations chọn ngăn Format.
    Picture 14
  7. Trong hộp Search, nhập vào Stepped.
    Picture 49
  8. Đặt thuộc tính Stepped LayoutOff.
    Picture 49
  9. Hãy chắc chắn rằng ma trận của bạn bây giờ có 4 cột như sau.
    Picture 50
    Ở Adventure Works, khu vực bán hàng (sales region) được chia thành các nhóm (group), nhóm gồm các quốc gia (country), quốc gia bao gồm các vùng miền (region). Ngoại trừ Mỹ (US), các nước còn lại đều chỉ có 1 vùng, tên vùng được đặt trung với tên của quốc gia đó. Riêng với Mỹ được chia thành 5 khu vực địa lý khác nhau.

Task 3: Kiểm soát filter context

Trong task này chúng ta sẽ tạo ra các measure bằng DAX với hàm CALCULATE() để kiểm soát ngữ cảnh bộ lọc – filter context.

  1. Thêm một measure vào bảng Sales theo công thức sau:
     Sales All Region =
    
     CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(Region))
    

    Hàm CALCULATE() là một hàm cực kỳ “mạnh” trong việc kiểm soát filter context. Hàm CALCULATE() có thể có 1 hoặc nhiều đối số (argument). Đối số đầu tiên là một câu lệnh, phép tính hoặc một measure, các đối số sau đó thể hiện bộ lọc được điều chỉnh và áp dụng trong công thức. 

    Hàm REMOVEFILTERS() sẽ bỏ đi tất cả các bộ lọc đang hoạt động. Hàm này có thể không có đối số nào, nhưng cũng có thể có đối số là một bảng, một cột hoặc nhiều cột. 

    Trong công thức trên, bạn đang tính tổng các giá trị trong cột Sales trong một bộ lọc đã được điều chỉnh, đó là bỉ đi tất cả các bộ lọc được áp dụng cho cột Region. 

  2. Để quan sát kết quả một cách rõ hơn, thêm measure Sales All Region vào biểu đồ ma trận. Bạn sẽ thấy công thức này trả về tổng sales cho tất cả các khu vực, quốc gia, vùng miền như sau:
    Picture 52
    Việc tính ra tổng doanh thu của tất cả các khu vực bán hàng như vậy sẽ giúp bạn tính toán được tỷ lệ phần trăm đóng góp (percent of total) của từng khu vực vào tổng, bằng cách chia Sales của từng khu vực cho Sales tổng (ở đây là measure Sales All Region)
  3. Bây giờ, click chọn lại measre Sales All Region trong ngăn Field và thay measure đó bằng công thức như sau:
     Sales % All Region =  
     ‎DIVIDE(  
     ‎ SUM(Sales[Sales]),  
     ‎ CALCULATE(  
     ‎ SUM(Sales[Sales]),  
     ‎ REMOVEFILTERS(Region)  
     ‎ )  
     ‎)
    

    Measure của bạn đã được đổi tên để thể hiện đúng ý nghĩa của measure. Hàm DIVIDE() sẽ chia measure Sales (không bị điều chỉnh bởi filter context) cho measure Sales đã được điều chỉnh trong filter context. 

  4. Trong biểu đồ ma trận, chú ý rằng measure của bạn đã được đổi tên và bây giờ đang chứa các giá trị khác nhau cho từng group, country, region.
  5. Sửa lại định dạng measure Sales % All Region thành phần trăm với 2 chứ số sau dấu thập phân
  6. Trong biểu đồ ma trận, xem lại giá trị của measure Sales % All Region.Picture 53
  7. Thêm một measure DAX khác vào bảng Sales và đặt định dạng là phần trăm như sau:
     Sales % Country =  
     ‎DIVIDE(  
     ‎ SUM(Sales[Sales]),  
     ‎ CALCULATE(  
     ‎ SUM(Sales[Sales]),  
     ‎ REMOVEFILTERS(Region[Region])  
     ‎ )  
     ‎)
    
  8. Chú ý rằng measure Sales % Country hơi khác một chút với measure Sales % All Region.Sự khác biệt nằm ở số chia trong cong thức đã được điều chỉnh bởi việc bỏ đi các bộ lọc trong cột Region trong bảng Region chứ không phải xóa bộ lọc cho tất cả các cột trong bảng Region. Điều này nghĩa là mọi bộ lọc được áp dụng cho group và country vẫn được giữ nguyên. Kết quả của measure sẽ thể hiện % sales cho từng country.
  9. Thêm measure Sales % Country vào biểu đồ ma trận.
  10. Chú ý rằng chỉ United States đang thể hiện các giá trị nhỏ hơn 100% cho mỗi region. Điều này là bởi vì chỉ có Mỹ có nhiều region, các quốc gia còn lại đều chỉ chứa một region duy nhất, dẫn đến giá trị hiển thị cho mỗi region cho quốc gia đó là 100%.
    Picture 54
  11. Bạn có thể làm công thức DAX của bạn tốt hơn nữa bằng cách sửa lại công thức của measure Sales % Country như sau.
    Sales % Country =  
    ‎IF(  
    ‎ ISINSCOPE(Region[Region]),  
    ‎ DIVIDE(  
    ‎ SUM(Sales[Sales]),  
    ‎ CALCULATE(  
    ‎ SUM(Sales[Sales]),  
    ‎ REMOVEFILTERS(Region[Region])  
    ‎ )  
    ‎ )  
    ‎)
    

    Hàm ISINSCOPE bên trong hàm IF được sử dụng khi muốn kiểm tra xem một cột region là một cấp bậc trong cây phân cấp. Nếu đúng, hàm DIVIDE() sẽ được thực thi. Vế false trong hàm IF() đang bị thiếu, nghĩa là nếu sai thì hàm sẽ trả về giá trị blank. 

  12. Notice that the Sales % Country measure now only returns a value when a region is in scope.Picture 55
  13. Thêm 1 measure DAX cho bảng Sales như sau và đặt định dạng là phần trăm (percentage), sau đó thêm vào biểu đồ ma trận.
    Sales % Group =  
    ‎DIVIDE(  
    ‎ SUM(Sales[Sales]),  
    ‎ CALCULATE(  
    ‎ SUM(Sales[Sales]),  
    ‎ REMOVEFILTERS(  
    ‎ Region[Region],  
    ‎ Region[Country]  
    ‎ )  
    ‎ )  
    ‎)
    
  14. Một cách khác để viết công thức DAX cho measure Sales % Group là:
    Sales % Group =  
    ‎IF(  
    ‎ ISINSCOPE(Region[Region])  
    ‎ || ISINSCOPE(Region[Country]),  
    ‎ DIVIDE(  
    ‎ SUM(Sales[Sales]),  
    ‎ CALCULATE(  
    ‎ SUM(Sales[Sales]),  
    ‎ REMOVEFILTERS(  
    ‎ Region[Region],  
    ‎ Region[Country]  
    ‎ )  
    ‎ )  
    ‎ )  
    ‎)
    
  15. Trong Model view, lưu 3 measure mới vào chung một folder tên là Ratios.
    Picture 56
  16. Lưu file Power BI Desktop.

Exercise 2: Làm việc với Time Intelligence

Trong bài tập này bán sẽ tạo ra meaure DAX sales year-to-date (YTD) và sales year-over-year (YoY) growth.

Task 1: Tạo measure DAX YTD

  1. Trong Report view, trên Page 2, chú ý răng biểu đồ ma trận đang thể hiện nhiều measures cho năm và tháng được gộp thành nhóm theo các dòng.
  2. Thêm measure DAX vào bảng Sales và đặt định dạng là 0 chữ số thập phân như sau:
     Sales YTD =  
     ‎TOTALYTD(SUM(Sales[Sales]), 'Date'[Date], "6-30")
    

    Hàm TOTALYTD() sẽ thực hiện phép tính – trong trường hợp này là tổng Sales – cho một cột Date được cho trước. Cột Date này bắt buộc phải thuộc về một bảng DATE được đánh dấu là bảng Date (date table) – cách đánh dấu một bảng là bảng date chúng ta đã học ở lab trước: Create DAX Calculations in Power BI Desktop, Part 1 lab.

    Hàm còn có thể có thêm (hoặc không) một đối số thứ ba thể hiện ngày cuối cùng của năm. Khi trong công thức không có phần đối số thứ ba, nghĩa là ngày cuối cùng của năm là ngày 31 tháng 12. Đối với Adventure Works, tháng 6 là tháng cuối cùng của năm, do đó 30/6 là ngày cuối cùng của năm và được thể hiện trong công thức là “6-30”. 

  3. Thêm trường Sales và meaure Sales YTD biểu đồ ma trận. Kết quả cho thấy measure Sales YTD trả về giá trị tích lũy của doanh sô từ đầu năm cho đến ngày được tính.
    Picture 59
    Hàm TOTALYTD() cũng thực hiện kiểm soát bộ lọc về thời gian. Ví dụ, để tính sales YTD cho tháng 9 năm 2017 (tháng thứ 3 trong năm tài chính), trong bảng Date, mọi bộ lọc đã được bỏ đi và thay bằng bộ lọc ngày mới bắt đầu từ ngày đầu tiên của năm tài chính 2017 (1 tháng 7 năm 2017) và mở rộng đến ngày cuối cùng trong giai đoạn được xét (30 tháng 9 năm 2017). function performs filter manipulation, specifically time filter manipulation. Ngoài ra, DAX có rất nhiều hàm Time Intelligence khác để kiểm soát các loại bộ lọc khác nhau. 

Task 2: Tạo measure DAX YoY growth

  1. Thêm vào bảng Sales một measure mới như sau:
     Sales YoY Growth =  
     ‎VAR SalesPriorYear =  
     ‎ CALCULATE(  
     ‎ SUM(Sales[Sales]),  
     ‎ PARALLELPERIOD(  
     ‎ 'Date'[Date],  
     ‎ -12,  
     ‎ MONTH  
     ‎ )  
     ‎ )  
     ‎RETURN  
     ‎ SalesPriorYear
    

    Công thức tính Sales YoY Growth có mệnh đề VAR – khai báo biến. Biến (variable) được sử dụng phổ biến để làm đơn giản hóa logic của công thức, đồng thời hiệu quả hơn trong trường hợp một phần công thức cần được dùng đi dùng lại trong toàn bộ phép tính. Các biến được khai báo phải có tên khác nhau, kết quả của phép tính được thể hiện sau mệnh đề với keyword là RETURN. 

    Trong công thức trên, biến SalesPriorYear được gán cho phép tính tổng cho cột Sales khi có bộ lọc là hàm PARALLELPERIOD() – hàm này lấy ra giá trị ngày cách ngày được lọc là 12 tháng. 

  2. Thêm measure Sales YoY Growth vào biểu đồ ma trận
  3. Chú ý rằng measure này sẽ trả về giá trị BLANK cho 12 tháng đầu tiên bởi vì 12 tháng này không có PARALLEL PERIOD ở trước nó 12 tháng (không có giá trị sales trước năm tài chính 2017).
  4. Chú ý rằng giá trị cho Sales YoY Growth cho 2018 Jul chính là giá trị Sales cho 2017 Jul.
    Picture 61
    Bây giờ, sau khi đã tìm ra được Sales của năm trước, chúng ta sẽ tạo công thức để tính ra tỷ lệ % tăng trưởng sales. 
  5. Để hoàn thành measure, ghi đè lên công thức cũ bằng công thức measure mới là Sales YoY Growth và đặt định dạng measure là phần trăm với hai chữ số thập phân như sau:
     Sales YoY Growth =  
     ‎VAR SalesPriorYear =  
     ‎ CALCULATE(  
     ‎ SUM(Sales[Sales]),  
     ‎ PARALLELPERIOD(  
     ‎ 'Date'[Date],  
     ‎ -12,  
     ‎ MONTH  
     ‎ )  
     ‎ )  
     ‎RETURN  
     ‎ DIVIDE(  
     ‎ (SUM(Sales[Sales]) - SalesPriorYear),  
     ‎ SalesPriorYear  
     ‎ )
    
  6. Kiểm tra lại để chắc chắn rằng Sales YoY Growth 2018 Jul392.83%.
    Picture 62
    Điều này có nghĩa là doanh số tháng 7 năm 2018 ($2,411,559) cao hơn cùng kỳ năm trước ($489,328) là 392.83%. 
  7. Trong Model view, chuyển 2 measure mới tạo vào chung một folder tên là Time Intelligence.
    Picture 63

Task 3: Kết thúc

Chúng ta đã tạo xong hai measure Sales YTD và Sales YoY Growth. Lưu file Power BI Desktop của bạn.

Video hướng dẫn thực hành DAX:

Xem tiếp: Lab 7 – Thiết kế Báo cáo trong Power BI – Phần 1

Xem thêm các bài viết về Power BI Lab:

Lab 1 – Prepare data in Power BI Desktop 

Lab 2 – Load data in Power BI

Lab 3 – Model Data in Power BI 

Lab 4 – Tạo quan hệ Many-to-many – Model Data in Power BI Desktop 

Lab 5 – Create DAX Calculations in Power BI Desktop, Part 1

-7%
108 giờ ++
Beginner
25.000.000 
10 giờ học
Beginner, Fresher
1.500.000 
-8%
52 giờ
Beginner
9.000.000 9.500.000 
21 giờ học
Intermediate/Advanced
7.500.000 
Fresher, Junior
2.000.000 
-6%
Fresher, Junior
6.190.000 
Fresher, Junior
3.000.000 

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 *