Joshua Fennessy

SSAS Tabular Model: Where are my Measures?

So, you’ve created your first Tabular model in SSAS 2012, congrats!  Have you tried browsing itimage in a client tool like Microsoft Excel yet?  If you have, you may notice that in the Values category there is only a sad message __No measures defined.

Why is this?

imageIn a tabular model ANY table you create, whether  it contains numerical data, character data, or a mix of both, is considered – by Excel at least – to be a dimension table.  Go ahead, scroll down your field list — In my example, I have a dimension called Sales if I try to drag one of my measure values to the Values section of the PivotTable Excel lets me know that I can’t place that value there.  It then very nicely places it on rows for me.  Boo.

So, how do you fix this?  It’s pretty simple really.  You just need to create a calculated measure for each value that you would like to expose to client tools like Excel.

Creating measures in SQL Server Data Tools

Back in SQL Data Tools, open up the table that contains the measure values.  In my case, it’s a table called Sales. I want to create four values that my end-users can utilize in Excel.  I will also want to hide all of the rest to ensure that [Sales] doesn’t show up in Excel as a dimension and create confusion.

With the desired table open, add a new calculation to the Calculation area.  In this first example, I’m adding a measure called [Sales Amount] with the formula:

 Sales Amount:=SUM(Sales[SalesAmount])image

I’ll repeat this same process for the next few measures that I need to create.  You’ll notice that all of these measures are created using DAX.  If you haven’t started learning DAX yet, you’ll want to.  It’s VERY important for working with Tabular cubes.

Verifying the results

I’ve created all of my measures and set my format type. I’ve also hidden all of the fields in the Sales table from client tools.  Remember, this is done to help simplify the Excel experience.

Next just a quick re-deploy, and refresh of the Excel workbook to ensure that my new measures are showing up and usable by my end-users.

image

That looks much better!  Remember, any value that needs to be exposed to a client tools as a measure NEEDS to be explicitly created as a calculated measure in Tabular Mode using DAX.

2 thoughts on “SSAS Tabular Model: Where are my Measures?”

    1. HI Manuel! Nope, it sounds like you did everything right. The Values category shouldn’t go away, it should be the place that your calculated Measures end up with Tabular models in SSAS. That’s one difference between that and Multidimensional. In Multidimensional, we put those measures in Measure Groups so they are labelled with a custom name. In Tabular, the “Measure Group” is called the same name as the table you placed them in.. That’s where they’ll be. The individual tables will be translated similar to Dimension tables in Excel.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: