# DAX 簡介Introduction to DAX

## DAX 簡介Introduction to DAX

DAX 代表資料分析運算式，且其是 Power BI 所使用的公式語言 (Power BI 也會在幕後加以使用)。DAX stands for Data Analysis Expressions, and it is the formula language used throughout Power BI (it is also used by Power BI behind the scenes). 在 Microsoft 的其他供應項目中也可以找到 DAX，例如 Power Pivot 和 SSAS Tabular，但本引導式學習的主題集合聚焦在 DAX 在 Power BI 中的使用方式，以及您可以如何加以使用。DAX is also found in other offerings from Microsoft, such as Power Pivot and SSAS Tabular, but this collection of Guided Learning topics focuses on how DAX is used - and can be used by you - in Power BI.

## 本影片內容 - DAX 簡介In this video - introduction to DAX

DAX 概念既簡單又直接，同時功能強大。DAX concepts are simple and straightforward, but DAX is powerful. DAX 使用獨特的程式設計概念和模式，要完整加以使用及理解有相當的難度。DAX uses some unique programming concepts and patterns which can make it difficult to fully use and understand. 傳統的語言學習方式可能不適用於 DAX，因此本影片的目標在於教導您有助於您稍後的 Power BI 工作的概念以及理論。Traditional ways of learning languages may not be the best approach to DAX, so the goal of this video is to teach you concepts and theory that will help later on in your Power BI work.

DAX 是 功能語言 ，這表示完整執行的程式碼包含在函數中。DAX is a functional language, which means the full executed code is contained inside a function.

DAX 設計用於資料表，因此僅有兩個主要資料類型：NumericOtherDAX is designed to work with tables, so it has just two primary data types: Numeric and Other. Numeric 可包含 整數小數 ，以及 貨幣Numeric can include integers, decimals, and currency. Other 可包含 字串二進位物件Other can include strings and binary objects. 這表示，如果您將 DAX 函數建置為使用一種類型的數字，您得以確保其能夠用於任何其他 Numeric 資料。This means that if you build your DAX function to work on one type of number, you can be assured that it will work on any other Numeric data.

DAX 會使用運算子多載，這表示您可以在計算中混合資料類型，而結果將會依據輸入中所使用的的資料類型變更。DAX uses operator overloading, which means that you can mix data types in your calculations and the results will change based on the type of data used in the inputs. 轉換會自動執行。Conversion happens automatically. 雖然這表示您不必知道您在 Power BI 中所使用的資料行資料類型，但這也表示轉換有時可能會以非預期的方式發生。This means you don't have to know the data types of the columns you're working with in Power BI, but it also means that sometimes conversion can happen in unexpected ways. 最佳做法是了解您所使用的資料，以確保運算子的表現符合預期。It's good practice to understand the data you are using to ensure that your operators are behaving as anticipated.

## DAX 計算類型DAX calculation types

• 計算結果欄calculated columns
• 導出量值calculated measures

## DAX 資料表和資料行名稱語法DAX table and column name syntax

``````'Table Name'[ColumnName]
``````

``````TableName[ColumnName]
``````

• 資料表名稱中不含空格No spaces in table names
• 一律將資料表名稱包含在公式中 (即使 DAX 允許您加以省略，也不要這麼做)Always include the table name in formulas (don't omit it, even though DAX lets you)

## 建立計算結果欄Creating calculated columns

• 新的資料行名稱a new column name
• 至少一個函數或運算式at least one function or expression

## 建立導出量值Creating calculated measures

• 新的量值名稱a new measure name
• 至少一個函數或運算式at least one function or expression

## DAX 函數DAX functions

• 彙總函數Aggregation functions
• 計算函數Counting functions
• 邏輯函數Logical functions
• 資訊函數Information functions
• 文字函數Text functions
• 日期函數Date functions

Power BI 會顯示符合您到目前為止輸入的字母的函數，因此如果您輸入 S ，只有 S 開頭的函數會出現在清單中。Power BI displays the functions that match the letters you've typed so far, so if you type S only functions that begin with S appear in the list. 如果您輸入 Su ，只有名稱中 包含 Su 字母順序的函數會出現在清單中 (函數不需要以 Su 開頭，其只需要包含該字母順序)。If you type Su, only functions that contain the letter sequence Su in their name appear in the list (they don't have to start with Su, they just have to contain that letter sequence).

