Các tình huống DAX trong Power Pivot

Tạo tính toán tùy chỉnh cho PivotTable

CALCULATE và CALCULATETABLE là các hàm mạnh mẽ, linh hoạt giúp xác định các trường được tính toán. Các hàm này cho phép bạn thay đổi ngữ cảnh trong đó việc tính toán sẽ được thực hiện. Bạn cũng có thể tùy chỉnh loại phép toán tổng hợp hoặc toán học để thực hiện. Xem các chủ đề sau để biết ví dụ.

• TÍNH TOÁN

• CALCULATETABLE

Áp dụng bộ lọc cho công thức

Ở hầu hết các vị trí mà hàm DAX nhận bảng làm đối số, bạn thường có thể truyền vào bảng đã lọc thay vào đó, bằng cách sử dụng hàm FILTER thay cho tên bảng hoặc bằng cách chỉ định 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 mục 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 các giá trị trống.

• BỘ LỌC

• ALLNOBLANKROW

• FIRSTNONBLANK

• LASTNONBLANK

Loại bỏ các bộ lọc có chọn lọc để tạo tỷ lệ động

Bằng cách tạo 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:

• Đóng góp gì vào doanh số của sản phẩm hiện tại vào tổng doanh thu trong năm?

• 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ông thức bạn sử 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 số của một nhà bán lại cụ thể đối với doanh số của tất cả các nhà bán lại, bạn tạo một số đo tính toán giá trị cho ngữ cảnh hiện tại được chia cho giá trị cho ngữ cảnh ALL.

Chủ đề ALLEXCEPT cung cấp ví dụ về cách xóa có chọn lọc các bộ lọc trên công thức. Cả hai ví dụ sẽ hướng dẫn bạn cách kết quả thay đổi tùy thuộc vào thiết kế của PivotTable.

• Tất cả

• ALLEXCEPT

Để biết các ví dụ khác về cách tính toán tỷ lệ và tỷ lệ phần trăm, hãy xem các chủ đề sau đây:

• NGƯỜI ISBLANK

• HÀM ISERROR

Sử dụng một giá trị từ vòng lặp bên ngoài

Ngoài việc sử dụng các giá trị từ ngữ cảnh hiện tại trong các phép tính, DAX có thể sử dụng một giá trị từ một vòng lặp trước đó trong việc tạo ra một tập hợp các phép tính 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 đến một 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 nhau.

• Hàm EARLIER

Để tìm hiểu thêm về ngữ cảnh hàng và bảng liên quan cũng như cách sử 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.

Tạo cột khóa theo ghép nối

Power Pivot không cho phép các 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, 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 toán dựa trên khóa tổng hợp.

• Hàm CONCATENATE

Soạn ngày dựa trên các phần ngày được trích xuất từ ngày tháng văn bản

Power Pivot sử dụng kiểu dữ liệu ngày/giờ SQL Server để làm việc với ngày; do đó, nếu dữ liệu ngoài của bạn chứa ngày tháng được định dạng khác -- ví dụ: nếu ngày của bạn được viết theo định dạng ngày theo vùng mà công cụ dữ liệu Power Pivot không nhận ra 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 sử 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 bản trình bày ngày/giờ hợp lệ.

Ví dụ: nếu bạn có một cột ngày tháng đã được biểu thị là số nguyên và sau đó được nhập dưới dạng chuỗi văn bản, bạn có thể chuyển đổi chuỗi đó thành giá trị ngày/giờ bằng cách sử dụng công thức sau đây:

=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))

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.

• Hàm DATEVALUE

• Hàm TIMEVALUE

• Hàm DATE

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 tiêu 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ý chính xác. Những định dạng này được sử 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 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 đầu ra được xác định bởi các cột nguồn và bạn không thể chỉ định rõ ràng kiểu dữ liệu của kết quả, vì kiểu dữ liệu tối ưu được xác định bởi Power Pivot. Tuy nhiên, bạn có thể sử dụng các chuyển đổi loại dữ liệu ngầm được thực hiện bởi Power Pivot để thao tác với kiểu dữ liệu đầu ra. 

• Để chuyển đổi ngày hoặc chuỗi số thành số, nhân với 1,0. Ví dụ, công thức sau đây tính toán ngày hiện tại trừ 3 ngày, rồi xuất 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 chuỗi, hãy ghép nối giá trị đó với chuỗi trống. Ví dụ: công thức sau đây trả về ngày hôm nay dưới 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 thành 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 tháng

• Hàm VALUE

• Hàm DATEVALUE

• Hàm TIMEVALUE

Tạo giá trị dựa trên điều kiện

Bạn có thể sử dụng các điều kiện IF lồng nhau để kiểm tra giá trị và tạo ra các giá trị mới theo điều kiện. Các chủ đề sau đây chứa một số ví dụ đơn giản về các giá trị có điều kiện và xử lý có điều kiện:

• NẾU

• Hàm OR

• Hàm CONCATENATE

Kiểm tra lỗi trong công thức

Không giống như 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ẽ được gắn cờ với lỗi, do đó bạn phải luôn sửa lỗi công thức dẫn đến 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 thành công nếu hàm gặp phải giá trị trống khi mong đợi giá trị số. Trong khi bạn đang 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 sự cố. Tuy nhiên, khi phát hành sổ làm việc, bạn nên kết hợp xử lý lỗi để ngăn không cho các giá trị không mong muốn khiến các phép tính 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ề 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:

• Hàm ISBLANK

• Hàm IFERROR

Tính toán doanh số tích lũy

Các chủ đề sau đây chứa các ví dụ về cách tính toán số dư đóng và mở. Ví dụ cho phép bạn tạo số dư đang chạy ở các khoảng thời gian 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 các ví dụ về cách so sánh tổng trong các khoảng thời gian khác nhau. Khoảng thời gian mặc định được DAX hỗ trợ là tháng, quý và năm.

• Hàm PREVIOUSMONTH, PREVIOUSQUARTER, Hàm PREVIOUSYEAR

• Hàm TOTALMTD, Hàm TOTALQTD, Hàm TOTALYTD

• Hàm PARALLELPERIOD

Tính một giá trị trong phạm vi ngày tùy chỉnh

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.

• Hàm DATESINPERIOD

• Hàm DATESBETWEEN

• Hàm DATEADD

• Hàm FIRSTDATE

• Hàm LASTDATE

Nếu bạn sử dụng 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ể 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 qua 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:

• Hàm PARALLELPERIOD

  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.

Chỉ hiển thị mười mục hàng đầu trong PivotTable

Tự động sắp xếp các mục bằng cách sử 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 cột được tính toán. Vì các công thức DAX được tính toán tự động, bạn luôn có thể đảm bảo 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 một cột được tính, bạn có thể sử dụng xếp hạng trong Slicer, rồi chọn 5 giá trị hàng đầu, 10 vị trí hàng đầu hoặc thậm chí là 100 giá trị hàng đầu.

• Hàm EARLIER.