Kiến thức

28 phút đọc

Tổng Quan Về DAX Trong Power BI Phần 1

Data Analysis Expressions (DAX) là ngôn ngữ biểu thức công thức được sử dụng trong Analysis Services, Power BI và Power Pivot trong Excel. Các công thức DAX bao gồm các hàm, toán tử và giá trị. DAX dùng để thực hiện các phép tính và truy vấn nâng cao trên dữ liệu trong các cột và bảng có liên quan đến mô hình dữ liệu dạng bảng.

Bài viết này chỉ cung cấp phần giới thiệu cơ bản về các khái niệm quan trọng nhất trong DAX. Nó mô tả DAX vì nó áp dụng cho tất cả các sản phẩm sử dụng nó. Một số chức năng có thể không áp dụng cho một số sản phẩm hoặc trường hợp sử dụng. Tham khảo tài liệu về sản phẩm của bạn mô tả cách triển khai DAX cụ thể của nó.

1. Tính toán các công thức DAX trong Power BI

Các công thức DAX được dùng trong các thang đo, cột tính, bảng tính và bảo mật cấp độ hàng.

1.1. Thang đo được tạo bởi công thức DAX trong Power BI

Các thang đo là công thức tính toán linh động trong đó kết quả thay đổi tùy thuộc. Chúng được sử dụng trong báo cáo hỗ trợ kết hợp và lọc mô hình dữ liệu bằng cách sử dụng nhiều thuộc tính chẳng hạn như báo cáo Power BI hoặc Excel PivotTable hoặc PivotChart. Các thang đo được tạo bằng cách sử dụng thanh công thức DAX trong công cụ thiết kế mô hình.

Một công thức trong một thang đo có thể sử dụng các hàm tổng hợp tiêu chuẩn được tạo tự động bằng cách sử dụng tính năng Autosum, chẳng hạn như COUNT hoặc SUM hoặc bạn có thể xác định công thức của riêng mình bằng cách sử dụng thanh công thức DAX. Các thang đo được đặt tên có thể được thông qua như một đối số cho các thang đo khác.

Khi xác định công thức cho một thước đo trong thanh công thức, tính năng Tooltip sẽ hiển thị bản xem trước kết quả với tổng số trong ngữ cảnh hiện tại, nhưng nếu không thì kết quả sẽ không xuất ra ở bất kỳ đâu. Lý do bạn không thể thấy kết quả (đã lọc) của phép tính ngay lập tức là vì kết quả của một thước đo không thể được xác định nếu không có ngữ cảnh. Để đánh giá một thang đo cần có ứng dụng báo cáo khách có thể cung cấp ngữ cảnh cần thiết để truy xuất dữ liệu liên quan đến từng ô và sau đó đánh giá biểu thức cho từng ô. Máy khách đó có thể là Excel PivotTable hoặc PivotChart, báo cáo Power BI hoặc biểu thức bảng trong truy vấn DAX trong SQL Server Management Studio (SSMS).

Bất kể máy khách nào, một truy vấn riêng biệt sẽ được chạy cho từng ô trong kết quả. Điều đó có nghĩa là, mỗi tổ hợp tiêu đề hàng và cột trong PivotTable hoặc mỗi lựa chọn bộ cắt và bộ lọc trong báo cáo Power BI, sẽ tạo ra một tập hợp con dữ liệu khác nhau để tính toán thước đo. Ví dụ: sử dụng công thức đo lường rất đơn giản này:

Total Sales = SUM([Sales Amount])

Khi người dùng đặt thước đo Tổng doanh số trong một báo cáo, sau đó đặt cột Danh mục sản phẩm từ bảng Sản phẩm vào Bộ lọc, tổng của Số tiền bán hàng được tính toán và hiển thị cho từng danh mục sản phẩm.

Không giống như các cột được tính toán, cú pháp cho một thước đo bao gồm tên của thước đo. Trong ví dụ vừa được cung cấp, tên Tổng doanh thu xuất hiện trước công thức. Sau khi bạn đã tạo một thước đo, tên và định nghĩa của nó sẽ xuất hiện trong danh sách Trường ứng dụng báo cáo khách và tùy thuộc vào các quan điểm và vai trò có sẵn cho tất cả người dùng của mô hình.

1.2. Các cột được tính toán

