DAX 簡介Introduction to DAX

了解如何從知名專家建立和使用 DAX。Learn how to create and work with DAX, from an established expert.

您將學到如何...

DAX 簡介Introduction to DAX

歡迎使用專為您介紹 DAX 而設計的 Power BI 引導式學習一節。Welcome to the Power BI Guided Learning section designed to introduce you 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 和此引導式學習影片系列DAX and this Guided Learning video series

引導式學習一節的目標是教導您 DAX 的基本知識和基本概念,包括如何看待 DAX、其運作方式,以及由知名 DAX 專家 Alberto Ferrari 所說明 (與透過眾多經驗所學習) 的最實用功能。The goal of this Guided Learning section is to teach you DAX basics and fundamentals - how to think about DAX, how it works, and the most useful features as explained (and learned with lots of experience) by a renowned DAX expert, Alberto Ferrari.

Alberto Ferrari 的相片

引導式學習一節中有關 DAX 的影片,從 DAX 公式語言運作方式的觀點教導您 DAX 的基本知識。The videos in this Guided Learning section on DAX teaches you DAX basics from the perspective of how the DAX formula language works. 從頭建立 DAX 公式時,這相當實用,但同時非常適合您在查詢編輯器中建立查詢時了解 Power BI 如何建立該等 DAX 公式。This is useful when creating DAX formulas from scratch, but it's also very useful for understanding how Power BI creates those DAX formulas as you create queries in the Query Editor.

本影片內容 - 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 中,函數可以包含其他巢狀函數、條件陳述式以及值的參考。In DAX, functions can contain other, nested functions, conditional statements, and value references. DAX 中的執行始於最內層的函數或參數,並向外運作。Execution in DAX starts from the innermost function or parameter, and works outward. 在 Power BI 中,DAX 公式會以單一行撰寫,因此正確地將函數格式化對可讀性而言相當重要。In Power BI, DAX formulas are written in a single line, so formatting your functions correctly is important for readability.

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.

在 Power BI 中,尤有一項資料類型您可能會經常使用︰DateTimeThere is one data type in particular that you'll likely be working with a lot in Power BI: DateTime. DateTime 會以浮點值的方式儲存,包含整數與小數部分。DateTime is stored as a floating point value with both integer and decimal parts. DateTime 可用於精確地計算任何 1900 年 3 月 1 日之後的時間週期。DateTime can be used accurately for calculations of any time period after March 1, 1900.

影片內容感謝下列提供者的協助 Alberto Ferrari、SQLBIVideo content courtesy of Alberto Ferrari, SQLBI

DAX 計算類型DAX calculation types

您可以使用 DAX 來建立兩種主要的計算結果︰There are two primary calculations you can create using DAX:

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

在深入探討建立任何計算結果之前,最好能確實掌握資料表和資料行的 DAX 語法,您在建立計算結果欄導出量值時會使用此等語法。Before digging into creating either of those, it's good to have a firm grasp on DAX syntax for tables and columns, which you will use when creating either calculated columns or calculated measures.

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

無論您是建立新的資料行或量值,皆請務必了解 DAX 中資料表名稱的一般格式︰Whether you're creating a new column or measure, it's important to know the general format of table names in DAX:

'Table Name'[ColumnName]

如果資料表名稱中有空格 (如上所示),資料表名稱必須強制括上單引號。If there are spaces in the table name (as shown above), the single quotes around the table name are mandatory. 如果資料表名稱不包含任何空格,可以省略單引號,因此語法看起來如下所示︰If the table name has no spaces, the single quotes can be omitted, so the syntax looks like the following:

TableName[ColumnName]

下圖顯示正在 Power BI 中建立 DAX 公式︰The following image shows a DAX formula being created in Power BI:

您也可以完全省略資料表名稱,並只使用資料行名稱,但這對於撰寫清楚的函數 (以及清楚的 DAX 碼) 而言是不良做法。You can also omit the table name completely and just use the column name, but this is poor practice for writing clear functions (and thus, for clear DAX code). 資料行名稱必須一律包含方括弧。Column names must always include the square brackets.

最佳做法是一律執行下列動作︰It's best practice to always do the following:

  • 資料表名稱中不含空格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

