SQL Server 2016 Analysis Services (SSAS)*, Power Pivot in Excel 2016, and Power BI Desktop include the following new Data Analysis Expressions (DAX) functions:

Date and Time Functions

CALENDAR Function
CALENDARAUTO Function
DATEDIFF Function

Information Functions

ISEMPTY Function
ISONORAFTER Function

Filter Functions

ADDMISSINGITEMS Function
SUBSTITUTEWITHINDEX Function

Math and Trig Functions

ACOS Function
ACOSH Function
ASIN Function
ASINH Function
ATAN Function
ATANH Function
COMBIN Function
COMBINA Function
COS Function
COSH Function
DEGREES Function
EVEN Function
EXP Function
GCD Function
ISO.CEILING Function
LCM Function
MROUND Function
ODD Function
PI Function
PRODUCT Function
PRODUCTX Function
QUOTIENT Function
RADIANS Function
SIN Function
SINH Function
SQRTPI Function
TAN Function
TANH Function

Statistical Functions

BETA.DIST Function
BETA.INV Function
CHISQ.INV Function
CHISQ.INV.RT Function
CONFIDENCE.NORM Function
CONFIDENCE.T Function
EXPON.DIST Function
GEOMEAN Function
GEOMEANX Function
MEDIAN Function
MEDIANX Function
PERCENTILE.EXC Function
PERCENTILE.INC Function
PERCENTILEX.EXC Function
PERCENTILEX.INC Function
SELECTCOLUMNS Function
XIRR Function
XNPV Function

Text Functions

CONCATENATEX Function

Other Functions

GROUPBY Function
INTERSECT Function
NATURALINNERJOIN Function
NATURALLEFTOUTERJOIN Function
SUMMARIZECOLUMNS Function
UNION Function
VAR

A key to understanding DAX is to distinguish Filter Context and Row Context.

Here are definitions of each context.

Filter Context: can be simply defines as filter context the set of filters applied to the evaluation of a DAX expression, usually a measure, regardless of how they have been generated. Usually, every cell of a report has a different filter context, which can be defined implicitly by the user interface (such as the pivot table in Excel), or explicitly by some DAX expression using CALCULATE or CALCULATETABLE. Filter Context always exist before a DAX expression been evaluated.

 

Row Context: the concept of row context is always exist in a DAX expression. In order to get the value of a DAX expression, you need a way to tell DAX the row to use AKA checking the current row of a table and provide the value back. You have a row context whenever you iterate a table, either explicitly (using an iterator) or implicitly (in a calculated column).

 

Use one DAX measure as an Example:

SumofProductSalesAmount:=SUMX(

VALUES([Product].[ProductName]),   

CALCULATE (

        [Sales Amount]

    )

)

 

Explain in detail of this calculation:

1. VALUES Function returns a table, meaning it provide All the Distinctive ProductName back as a table outcome
2. SUMX iterate each row of the table in its first argument, then it aggregate the value each row returns in sum. In this case, for each row of a table which provided by VALUES function AKA for each product, it calculate the value in the second argument of the SUMX function. Then, Sum all the value together and return back to the caller.
3. Things happens outside CALCULATE Function are all treated as Filter Context of the CALCULATE expression. In this case, there are two places feeding the CACULATE section with Filter Context.
   • First place is, the Current Row defined by SUMX, in our case, it is the Current Row of VALUES([Product].[ProductName]) which will be a specific Product depends on where the iterator at. Meaning Evaluated [Sales Amount] Measure with a filter which limit to a certain Product, and then provide the value (sales amount) back.
   • Second Place is, whenever this measure [SumofProductSalesAmount] exposed in a cell user application (like Excel, PowerBI, SSRS), it exposed in a filter context set on that specific Cell. Depends on wherever your cell is at you have different filter context. For example you may have a filter on a cell to limit the time frame to 2017, etc.

 

Thanks,

Your friend – Annie.

— Single axis

select [Measures].[Sales Amount] on Columns

from [InternetSales];

— Double axis

Select [Measures].[Sales Amount] on Columns,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on rows

from [InternetSales];

–two aggregation wrong no supported

Select [Measures].[Sales Amount] on 0,

[Dim Employee].[Reports To].[Level 02].ALLMEMBERS on 1,

[Ship Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 2

from [InternetSales];

–two aggregation right

Select [Measures].[Sales Amount] on 0,

[Dim Sales Territory].[Sales Territory Country].[Sales Territory Country]*[Ship Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales];

–Filter out emplty

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].ALLMEMBERS) on 1

from [InternetSales];

–“ALLMEMBER”

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].ALLMEMBERS) on 1

from [InternetSales];

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].&[Canada]) on 1

from [InternetSales];

–using tuples

Select NON EMPTY [Measures].[Sales Amount] on Columns,

NON EMPTY [Order Date].[Calendar Year].[Calendar Year] on rows

from [InternetSales];

Select NON EMPTY ( [Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year]) on columns

FROM [InternetSales];

–creating a tuple set

Select NON EMPTY {([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2006]),

([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2007]),

([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2008])

} on columns

FROM [InternetSales];

–Multiple Tuple Sets

Select [Measures].[Sales Amount] on Columns,

NONEMPTY(

([Dim Product].[Model Name].Allmembers, {[Order Date].[Calendar Year].[Calendar Year].&[2007]

,[Order Date].[Calendar Year].[Calendar Year].&[2008]}),[Measures].[Sales Amount]

) on rows

FROM [InternetSales];

–Functions

–TOP Percent

Select [Measures].[Sales Amount] on COLUMNS,

TopPercent([Dim Product].[Model Name].[Model Name],50,[Measures].[Sales Amount]) on ROWS

