PowerPivot Posts

Percentile Measures in DAX - Errata

webadmin@SSAS-Info.com (Colin Banfield) — Thu, 24 May 2012 19:26:43 +0000

In September of last year, I posted two articles on creating percentile measures in DAX. See Creating Accurate Percentile Measures in DAX - Part I and Creating Accurate Percentile Measures in DAX - Part II. About three months after I posted Part I, Richard Mintz left a comment indicating that he was having trouble getting correct results when his data sets had a wide range of values and many duplicates. I haven't been receiving notifications when comments are left, so it's purely by chance that I saw Richard's comment recently.

"Drill Across" in PowerPivot - Live Demo

webadmin@SSAS-Info.com (Rob Collie) — Thu, 24 May 2012 19:26:39 +0000

The retailer overlap application is one that I've covered before, in my post announcing our live PowerPivot demo site, but I've recently spent some time improving it based on customer feedback and requests.

Specifically, our retail customers have asked the following: "It's great that I can see that Retailer X competes with me for our senior citizen customers much more aggressively than we thought, but can I get a list of the actual stores that overlap, with addresses?"

Upgrade PowerPivot SharePoint from SQL 2012 RC0 to RTM

webadmin@SSAS-Info.com (Dan English) — Thu, 24 May 2012 19:26:35 +0000

So last week I was doing a presentation on Self-Service BI with SQL Server 2012 at the TechFuse MN technical conference and I realized that my demo virtual machine was still running SQL Server 2012 RC0. I was thinking that I shouldn't do my presentation and demos using RC0, so why not upgrade. Probably not the best idea to do this just a few days out from a presentation, but I had my fingers cross and just went for it.

I found some guidance from a thread in the forums by Jen Underwood here Upgrade in place RC0 to RTM and decided to go for it. I did the in-place upgrade and my SharePoint virtual machine with SQL 2012 was still functioning after I completed all of the steps. Then I moved on to testing my integrated SSRS setup and realized that the Power View reports were not working and neither were my data sources. The quick fix for that was to go into Central Administration and go into the properties of the Reporting Services application and click OK. That completed the upgrade process for my shared service and my Power View reports functioned properly after that.

Consolidation and Intercompany Elimination made easy using PowerPivot and DAX

webadmin@SSAS-Info.com (Gerhard Brueckl) — Mon, 21 May 2012 21:28:02 +0000

In my past years as a consultant for Microsoft BI I had to deal with financial models quite a lot. They are usually small in size but very complex in terms of calculations like currency conversion, margin-calculations, benchmarks and so on. Another topic I came across very frequently was consolidation and intercompany eliminations. Recently I had to deal with this topic again and thought of how this could be solved in PowerPivot/DAX.

SCOPING at different granularities (Part III)

webadmin@SSAS-Info.com (Javier Guillen) — Mon, 21 May 2012 21:27:58 +0000

In the last two blog entries on this topic (available here and here), we got a bit closer to reproducing - in DAX - the same output one would normally achieve with the MDX scope statement.

In this new entry, we perfect the formula a bit more by ensuring filter context propagates to the higher levels of granularity we are affecting.

Excel 5-Calendar Date Table

webadmin@SSAS-Info.com (Colin Banfield) — Mon, 21 May 2012 21:27:56 +0000

For some time, I have been looking around for a fairly complete date table in Excel for use with PowerPivot. If you are working with data derived from a data warehouse, a date table is perhaps the most common dimension table that exists in the warehouse. However, not every scenario involves working with a data warehouse directly, and I simply wanted a "portable" date table. I found very little online, the best perhaps being this Excel table offered by the Kimball group (the table has been expanded since I originally downloaded it). I could have modified the Kimball table for my particular needs, but I decided to create one from scratch. Late last year, Rob posted an article titled the Ultimate Date Table, which is available from the Azure Marketplace. I considered using this table instead of the one I was building in Excel, but the "Ultimate Table" lacks fiscal periods. Much of the analysis work I do includes fiscal periods.

Filters aren't applied to related tables in PowerPivot unless a measure is added to the PowerPivot Workbook

webadmin@SSAS-Info.com (John Desch) — Wed, 16 May 2012 22:55:14 +0000

