Excel DAX - Công thức
DAX là ngôn ngữ công thức để tạo các phép tính tùy chỉnh trong Power PivotTables. Bạn có thể sử dụng các hàm DAX được thiết kế để làm việc với dữ liệu quan hệ và thực hiện tổng hợp động trong các công thức DAX.
DAX formulasrất giống với các công thức Excel. Để tạo công thức DAX, bạn nhập dấu bằng, theo sau là tên hàm hoặc biểu thức và bất kỳ giá trị hoặc đối số bắt buộc nào.
Chức năng DAX so với Công thức DAX
Công thức DAX có thể bao gồm các hàm DAX và tận dụng việc sử dụng chúng. Đây là nơi mà các công thức DAX có xu hướng khác với các hàm DAX theo những cách quan trọng.
Hàm DAX luôn tham chiếu đến một cột hoặc một bảng hoàn chỉnh. Nếu bạn chỉ muốn sử dụng các giá trị cụ thể từ bảng hoặc cột, bạn có thể thêm bộ lọc vào công thức.
Nếu bạn muốn tùy chỉnh các phép tính trên cơ sở từng hàng, Power Pivot cung cấp các hàm cho phép bạn sử dụng giá trị hàng hiện tại hoặc giá trị có liên quan để thực hiện các phép tính thay đổi theo ngữ cảnh.
DAX bao gồm một loại hàm trả về một bảng là kết quả của nó, thay vì một giá trị duy nhất. Các hàm này có thể được sử dụng để cung cấp đầu vào cho các hàm khác, do đó tính toán các giá trị cho toàn bộ bảng hoặc cột.
Một số hàm DAX cung cấp thông tin thời gian, cho phép bạn tạo các phép tính bằng cách sử dụng các phạm vi ngày có ý nghĩa và so sánh kết quả trong các khoảng thời gian song song.
Hiểu cú pháp công thức DAX
Mọi công thức DAX đều có cú pháp sau:
Mỗi công thức phải bắt đầu bằng một dấu bằng.
Ở bên phải của dấu bằng, bạn có thể nhập hoặc chọn tên hàm hoặc nhập biểu thức. Biểu thức có thể chứa tên bảng và tên cột được kết nối bởi toán tử DAX.
Sau đây là một số công thức DAX hợp lệ:
- [column_Cost] + [column_Tax]
- = Hôm nay ()
Hiểu tính năng của IntelliSense
DAX cung cấp tính năng IntelliSense cho phép bạn viết các công thức DAX nhanh chóng và chính xác. Với tính năng này, bạn không cần nhập hoàn toàn tên bảng, cột và hàm mà hãy chọn các tên có liên quan từ danh sách thả xuống trong khi viết công thức DAX.
Bắt đầu nhập một vài ký tự đầu tiên của tên hàm. AutoComplete hiển thị danh sách các chức năng có sẵn với tên bắt đầu bằng các chữ cái đó.
Đặt con trỏ vào bất kỳ tên hàm nào. Chú giải công cụ IntelliSense sẽ được hiển thị để bạn sử dụng chức năng.
Nhấp vào tên chức năng. Tên hàm xuất hiện trong thanh công thức và cú pháp được hiển thị, sẽ hướng dẫn bạn khi bạn chọn các đối số.
Nhập chữ cái đầu tiên của tên bảng mà bạn muốn. AutoComplete hiển thị danh sách các bảng và cột có sẵn với tên bắt đầu bằng chữ cái đó.
Nhấn TAB hoặc nhấp vào tên để thêm một mục từ danh sách Tự động điền vào công thức.
Nhấn vào Fxđể hiển thị danh sách các chức năng có sẵn. Để chọn một chức năng từ danh sách thả xuống, sử dụng các phím mũi tên để đánh dấu mục đó và bấm OK để thêm chức năng vào công thức.
Cung cấp các đối số cho hàm bằng cách chọn chúng từ danh sách thả xuống gồm các bảng và cột có thể có hoặc bằng cách nhập các giá trị bắt buộc.
Khuyến khích sử dụng tính năng IntelliSense tiện dụng này.
Sử dụng công thức DAX ở đâu?
Bạn có thể sử dụng công thức DAX trong việc tạo các cột được tính toán và các trường được tính toán.
Bạn có thể sử dụng công thức DAX trong các cột được tính toán, bằng cách thêm một cột và sau đó nhập một biểu thức vào thanh công thức. Bạn tạo các công thức này trong cửa sổ PowerPivot.
Bạn có thể sử dụng công thức DAX trong các trường được tính toán. Bạn tạo các công thức này -
Trong cửa sổ Excel trong hộp thoại Trường Tính toán, hoặc
Trong cửa sổ Power Pivot trong vùng tính toán của bảng.
Cùng một công thức có thể hoạt động khác nhau tùy thuộc vào việc công thức được sử dụng trong cột được tính toán hay trường được tính toán.
Trong cột được tính toán, công thức luôn được áp dụng cho mọi hàng trong cột, trong toàn bộ bảng. Tùy thuộc vào ngữ cảnh hàng, giá trị có thể thay đổi.
Tuy nhiên, trong một trường được tính toán, việc tính toán kết quả phụ thuộc nhiều vào ngữ cảnh. Đó là, thiết kế của PivotTable và lựa chọn tiêu đề hàng và cột ảnh hưởng đến các giá trị được sử dụng trong tính toán.
Điều quan trọng là phải hiểu khái niệm ngữ cảnh trong DAX để viết công thức DAX. Điều này có thể hơi khó khăn khi bắt đầu hành trình DAX của bạn, nhưng khi bạn đã nắm bắt được nó, bạn có thể viết các công thức DAX hiệu quả cần thiết để phân tích dữ liệu động và phức tạp. Để biết chi tiết, hãy tham khảo chương - Bối cảnh DAX.
Tạo công thức DAX
Bạn đã tìm hiểu về tính năng IntelliSense trong phần trước. Hãy nhớ sử dụng nó trong khi tạo bất kỳ công thức DAX nào.
Để tạo công thức DAX, hãy sử dụng các bước sau:
Nhập dấu bằng.
Ở bên phải của dấu bằng, nhập như sau:
Nhập chữ cái đầu tiên của một hàm hoặc tên bảng và chọn tên đầy đủ từ danh sách thả xuống.
Nếu bạn đã chọn một tên hàm, hãy nhập dấu ngoặc đơn '('.
Nếu bạn đã chọn tên bảng, hãy nhập dấu ngoặc vuông '['. Nhập chữ cái đầu tiên của tên cột và chọn tên đầy đủ từ danh sách thả xuống.
Đóng tên cột bằng ']' và tên hàm bằng ')'.
Nhập toán tử DAX giữa các biểu thức hoặc kiểu ',' để tách các đối số của hàm.
Lặp lại các bước 1 - 5 cho đến khi công thức DAX hoàn tất.
Ví dụ: bạn muốn tìm tổng số tiền bán hàng ở khu vực phía Đông. Bạn có thể viết công thức DAX như hình dưới đây. East_Sales là tên của bảng. Số tiền là một cột trong bảng.
SUM ([East_Sales[Amount])Như đã thảo luận trong chương - Cú pháp DAX, bạn nên sử dụng tên bảng cùng với tên cột trong mọi tham chiếu đến bất kỳ tên cột nào. Điều này được gọi là - "tên đủ điều kiện".
Công thức DAX có thể thay đổi dựa trên việc nó dành cho trường được tính toán hay cột được tính toán. Tham khảo các phần bên dưới để biết chi tiết.
Tạo công thức DAX cho cột được tính
Bạn có thể tạo công thức DAX cho một cột được tính toán trong cửa sổ Power Pivot.
- Bấm vào tab của bảng mà bạn muốn thêm cột được tính toán.
- Bấm vào tab Thiết kế trên Ruy-băng.
- Nhấp vào Thêm.
- Nhập công thức DAX cho cột được tính toán trong thanh công thức.
= DIVIDE (East_Sales[Amount], East_Sales[Units])Công thức DAX này thực hiện như sau cho mọi hàng trong bảng East_Sales -
Chia giá trị trong cột Số lượng của một hàng cho giá trị trong cột Đơn vị trong cùng một hàng.
Đặt kết quả vào cột mới được thêm vào trong cùng một hàng.
Lặp đi lặp lại các bước 1 và 2 cho đến khi hoàn thành tất cả các hàng trong bảng.
Bạn đã thêm một cột cho Đơn giá mà tại đó những đơn vị đó được bán với công thức trên.
Như bạn có thể quan sát, các cột được tính toán cũng yêu cầu tính toán và không gian lưu trữ. Do đó, chỉ sử dụng các cột được tính toán nếu cần thiết. Sử dụng các trường được tính toán nếu có thể và đủ.
Tham khảo chương - Cột Tính toán để biết thêm chi tiết.
Tạo công thức DAX cho trường được tính
Bạn có thể tạo công thức DAX cho trường được tính toán trong cửa sổ Excel hoặc trong cửa sổ Power Pivot. Trong trường hợp được tính toán, bạn cần phải cung cấp tên trước.
Để tạo công thức DAX cho một trường được tính toán trong cửa sổ Excel, hãy sử dụng hộp thoại Trường Tính toán.
Để tạo công thức DAX cho trường được tính toán trong cửa sổ Power Pivot, hãy bấm vào ô trong vùng tính toán trong bảng có liên quan. Bắt đầu công thức DAX với CalculatedFieldName: =.
Ví dụ, Tổng số tiền bán hàng miền Đông: = SUM ([Số lượng bán hàng miền Đông [Số tiền])
Nếu bạn sử dụng hộp thoại Trường Tính toán trong cửa sổ Excel, bạn có thể kiểm tra công thức trước khi lưu và tạo thói quen bắt buộc để đảm bảo sử dụng đúng công thức.
Để biết thêm chi tiết về các tùy chọn này, hãy tham khảo chương - Các trường được tính toán.
Tạo công thức DAX bằng thanh công thức
Cửa sổ Power Pivot cũng có một thanh công thức giống như thanh công thức của cửa sổ Excel. Thanh công thức giúp tạo và chỉnh sửa công thức dễ dàng hơn, bằng cách sử dụng chức năng Tự động điền để giảm thiểu lỗi cú pháp.
Để nhập tên của bảng, hãy bắt đầu nhập tên của bảng. Công thức AutoComplete cung cấp một danh sách thả xuống chứa các tên bảng hợp lệ bắt đầu bằng các chữ cái đó. Bạn có thể bắt đầu bằng một chữ cái và nhập nhiều chữ cái hơn để thu hẹp danh sách nếu cần.
Để nhập tên của cột, bạn có thể chọn từ danh sách tên cột trong bảng đã chọn. Nhập dấu ngoặc nhọn '[' ở bên phải tên bảng, sau đó chọn cột từ danh sách các cột trong bảng đã chọn.
Mẹo sử dụng tự động điền
Sau đây là một số mẹo để sử dụng Tự động điền -
Bạn có thể lồng các hàm và công thức trong một công thức DAX. Trong trường hợp như vậy, bạn có thể sử dụng Công thức Tự động điền ở giữa một công thức hiện có với các hàm lồng nhau. Văn bản ngay trước điểm chèn được sử dụng để hiển thị các giá trị trong danh sách thả xuống và tất cả văn bản sau điểm chèn vẫn không thay đổi.
Các tên đã xác định mà bạn tạo cho các hằng số không được hiển thị trong danh sách thả xuống Tự động điền, nhưng bạn vẫn có thể nhập chúng.
Dấu ngoặc đóng của các hàm không được thêm tự động. Bạn cần phải làm điều đó một mình.
Bạn phải đảm bảo rằng mỗi hàm đều đúng về mặt cú pháp.
Hiểu tính năng của chức năng Chèn
Bạn có thể tìm thấy nút Chèn Hàm có nhãn là fx, cả trong cửa sổ Power Pivot và cửa sổ Excel.
Nút Chèn Hàm trong cửa sổ Power Pivot ở bên trái thanh công thức.
Nút Chèn Hàm trong cửa sổ Excel nằm trong hộp thoại Trường Tính ở bên phải Công thức.
Khi bạn nhấp vào fx, hộp thoại Chèn Hàm xuất hiện. Hộp thoại Chèn Hàm là cách dễ nhất để tìm một hàm DAX có liên quan đến công thức DAX của bạn.
Hộp thoại Chèn Hàm giúp bạn chọn các chức năng theo danh mục và cung cấp các mô tả ngắn cho từng chức năng.
Sử dụng Hàm Chèn trong Công thức DAX
Giả sử bạn muốn tạo trường được tính toán sau:
Medal Count: = COUNTA (]Medal])Bạn có thể sử dụng hộp thoại Chèn hàm theo các bước sau:
- Nhấp vào vùng tính toán của bảng Kết quả.
- Nhập nội dung sau vào thanh công thức:
Medal Count: =- Nhấp vào nút Chèn Hàm (fx).
Hộp thoại Chèn Hàm xuất hiện.
Chọn Thống kê trong hộp Chọn một danh mục như được hiển thị trong ảnh chụp màn hình sau.
Chọn COUNTA trong hộp Chọn chức năng như được hiển thị trong ảnh chụp màn hình sau.
Như bạn có thể quan sát, cú pháp hàm DAX đã chọn và mô tả hàm được hiển thị. Điều này cho phép bạn đảm bảo rằng đó là hàm mà bạn muốn chèn.
Bấm OK. Số lượng huy chương: = COUNTA (xuất hiện trong thanh công thức và chú giải công cụ hiển thị cú pháp hàm cũng xuất hiện.
Kiểu [. Điều này có nghĩa là bạn sắp nhập tên cột. Tên của tất cả các cột và các trường được tính toán trong bảng hiện tại sẽ được hiển thị trong danh sách thả xuống. Bạn có thể sử dụng IntelliSense để hoàn thành công thức.
Nhập M. Các tên được hiển thị trong danh sách thả xuống sẽ được giới hạn ở những tên bắt đầu bằng 'M'.
Nhấp vào Huy chương.
Bấm đúp vào Huy chương. Tổng số huy chương: = COUNTA ([Huy chương] sẽ được hiển thị trong thanh công thức. Đóng dấu ngoặc đơn.
Nhấn Enter. Bạn xong việc rồi. Bạn cũng có thể sử dụng quy trình tương tự để tạo một cột được tính toán. Bạn cũng có thể làm theo các bước tương tự để chèn một hàm trong hộp thoại Trường Tính toán trong cửa sổ Excel bằng cách sử dụng tính năng Chèn Hàm.
Bấm vào Hàm Chèn (fx) ở bên phải Công thức.
Hộp thoại Chèn Hàm xuất hiện. Các bước còn lại tương tự như trên.
Sử dụng nhiều hàm trong một công thức DAX
Công thức DAX có thể chứa tối đa 64 hàm lồng nhau. Tuy nhiên, không chắc rằng một công thức DAX chứa nhiều hàm lồng nhau như vậy.
Nếu một công thức DAX có nhiều hàm lồng nhau, nó có những nhược điểm sau:
- Công thức sẽ rất khó tạo.
- Nếu công thức có lỗi, sẽ rất khó gỡ lỗi.
- Việc đánh giá công thức sẽ không nhanh lắm.
Trong những trường hợp như vậy, bạn có thể chia công thức thành các công thức nhỏ hơn có thể quản lý được và xây dựng công thức lớn dần dần.
Tạo công thức DAX bằng cách sử dụng tổng hợp chuẩn
Khi bạn thực hiện phân tích dữ liệu, bạn sẽ thực hiện tính toán trên dữ liệu tổng hợp. Có một số hàm tổng hợp DAX, chẳng hạn như SUM, COUNT, MIN, MAX, DISTINCTCOUNT, v.v. mà bạn có thể sử dụng trong công thức DAX.
Bạn có thể tự động tạo công thức bằng cách sử dụng tổng hợp tiêu chuẩn bằng cách sử dụng tính năng Tự động tính tổng trong cửa sổ Power Pivot.
- Bấm vào tab Kết quả trong cửa sổ Power Pivot. Bảng kết quả sẽ được hiển thị.
- Nhấp vào cột Huy chương. Toàn bộ cột - Huy chương sẽ được chọn.
- Bấm vào tab Trang đầu trên Ruy-băng.
- Bấm vào mũi tên xuống bên cạnh Tự động cộng trong nhóm Tính toán.
- Nhấp vào COUNT trong danh sách thả xuống.
Như bạn có thể quan sát, trường được tính toán Số huy chương xuất hiện trong vùng tính toán bên dưới cột - Huy chương. Công thức DAX cũng xuất hiện trong thanh công thức -
Count of Medal: = COUNTA([Medal])Tính năng AutoSum đã thực hiện công việc cho bạn - tạo trường được tính toán để tổng hợp dữ liệu. Hơn nữa, AutoSum đã sử dụng biến thể thích hợp của hàm DAX COUNT, tức là COUNTA (DAX có các hàm COUNT, COUNTA, COUNTAX).
Lưu ý - Để sử dụng tính năng AutoSum, bạn cần nhấp vào mũi tên xuống bên cạnh AutoSum trên Ribbon. Nếu bạn nhấp vào AutoSum thay vào đó, bạn sẽ nhận được -
Sum of Medal: = SUM([Medal])Và một lỗi được gắn cờ là Huy chương không phải là cột dữ liệu số và văn bản trong cột không thể chuyển đổi thành số.
Bạn có thể tham khảo chương - DAX Error Reference để biết chi tiết về lỗi DAX.
Công thức DAX và Mô hình quan hệ
Như bạn đã biết, trong Mô hình Dữ liệu của Power Pivot, bạn có thể làm việc với nhiều bảng dữ liệu và kết nối các bảng bằng cách xác định mối quan hệ. Điều này sẽ cho phép bạn tạo các công thức DAX thú vị sử dụng mối tương quan của các cột giữa các bảng liên quan để tính toán.
Khi bạn tạo mối quan hệ giữa hai bảng, bạn phải đảm bảo rằng hai cột được sử dụng làm khóa có giá trị khớp với nhau, ít nhất là đối với hầu hết các hàng, nếu không hoàn toàn. Trong Mô hình Dữ liệu Power Pivot, có thể có các giá trị không khớp trong cột khóa và vẫn tạo mối quan hệ, vì Power Pivot không thực thi tính toàn vẹn của tham chiếu (xem phần tiếp theo để biết chi tiết). Tuy nhiên, sự hiện diện của các giá trị trống hoặc không khớp trong cột khóa có thể ảnh hưởng đến kết quả của công thức DAX và sự xuất hiện của PivotTable.
Tính toàn vẹn tham chiếu
Thiết lập tính toàn vẹn tham chiếu liên quan đến việc xây dựng một bộ quy tắc để bảo toàn các mối quan hệ đã xác định giữa các bảng khi bạn nhập hoặc xóa dữ liệu. Nếu bạn không hoàn toàn đảm bảo điều này, vì Power Pivot không thực thi nó, bạn có thể không nhận được kết quả chính xác với các công thức DAX được tạo trước khi thay đổi dữ liệu được thực hiện.
Nếu bạn thực thi tính toàn vẹn của tham chiếu, bạn có thể ngăn chặn các cạm bẫy sau:
Thêm hàng vào bảng có liên quan khi không có hàng được liên kết trong bảng chính (tức là với các giá trị khớp trong các cột chính).
Thay đổi dữ liệu trong bảng chính dẫn đến các hàng mồ côi trong một bảng có liên quan (tức là các hàng có giá trị dữ liệu trong cột khóa không có giá trị khớp trong cột khóa của bảng chính).
Xóa các hàng khỏi bảng chính khi có các giá trị dữ liệu phù hợp trong các hàng của bảng liên quan.