Set up Table just to house calculated fields?

[bigck2]

I'm curious if it is possible to set up a table in the data model just to house the calculated fields I create. And if this is a good idea?

I have a non-calculated field called 'ForecastTotalRevenue' and I want to create a calculated field:

Forecast Total Revenue = SUM ( FactForecast[ForecastTotalRevenue] )

There are a few other calculated fields like this I want to use a pieces of bigger calculated fields. I also am doing this because I want the name of the field and formatting to look pretty for reports. Since the names are so similar my thought was if I could create a table to just house all these calculated fields, I could easily distinguish between the regular field and the calculated fields.

Thoughts??

 

[Matt Allington]

Yes this approach is often used. This is the best way as follows.

Create a linked table in Excel with 1 column and no rows.
Rename the table Measures
Add to the data model
Move one of your measures to this table.
Then go to the data model and hide the 1 column

Now you will see just the measures in this table. You may get false positive warnings from relationship detection, but no harm comes from this. You can turn it off in 2010 but not in 2013

 

[bigck2]

Hey Matt,

Thanks for the feedback and tips. Before I saw your reply I went down the road of creating a lot of calculated fields with a prefix "C - " to group these together in an existing table. I may want to switch these over to a table called Measures.

Just to make sure I'm not missing anything here. Let me ask you a question. Here is what I did:

In a new sheet created a table called 'Measures" with a Column Heading "ColumnTest" and no rows. I inserted a table called measures. Then I added the table to the data model. It showed up in the data model with a name of 'A'. I tried to change it to 'Measures' in the data model, but I was told that was a reserved phrase. I instead changed the name to 'Calcs'.

To be clear I did not create any relationships with this table to the other tables in my data model. It's just kind of sitting there by itself. I went to PowerPivot > Calculated Fields > Manage Calculated Fields, and I changed one of my calculated fields 'C - Forecast Total Rev' to live in this new table. However when I tried to add it to the new table I'm having to add it to the name of the table 'Calcs' which I provided in the Data Model.

When I went to a sheet with a PivotTable it is showing up in a table called 'Measures'.

I did get the yellow message bar saying "Relationships between tables may be needed." I'm thinking based on your comments this really doesn't matter. Besides the calculated fields are all based on the table that do have relationships built. Since the only thing I'm using this 'Measures/Calcs' table for is to house the calculated fields it shouldn't matter.

Before I move all the fields to the new table, do you know if there might be any problems with having the table called 'Measures' in the excel worksheet and called 'Calcs' in the data model. Like I said when I accessing the data model through a Pivot Table it is showing up as 'Measures'. Seems a little bit strange.

Thanks a lot for your help!

-Chris

 

[bigck2]

I

 

[Matt Allington]

You can't easily rename a linked table in Power Pivot, you have to get it right in Excel first. If you have one name in Excel And a different name in Power Pivot, then it really doesn't matter. No harm will come. If you want to fix it, delete the table from Power Pivot, rename it in Excel and then relink it.

I thought Measures as a name was OK (and I thought Measure was a problem), but maybe Measures it is a problem too. Eg you can't call a table "calculate" or "sum" because Power Pivot will get confused.

The he yellow warning is very annoying but inert - it doesn't hurt anything. The real issue is you can't turn it off in excel 2013.

Regarding the KPI problem, I have never come across that before, but then again I rarely use KPIs. It is interesting though. Which table does it put them back into? Is it the correct original table? Have you tried to move it again after creating the KPI?

 

[bigck2]

Thanks for clarifying.

I totally understand the fact you must name the linked table correctly in Excel first. (I've made that mistake many times.)

I originally names the table 'Measures' in excel. Then I think I tried to name it 'Measure' in the data model. Excel didn't like that so I named it calcs. I went back in and tried again. It let me rename the table 'Measure' but NOT 'Measures'.

Thanks for clarifying the yellow warning is not an issue.

The KPI problem was a user error. For that particular field I defined it in the wrong table, and then when I converted to a KPI it stayed in the wrong table. I was able to delete the KPI, then go into to manage the field and put it in the correct table. Then when I recreated the KPI it appeared where I wanted it to. I tried to take down my message from this site once I realized it was just a user error.

Thanks again for all your insight. Very helpful stuff!

Cheers,

Chris

 

[Matt Allington]

I remember now. measures is a name that is used in SSAS to store the measures under the hood. You can see this if you start to type a cube formula. Go to a cell and type the following (exactly including the last double quote).

=cubevalue("ThisWorkbookDataModel","

you you will see a list of all the tables in your data model PLUS one additional "table" called Measures. It is ironic that under the hood all calculate fields (what a horrible name) are actually stored in a "table" called measures.

 

[bigck2]

Hey Matt,

Thanks for your help on this a little while back. I have a related question you might know.

Having these calculated fields / measures in a separate table really helps simply things. If I had a data model where I already created a bunch of calculated fields / measures, would it do any damage if I started moving these to a measures table?

I'm thinking as long as I don't change the name of the fields, then it won't impact anything. Of course changing the name of a calculated field causes all sorts of problems if there are other calculated fields that depended on that field so I thought would run it by the community.

Thanks,

Chris

 

[Matt Allington]

You are correct on both counts. No issues moving them. I normally do this from the calc fields\manage calc fields dialog in 2013. Note, this is one reason you never specify the table name in front of a measure name in DAX. If you do, this move will break all your other measures.