DAX CƠ BẢN: ITERATOR FUNCTIONS – HÀM LẶP VÀ CÁCH SỬ DỤNG KÈM VÍ DỤ
DAX (Data Analysis Expression) trong Power BI bao gồm các hàm, toán tử và hằng số được sử dụng trong các công thức hoặc biểu thức để thực hiện tính toán và trả về một hoặc nhiều giá trị. DAX phân loại thành các nhóm như sau: Date and Time, Time Intelligence, Information, Logical, Mathematical, Statistical, Text, Parent/Child và các hàm khác.
Trong bài viết này, Datapot sẽ chia sẻ với mọi người 4 hàm DAX thuộc nhóm Aggregation thường được sử dụng nhiều trong công việc của một Data Analyst là: Hàm SUMX, AVERAGEX, MINX và MAXX. Chúng ta hãy cũng tìm hiểu về các tìm hiểu về các hàm này cũng như ví dụ về cách ứng dụng của hàm trong thực tế nhé.
HÀM SUMX
Mục đích sử dụng hàm SUMX
Hàm SUMX cho phép bạn thực hiện các phép tính trên từng hàng dữ liệu riêng lẻ và sau đó tổng hợp các kết quả đó lại . Điểm đặc biệt của hàm này là nó hỗ trợ tiến hành các phân tích phức tạp mà không cần thêm cột mới vào bảng dữ liệu.
Cú pháp hàm SUMX
Cú pháp:
= SUMX ( <Table>, <Expression> )
Giải thích thông số
Thông số | Mô tả |
Table | Bảng mà bạn muốn thực hiện tính toán. |
Expression | Biểu thức mà bạn muốn thực hiện trên từng dòng một và kết quả của mỗi dòng sẽ được sử dụng để tính tổng.
|
Lưu ý:
- Hàm SUM là một hàm thay thế cho SUMX ở những trường hợp tổng hợp cơ bản.
- Hàm SUMX Chỉ tính cho dữ liệu ở dạng số (number).
- Các giá trị kiểu dữ liệu Logic (True/False), Text hoặc ô trống sẽ bị bỏ qua, không tính.
- Hàm SUMX không thể sử dụng được với phương thức DirectQuery trong Power BI.
Kết quả trả về
Kết quả trả về là kiểu dữ liệu số ở dạng thập phân ( Decimal data type)
Trường hợp cụ thể sử dụng hàm SUMX
Trường hợp 1: Tính tổng tất cả giá trị các giao dịch
Giả sử bạn đang làm việc với một bảng dữ liệu Sales chứa thông tin về giao dịch, gồm số của hóa đơn (Billing No), tên sản phẩm(Product), số lượng mua( Quantity), Gía bán mỗi sản phẩm ( Selling Price) và khu vực cửa hàng ( Store Region). Tính doanh thu của mỗi đơn hàng và tổng doanh thu của toàn bộ đơn hàng dựa trên 2 cột [Quantity] và [Selling Price] bằng cách sử dụng hàm SUMX:
Giải thích: Xác định 2 cột cần sử dụng để tính toán là [Quantity] và [Selling Price] nằm trong bảng Sales.
- Bước 1: Tính doanh thu cho từng dòng
Để tính được doanh thu của toàn bộ đơn hàng thì trước tiên cần tính doanh thu ở mỗi đơn hàng (mỗi dòng), ở từng dòng lấy [Quantity] * [Selling Price]
- Bước 2: Tính tổng doanh số cho tất cả các dòng
Tổng hợp kết quả phép tính ([Quantity] * [Selling Price]) ở mỗi dòng để tính tổng.
Kết quả trả ra:
Trường hợp 2: Hàm SUMX kết hợp với hàm FILTER, CALCULATE để tính tính toán dựa trên các điều kiện
Tiếp tục sử dụng bảng dữ liệu Sales, tính doanh thu của mỗi đơn hàng và tổng doanh thu của toàn bộ đơn hàng bán tại khu vực miền Bắc (North) bằng cách sử dụng hàm SUMX kết hợp với hàm FILTER:
Kết hợp với hàm CALCULATE:
Giải thích: Xác định 2 cột cần sử dụng để tính toán là [Quantity] và [Selling Price], cột Store Region dùng để lọc dữ liệu.
- Bước 1: Lọc ra các dòng thỏa mãn điều kiện
Sử dụng hàm FILTER, CALCULATE để lấy ra những dòng thỏa mãn điều kiện [Store Region] = “North”.
- Bước 2: Tính doanh thu cho từng dòng thỏa mãn điều kiện
Với các dòng thỏa mãn điều kiện, lấy [Quantity] * [Selling Price] ở từng dòng để tính được doanh thu của mỗi đơn hàng.
- Bước 3: Tính tổng doanh thu cho tất cả các dòng thỏa mãn điều kiện
Tổng hợp kết quả phép tính ([Quantity] * [Selling Price]) ở mỗi dòng thỏa mãn điều kiện để tính tổng.
Kết quả trả ra:
So sánh SUM và SUMX
Đặc điểm | SUM | SUMX |
Sử dụng | Tính tổng các giá trị trong một cột cụ thể. | Tính tổng các giá trị dựa trên một biểu thức cho từng hàng trong bảng hoặc tập hợp dữ liệu. Bổ sung thêm ý là nó cũng tính trên một cột cụ thể bình thường nha. SUM là sugar syntax của SUMX. |
Tham số | Chỉ yêu cầu một tham số đầu vào là cột muốn tính tổng. | Yêu cầu hai tham số đầu vào: một bảng và một biểu thức hoặc một cột để tính tổng. |
Loại hàm | Hàm Aggregate (tổng hợp) trực tiếp | Hàm Iterator sử dụng để tổng hợp (lặp qua từng hàng trong bảng hoặc tập hợp dữ liệu). |
Linh hoạt | Ít linh hoạt, chỉ tính tổng các giá trị trong cột đã chỉ định. | Linh hoạt hơn và có thể thực hiện các phép tính phức tạp hơn trên từng hàng dữ liệu. |
HÀM AVERAGEX
Mục đích sử dụng hàm AVERAGEX
Hàm AVERAGEX dùng để thực hiện một loạt các phép tính phức tạp trên từng dòng dữ liệu trong nhiều cột (cộng, trừ, nhân, chia, các phép toán lồng nhau,…), sau đó lấy trung bình kết quả của những phép tính đó. Hàm này thuộc nhóm hàm Iterator, thường dùng khi bạn muốn thực hiện các phép tính toán, thống kê.
Cú pháp hàm AVERAGEX
Cú pháp:
AVERAGEX ( <Table>, <Expression> )
Giải thích thông số
Tham số | Giải thích |
Table | Bảng mà bạn muốn thực hiện tính toán |
Expression | biểu thức tính toán áp dụng cho từng dòng trong bảng trước khi phép tính giá trị trung bình được thực hiên. |
Lưu ý
- Hàm AVERAGEX chỉ tính cho dữ liệu ở dạng số (number), các giá trị có kiểu dữ liệu Logic, Text hoặc ô trống sẽ được Power BI bỏ qua, không tính toán.
- Khi không có hàng nào để tổng hợp, hàm sẽ trả về giá trị trống. Khi có hàng, nhưng không có hàng nào trong số chúng đáp ứng tiêu chí đã chỉ định, thì hàm trả về 0.
- Trong Power BI, không thể sử dụng hàm AVERAGEX khi bạn đang làm việc trong phương thức kết nối DirectQuery.
Kết quả trả về
Giá trị được trả về cuối cùng là một dạng dữ liệu bất kỳ nhưng thường là ở dạng số thập phân (decimal data type)
Trường hợp cụ thể sử dụng hàm AVERAGEX
Trường hợp 1: Tính trung bình mức lương của tất cả nhân viên sử dụng hàm AVERAGEX
Chúng ta sẽ có bảng EmployeeSalary với các cột [Department, [EmployeeID] và [Salary], và [Bonus Multiplier] để tính trung bình mức lương của tất cả nhân viên chúng ta sẽ sử dung công thức DAX sau:
Giải thích: Xác định 2 cột cần sử dụng để tính toán là [Salary] và [Bonus Multiplier] nằm trong bảng Employee Salary
- Bước 1: Tính mức lương cần phải chi trả cho mỗi nhân viên
Để tính được mức lương trung bình của toàn bộ nhân viên thì trước tiên cần tính mức lương của từng nhân viên (mỗi dòng), ở từng dòng lấy [Salary] * [Bonus Multiplier]
- Bước 2: Tính mức lương trung bình cần phải chi trả cho mỗi nhân viên
Tổng hợp kết quả phép tính ([Salary] * [Bonus Multiplier]) ở mỗi dòng để tính mức lương trung bình của nhân viên
Kết quả trả ra: $167,263
Trường hợp 2: Tính trung bình mức lương cho từng phòng ban sử dụng hàm AVERAGEX kết hợp với hàm SUMMARIZE
Tiếp tục sử dụng bảng Employee Salary trên, để tính mức lương trung bình cho từng phòng ban ta sử dụng công thức DAX như sau:
Giải thích: Xác định 2 cột cần sử dụng để tính toán là [Salary], [Bonus Multiplier] và cột [Department] để nhóm dữ liệu trong bảng Employee Salary
- Bước 1: Nhóm các nhân viên theo phòng ban và tính mức lương trung bình cho mỗi phòng ban.
Sử dụng hàm SUMMARIZE để tạo bảng tóm tắt từ bảng dữ liệu ‘EmployeeSalary’, nhóm các dòng dựa trên giá trị của cột [Department]
Chỉ định cột mới [AvgSalary] chứa giá trị trung bình biểu thức (EmployeeSalary[Salary] * EmployeeSalary[Bonus Multiplier]) cho mỗi nhóm (Department) với hàm AVERAGEX
- Bước 2: Tính mức lương trung bình cho tất cả các phòng ban
Sử dụng hàm AVERAGEX để lặp qua từng nhóm ( Department ) tính toán giá trị trung bình của cột [AvgSalary]
Kết quả trả ra: $162,864
So sánh AVERAGE, AVERAGEA, AVERAGEX
Đặc điểm | AVERAGE | AVERAGEA | AVERAGEX |
Sử dụng | Tính trung bình của các giá trị trong một cột số. | Tính trung bình của các giá trị trong một cột số, bao gồm cả giá trị logic TRUE và FALSE, và giá trị không số. | Tính trung bình của kết quả một biểu thức cho từng hàng trong bảng hoặc tập hợp dữ liệu. |
Tham số | Chỉ yêu cầu một tham số đầu vào là cột mà bạn muốn tính trung bình. | Chỉ yêu cầu một tham số đầu vào là cột mà bạn muốn tính trung bình. | Yêu cầu hai tham số đầu vào: một bảng hoặc tập hợp dữ liệu, và một biểu thức để tính trung bình. |
Loại hàm | Hàm Aggregation (tổng hợp) | Hàm Aggregation (tổng hợp) | Hàm Iterator (lặp qua từng hàng trong bảng hoặc tập hợp dữ liệu). |
Điều kiện | Bỏ qua giá trị logic FALSE trong tính toán. | Bao gồm cả giá trị logic TRUE và FALSE, và giá trị không số trong tính toán. | Linh hoạt hơn và có thể thực hiện các phép tính phức tạp hơn trên từng hàng dữ liệu. |
HÀM MINX VÀ MAXX
Mục đích sử dụng hàm MINX và MAXX
Hàm MINX/MAXX cho phép bạn thực hiện một loạt các phép tính phức tạp trên từng dòng dữ liệu trong nhiều cột (cộng, trừ, nhân, chia, các phép toán lồng nhau,…), sau đó sẽ lấy ra kết quả có giá trị nhỏ nhất/lớn nhất trong tập dữ liệu tuyền vào.
Cú pháp của hàm MINX/MAXX
Cú pháp
= MINX(<table>, < expression>,[<variant>]) | = MAXX(<table>, < expression>,[<variant>]) |
Giải thích tham số:
Tham số | Giải thích |
table | Bảng chứa dữ liệu cần tính toán. |
experssion | Có thể là tên cột trong bảng hoặc là biểu thức được tính toán lần lượt cho từng dòng dữ liệu trong bảng, sau đó Power BI sẽ trả về giá trị lớn nhất/ nhỏ nhất trong tập dữ liệu. |
variant | (Tùy chọn) nếu được đặt là TRUE, và nếu trong cột có các giá trị hỗn hợp( bao gồm cả số và chuỗi), hàm sẽ trả về giá trị nhỏ nhất/lớn nhất dựa trên sắp xếp theo thứ tự giảm dần/tăng dần. |
Lưu ý:
- Tham số đầu tiên (<table>) của hàm MINX/MAXX phải là một bảng hoặc một biểu thức trả về bảng.
- Tham số thứ hai (<expression>) của hàm có thể là một công thức hoặc một cột cụ thể từ bảng.
- Hàm chỉ tính cho kiểu dữ liệu số (Numbers), văn bản (Texts) và ngày (Dates):
→ Đối với kiểu dữ liệu số, giá trị nhỏ nhất sẽ là giá trị nhỏ nhất trong số các giá trị số.
→ Đối với kiểu dữ liệu văn bản, giá trị nhỏ nhất sẽ là giá trị được sắp xếp theo thứ tự từ A đến Z.
→ Đối với kiểu dữ liệu ngày, giá trị nhỏ nhất sẽ là ngày tháng gần nhất về phía trước.
- Hàm sẽ bỏ qua các hàng có giá trị là null trong cột mà nó tính toán. Khi sử dụng hàm với giá trị logic thì Power BI sẽ báo lỗi.
- Khi sử dụng hàm trong các cột tính toán hoặc hàm tuân theo quy tắc bảo mật cấp hàng thì hàm sẽ không được hỗ trợ sử dụng chế độ DirectQuery.
Kết quả trả về:
• Trả về một giá trị duy nhất và là giá trị nhỏ nhất/lớn nhất. Kiểu dữ liệu trả về sẽ phụ thuộc vào kiểu dữ liệu của cột hoặc biểu thức đầu vào, có thể là số, văn bản hoặc ngày.
Trường hợp cụ thể sử dụng hàm MINX/MAXX
Để hiểu rõ và vận dụng hàm MINX/MAXX trong Power BI một cách thành thục, chúng ta sẽ cùng đi vào thực hành các trường hợp phổ biến sử dụng hai hàm:
- Trường hợp 1: Sử dụng hai hàm để tìm mức lương thấp nhất, cao nhất
- Trường hợp 2: Sử dụng MINX/MAXX kết hợp với hàm FILTER để̀ tính toán dựa trên các điều kiện
Trường hợp 1: Sử dụng hai hàm để tìm mức lương thấp nhất và cao nhất
Giả sử bạn đang làm việc với bảng ‘Employee’ chứa thông tin về tên nhân viên, số giờ làm, mức lương trên giờ, phòng ban làm việc. Bạn cần tạo Measure để tìm ra nhân viên có mức lương thấp nhất và cao nhất:
• Công thức:
Thấp nhất | Cao nhất |
| MinSalary = MINX ( Employee, Employee[HoursWorked] * Employee[HourlyWage] ) | MaxSalary = |
• Giải thích công thức:
Bước 1: Power BI truy cập bảng ‘Employee’ chứa dữ liệu dùng để tính toán, ở đây là cột [HoursWorked] và cột [HourlyWage].
Bước 2: Ở mỗi dòng sẽ thực hiện phép tính “Employee[HoursWorked] * Employee[HourlyWage]”.
Bước 3: Tổng hợp kết quả của phép tính ở mỗi dòng lấy ra giá trị nhỏ nhất/lớn nhất.
• Kết quả trả về:
Bảng lương của từng nhân viên
Trường hợp 2: Sử dụng MINX/MAXX FILTER để̀ tính toán dựa trên các điều kiện
Bạn vẫn sử dụng bảng dữ liệu trên. Bạn cần tạo ra một Measure để tìm ra mức lương cao nhất ở phòng ban “HR”:
• Công thức:
Thấp nhất | Cao nhất |
MinSalary = | MaxSalary = |
• Giải thích công thức:
Bước 1: Power BI truy cập bảng ‘Employee’ chứa dữ liệu dùng để tính toán, ở đây là cột [HoursWorked] và cột [HourlyWage].
Bước 2: Thực hiện bước lọc dữ liệu dựa trên điều kiện đã cho (Employee[Department]==”HR”) với hàm FILTER
Bước 3: Trên mỗi dòng dữ liệu đã lọc, mỗi dòng sẽ thực hiện phép tính “Employee[HoursWorked] * Employee[HourlyWage]”
Bước 4: Tổng hợp kết quả của phép tính ở mỗi dòng lấy ra giá trị nhỏ nhất/lớn nhất.
• Kết quả trả về:
Bảng lương nhân viên ở phòng ban HR
Một vài thay đổi gần đây
Với bản cập nhật tháng 9 năm 2023, Microsoft đã công bố một số thay đổi quan trọng đối với các hàm MINX và MAXX trong ngôn ngữ DAX (Tính năng tính năng Power BI tháng 9 năm 2023).
Hiện tại hàm MINX và MAXX đã được bổ xung thêm một tham số <variant> có thể tùy chon để hàm có thể linh hoạt và mạnh mẽ hơn.
Tạm kết
Thông qua các ví dụ cụ thể đã được trình bày, bạn có thể hiểu rõ về các hàm lặp và cách áp dụng chúng từ những tình huống đơn giản đến phức tạp. Những minh họa này không chỉ giúp bạn nắm vững nguyên lý hoạt động của các hàm lặp mà còn cung cấp cho bạn những kiến thức cần thiết để giải quyết các bài toán thực tế trong công việc.
Hy vọng rằng bài viết này của Datapot đã mang đến cho bạn những kiến thức cần thiết để sử dụng hàm lặp trong Power BI một cách hiệu quả. Với những thông tin chi tiết và dễ hiểu, bạn sẽ có thể tự tin áp dụng các hàm lặp vào công việc phân tích dữ liệu của mình, nâng cao độ chính xác và hiệu quả trong các báo cáo và phân tích.
