SSAS Tabular Part 7 : Create Measures

A measure is a calculation of column values. We can create it using a DAX formula. One thing we have to remember is ‘It is not like column in a table’. Most of the cases it return only single line. For example SUM of a column, Count of rows for a particular column applying filter (Monthly Total, Quarterly Total, Average, Maximum, Minimum etc).

To create measures, we will use the Measure Grid. By default, each table has an empty measure grid. The Measure Grid appears below a table in the model designer when in Data View.

Apart from this, we can click the Table menu, and then click Show Measure Grid.

Steps to create measures

For Example, I am taking Fact Internet Sales  table and going to create a measure on it.

1. In the model designer, click the Internet Sales table (tab).
2. Click on the Sales Order Number column heading.
3. On the toolbar, click the down-arrow next to the AutoSum (∑) button, and then select DistinctCount.
4. In the measure grid, click the new measure, and then in the Properties window, in Measure Name, rename the measure to Internet Distinct Count Sales Order.

Here we see the simple logic for this Measure. We can write some DAX expression also.

Like this we are going to create one more measure for 'Order Quantity' column using another method.

1. In the model designer, click the Internet Sales table (tab).
2. Click on the Measure Grid and select a cell.
3. In the formula bar, type the following formula: TotalOrderQuantity:=SUM([OrderQuantity]) (Here 'TotalOrderQuantity' is the Measure name followed by ':' and then we written DAX formula for aggregation)