Cột được tính toán là cột thêm vào bảng rồi tạo công thức DAX xác định giá trị của cột. Khi một cột được tính toán chứa công thức DAX hợp lệ, các giá trị được tính cho mỗi hàng ngay sau khi công thức được nhập. Các giá trị sau đó được lưu trữ trong mô hình dữ liệu trong bộ nhớ. Ví dụ: trong bảng Ngày, khi công thức được nhập vào thanh công thức:

= [Calendar Year] & ” Q” & [Calendar Quarter]

Giá trị cho mỗi hàng trong bảng được tính bằng cách lấy các giá trị từ cột Lịch năm (trong cùng bảng Ngày), thêm khoảng trắng và chữ in hoa Q, sau đó cộng các giá trị từ cột lịch quý (trong cùng bảng Ngày). Kết quả cho mỗi hàng trong cột được tính toán được tính ngay lập tức và xuất hiện, chẳng hạn như 2017 Q1 . Các giá trị cột chỉ được tính toán lại nếu bảng hoặc bất kỳ bảng liên quan nào được xử lý (làm mới) hoặc mô hình được dỡ tải khỏi bộ nhớ rồi tải lại, chẳng hạn như khi đóng và mở lại tệp Power BI Desktop.

1.3. Bảng tính

Bảng tính là một đối tượng được tính toán, dựa trên biểu thức công thức, được lấy từ tất cả hoặc một phần của các bảng khác trong cùng một mô hình. Thay vì truy vấn và tải các giá trị vào các cột của bảng mới của bạn từ một nguồn dữ liệu, công thức DAX xác định các giá trị của bảng.

Các bảng tính có thể hữu ích trong khía cạnh nhập vai. Một ví dụ là bảng Ngày, dưới dạng OrderDate, ShipDate hoặc DueDate, tùy thuộc vào mối quan hệ khóa ngoại. Bằng cách tạo một bảng tính cho ShipDate một cách rõ ràng, bạn sẽ có được một bảng độc lập có sẵn cho các truy vấn, có thể hoạt động đầy đủ như bất kỳ bảng nào khác. Các bảng tính cũng hữu ích khi định cấu hình tập hợp hàng đã lọc hoặc tập hợp con hoặc tập hợp siêu cột từ các bảng hiện có khác. Điều này cho phép bạn giữ nguyên bảng gốc trong khi tạo các biến thể của bảng đó để hỗ trợ các tình huống cụ thể.

Các bảng tính hỗ trợ mối quan hệ với các bảng khác. Cột trong bảng tính có kiểu dữ liệu, định dạng và có thể thuộc về một thể loại dữ liệu. Các bảng tính có thể được đặt tên và hiển thị hoặc ẩn giống như bất kỳ bảng nào khác. Các bảng tính được tính toán lại nếu nó lấy dữ liệu từ đó được làm mới hoặc cập nhật.

1.4. Bảo mật cấp hàng

Với bảo mật cấp hàng, công thức DAX phải đánh giá theo điều kiện Boolean TRUE/FALSE, xác định những hàng nào có thể được trả về bởi kết quả của một truy vấn bởi các thành viên của một vai trò cụ thể. Ví dụ: đối với các thành viên có vai trò Bán hàng, bảng Khách hàng có công thức DAX sau:

= Customers[Country] = “USA”

Các thành viên có vai trò Bán hàng sẽ chỉ có thể xem dữ liệu cho khách hàng ở Hoa Kỳ và các giá trị tổng hợp, chẳng hạn như SUM, chỉ được trả về cho khách hàng ở Hoa Kỳ. Bảo mật mức hàng không sẵn dùng trong Power Pivot trong Excel.

Khi xác định bảo mật cấp hàng bằng công thức DAX, bạn đang tạo một tập hợp hàng được phép. Điều này không từ chối quyền truy cập vào các hàng khác; đúng hơn, chúng chỉ đơn giản là không được trả lại như một phần của tập hợp hàng được phép. Các vai trò khác có thể cho phép truy cập vào các hàng bị công thức DAX loại trừ. Nếu người dùng là thành viên của một vai trò khác và bảo mật cấp hàng của vai trò đó cho phép truy cập vào nhóm hàng cụ thể đó, thì người dùng có thể xem dữ liệu cho hàng đó.

Công thức bảo mật cấp hàng áp dụng cho các hàng đã chỉ định cũng như các hàng liên quan. Khi một bảng có nhiều mối quan hệ, các bộ lọc sẽ áp dụng bảo mật cho mối quan hệ đang hoạt động. Các công thức bảo mật được giao với công thức khác được xác định cho các bảng có liên quan.