from [InternetSales];

–Top Sum (running total of category aggregattion exceed a certain amount)

Select [Measures].[Sales Amount] on COLUMNS,

TopSum([Dim Product].[Model Name].[Model Name],150000,[Measures].[Sales Amount]) on ROWS

from [InternetSales];

–use set functions

–Members and Allmembers (to see or not to see calculated members)

Select [Measures].[Sales Amount] on COLUMNS,

[Dim Product].[Model Name].[Model Name].Members on ROWS

from [InternetSales];

–non emplty

Select [Measures].[Sales Amount] on COLUMNS,

Non empty [Dim Product].[Model Name].[Model Name].Members on ROWS

from [InternetSales];

–nonemplty function against tuple

Select [Measures].[Sales Amount] on COLUMNS,

Nonempty( [Dim Product].[Model Name].[Model Name].Members, [Measures].[Sales Amount]

) on ROWS

from [InternetSales];

–Top/Bottom Count

Select [Measures].[Sales Amount] on COLUMNS,

TopCount( [Dim Product].[Model Name].[Model Name].Members, 5,[Measures].[Sales Amount]

) on ROWS

from [InternetSales];

Select [Measures].[Sales Amount] on COLUMNS,

BottomCount([Dim Product].[Model Name].[Model Name].Members, 31,[Measures].[Sales Amount]

) on ROWS

from [InternetSales];

— Specifying Axis

Select [Measures].[Sales Amount] on Columns,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on rows

from [InternetSales];

–Not supported in SSMS

Select [Measures].[Sales Amount] on 0,

[Measures].[Discount Amount] on 1,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 2

from [InternetSales];

–use tuples instead has to start at 0

Select non empty {[Measures].[Sales Amount],[Measures].[Tax Amt]} on 0,

non empty [Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales];

–use Having clause

Select non empty {[Measures].[Sales Amount],[Measures].[Tax Amt]} on 0,

non empty [Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS

Having [Measures].[Sales Amount] >=8000000 on 1

from [InternetSales];

–use slicers

Select [Measures].[Sales Amount – Fact Reseller Sales] on 0,

non empty [Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales]

where [Dim Sales Territory].[Sales Territory Country].&[United States];

–AND/OR not supported, use Tuples

Select [Measures].[Sales Amount – Fact Reseller Sales] on 0,

non empty [Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales]

where [Dim Sales Territory].[Sales Territory Country].&[United States]

AND [Order Date].[Calendar Year].&[2007];

–instead, from different hiarchy

Select [Measures].[Sales Amount] on 0,

non empty [Due Date].[Calendar Year].Members on 1

from [InternetSales]

where ([Dim Sales Territory].[Sales Territory Country].&[United States]

,[Order Date].[Calendar Year].&[2007]);

–instead, from same hiarchy

Select [Measures].[Sales Amount] on 0,

non empty [Due Date].[Calendar Year].Members on 1

from [InternetSales]

where ({[Dim Sales Territory].[Sales Territory Country].&[United States]

,[Dim Sales Territory].[Sales Territory Country].&[Canada]})

–Scope MDS Statement (create sub-cube)

Step 1: reconnect to server use “options”->”Additional Connection Parameters”

and set statement as Cube=”InternetSales”

Step 2: Run Scope Function one step a time

scope ([Measures].[Sales Amount],[Dim Sales Territory].[Sales Territory Country].&[Canada]);

this = [Measures].[Sales Amount]*1.1;

end scope;

–case statements

With Member [Measures].[Sales Targets] as

Case

when [Measures].[Sales Amount]>3000000 then “Achived”

when [Measures].[Sales Amount]<2000000 then “Below Expected”

ELSE “On Track”

End

Select {[Measures].[Sales Amount],[Measures].[Sales Targets]} on Columns,

[Dim Sales Territory].[Sales Territory Country].[Sales Territory Country] on Rows

from [InternetSales]

 

 

 

1. IF EXISTS

When we create or delete or alter any objects in SQL server versions before 2016, we have do use syntax like this

IF OBJECT_ID(‘[dbo].[V_ABC’) IS NOT NULL

BEGIN

DROP VIEW [dbo].[V_ABC];

END;

GO

With SQL server 2016, the syntax is much easier. And this applies to table, view, function, store procedure, etc.

DROP VIEW IF EXISTS [dbo].[V_ABC];

2. Split

I found split function especially useful when you have a column with values with you need to split into different categories. The new function called STRING_SPLIT comes very handy especially work with Cross Apply

Let’s say you have a service requests table, which has a column called REPORTING_SEGMENT and the value storied in each row of the report segment column contains multiple values. The requirement is to get the request counts for each REPORTING_SEGMENT.

SELECT value AS reporting_seg,

COUNT([ID]) AS countofrequests

FROM [Ad_Hoc].[dbo].[Service_Requests]

CROSS APPLY string_split([REPORTING_SEGMENT], ‘,’)

GROUP BY value;

3. Temporal tables

I found temporal tables are super valuable for auditing, SCD, history tracking purposes. Essentially, the SQL server is doing the job in the back-end which usually you will need to configure by yourself using SSIS package or Store Procedures. Also the history table of those temporal table are automatically configured with column store index and is compressed which provide fast read and saved storage space.

4. In-memory tables

With memory price becoming inexpensive compare years ago, now with SQL server 2016 in-memory table capability, we can use in-memory table which much faster for read and write.

5. Column-store indexes

Very glad to see that Microsoft implemented what the technology used in tabular Analysis service to SQL server. Which make query which depend highly on analytics (like aggregation) much faster than if use row-store indexes.