DAX公式语言：BI Tabular模型中的数据处理与分析

需积分: 10 9 浏览量更新于2024-07-16 收藏 2.2MB PDF 举报

"DAX in the BI Tabular Model" 是一份由SQL Server技术文章作者Howie Dickerman和Peter Myers撰写的官方白皮书，发表于2011年12月，适用于Power Pivot和SQL Server 2012 Analysis Services。该文档的核心内容聚焦于Data Analysis Expressions (DAX)，这是一种专为Excel工作簿中的Power Pivot和SQL Server Data Tools中创建的tabular模型项目设计的公式表达语言。 DAX的重要性在于它为数据分析师提供了一种强大的工具，用于定义计算、过滤、跨多张表格进行复杂分析，并利用内存优化引擎快速查询和处理大型数据集。在tabular BI模型中，DAX函数允许用户执行动态聚合，这意味着它们可以根据输入数据的变化实时调整计算结果。这种灵活性对于解决实际商业问题和满足组织的业务智能需求至关重要。本文涵盖了DAX的基础概念，包括但不限于：如何编写有效的公式，如何利用DAX处理关系数据，以及如何在高效内存环境中执行高性能计算。此外，白皮书中还可能讨论了DAX与其他BI工具的集成，以及最佳实践和性能优化策略。学习并掌握DAX对于那些希望利用Excel或SQL Server数据分析的强大功能的专业人士来说是至关重要的，因为它能帮助他们更深入地理解和分析数据，从而支持决策制定。通过理解DAX的内在机制和特性，读者可以提升自己的数据分析技能，解决复杂的商业挑战，并推动组织的数据驱动决策文化。阅读这份官方白皮书不仅能够增进对DAX语言的理解，还能帮助读者熟练运用DAX来解决实际工作中的问题，提升工作效率和业务洞察力。无论是初学者还是经验丰富的数据专业人士，这份资源都是一份宝贵的参考资料。

Data Analysis Expressions in the Tabular BI Semantic Model - 12 -

To illustrate this, consider the PivotTable shown below. There are two measures in this PivotTable that show

precisely the same results. One (named Sales) uses a DAX formula, (as illustrated above) while the other one

(named Sum of Sales Amount) does not.

Row Labels

Sales

Sum of SalesAmount

Catalog

$1,078,007,547.23

Online

$2,677,599,035.07

Reseller

$1,715,197,831.44

Store

$6,942,853,195.14

Grand Total

$12,413,657,608.89

The Measure Settings dialog window for the measure “Sum of SalesAmount” shows that this measure was

defined by simply checking the SalesAmount column in the field list and leaving the default aggregation as

“Sum”. This dialog window is slightly different than the dialog window that appears when a measure is

created using a DAX formula.

Data Analysis Expressions in the Tabular BI Semantic Model - 13 -

DAX Syntax

DAX syntax mimics Excel syntax as much as possible. In particular, DAX uses function composition just as

Excel does, and also refers to columns in tables using the same syntax as Excel. DAX then extends that syntax

to measures as well.

References to Columns and Measures

The name of a column is always unique within a given table, and the name of a measure must be unique

across the entire data model. Just as each column belongs to a particular table, each measure also belongs

to a particular table. We mimic the syntax used by Excel for columns. When a DAX expression refers to a

column or measure, the name of that column or measure must be appear within square brackets, and it will

sometimes be preceded by the name of the table to which the column or measure belongs. Here are some

examples:

Fully qualified names:

• Table1[Column2] as in =SUM(Table1[Column2])

• Table2[Measure1] as in = 2.5 * Table2[Measure1]

Unqualified names:

• [Column2] as in =[Column1] + [Column2]

• [Measure2] as in =[Measure2]/[Measure3]

DAX formulas that refer to columns generally require the references be fully qualified, but an exception is

made when a calculated column refers to other columns from within the same table. In these cases, the

column references need not be fully qualified. Since measure names are globally unique within a model, we

do not generally require measure names to be fully qualified.

Unlike Excel formulas, DAX has no notion of addressing individual cells or ranges. Notation such as B14 or

C12:C15 which are valid in Excel will not work in DAX expressions. Instead, DAX always refers to a column of

data by providing the (qualified or unqualified) column name. When there is a row context, this reference to

a column will be interpreted as the value of that column in the current row.

Note: It is possible to create a calculated column using the same name as a measure from a different table.

When this occurs, it is important to use a fully qualified reference to the column in any formula to avoid

invoking the measure when the intention was to invoke the calculated column.

Split Measures into Separate Measures

To make measures easier to define and maintain, when possible, it is recommended to split measures into

separate measures. So instead of writing a single measure that does SUM(FactSales[SalesAmount]) /

SUM(Table1[SalesQuantity]) it is recommended to create two measures, and use them in a new measure.

You can hide the intermediate measures so they’re not exposed to the end users. This will improve

readability, improve the ability to debug parts of the measure and can help when defining the measure. This

will become increasingly important when your measures become more complex.

Data Analysis Expressions in the Tabular BI Semantic Model - 14 -

DAX Uses Tabular Model Data Types

DAX assumes that all values in columns (and the inputs and outputs of all DAX formulas) are one of the

following seven data types:

Data Type

Description

Whole Number

Integer

Decimal Number

Double precision real number

Currency

Four decimal places of precision (integer divided by 10,000)

TRUE/FALSE

Boolean

Text

String

Date

Datetime values beginning with Mar 1, 1900

Binary

Binary data imported from the data source (typically for image

data)

This is slightly different from Excel, where everything is either a number (real) or a string. In Excel, data types

are handled by formatting numeric values rather than by using different data types. Note that date values in

DAX, like Excel, are restricted to dates after March 1, 1900.

DAX also has functions that return tables of data, but those tables may not be the final value in a measure or

calculated column. Tables are used only as intermediate results, and are passed as arguments into additional

functions, so that a DAX formula eventually returns a scalar value that has one of the six data types

mentioned above.

Intro to Context – Row Context and Filter Context

In DAX, one of the most important concepts to understand is the context in which each DAX expression is

evaluated. Context will be examined in more detail later in this document (after some DAX functions are

described, so that meaningful examples can be shown,) but for now, it is enough to be aware that each

formula may be evaluated in the context of a specific row of table data (a “row context”) and/or in the

context of a specific set of filters (a “filter context”). For example, the formula for a calculated column will be

evaluated for each row of a table, using the “row context”. Similarly, a measure that is placed into a

PivotTable will be evaluated for each cell in the values area, and each of those cells has its own “filter

context” which is the combination of the cell’s row labels, column labels, report filters, and slicers.

剩余68页未读，继续阅读

hzykelvin

粉丝: 1
资源: 5

DAX公式语言：BI Tabular模型中的数据处理与分析

opendax：开源云原生数字资产和加密货币交换平台

DAX权威指南(英文原版)

DAX语言的文档

Crsctl stat res -t

C++QSqlQueryModel QMLTableView

qabstracttablemodel

AsposeUtil.createCachedRowSet

hive explode

vue-virtual-scroller

from fastai.tabular.core import add_datepart new_data = df[['trade_date','close']].sort_index(ascending=True, axis=0) #create features add_datepart(new_data,'trade_date') new_data.drop('trade_Elapsed', axis=1, inplace=True) #elapsed will be the time stamp new_data.head()解释一下每行代码

最新资源