Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SSAS DAX Query Examples

SSAS Basic Data Analysis Expression Queries

This article displays several basic DAX queries one might use on a Tabular Database instance created in Visual Studio as an Analysis Services Tabular Project Model from the AdventureWorksDW2012 database. Tables. After building and deploying the database to the server, you can go to SQL Server Management Studio and run DAX queries by opening and MDX Query window. These are a few basic "starter" queries on a Tabular database showing how to query the data and show the syntax of DAX.

Tabular Database Installation

Install a Tabular SQL Server instance first and then open Visual Studio to create the Model which will be deployed to the Tabular Server as a Tabular Database. The Tabular Database created will be used in the queries of this article. This website has great details of how to start an Analysis Services Tabular Project and set up a Model to be deployed on a Tabular SQL Server instance: Creating your First SSAS Tabular Model Database.

When the model is complete, the tabs in Visual Studio look like worksheets in Excel with tabs at the bottom. There is also an option to change the view from Grid to Diagram in the lower right corner of the screen.

SSAS Tabular Project in Visual Studio 2015

After deploying the Model as a new Tabular Database, you can open an SSAS Connection through SQL Server Management Studio to view the Tables and open a new MDX query window to see the Model metadata.

SSAS Tabular Database and MDX Query screen

DAX Evaluate Statement

The simple statement of Evaluate ( <table name> ) appears to be similar to a SELECT statement in T-SQL.

 evaluate (
   'DimProduct')
 order by 'DimProduct'[ProductKey]

Using ROW with the evaluate statement on aggregate functions is similar to the GROUP BY clause in T-SQL.

 evaluate (
   Row(
    "Product List", average('DimProduct'[ListPrice]),
    "Minimum Value", Min('dimProduct'[ProductKey]),
    "Maximum Value", Max('dimProduct'[ProductKey])
  )
)

DAX Evaluate Row Statement

DAX Filter Statements

Filter allows the user to display results based on criteria like a WHERE clause in T-SQL. This filter statement uses a filter of FinishedGoodsFlag equals True.

 evaluate
  Filter( 'DimProduct',
   'DimProduct'[FinishedGoodsFlag]=True
 )
 order by 'DimProduct'[ProductKey]

DAX Filter Statement Results

The DAX Statement can also use OR (||), AND (&&), IF, SWITCH, etc. This example uses OR in the Filter clause to select Product Colors of either Black or Red.

--filter with color red or black
evaluate
Filter( 'DimProduct',
'DimProduct'[Color]="Black" || 'DimProduct'[Color]="Red"
)
order by 'DimProduct'[ProductKey]

DAX Statement with OR operator in Filter

This DAX filter statement uses the Blank() function to select Products where the Subcategory Key is not Blank.

 evaluate
  filter
  (
   'DimProduct',
   'DimProduct'[ProductSubcategoryKey]<>Blank()
  )

DAX Filter using Blank()

DAX Summarize Statement

The statement below uses a Summarize Statement (following the Evaluate statement) to calculate the sum of Internet Sales Amounts on Products by Year. Notice that the first row listed for each Year \ Product that has a Blank Year is actually the Total for the Years listed below it.

 evaluate
 (
  summarize
  (
   'FactInternetSales',
   'DimProduct'[ProductName],
   ROLLUP('DimDate'[CalendarYear]),
   "Total Sales", sum('FactInternetSales'[SalesAmount])
  )
 )
order by 'DimProduct'[ProductName],'DimDate'[CalendarYear]

DAX Summarize Statement with Rollup

This Summarize statement uses the SUMX function to limit the Sum of Sales Amounts to only those Products with the Category Name of Bikes.

 evaluate
  (
  summarize
   (
    'FactInternetSales',
    'DimProduct'[ProductName],
    'DimProductCategory'[CategoryName],
    'DimDate'[CalendarYear],
    "Total Sales", sumx(Filter('FactInternetSales', 'DimProductCategory'[CategoryName]="Bikes"),[SalesAmount])
   )
  )
order by 'DimProduct'[ProductName],'DimDate'[CalendarYear]

DAX Summarize with SUMX Function to filter Total

Note that if the Category Name is omitted from the query in the Summarize part, an Error is generated.

--without the categoryname in the summarize section *ERROR
 evaluate
  (
   summarize
   (
    'FactInternetSales',
    'DimProduct'[ProductName],
-- 'DimProductCategory'[CategoryName],
    'DimDate'[CalendarYear],
    "Total Sales", sumx(Filter('FactInternetSales', 'DimProductCategory'[CategoryName]="Bikes"),[SalesAmount])
   )
  )
order by 'DimProduct'[ProductName],'DimDate'[CalendarYear]

> Executing the query ...
Query (9, 55) The value for column 'CategoryName' in table 'DimProductCategory' cannot be determined
in the current context. Check that all columns referenced in the calculation expression exist, and
that there are no circular dependencies. This can also occur when the formula for a measure refers
directly to a column without performing any aggregation--such as sum, average, or count--on that
column. The column does not have a single value; it has many values, one for each row of the table,
and no row has been specified.

Execution complete

The query below simply uses a Format statement to change the display of the Total Sales Amount from Decimal to Currency.

 evaluate
  (
   summarize
   (
    'FactInternetSales',
    'DimProduct'[ProductName],
    'DimDate'[CalendarYear],
    "Total Sales Amount", format(sum('FactInternetSales'[SalesAmount]), "Currency")
   )
  )
 order by 'DimProduct'[ProductName],'DimDate'[CalendarYear]

DAX Data Formatted using Currency

DAX Statement - AddColumns & Define Measure

This DAX query includes the usage of an AddColumns statement to compute the profit for each Product listed in the DimProduct table.

 evaluate
 (
  summarize (
   AddColumns(
   'DimProduct',
   "List Price Less Cost",
   ('DimProduct'[ListPrice] - 'DimProduct'[StandardCost])
   ), [List Price Less Cost],
  'DimProduct'[ProductName],
  'DimProduct'[ListPrice],
  'DimProduct'[StandardCost]
 )
)

DAX AddColumns Statement

The DAX statements below include a Define Measure statement so we can reuse the calculated Profit Percentage in the Summarize query. Also included is a Format statement to display the value for Profit Pct as a Percent instead of a Decimal datatype.

 define
  measure 'FactInternetSales'[Profit Pct]= (sum(FactInternetSales[Margin]) /    sum(FactInternetSales[SalesAmount]))

 evaluate
  (
  summarize(

   'FactInternetSales',
   'DimProduct'[ProductName],
   'DimProductCategory'[CategoryName],
   "Total Sales", Format(sum('FactInternetSales'[SalesAmount]),"Currency"),
   "Percent Profit", Format([Profit Pct], "Percent")
  )
 )

DAX Define Measure & Format Percent