2. Truy vấn

Truy vấn:

Lệnh truy vấn DAX có thể được tạo và chạy trong SQL Server Management Studio (SSMS) và các công cụ nguồn mở như DAX Studio (daxstudio.org). Không giống như các công thức tính toán DAX, vốn chỉ có thể được tạo trong các mô hình dữ liệu dạng bảng, các truy vấn DAX cũng có thể được chạy đối với các mô hình đa chiều của Analysis Services. Các truy vấn DAX thường dễ viết hơn và hiệu quả hơn các truy vấn Multidimensional Data Expressions (MDX).

Truy vấn DAX là một câu lệnh, tương tự như câu lệnh SELECT trong T-SQL. Loại truy vấn DAX cơ bản nhất là một câu lệnh đánh giá. Ví dụ,

EVALUATE

 ( FILTER ( ‘DimProduct’, [SafetyStockLevel] < 200 ) )

ORDER BY [EnglishProductName] ASC

Trả về trong Kết quả một bảng chỉ liệt kê những sản phẩm có SafetyStockLevel < 200, tăng dần theo EnglishProductName.

Bạn có thể tạo các thang đo như một phần của truy vấn. Các thang đo chỉ tồn tại trong khoảng thời gian truy vấn.

3. Công thức DAX trong Power BI

Các công thức DAX rất cần thiết để tạo các phép tính trong các cột và thước đo được tính toán, đồng thời bảo mật dữ liệu của bạn bằng cách sử dụng bảo mật cấp hàng. Để tạo công thức cho các cột tính và thước đo, hãy sử dụng thanh công thức dọc theo đầu cửa sổ công cụ thiết kế mô hình hoặc DAX Editor. Để tạo công thức cho bảo mật cấp hàng, hãy sử dụng hộp thoại Role Manager hoặc Manage. Thông tin trong phần này nhằm giúp bạn bắt đầu hiểu những điều cơ bản về công thức DAX.

3.1. Khái niệm cơ bản về công thức:

Công thức DAX có thể rất đơn giản hoặc khá phức tạp. Bảng dưới cho thấy ví dụ về các công thức đơn giản được dùng trong một cột được tính toán.

Cho dù công thức bạn tạo đơn giản hay phức tạp, bạn có thể sử dụng các bước sau khi xây dựng công thức:

1. Mỗi công thức phải bắt đầu bằng dấu bằng (=).

2. Bạn có thể nhập hoặc chọn tên hàm hoặc nhập biểu thức.

3. Bắt đầu nhập một vài chữ cái đầu tiên của hàm hoặc tên bạn muốn và AutoComplete sẽ hiển thị danh sách các hàm, bảng và cột có sẵn. Nhấn TAB để thêm một mục từ danh sách AutoComplete vào công thức.

Bạn cũng có thể nhấp vào nút Fx để hiển thị danh sách các chức năng khả dụng. Để chọn một hàm từ danh sách thả xuống, hãy sử dụng các phím mũi tên để tô sáng mục đó và bấm OK để thêm hàm vào công thức.

4. 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ể hoặc bằng cách nhập giá trị.

5. Kiểm tra lỗi cú pháp: đảm bảo rằng tất cả các dấu ngoặc đơn đều được đóng và các cột, bảng và giá trị được tham chiếu chính xác.

6. Nhấn ENTER để chấp nhận công thức.

Trong ví dụ này, hãy xem xét một công thức trong thước đo có tên Days in Current Quarter :

