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.

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.

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 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]

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]

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 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]

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]

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

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