Month: January 2014

Excel, Microsoft Technologies, Power BI, Power Pivot, Power Query

Moving Calculated Measures in Power Pivot for Excel 2013

Meagan Longoria

I learned a lesson the hard way: I shouldn’t change field names and data types in Power Pivot on tables that were imported using Power Query. My changes broke the connection between the two tools, so when I refreshed a query in Power Query that was set to load the results to my data model it caused a new/duplicate table to be created in my data model instead of updating the original table.

PPBlogTables

I already had Power View reports built off of my model that I didn’t want to rebuild, and I didn’t want to leave a duplicate table in my model, but I needed to make the data model refreshable. The issue can be fixed, but it’s a bit tedious:

  1. Create relationships for the new table to match those of the old table
  2. Rename the old table to something else
  3. Rename the new table to the original table name (usually it has a 1 after the table name)
  4. Rebuild or move any calculated measures to the new table
  5. Delete the old table

I was fine until I got to #4. You can’t just copy a calculated measure to a new table by cutting and pasting. You can copy the formula from the formula bar and paste it into a cell on another table, but you will get a warning that you can’t have two measures with the same name.

PPBLogDupName

Going this route means pasting your measure, renaming the old measure, and then renaming the new copy of the measure to the original name.  Due to the lack of the ability to filter data in Power Map, I had to create several calculated measures in my data model to limit the data shown in my map.  The idea of copying, pasting, and renaming several dozen measures was less than appealing.

It should be noted that in the current version of Power Pivot, you can put calculated measures on any table in the model without affecting the result of a query using the measure. The table in which it is located is more of an organization issue rather than a technical calculation issue.

I thought there must be a better way to move a measure. I looked through all of the buttons on the ribbon in the Power Pivot window and saw nothing helpful so I turned to Google (yes, I still choose Google over Bing). A TechNet article provided just the guidance I needed:

“To move a measure, you can cut and paste the formula in the Calculation Area. You can only move it to a different location in the Calculation Area of the table in which it was created. You cannot move a measure to different table in this view; instead use the Measure Settings dialog in the PowerPivot ribbon in Excel to change the association.”

The Measure Settings dialog box held the answer, although I found these settings under the Calculated Fields -> Managed Calculated Fields.  PPBlogManageFields

It contains a drop-down box that allows me to choose the table where the definition of the measure should be stored. This was a much quicker way to quickly move several calculated measures to another table in Power Pivot.

PPBlogcalcFieldSet

I have built several Power Pivot models and never run into this particular situation. I can’t say I’m glad it happened, but I can appreciate that it reminded me that there are useful things on the Power Pivot ribbon in the Excel window that aren’t accessible through the Power Pivot window.

Books, Data Visualization, SQL Saturday

I’m speaking in January about data visualization

Meagan Longoria

I am excited to have two opportunities to speak in January. The first is the Kansas City SQL Server User Group. I will be speaking at the monthly KCSSUG meeting on January 16th at 2:30pm CST. You can RSVP for the event here. Next I will be speaking at SQL Saturday #271 in Albuquerque. At both events, I will be presenting on The Accidental Report Designer: Data Visualization Best Practices in SSRS.

This presentation is geared towards data professionals who may not see report design as one of their main responsibilities, but who occasionally have to deliver information through reports, dashboards. I think most people have to do this from time to time in their jobs, so anyone could benefit from this information. I am passionate about the content in this presentation because it changed the way I work and has positively impacted my career path. My interest in data visualization started when I read Stephen Few’s book Information Dashboard Design while working on a marketing dashboard at a previous job. I strongly believe that the way you display information to the end user can enhance or render useless any great data model/ETL you have created. The end user of your report is probably missing the point if you aren’t providing useful information in a consumable format.  Poor report design, in addition to being ineffective, can actually mislead your audience. As a data professional, this should concern you.

As an example, let’s look at some crime data for my neighborhood that I pulled from data.kcmo.org. I want to know: what was the most common crime committed in my neighborhood in 2013?
First, take a look at this graph:

crimes donut chartThis is a 3-D exploded donut chart that shows the number of crimes committed by type.

Now let’s try to answer the question with this graph: crimes bar chartThis is a bar chart that shows the types of crimes committed as a percent of total crimes. I think you will agree that you immediately see from this graph that non-aggravated assault is the most common crime, followed closely by stealing from an automobile.  I can easily see the percent of total incidents that each type represents and how each type compares to other types.

Even if your data isn’t saving any lives, you can still learn to make professional data visualizations that effectively communicate information while being visually appealing, which can impress and earn trust from your management, clients, and other audiences. If you would like to learn more, I would love for you to attend my session in KC or Albuquerque. If you can’t make it, feel free to check out the slides on the Presentation page.