當您想要分割或篩選值,或如果您想要計算您的資料表中的每個資料列時,計算結果欄很有用。Calculated columns are useful when you want to slice or filter on the value, or if you want a calculation for every row in your table.

您也可以透過選取 [模型] 索引標籤中的 [新增資料行],在 Power BI Desktop 中建立計算結果欄。最好位於 [資料] 檢視 (而非 [報表] 或 [關聯性]檢視),因為您可以看到新建立的資料行,且 [公式列] 已填入並可用於您的 DAX 公式。You can create calculated columns in Power BI Desktop by selecting New Column from the Modeling tab. It's best to be in Data view (rather than Report or Relationships view), since you can see the new column created and the Formula Bar is populated and ready for your DAX formula.

一旦您選取 [新的資料行] 按鈕,[公式列] 會填入基本資料行名稱 (當然,您會變更此名稱以符合您的公式) 與 = 運算子,而新的資料行會出現在資料格中,如下圖所示。Once you select the New Column button, the Formula Bar is populated with a basic column name (which you change to suit your formula, of course) and the = operator, and the new column appears in the data grid, as shown in the following image.

計算結果欄所需的項目如下所示︰The required elements for a calculated column are the following:

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

如果您在計算結果欄公式中參考資料表或資料行,您並不需要指定資料表中的資料列,Power BI 每次計算都會計算目前資料列的資料行。If you reference a table or column in your calculated column formula, you do not need to specify a row in the table - Power BI calculates the column for the current row for each calculation.

建立導出量值Creating calculated measures

當您在計算百分比或比例,或當您需要複雜彙總時,使用導出量值Use a calculated measure when you are calculating percentages or ratios, or you need complex aggregations. 使用 DAX 公式建立量值時,請選取 [模型] 索引標籤中的 [新增量值] 按鈕。同樣地,最好位於 Power BI Desktop 的 [資料] 檢視,因為其會顯示 [公式列],並可讓您更輕易地撰寫 DAX 公式。To create a measure using a DAX formula, select the New Measure button from the Modeling tab. Again, it's best to be in the Data view of Power BI Desktop since it shows the Formula Bar and makes it easy to write your DAX formula.

使用量值,您會看到帶有量值名稱的新量值圖示出現在 [欄位] 窗格中。With measures, you see a new measure icon appear in the Fields pane with the name of the measure. 公式列也會填入您的 DAX 公式名稱 (這次是您的量值)。The Formula Bar is again populated with the name of your DAX formula (this time, with your measure).

導出量值的必要項目與計算結果欄相同:The required elements for a calculated measure are the same as they are for a calculated column:

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

影片內容感謝下列提供者的協助 Alberto Ferrari、SQLBIVideo content courtesy of Alberto Ferrari, SQLBI

DAX 函數DAX functions

使用 DAX,有許多函數可用於形成、塑造,或分析您的資料。With DAX, there are many functions available to shape, form, or otherwise analyze your data. 這些函數可分為數個類別︰These functions can be grouped into a handful of categories:

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

類似於 Excel,當您開始將公式輸入 Power BI Desktop 公式列時,可用的函數清單隨即出現,協助您決定您想要選取哪些可用的函數。Similar to Excel, when you start typing your formula into the Power BI Desktop Formula Bar, a list of available functions appears to help you determine which available function you want to select. 您還可透過使用鍵盤上的方向鍵,將任何可用的函數反白顯示,即會顯示一段簡短描述。And by using the up and down arrow keys on your keyboard, you can highlight any of the available functions, and a brief description is displayed.

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).

可以輕易以此方式試驗 DAX,並找出 Power BI 中可用的各種 DAX 函數。It's easy to experiment with DAX in this way, and to find each of the various DAX functions that are available in Power BI. 您只需要開始輸入,Power BI 就會協助您順利進行。All you have to do is start typing, and Power BI helps you along.

既然我們已經知道如何啟動該 DAX 公式,我們應該逐一看看這些函數類別。Now that we know how to get that DAX formula started, let's take a look at each of these function categories in turn.

彙總函數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)

這些函數只能用於數值資料行,且通常一次只能彙總一個資料行。These functions work only on numeric columns, and generally can aggregate only one column at a time.

