Phần này cung cấp liên kết đến các ví dụ thể hiện cách sử dụng công thức DAX trong các kịch bản sau đây.
- Thực hiện các phép tính phức tạp
- Làm việc với văn bản và ngày
- Giá trị có điều kiện và kiểm tra lỗi
- Sử dụng hiển thị thời gian thông minh
- Xếp hạng và so sánh các giá trị
Trong bài viết này
Bắt đầu
Truy nhập Wiki của Trung tâm Tài nguyên DAX , nơi bạn có thể tìm thấy tất cả các loại thông tin về DAX bao gồm blog, mẫu, sách trắng và video được cung cấp bởi các chuyên gia hàng đầu trong ngành và Microsoft.
Kịch bản: Thực hiện các tính toán phức tạp
Công thức DAX có thể thực hiện các phép tính phức tạp liên quan đến tổng hợp tùy chỉnh, lọc và sử dụng các giá trị có điều kiện. Mục này cung cấp ví dụ về cách bắt đầu với các phép tính tùy chỉnh.
Tạo phép tính tùy chỉnh cho PivotTable
CALCULATE và CALCULATETABLE là các hàm mạnh mẽ, linh hoạt, hữu ích cho việc xác định các trường tính toán. Những hàm này cho phép bạn thay đổi ngữ cảnh mà tính toán sẽ được thực hiện. Bạn cũng có thể tùy chỉnh loại tổng hợp hoặc phép toán cần thực hiện. Hãy xem các chủ đề sau đây để biết ví dụ.
Áp dụng bộ lọc cho công thức
Ở hầu hết những nơi mà hàm DAX lấy một bảng làm đối số, bạn thường có thể chuyển một bảng đã lọc thay vào đó, bằng cách sử dụng hàm FILTER thay vì tên bảng hoặc bằng cách chỉ định một biểu thức bộ lọc làm một trong các đối số của hàm. Các chủ đề sau đây cung cấp ví dụ về cách tạo bộ lọc và cách bộ lọc ảnh hưởng đến kết quả của công thức. Để biết thêm thông tin, hãy xem Lọc Dữ liệu trong Công thức DAX.
Hàm FILTER cho phép bạn chỉ định tiêu chí lọc bằng cách sử dụng biểu thức, trong khi các hàm khác được thiết kế đặc biệt để lọc ra các giá trị trống.
Loại bỏ các bộ lọc có chọn lọc để tạo tỷ lệ động
Bằng cách tạo các bộ lọc động trong công thức, bạn có thể dễ dàng trả lời các câu hỏi như sau:
- Doanh số của sản phẩm hiện tại đóng góp vào tổng doanh thu trong năm là bao nhiêu?
- Bộ phận này đã đóng góp bao nhiêu vào tổng lợi nhuận trong tất cả các năm hoạt động, so với các bộ phận khác?
Các công thức mà bạn dùng trong PivotTable có thể bị ảnh hưởng bởi ngữ cảnh PivotTable, nhưng bạn có thể thay đổi ngữ cảnh một cách có chọn lọc bằng cách thêm hoặc loại bỏ bộ lọc. Ví dụ trong chủ đề TẤT CẢ cho bạn biết cách thực hiện điều này. Để tìm tỷ lệ doanh thu của một người bán lại cụ thể trên doanh thu của tất cả người bán lại, bạn tạo một thước đo để tính giá trị của ngữ cảnh hiện tại chia cho giá trị của ngữ cảnh TẤT CẢ.
Chủ đề ALLEXCEPT cung cấp ví dụ về cách xóa có chọn lọc trên công thức. Cả hai ví dụ đều hướng dẫn bạn cách kết quả thay đổi như thế nào tùy thuộc vào thiết kế của PivotTable.
Để xem các ví dụ khác về cách tính tỷ lệ và tỷ lệ phần trăm, hãy xem các chủ đề sau:
Sử dụng giá trị từ vòng lặp bên ngoài
Ngoài việc dùng giá trị từ ngữ cảnh hiện tại trong tính toán, DAX có thể dùng giá trị từ một vòng lặp trước đó để tạo một tập hợp các phép tính có liên quan. Chủ đề sau đây cung cấp hướng dẫn về cách xây dựng công thức tham chiếu giá trị từ vòng lặp bên ngoài. Hàm EARLIER hỗ trợ tối đa hai cấp độ vòng lặp lồng.
Để tìm hiểu thêm về ngữ cảnh hàng và các bảng liên quan, cũng như cách dùng khái niệm này trong công thức, hãy xem Ngữ cảnh trong Công thức DAX.
Kịch bản: Làm việc với Văn bản và Ngày
Phần này cung cấp link đến các chủ đề tham khảo DAX chứa ví dụ về các kịch bản phổ biến liên quan đến làm việc với văn bản, trích xuất và soạn giá trị ngày và thời gian hoặc tạo giá trị dựa trên điều kiện.
Tạo cột khóa bằng cách ghép nối
Power Pivot không cho phép khóa tổng hợp; Do đó, nếu bạn có các khóa tổng hợp trong nguồn dữ liệu của mình, bạn có thể cần kết hợp chúng thành một cột khóa duy nhất. Chủ đề sau đây cung cấp một ví dụ về cách tạo cột được tính dựa trên khóa tổng hợp.
Compose a date based on the date parts extracted from a text date
Power Pivot dùng kiểu dữ liệu ngày/giờ SQL Server để làm việc với ngày tháng; do đó, nếu dữ liệu bên ngoài của bạn chứa ngày tháng được định dạng khác -- ví dụ, nếu ngày tháng của bạn được viết theo định dạng ngày khu vực không được công nhận bởi bộ máy dữ liệu Power Pivot, hoặc nếu dữ liệu của bạn sử dụng các khóa thay thế số nguyên -- bạn có thể cần dùng công thức DAX để trích xuất các phần ngày, rồi soạn các phần đó thành một ngày hợp lệ/ biểu thị thời gian.
Ví dụ: nếu bạn có một cột ngày được biểu diễn ở dạng số nguyên rồi nhập dưới dạng chuỗi văn bản thì bạn có thể chuyển đổi chuỗi đó sang giá trị ngày/giờ bằng công thức sau:
=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))
| Value1 | Kết quả |
|---|---|
| 01032009 | 1/3/2009 |
| 12132008 | 12/13/2008 |
| 06252007 | 6/25/2007 |
Các chủ đề sau đây cung cấp thêm thông tin về các hàm được sử dụng để trích xuất và soạn ngày.
Xác định định dạng ngày hoặc số tùy chỉnh
Nếu dữ liệu của bạn chứa ngày hoặc số không được thể hiện ở một trong các định dạng văn bản Windows chuẩn, bạn có thể xác định định dạng tùy chỉnh để đảm bảo rằng các giá trị đó được xử lý đúng. Những định dạng này được dùng khi chuyển đổi giá trị thành chuỗi hoặc từ chuỗi. Các chủ đề sau đây cũng cung cấp danh sách chi tiết các định dạng được xác định trước sẵn dùng để làm việc với ngày tháng và số.
- Định dạng số xác định trước cho hàm FORMAT
- Định dạng số tùy chỉnh cho hàm FORMAT
- Định dạng Ngày và Giờ Xác định Trước cho hàm FORMAT
- Định dạng Ngày và Giờ Tùy chỉnh cho hàm FORMAT
Thay đổi kiểu dữ liệu bằng công thức
Trong Power Pivot, kiểu dữ liệu của kết quả được xác định bởi các cột nguồn và bạn không thể chỉ định rõ kiểu dữ liệu của kết quả vì kiểu dữ liệu tối ưu do Power Pivot xác định. Tuy nhiên, bạn có thể sử dụng các chuyển đổi kiểu dữ liệu ngầm được Power Pivot thực hiện để điều chỉnh kiểu dữ liệu đầu ra.
- Để chuyển đổi một ngày hoặc một chuỗi số thành một số, hãy nhân với 1,0. Ví dụ, công thức sau tính toán ngày hiện tại trừ 3 ngày, rồi đưa ra giá trị số nguyên tương ứng.
=(TODAY()-3)*1,0 - Để chuyển đổi giá trị ngày, số hoặc tiền tệ thành một chuỗi, hãy ghép giá trị đó bằng chuỗi trống. Ví dụ, công thức sau đây trả về ngày hôm nay ở dạng chuỗi.
=""& TODAY()
Các hàm sau đây cũng có thể được sử dụng để đảm bảo rằng một kiểu dữ liệu cụ thể được trả về:
Chuyển đổi số thực sang số nguyên
- Hàm ROUND
- Hàm CEILING
-
Hàm FLOOR
Chuyển đổi số thực, số nguyên hoặc ngày thành chuỗi - Hàm FIXED
-
Hàm FORMAT
Chuyển đổi chuỗi thành số thực hoặc ngày - Hàm VALUE
- Hàm DATEVALUE
- Hàm TIMEVALUE
Kịch bản: Giá trị có điều kiện và kiểm tra lỗi
Giống như Excel, DAX có các hàm cho phép bạn kiểm tra các giá trị trong dữ liệu và trả về một giá trị khác dựa trên điều kiện. Ví dụ: bạn có thể tạo cột được tính toán để đánh nhãn người bán lại là Ưu tiên hoặc Giá trị tùy theo doanh thu hàng năm. Các hàm kiểm tra giá trị cũng hữu ích cho việc kiểm tra phạm vi hoặc loại giá trị, để ngăn các lỗi dữ liệu không mong muốn phá vỡ tính toán.
Tạo một giá trị dựa trên một điều kiện
Bạn có thể dùng các điều kiện IF lồng nhau để kiểm tra giá trị và tạo giá trị mới có điều kiện. Các chủ đề sau đây chứa một số ví dụ đơn giản về xử lý có điều kiện và giá trị điều kiện:
Kiểm tra lỗi trong công thức
Không giống Excel, bạn không thể có các giá trị hợp lệ trong một hàng của cột được tính và các giá trị không hợp lệ trong một hàng khác. Nghĩa là, nếu có lỗi trong bất kỳ phần nào của cột Power Pivot, toàn bộ cột sẽ bị gắn cờ lỗi, vì vậy bạn phải luôn sửa lỗi công thức dẫn đến các giá trị không hợp lệ.
Ví dụ, nếu bạn tạo công thức chia cho không, bạn có thể nhận được kết quả vô cực hoặc lỗi. Một số công thức cũng sẽ không hoạt động nếu hàm gặp phải một giá trị trống trong khi hàm mong đợi sẽ có giá trị số. Trong khi bạn phát triển mô hình dữ liệu của mình, tốt nhất là cho phép lỗi xuất hiện để bạn có thể bấm vào thông báo và khắc phục vấn đề. Tuy nhiên, khi bạn phát hành sổ làm việc, bạn nên kết hợp xử lý lỗi để ngăn các giá trị không mong muốn khiến các tính toán không thành công.
Để tránh trả về lỗi trong cột được tính, bạn hãy sử dụng kết hợp hàm lô-gic và hàm thông tin để kiểm tra lỗi và luôn trả về các giá trị hợp lệ. Các chủ đề sau đây cung cấp một số ví dụ đơn giản về cách thực hiện điều này trong DAX:
Kịch bản: Sử dụng Hiển thị Thời gian Thông minh
Hàm hiển thị thời gian thông minh của DAX bao gồm các hàm giúp bạn truy xuất ngày hoặc phạm vi ngày từ dữ liệu của mình. Sau đó bạn có thể dùng các ngày hoặc phạm vi ngày đó để tính toán giá trị trong các khoảng thời gian tương tự. Hàm hiển thị thời gian thông minh cũng bao gồm các hàm làm việc với các khoảng ngày tiêu chuẩn, nhằm cho phép bạn so sánh các giá trị qua các tháng, năm hoặc quý. Bạn cũng có thể tạo công thức so sánh các giá trị cho ngày đầu tiên và ngày cuối cùng trong khoảng thời gian đã xác định.
Để biết danh sách tất cả các hàm hiển thị thời gian thông minh, hãy xem các Hàm Hiển thị Thời gian Thông minh (DAX). Để biết các mẹo về cách sử dụng ngày và giờ hiệu quả trong phân tích Power Pivot, hãy xem Ngày trong Power Pivot.
Tính doanh số tích lũy
Các chủ đề sau đây chứa ví dụ về cách tính số dư đầu kỳ và cuối kỳ. Ví dụ cho phép bạn tạo số dư hiện thời qua các khoảng khác nhau như ngày, tháng, quý hoặc năm.
- Hàm CLOSINGBALANCEMONTH, Hàm CLOSINGBALANCEQUARTER, Hàm CLOSINGBALANCEYEAR
- Hàm OPENINGBALANCEMONTH, Hàm OPENINGBALANCEQUARTER, Hàm OPENINGBALANCEYEAR
So sánh các giá trị theo thời gian
Các chủ đề sau đây chứa ví dụ về cách so sánh tổng trong các khoảng thời gian khác nhau. Các khoảng thời gian mặc định được DAX hỗ trợ là tháng, quý và năm.
- Hàm PREVIOUSMONTH, Hàm PREVIOUSQUARTER,PREVIOUSYEAR
- Hàm TOTALMTD, Hàm TOTALQTD, Hàm TOTALYTD
- Hàm PARALLELPERIOD
Tính toán một giá trị trong phạm vi ngày tùy chỉnh
Hãy xem các chủ đề sau đây để biết ví dụ về cách truy xuất phạm vi ngày tùy chỉnh, chẳng hạn như 15 ngày đầu tiên sau khi bắt đầu khuyến mại bán hàng.
Nếu bạn sử dụng các hàm hiển thị thời gian thông minh để truy xuất tập hợp ngày tùy chỉnh, bạn có thể sử dụng tập hợp ngày đó làm dữ liệu đầu vào cho hàm thực hiện tính toán để tạo tổng hợp tùy chỉnh xuyên suốt các khoảng thời gian. Hãy xem chủ đề sau đây để biết ví dụ về cách thực hiện việc này:
-
Lưu ý
Nếu bạn không cần xác định phạm vi ngày tùy chỉnh nhưng đang làm việc với các đơn vị kế toán tiêu chuẩn như tháng, quý hoặc năm, chúng tôi khuyên bạn nên thực hiện tính toán bằng cách sử dụng các hàm hiển thị thời gian thông minh được thiết kế cho mục đích này, chẳng hạn như TOTALQTD, TOTALMTD, TOTALQTD, v.v.
Kịch bản: Xếp hạng và so sánh các giá trị
Để chỉ hiển thị n số mục trên cùng trong một cột hoặc trong PivotTable, bạn có một số tùy chọn:
- Bạn có thể sử dụng các tính năng trong Excel để tạo bộ lọc Hàng đầu. Bạn cũng có thể chọn một số giá trị cao nhất hoặc thấp nhất trong PivotTable. Phần đầu của mục này mô tả cách lọc 10 mục trên cùng trong PivotTable. Để biết thêm thông tin, hãy xem tài liệu Excel.
- Bạn có thể tạo một công thức để tự động xếp hạng các giá trị, rồi lọc theo các giá trị xếp hạng hoặc sử dụng giá trị xếp hạng như một Slicer. Phần thứ hai của mục này mô tả cách tạo công thức này, rồi sử dụng xếp hạng đó trong Slicer.
Mỗi phương pháp đều có những ưu điểm và nhược điểm.
- Bộ lọc Excel Top dễ sử dụng nhưng bộ lọc chỉ dành cho mục đích hiển thị. Nếu dữ liệu cơ sở PivotTable thay đổi, bạn phải làm mới PivotTable theo cách thủ công để xem những thay đổi đó. Nếu bạn cần linh động làm việc với thứ hạng, bạn có thể dùng DAX để tạo công thức so sánh các giá trị với các giá trị khác trong một cột.
- Công thức DAX mạnh hơn; Hơn nữa, bằng cách thêm giá trị xếp hạng vào Slicer, bạn có thể chỉ cần bấm vào Slicer để thay đổi số lượng các giá trị hàng đầu được hiển thị. Tuy nhiên, các tính toán này tốn kém về mặt tính toán và phương pháp này có thể không phù hợp với các bảng có nhiều hàng.
Chỉ hiện mười mục trên cùng trong một PivotTable
Để hiện các giá trị trên cùng hoặc dưới cùng trong PivotTable
|
|---|
Sắp xếp các mục một cách linh động bằng cách dùng công thức
Chủ đề sau đây chứa ví dụ về cách sử dụng DAX để tạo xếp hạng được lưu trữ trong một cột được tính. Vì công thức DAX được tính toán linh động, nên bạn luôn có thể chắc chắn rằng xếp hạng là chính xác ngay cả khi dữ liệu cơ sở đã thay đổi. Ngoài ra, vì công thức được sử dụng trong cột được tính nên bạn có thể sử dụng xếp hạng trong Slicer và sau đó chọn các giá trị 5 hàng đầu, 10 người đứng đầu hoặc thậm chí là 100 người đứng đầu.