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

Tạo các phép tính tùy chỉnh cho PivotTable

CALCULATE và CALCULATETABLE là các hàm linh hoạt, mạnh mẽ, hữu ích để 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 nơi 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. Hãy xem các chủ đề sau đây để biết ví dụ.

• CALCULATE

• CALCULATETABLE

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

Trong hầu hết các vị trí mà một hàm DAX nhận một bảng làm đối số, thay vào đó bạn có thể truyền vào một bảng đã lọc, bằng cách dùng hàm FILTER thay cho tên bảng hoặc bằng cách xác định biểu thức bộ lọc như một trong các đối số của hàm. Các chủ đề sau đây cung cấp các 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.

• FILTER

• 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 của doanh thu sản phẩm hiện tại vào tổng doanh thu trong năm là như thế nào?

• Bộ phận chia 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 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 theo lựa chọn 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ẻ cụ thể so với doanh thu của tất cả các đại lý, bạn hãy 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 một 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.

• ALL

• 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:

• ISBLANK

• 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ừ vòng lặp trước đó trong việc tạo 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 một 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 bằng cách kết hợp

Power Pivot 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 các khóa đó 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 của văn bản

Power Pivot dùng kiểu SQL Server liệu ngày/giờ để làm việc với ngày tháng; 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 phải 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 vào 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 rồi 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 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 dùng để trích xuất và soạn ngày.

• Hàm DATEVALUE

• Hàm TIMEVALUE

• Hàm DATE

Xác định ngày hoặc định dạng số tùy chỉnh

Nếu dữ liệu của bạn chứa ngày hoặc số không được trình bày ở 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ý 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 về 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ách dùng công thức

Trong Power Pivot, kiểu dữ liệu của đầu ra được xác định bởi 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 được xác định bởi trung Power Pivot. Tuy nhiên, bạn có thể sử dụng chuyển đổi kiểu dữ liệu ngầm được thực hiện bằng Power Pivot để thao tác với loại 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 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 kết hợp giá trị đó với một 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 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 các chuỗi sang số thực hoặc ngày tháng

• Hàm VALUE

• Hàm DATEVALUE

• Hàm TIMEVALUE

Tạo một giá trị dựa trên một đ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 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ề xử lý có điều kiện và giá trị điều kiện:

• IF

• Hàm OR

• Hàm CONCATENATE

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 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 một công thức chia cho 0, 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 có 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à nên cho phép các 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ị ngoài ý muốn khiến cho các phép tính không thành công.

Để tránh trả về lỗi trong một cột được tính, bạn hãy dùng kết hợp các hàm lô-gic và thông tin để kiểm tra lỗi và luôn 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 việc này trong DAX:

• Hàm ISBLANK

• Hàm IFERROR

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

Các chủ đề sau đây chứa các ví dụ về cách tính toán số dư đóng và số dư mở. Các ví dụ cho phép bạn tạo số dư hiện thời trên 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, Hàm PREVIOUSQUARTER, Hàm PREVIOUSYEAR

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

• Hàm PARALLELPERIOD

Tính toán 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.

• Hàm DATESINPERIOD

• Hàm DATESBETWEEN

• Hàm DATEADD

• Hàm FIRSTDATE

• Hàm LASTDATE

Nếu bạn dùng hàm hiển thị thời gian thông minh để truy xuất một tập hợp ngày tùy chỉnh, thì bạn có thể dùng tập hợp ngày đó làm dữ liệu đầu vào cho một hàm thực hiện tính toán, để tạo tổng hợp tùy chỉnh trong 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 phải 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 dùng các hàm hiệp thông minh thời gian đượ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 hàng linh động 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 một 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 một cách linh động, 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 một cột được tính, bạn có thể sử dụng cách xếp hạng trong Slicer, rồi chọn 5 giá trị trên cùng, 10 vị trí hàng đầu hoặc thậm chí là 100 giá trị hàng đầu.

• Hàm EARLIER.