不過,以 X 結尾的特殊彙總函數,例如 SUMX,可以處理多個資料行。However, special aggregation functions that end in X, such as SUMX, can work on multiple columns. 這些函數會逐一查看資料表,並評估每個資料列的運算式。These functions iterate through the table, and evaluate the expression for each row.

計算函數Counting functions

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

  • COUNTCOUNT
  • COUNTACOUNTA
  • COUNTBLANKCOUNTBLANK
  • COUNTROWSCOUNTROWS
  • DISTINCTCOUNTDISTINCTCOUNT

這些函數會計算不同的項目,例如相異值、非空白值及資料表的資料列。These functions count different elements, such as distinct values, non-empty values, and table rows.

邏輯函數Logical functions

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

  • ANDAND
  • OROR
  • NOTNOT
  • IFIF
  • IFERRORIFERROR

這些特殊函數也可以使用運算子表示。These special functions can also be expressed with operators. 例如,您 DAX 公式中的 AND 可以輸入為 (取代為) &&For example, AND can be typed as (replaced with) && in your DAX formula.

當您的公式需要兩個以上的條件時,您可以使用運算子 (例如 &&),否則,為了您的 DAX 碼的可讀性,使用函數本身的名稱是最佳做法 (例如 AND)。You can use operators (such as &&) when you need more than two conditions in your formula, but otherwise, it's best practice use the function name itself (such as AND) for readability of your DAX code.

資訊函數Information functions

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

  • ISBLANKISBLANK
  • ISNUMBERISNUMBER
  • ISTEXTISTEXT
  • ISNONTEXTISNONTEXT
  • ISERRORISERROR

雖然這些函數可能在特定方面很有用,但事先知道您資料行中的資料類型仍有其價值,而不是依賴這些函數來提供資料類型。While these functions can be situationally useful, there is value in knowing the data type of your columns ahead of time, rather than depending on these functions to provide the data type.

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

這些文字與相同名稱的 Excel 函數的作用非常雷同,因此如果您熟悉 Excel 如何處理文字函數,您已經超前一步。These text work very similarly to the Excel functions that have the same name, so if you're familiar with how Excel handles text functions, you're already a step ahead. 若否,您隨時都可以在 Power BI 中試驗這些函數,並深入了解其行為。If not, you can always experiment with these functions in Power BI, and learn more about how they behave.

日期函數Date functions

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

  • DATEDATE
  • HOURHOUR
  • NOWNOW
  • EOMONTHEOMONTH
  • WEEKDAYWEEKDAY

雖然這些函數用來計算和擷取日期值的資訊很實用,他們並不適用於使用日期資料表的時間智慧。While these functions are useful to calculate and extract information from date values, they do not apply to time intelligence, which uses a date table.

影片內容感謝下列提供者的協助 Alberto Ferrari、SQLBIVideo content courtesy of Alberto Ferrari, SQLBI

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

使用變數是 DAX 運算式中功能極為強大的一部分。Using variables are an extremely powerful part of a DAX expression.

您可以使用下列語法,在 DAX 運算式中的任何地方定義變數︰You can define a variable anywhere in a DAX expression, using the following syntax:

VARNAME = RETURNEDVALUE

變數可以是任何資料類型,包括整個資料表。Variables can be any data type, including entire tables.

請記住,每當您在 DAX 運算式中參考變數,Power BI 都必須根據您的定義重新計算其值。Keep in mind that each time you reference a variable in your DAX expression, Power BI must recalculate its value according to your definition. 基於這項理由,建議您避免在函數中重複使用變數。For this reason, it's good practice to avoid repeating variables in your function.

影片內容感謝下列提供者的協助 Alberto Ferrari、SQLBIVideo content courtesy of Alberto Ferrari, SQLBI

資料表關聯性與 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.

您可以傳回資料行的值,或您可以使用 DAX 函數傳回具有關聯性的所有資料列。You can return the value of a column, or you can return all rows in a relationship using DAX functions.

例如,TABLE 函數會遵循關聯性,並傳回資料行的值,而 RELATEDTABLE 會遵循關聯性,並傳回經過篩選只包含關聯資料列的整個資料表。For example, the TABLE function follows relationships and returns the value of a column, while RELATEDTABLE follows relationships, and returns an entire table that is filtered to include only related rows.

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