Days in Current Quarter = COUNTROWS( DATESBETWEEN( ‘Date'[Date], STARTOFQUARTER( LASTDATE(‘Date'[Date])), ENDOFQUARTER(‘Date'[Date])))

Thước đo này dùng để tạo tỷ lệ so sánh giữa kỳ chưa hoàn thành với kỳ trước. Công thức phải tính đến tỷ lệ của khoảng thời gian đã trôi qua và so sánh nó với tỷ lệ tương tự trong khoảng thời gian trước đó. Trong trường hợp này, [Days Current Quarter to Date]/[Days in Current Quarter] đưa ra tỷ lệ đã trôi qua trong giai đoạn hiện tại.

Công thức này chứa các yếu tố sau:

3.2. Sử dụng công thức AutoComplete

AutoComplete giúp nhập cú pháp hợp lệ bằng cách cung cấp các tùy chọn từng thành phần trong công thức.

• Bạn có thể sử dụng tính năng AutoComplete ở giữa một công thức hiện có với các hàm lồng nhau. Chữ 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ả chữ sau điểm chèn không thay đổi.

• AutoComplete không thêm dấu ngoặc đóng của hàm hoặc tự động khớp với dấu ngoặc đơn. Bạn phải đảm bảo rằng mỗi hàm đều đúng về mặt cú pháp, nếu không bạn không thể lưu hoặc sử dụng công thức.

3.3. Sử dụng nhiều hàm trong một công thức

Bạn có thể lồng các hàm, sử dụng kết quả từ một hàm làm đối số của một hàm khác. Bạn có thể lồng tối đa 64 cấp hàm trong các cột tính. Tuy nhiên, việc lồng có thể gây khó khăn cho việc tạo hoặc khắc phục sự cố công thức. Nhiều chức năng được thiết kế để chỉ được sử dụng như các chức năng lồng công thức. Các hàm này trả về một bảng, kết quả là không thể lưu trực tiếp; nó phải được cung cấp làm đầu vào cho chức năng bảng. Ví dụ: các hàm SUMX, AVERAGEX và MINX đều yêu cầu một bảng làm đối số đầu tiên.

Xem thêm: Làm Việc Với Dartaa View Trong Power BI Desktop

4. Chức năng

Một hàm là một công thức được đặt tên trong một biểu thức. Hầu hết các hàm đều có đối số bắt buộc và tùy chọn hay tham số làm đầu vào. Khi hàm được thực thi, một giá trị được trả về. DAX bao gồm các hàm bạn có thể sử dụng để thực hiện phép tính sử dụng ngày và giờ, tạo giá trị có điều kiện, làm việc với chuỗi, thực hiện tra cứu dựa trên mối quan hệ và khả năng lặp qua bảng để thực hiện phép tính đệ quy. Nếu bạn đã quen thuộc với các công thức Excel, nhiều hàm trong số này sẽ xuất hiện rất giống nhau; tuy nhiên, các công thức DAX khác nhau theo những cách quan trọng sau:

• Hàm DAX luôn tham chiếu đến một cột hoặc bảng hoàn chỉnh. Nếu bạn chỉ muốn sử dụng các giá trị cụ thể từ một bảng hoặc cột, bạn có thể thêm các bộ lọc vào công thức.

• Nếu bạn cần tùy chỉnh phép tính trên cơ sở từng hàng, DAX 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ị liên quan dưới dạng một loại tham số, để thực hiện các phép tính thay đổi theo ngữ cảnh. Để hiểu cách hoạt động của các hàm này, hãy xem Ngữ cảnh trong bài viết này.

• DAX bao gồm nhiều hàm trả về bảng chứ không phải giá trị. Bảng không được hiển thị trong ứng dụng báo cáo khách, nhưng được sử dụng để cung cấp thông tin đầu vào cho các chức năng khác. Ví dụ: bạn có thể truy xuất một bảng rồi đếm các giá trị riêng biệt trong đó hoặc tính tổng linh động trên các bảng hoặc cột đã lọc.

• Hàm DAX bao gồm nhiều hàm thông minh về thời gian . Các hàm này cho phép bạn xác định hoặc chọn phạm vi ngày và thực hiện các phép tính linh động dựa trên những ngày hoặc phạm vi này. Ví dụ: bạn có thể so sánh tổng trong các khoảng thời gian song song.

4.1. Chức năng tổng hợp

Các hàm tổng hợp tính toán một giá trị (vô hướng) chẳng hạn như số lượng, tổng, trung bình, tối thiểu hoặc tối đa cho tất cả các hàng trong một cột hoặc bảng như được xác định bởi biểu thức.

4.2. Chức năng ngày và giờ

Hàm ngày và giờ trong DAX tương tự như hàm ngày và giờ trong Microsoft Excel. Tuy nhiên, các hàm DAX dựa trên kiểu dữ liệu ngày giờ bắt đầu từ ngày 1/ 3/1900.

4.3. Chức năng lọc

Các hàm lọc trong DAX trả về các loại dữ liệu cụ thể, tra cứu các giá trị trong các câu chuyện liên quan và lọc theo các giá trị liên quan. Hàm tra cứu hoạt động bằng cách sử dụng bảng và mối quan hệ, giống như cơ sở dữ liệu. Các hàm lọc cho phép bạn thao tác với ngữ cảnh dữ liệu để tạo các phép tính linh động.

4.4. Chức năng tài chính

Các hàm tài chính trong DAX được sử dụng trong các công thức thực hiện các phép tính tài chính, chẳng hạn như giá trị hiện tại ròng và tỷ lệ hoàn vốn. Các hàm này tương tự như các hàm tài chính được sử dụng trong Microsoft Excel.

4.5. Chức năng thông tin

Hàm thông tin xem xét ô hoặc hàng được cung cấp dưới dạng đối số và cho bạn biết liệu giá trị có khớp với loại dự kiến hay không. Ví dụ: hàm ISERROR trả về TRUE nếu giá trị mà bạn tham chiếu có lỗi.

4.6. Hàm logic

Các hàm logic tác động lên một biểu thức để trả về thông tin về các giá trị trong biểu thức. Ví dụ: hàm TRUE cho phép bạn biết liệu một biểu thức mà bạn đang đánh giá có trả về giá trị TRUE hay không.

4.7. Các hàm toán học và lượng giác

Các hàm toán học trong DAX rất giống với các hàm toán học và lượng giác trong Excel. Một số khác biệt nhỏ tồn tại trong các kiểu dữ liệu số được sử dụng bởi các hàm DAX. 

4.8. Các chức năng khác

Các chức năng này thực hiện các hành động duy nhất không thể được xác định bởi bất kỳ danh mục nào mà hầu hết các chức năng khác thuộc về.

4.9. Hàm quan hệ

Các hàm mối quan hệ trong DAX cho phép bạn trả về các giá trị từ một bảng có liên quan khác, chỉ định một mối quan hệ cụ thể để sử dụng trong một biểu thức và chỉ định hướng lọc chéo.

4.10. Hàm thống kê

Các hàm thống kê tính toán các giá trị liên quan đến phân phối thống kê và xác suất, chẳng hạn như độ lệch chuẩn và số lượng hoán vị.

4.11. Chức năng văn bản

Các hàm văn bản trong DAX rất giống với các đối tác của chúng trong Excel. Bạn có thể trả về một phần của chuỗi, tìm kiếm văn bản trong chuỗi hoặc nối các giá trị chuỗi. DAX cũng cung cấp các hàm để kiểm soát định dạng ngày, giờ và số.

4.12. Chức năng thông minh thời gian

Các hàm thông minh về thời gian được cung cấp trong DAX cho phép bạn tạo các phép tính sử dụng kiến ​​thức tích hợp sẵn về lịch và ngày. Bằng cách sử dụng phạm vi thời gian và ngày kết hợp với tổng hợp hoặc tính toán, bạn có thể xây dựng các phép so sánh có ý nghĩa trong các khoảng thời gian có thể so sánh đối với bán hàng, hàng tồn kho, v.v.

4.13. Hàm thao tác trên bảng

Các hàm này trả về một bảng hoặc thao tác với các bảng hiện có. Ví dụ: bằng cách sử dụng ADDCOLUMNS, bạn có thể thêm các cột được tính toán vào một bảng đã chỉ định hoặc bạn có thể trả về một bảng tóm tắt trên một tập hợp các nhóm bằng hàm SUMMARIZECOLUMNS.

Bạn có thể tạo các biến trong một biểu thức bằng cách sử dụng VAR . VAR về mặt kỹ thuật không phải là một hàm, nó là một từ khóa để lưu trữ kết quả của một biểu thức dưới dạng một biến được đặt tên. Sau đó, biến đó có thể được truyền dưới dạng đối số cho các biểu thức đo lường khác. Ví dụ:

VAR

    TotalQty = SUM ( Sales[Quantity] )

Return

    IF (

        TotalQty > 1000, TotalQty * 0.95, TotalQty * 1.25

        )

Trong ví dụ này, TotalQty có thể được chuyển dưới dạng biến được đặt tên cho các biểu thức khác. Các biến có thể thuộc bất kỳ loại dữ liệu vô hướng nào, kể cả bảng. Việc sử dụng các biến trong công thức DAX của bạn có thể cực kỳ hiệu quả.

Xem thông tin khai giảng Khóa học Business Intelligence mới nhất tại Mastering Data Analytics. Liên hệ 0961 48 48 66 hoặc inbox Fanpage Mastering Data Analytics để đăng ký nhanh nhất nhé!

Chia sẻ bài viết