A colleague, with whom I work on a regular basis, recently pointed out a somewhat unexpected feature of PowerPivot PivotTables. What my colleague was seeing was that when creating a PowerPivot PivotTable, placing a column from one of the Dimension tables on rows then adding a column from a related (Parent) table in one of the slicers and selecting a specific member from the slicer the action did not apply a filter to the related dimension that had been placed on rows. When a measure was added to the PivotTable, however, the dimension that was on rows the filter was magically applied. This behavior is relatively easy to reproduce using a few tables from the Adventure Works relational database. So for those who want a reason to just play with PowerPivot (take your pick of the version), here are some relatively simple steps to reproduce the behavior.

Controlling the Position of Subtotals in DAX with GenerateAll()

webadmin@SSAS-Info.com (Chris Webb) — Wed, 16 May 2012 02:20:22 +0000

Reposted from Chris Webb's blog with the author's permission.

Today I’m teaching the SSAS 2012 Tabular workshop with Alberto Ferrari in Belgium, and an interesting question came up during my session on DAX queries to do with subtotals that I couldn’t answer immediately. However, I found a solution fairly quickly afterwards and so I thought it was worth blogging about – especially since I think it’s a better solution than the one that Alberto knew about already!

Consider this DAX query that runs on a table sourced from the DimDate table in Adventure Works:

evaluate
summarize(
DimDate
, DimDate[CalendarYear]
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, countrows(DimDate)
)

It returns the number of Days in the table by calendar year and day number of week – it’s very similar to a basic GROUP BY query in SQL. I blogged about this use of Summarize() and Rollup() last year here.

The problem with this query is that all of the year subtotals (which appear as rows with blank values returned in the day number of week column) created by this query appear at the end of the result set, as you can see here:

This isn’t very clear though. How can we put each year’s subtotal at the end of the distinct list of day numbers instead? Alberto’s solution (and I think this is the solution we’ve got in our new SSAS 2012 Tabular book) involves using the IsSubtotal() function (see here for more details on this) and ordering, similar to this:

evaluate
summarize(
DimDate
, DimDate[CalendarYear]
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, countrows(DimDate)
, "Is Subtotal"
, IsSubtotal(DimDate[DayNumberOfWeek])
)
order by
DimDate[CalendarYear] ASC
, DimDate[DayNumberOfWeek] ASC
, [Is Subtotal] ASC

But I thought there was an alternative, more elegant approach and found one. Here it is:

evaluate
generateall(
values(DimDate[CalendarYear])
,
summarize(
DimDate
, rollup(DimDate[DayNumberOfWeek])
, "Number of Days"
, calculate(countrows(DimDate))
)
)

As you can see, the subtotals appear after the list of day numbers for each year. There are two important things to understand about how this query works:

I’m using the GenerateAll() function to take the list of distinct years returned by the Values() function and then, for each year, return a table of day numbers with a subtotal and crossjoin the result. This gives us our subtotals in the correct position without any sorting required.
I had to wrap my original countrows() with calculate to make sure it returned the correct value.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .

PowerPivot - Personal BI on your desktop

webadmin@SSAS-Info.com (Andrew Chan) — Tue, 15 May 2012 22:11:30 +0000

What do you think about business intelligence (BI), a lot of people believe BI is only for big enterprise because it is expensive, takes long time to implement and requires special trained consultants to set it up. The worst part is that it does not often deliver what it promises to do - real time, self-service information delivery. Even they have BI within their organization, many analysts still heavily rely on their most trusted software, Excel to do their analysis because their corporate BI just does not provide everything they want or they are not familiar with the new BI environments.

Resolving Many to Many relationships leveraging DAX Cross Table Filtering

webadmin@SSAS-Info.com (Gerhard Brueckl) — Sun, 13 May 2012 21:39:00 +0000

If you ever had to deal with many-to-many relationships in PowerPivot then I am quite sure that you came across the blog-post Many-to-Many relationships in PowerPivot by Marco Russo and PowerPivot and Many to Many Relationships by Alberto Ferrari. Both posts describe how this issue can be solved using DAX and provide examples and also very good background information on that topic.