## 彙總函數Aggregation functions

DAX 擁有若干彙總函數，包括下列常用函數︰DAX has a number of aggregation functions, including the following commonly used functions:

• SUMSUM
• AVERAGEAVERAGE
• MINMIN
• MAXMAX
• SUMX (和其他 X 函數)SUMX (and other X functions)

## 計算函數Counting functions

DAX 中常用的計算函數包括下列︰Often-used counting functions in DAX include the following:

• COUNTCOUNT
• COUNTACOUNTA
• COUNTBLANKCOUNTBLANK
• COUNTROWSCOUNTROWS
• DISTINCTCOUNTDISTINCTCOUNT

## 邏輯函數Logical functions

DAX 中的邏輯函數集合包括︰The collection of logical functions in DAX include:

• ANDAND
• OROR
• NOTNOT
• IFIF
• IFERRORIFERROR

## 資訊函數Information functions

DAX 中的資訊函數包括︰Information functions in DAX include:

• ISBLANKISBLANK
• ISNUMBERISNUMBER
• ISTEXTISTEXT
• ISNONTEXTISNONTEXT
• ISERRORISERROR

DAX 會使用 MAX 和 MIN 函數彙總並比較值。DAX uses the MAX and MIN functions to both aggregate values, and to compare values.

## 文字函數Text functions

DAX 中的文字函數包括下列︰The text functions in DAX include the following:

• CONCATENTATECONCATENTATE
• REPLACEREPLACE
• SEARCHSEARCH
• UPPERUPPER
• FIXEDFIXED

## 日期函數Date functions

DAX 包括下列日期函數︰DAX includes the following Date functions:

• DATEDATE
• HOURHOUR
• NOWNOW
• EOMONTHEOMONTH
• WEEKDAYWEEKDAY

## 在 DAX 運算式中使用變數Using variables in DAX expressions

``````VARNAME = RETURNEDVALUE
``````

## 資料表關聯性與 DAXTable relationships and DAX

Power BI 可讓您建立多個資料表之間的關聯性，包括資料來源完全不同的資料表之間的關聯性。Power BI lets you create relationships among multiple tables, including tables that come from completely different data sources. 您可以在 Power BI Desktop 的 [關聯性] 檢視中，查看任何資料模型的關聯性。You can see those relationships for any data model in the Relationships view of Power BI Desktop.

## DAX 關聯式函數DAX relational functions

DAX 具有關聯式函數，可讓您與已建立關聯性的資料表進行互動。DAX has relational functions that enable you to interact with tables that have established relationships.

RELATED 函數適用於 多對一 關聯性，而 RELATEDTABLE 適用於 一對多 關聯性。The RELATED function works on many-to-one relationships, while RELATEDTABLE is for one-to-many relationships.

## DAX 資料表和篩選DAX tables and filtering

DAX 及 Excel 公式語言之間的一個重大差異，是 DAX 可讓您在運算式之間傳遞 整個資料表 ，而不是限制在單一值。One significant difference between DAX and the Excel formula language is that DAX allows you to pass entire tables between expressions, rather than being constrained to a single value. 其中一個功能強大的作用是 DAX 可讓您在其運算式中篩選資料表，然後使用已篩選的一組值。One powerful effect is that DAX allows you to filter tables in its expressions, then work with the filtered set of values.

## DAX 資料表函數DAX table functions

DAX 擁有一組豐富的資料表函數，包括下列︰DAX has a rich set of table functions, including the following:

• FILTERFILTER
• ALLALL
• VALUESVALUES
• DISTINCTDISTINCT
• RELATEDTABLERELATEDTABLE

``````FILTER (ALL (Table), Condition)
``````

DISTINCT 函數會針對目前內容中亦可見的資料行，傳回該資料行的相異值。The DISTINCT function returns the distinct values of a column that are also visible in the current context. 因此若要使用上述的 DAX 運算式範例，在該運算式中使用 ALL 會忽略篩選，而以 DISTINCT 取代 ALL 則會加以觀察。So touse the above DAX expression example, using ALL in that expression ignores filters, while replacing ALL with DISTINCT would observe them.

## 使用 DAX 計算值Counting values with DAX

• 此資料行有多少值？How many values do I have for this column?