Thursday, February 9, 2017

Measures(Calculation) in SSAS Tabular

While you work with SSAS Tabular or OLAP you have option to create measures. As found calculation in SSAS OLAP is easier than Tabular. However, I have recently made SSAS Tabular cube and going to share how to create Measures/Calculation in SSAS Tabular model.

SSAS tabular project look like below, I have marked the area for the calculation/measures.

Fig 1: Measures/Calculation area.

How do we make Measures?

Select any cell from above fig 1, and then as like excel you  have option to put the calculation or formula at the formula bar as like below fig 2 (yellow shaded).

Fig 2: Calculation bar
Here, I am going to calculate how many product is sold in Canada region, So I have calculated number of rows where productno are not empty and filter the salesTerritory with Canada.

The measure name is: Prod_Cnt_Canada (product count in the canda region)

Prod_Cnt_Canada:=COUNTAX(FILTER(FactInternetSales;[SalesTerritoryKey]=6);[ProductKey])

The above example counts the number of nonblank rows in the column, ProductKey, using the table FactInternetSales that results from filtering the SalesTerritoryKey=6 (means canada).

COUNTAX function is to return the number of rows for non empty rows. And FILTER function is to do the filter.

Result after the calculation is show in the below figure: 3

Fig 3: Measure after the calcuation