Shulou(Shulou.com)06/03 Report--

Foreword:

This article is easy to understand for people with a little bit of SQL query basics, such as mastering SELECT,SUM,GROUP BY, etc.

Note: this article does not refer to the introduction of Filter Context (filtering context).

Text:

For people who have some knowledge of SQL, they are not used to looking at DAX. However, if you understand the following, DAX will be easier to learn.

SUMMARIZE

FILTER

CALCULATE and CALTULATETABLE

Note: I will not explain these syntax in detail here, but look at the equivalent similar statements of DAX from the perspective of SQL.

Let's take a look at an example.

Query all rows in the Products table:

Table = 'tblProduct'SELECT * FROM tblProduct [object Object] DAX Filter vs SQL Filter

DAX:

FILTER (Product, RELATED (Category [Product Category Name]) = "Bike")

SQL:

SELECT * FROM Product PJOIN Category c on P. [Category _ KEY] = c. [Category _ KEY] WHERE c. [Product Category Name] = 'Bikes'FILTER is a statement of MUST know

Count sales from the Transaction table-SUM-GRUOP BY:

DAX

-- display the number of sales per product SUMMARIZE ('Transaction','Transaction' [ProductId], "Total qty", SUM (' Transaction' [Quantity]))-- display the number of sales per product (Quantity) ProductId

SUMMERIZE

Select the column specified in a table:

Dax

SUMMARIZE (Product,Product [Name], Product [Size]) SELECT Name,SizeFROM tblProduct selects the first few rows: TOPN (5, Product,Product [FullPrice]) TOP3 = TOPN (3MARIZE ('order table', 'order table' [create_time], "Sale Amount", sum ('order table' [amount])), [Sale Amount]) / / returns the value of the table, you need to add the table select * from Product LIMIT 5

SUMMARIZE is an important statement:

Remember: this is very similar to SQL, it is much easier to understand as long as you think about SQL in the learning process.

SUMMARIZE (source table name, Group by column 1,..., Group by column N, summary column name 1, expression corresponding to summary column name 1, summary column name N, expression corresponding to summary column name N)

Let's take another example:

Statistics on product classification, color and product name: transaction volume, number of sales

The example of MSDN

DAX:

SUMMARIZE ('Internet Sales', ROLLUP (' Date' [Calendar Year], 'Product Category' [Product Category Name]), "Sales Amount", SUM (' Internet Sales' [Sales Amount]), "Discount Amount", SUM ('Internet Sales' [Discount Amount]) SELECT D. [Calendar Year], PC. [Product Category Name], SUM (F.[ sales Amount])' Sales Amount' SUM (F.[ discount Amount]) 'Discount Amount'FROM [Internet Sales] FJOIN DATE D ON S.[ day _ KEY] = F.[ day _ KEY] JOIN [Product Category] PC ON PC.[ Category _ KEY] = F.[ Category _ KEY] GROUP BY D. [Calendar Year], PC. [Product Category Name]

The equivalent SQL is as follows, isn't it easy to understand if you only look at the light blue part?

SUMMARIZE and other Option will not be described in detail here.

For more information on SUMMARIZE, please see https://msdn.microsoft.com/en-us/library/gg492171.aspx.

CALCULATETABLE vs Sub Query

The following statement counts the number of products sold in the category Bike.

SUMMARIZE (CALCULATETABLE ('Internet Sales',' Product Category' [Product Category Name] = "Bikes"),-- field to group by Product [Product Name], "Quantity sold", SUM ('Internet Sales' [Order Quantity])) SELECT P. [Product Name] SUM ([quantity]) as' Quantity sold'FROM (SELECT F. * FROM [Transaction] F JOIN Category c ON F.[ Category _ Key] = C.[ Category _ Key] WHERE C. [Product Category Name] = 'Bikes') Fact join Product P ON P. [Key] GROUP BY P. [Product Name] there are many ways to write the SQL statement above.

The Bikes category is filtered in the highlighted section of CalculateTable, just like SQL's sub Query.

Summary:

The DAX syntax is very flexible, and some of it looks obscure, but if you can cut into it from a SQL-based perspective, you will get twice the result with half the effort.

Also, Filter Context, which is not covered in this article, is an important concept, and a deep understanding of context is necessary if you are to really master DAX and some advanced usage.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.