您可以使用關聯式函數建立包含多個資料表值的運算式。You can use relational functions to build expressions that include values across multiple tables. 不論關聯性的鏈結長度為何,DAX 皆會以此等函數傳回結果。DAX will return a result with these functions, regardless of the length of the chain of the relationship.

影片內容感謝下列提供者的協助 Alberto Ferrari、SQLBIVideo content courtesy of Alberto Ferrari, SQLBI

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,您可以建立全新的計算資料表,然後將其視為任何其他資料表,包括建立其和您資料模型中其他資料表的關聯性。With DAX, you can create entirely new calculated tables and then treat them like any other table - including creating relationships between them and other tables in your data model.

DAX 資料表函數DAX table functions

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

  • FILTERFILTER
  • ALLALL
  • VALUESVALUES
  • DISTINCTDISTINCT
  • RELATEDTABLERELATEDTABLE

這些函數傳回完整資料表,而不是值。These functions return a full table rather, rather than a value. 通常您會將資料表函數的結果用在更高的運算式中進行進一步分析,而非將傳回的資料表當作終值。Typically you'll use the results of a table function in further analysis as part of a greater expression, rather than using that returned table a final value. 請特別注意,當您使用資料表函數時,結果會繼承其資料行之間的關聯性。It's important to note that When you use a table function, the results inherit the relationships of their columns.

只要每一個函數都使用資料表並傳回資料表,您便可以在運算式中混合資料表函數。You can mix table functions in your expression, as long as each function uses a table and returns a table. 例如,以下列 DAX 運算式為例︰For example, consider the following DAX expression:

FILTER (ALL (Table), Condition)

運算式會將篩選套用至整個 資料表 ,並忽略任何目前篩選內容。That expression would put a filter over the entirety of Table, ignoring any current filter content.

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

其中一項 Power BI 報表產生器要回答的常見問題如下︰One common question that Power BI report builders want to answer is the following:

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

當資料表顯示在面前時這可能是個簡單的問題,但 DAX 以不同的方式處理,特別是當資料表之間有關連性時。That may be a simple question to answer with a table displayed in front of you, but DAX approaches in a different way in a different way, particularly when there's a relationship between tables.

例如,Power BI 和 DAX 會包含未正確相互檢索的值。For example, Power BI and DAX includes values that are not properly cross-indexed. 如果內送關聯性已損毀,DAX 會將新的資料列新增至每個欄位皆為空白的相關資料表中,並將該新資料列連結至未編製索引的資料列,以確保參考完整性。If the incoming relationship is broken, DAX adds a new row to the related table that has blanks in every field, and links that new row to the unindexed row to guarantee referential integrity. 如果您的函數包含空白資料列,例如,通常是在使用 ALL 時,該等空白資料列就會包括在為該資料行傳回的值數目中。If your function includes blank rows, such as is often the case when using ALL, those blank rows will then be included in the number of values returned for that column.

您也可以使用 DAX 函數建立整個計算資料表。You can also create entire calculated tables using DAX functions. 使用 DAX 建立的計算資料表都需要 NAMETABLE 函數。Calculated tables created using DAX require a NAME and a TABLE function. 計算資料表可像任何其他資料表般使用,包括建立關聯性。Calculated tables can be used like any other table, including establishing relationships.

影片內容感謝下列提供者的協助 Alberto Ferrari、SQLBIVideo content courtesy of Alberto Ferrari, SQLBI

恭喜!

您已完成 Power BI 引導式學習的 DAX 簡介一節。You've completed the Introduction to DAX section of Power BI Guided Learning. 美妙之旅!What a journey! 如果您已依序完成這些教學課程,就已完成引導式學習的所有小節。If you've done these tutorials in sequence, then you've finished all the sections of Guided Learning. 做得很棒!Way to go! 現在請使用 Power BI 來建立吸引人的報告和內容豐富的儀表板。Now go use Power BI to create compelling reports and dashboards that tell your story.

您已學到如何...

參與者

  • Davidiseminger
  • olprod
  • Alisha-Acharya