Lots of fun quotes from these great folks.  Some that jump out at me:

“Our old version of this report was 22 sheets.  We just got it down to 2.”

“OK Rob, save that workbook right there.  That’s like, ALL of my work for the next two days.”  (Not said in a joking manner).

“Tell ya what, why don’t you just…  put that file on my thumbdrive before you leave.”  (Also not joking).

“The weather in Columbus, Ohio is not bad at all.”  (Ironically, not joking).

“Hey a few of us are going to the bar, wanna come?”

(And if my friends in San Diego are reading this…  no.  Not even close.  But May is just around the corner isn’t it?  I think it’s almost time I come visit again.)

I also got to meet one of our other clients in person on this trip – they very kindly took me to dinner at an awesome burger joint and bar.  Thanks AH and B!  Very nice and intelligent people – even more so in person than in email.

Anyway, it’s midnight and I’m still in the air (flight delayed) with a busy morning.  So I’m gonna give myself one day of respite and go live with a real post on Friday.

Next week, back to Tuesday/Thursday schedule.

One of Azzurri Communications Ltd’s PowerPivot
Applications Running in the Browser

Six Months Ago:  The “Lightbulb” Moment

Almost exactly six months ago, after being a long time reader of this blog, I emailed Rob and asked him a question regarding something that I just couldn’t get my head around in DAX – Banding!  He kindly responded, and his answer solved my problem, so I asked him if I could return the favour somehow.  He asked if I’d mind writing it up as a guest post, which I did.

Now, double-digit guest posts later, I’m amazed at how far I’ve come in short order.  Something definitely “clicked” for me that day, and my grasp of PowerPivot’s capabilities expanded rapidly.  It felt like that moment that I imagine Pianists reach where they can suddenly play by ear, because whilst I could conquer most things in DAX, it didn’t seem to quite “flow” – until that day!

Up until that point I had viewed PowerPivot as a “private” tool – something that was useful for me in my work, a supplement to other tools and methods.  But starting six months ago, I started to understand that PowerPivot could, and SHOULD, be used to improve or replace most of our existing Business Intelligence and Analytics tools.

Step One:  Azzurri Deploys its First “On Premise” SharePoint Server

At Azzurri, I am fortunate to enjoy two critical flavours of support:

1. Our executive team is open-minded to progressive ways of doing things (so long as there is a solid value proposition).
2. My tech team is a crack outfit who will bring me the moon if I ask for it, but tend to make reasonable alternative suggestions such as building data warehouses.

In other words, Azzurri is the perfect sort of place to deploy PowerPivot for SharePoint, bordering on a textbook example.  There aren’t many companies of Azzurri’s size where I could explain the benefits of a PowerPivot server, win people over, and have a server deployed two weeks later.  But that is precisely what happened

Step Two:  Start Emailing Rob Again

We didn’t just deploy the server, we immediately began USING it for serious work.  And that led to questions – questions about performance.  Questions about hardware.  Questions about customisation.  Questions about refresh.  Questions about “core and thin” workbooks.

Rob and I had a friendly correspondence going at that point, so I started sending those questions over.  I even looped him into email chains with our tech team, and we talked through a number of issues and optimisations.

Step Three:  Try Out Hosted PowerPivot in Parallel

Everything I do in Systems Development, especially with my Finance background, is about Cost V Benefit, ROI, IRR and payback.  With this in mind, I started wondering whether it made sense for us to develop PowerPivot for SharePoint expertise of our own.

We had originally decided to go with our own SharePoint deployment because we had the required licences and a particularly clever team who I had every faith could deliver.  This seemed obvious as SharePoint was already in operation at Azzurri.  My initial view was that it must be relatively straight forward to bring all of the BI tools into the equation.

Two weeks into the process, however, I was already seeing that things might not be as straight forward as I had first hoped.  Performance was the first major barrier that I hit and I couldn’t be entirely sure what kind of investment in hardware might be required to alleviate this.  Out of nowhere, PowerPivot gallery started playing games which turned out to be an IE9 issue and then I was introduced to Kerberos which, it turns out, isn’t a breakfast cereal that I was yet to encounter!

I knew about the Pivotstream Hosted Solution of course, and I still wasn’t ready to commit to hosting, but I decided that running a trial in parallel made a lot of sense, especially since I was particularly aware that my tech team needed to be doing other things.

I’m very glad that we decided on a trial, because step four was to switch over full-time.

Goodbye “Do it Yourself”, Hello SaaS

The journey I’ve been on as a customer of Pivotstream has validated for me that the SaaS model together with the capability of PowerPivot makes for a more compelling business solution for reporting and BI than any alternative that I can find.

I’ve been particularly conscious of making sure that my tech team spend their time where they can really drive business value – building Data Warehouses, ETL and efficient business processes.  It was clear that time spent developing SharePoint Server was time not spent adding value elsewhere.  There’s no doubt in my mind that they would have delivered, but I knew that they could deliver more value elsewhere to more than offset any cost of hosting.

Summary of Our Experience

Once I had taken the decision to try out Pivotstream’s hosted solution, it became clear that “elapsed time” taken was no longer going to be a constraint to the project.  On that same day, Azzurri had it’s own Pivotstream site in full working order with admin and consumer accounts setup for the trial.  It was now down to me to start making this a fully functional dashboard.

Naturally, I had workbooks at the ready and I loaded a few up immediately.  I started sniffing some of the additional features that I could now start playing with.  Before I knew it, I was canvassing Pivotstream for direction on Query String URL filtering (an awesome attribute to drill across to other dashboards).  A handy guidance document found it’s way into my inbox and I was away.

I was supplied with a program to split core and thin workbooks, another gem that just saves time and aggravation.  I served up a query with web part layouts and, next day, I get a new page layout deployed straight to our site.

Immediately, the focus of what I was delivering was about end user usability as opposed to finding ways around potential (and in some cases very evident) performance issues.  Performance was immediately apparent on the Pivotstream solution, as could be immediately seen by some of my more “chunky” analysis that didn’t even make it flinch.  My in-house SharePoint Server could take upwards of a minute to open these workbooks whereas the hosted solution barely registers seconds.

Within a matter of days, I realised that the limits of this solution only existed to the extent of the limits of my imagination in creating dashboards.  All of a sudden, my focus was turned on making sure that full value was derived and, to that end, I started spreading the word within Azzurri.  Some initial training took place and I immediately recognised that these clever individuals that I was working with had even more insight bursting to get out and the fact that we were playing in Excel meant that they could immediately relate to what they were being shown.  I had hit that fantastic point in the project where momentum starts taking over and this is probably less than two weeks after starting the trial.

Speed (both of implementation and application), elimination of complexity and additional value adding applications delivered in a scalable data-centre model with an OPEX cost model sums it up for me.  Now, it’s just about making the dashboards deliver the real benefit to the business – insight!

As I’ve been writing this, Rob’s reminded me of a comment I made back towards when this whole thing started:

“My key driver is laziness so I’m always looking for quicker and better ways to do things. In doing so, I find myself working non-stop so I may have my driver wrong or I’m failing miserably!”

The reality is that Hosted PowerPivot does do it quicker and better.  I’m working non-stop because the results speak for themselves and I fundamentally “get it”.  The reality is that my driver was wrong!

“Sniff something? Did ya, rat boy?”

Continuity!

Folks I just can’t resist a quick followup on the Rat Sniffing Project.  I…  just… can’t.  Plus I am absolutely worn down and don’t have the energy tonight for anything that isn’t inherently entertaining.

All of those calculated columns from the last post, remember, were just the setup so we could start doing some REAL observational stuff.

You know, something like this:

This wasn’t difficult at all.

Remember that I now had a column that flagged each row as “1” if it represented the peak of an inhalation:

Each Row is 0.01 second, Flagged as 1 if
it’s a Peak Inhale, aka a “Sniff”

So now I really just need some measures.

The First Measure I’ve Ever Written in the Unit of Hertz!

(Hmmm.  There’s a joke in here somewhere, the first line of which is something like “what do you do if your unit Hertz?”  Listen, I told you I was tired.)

Since I have a 1 in the RobPeak column, summing it will yield the number of peaks in an interval – I don’t need to do a CALCULATE(COUNTROWS()) with a filter set to 1.

So the measure formula for Hz (events per second) is:

(SUM(Data[RobPeak]) / COUNTROWS(Data)) * 100

Why times 100?  Because each row is actually 0.01 second.

Now THIS is a *Time* Table

This is another first:  creating a separate time table that is NOT measured in days.  In this case…  hundredths of a second:

Not a Calendar Table – a Time Table.  In  0.01 Second Increments

OK, it’s not a Calendar table but it is VERY similar.  Just like a Calendar table has columns like DayofWeek, DayOfMonth, CalendarYear, etc. – those columns represent properties of dates.  Well, the columns I have here like Second and FiveSecond are the same sort of thing.

And since I have 6 rats in the experiment, it is wasteful to duplicate those properties in my Data table.  Plus there’s that whole speed thing.

So I created this separate table and related it to my Data table.

And this table then “powers” my slicers like these:

Slicers Based on a Time Table Measured in Minutes and Seconds

These slicers represent a navigation method more than they do a “filter” in the classic sense.  I say that because fields from the time table are on the axis of the chart as well:

The net effect is that I can use my slicers to quickly move around the data and examine “windows” that are interesting or relevant.  Couple of quick clicks and I am now looking at minute 4 instead of minute 3, and just the first 10 seconds of that minute:

Quickly “Jumped” to the 4:00-4:10 Interval

It’s ALMOST like the slider control on a YouTube video:

YouTube Has a Time Slicer Control Too!

Wouldn’t it be neat if Excel gave us a YouTube-style slicer for time?  We can dream.

Side Note:  Rats Breathe FAST!

Check those charts out.  Between 5 and 9 Hz???  Really?  Those little rascals inhale between 5 and 9 times per second, on a SUSTAINED basis?  Wow.  I’m easily amused I guess, but wow.

“Who are YOU, who are so WISE in the ways of SCIENCE?”

It’s all about following through…

In a recent post I covered a very simple scientific scenario.  It was an interesting diversion from the normal biz-style scenarios but it left me feeling hollow in two ways.  First, it was too simple and didn’t account for the possibility of multiple different treatment types, so I pinged the Italians.  (They responded, as they always do, and I owe a post on their reply, probably Thursday).

But the other problem was that, while claiming to be a scientific scenario, it was manufactured by me, and hey, I’m no scientist.  Then it hit me…

Hey!  My Neighbor is a REAL Scientist!

Yeah, Dan Wesson is a “he-runs-his-own-research-lab-at-the-university, is-published-in-prestigious-science-journals” kind of scientist.  So I decided to bounce the question off of him – was my example scenario at all useful, and if not, what WOULD be useful?  Boy, am I glad I asked.

Perhaps My Favorite PowerPivot Visualization Yet

Click for Full Size!

First of all, HOW COOL IS THAT?  It looks like the double helix or something.  I joked with my wife that I was gonna have it turned into an eight-foot mural for the dining room.  (Yeah, I was joking sweetie.  Totally joking, I would never do something like that).

What do the Values Mean?

These are measurements of respiration taken from rats in Dan’s lab.  Negative values indicate “inhale” and positive indicate “exhale.”

Each value represents one-one-hundredth of a second – how’s that for time intelligence? 

What Does “Peak Detection” Mean?

In order to make sense of the data, Dan needs to measure the frequency of how often the rats “sniff.”  Sniffing is an indicator of all sorts of things apparently – a sign of interest, a sign of cognition, a sign of dominance…  I had no idea.

Anyway, he needs to measure the number of “sniffs” the rats take in a given time frame, and in order to do that, he needs to count the negative peaks in the breathing data:

PowerPivot Can Do This?  Yes, Better Than the Professional Tools!

I went into this mostly expecting that PowerPivot was not going to be a good tool for his needs.  And as usual, PowerPivot surprised me.

Not only can PowerPivot do this, it outperforms expensive specialized software.  Software that is designed to do precisely this task.  Let that sink in for a moment:

$10,000 Specialized Scientific Software Often “Misses” the Peaks,
PowerPivot is Dead On

How Did I Do It?

It actually wasn’t that hard.

Given a data set like this with 180 thousand rows:

Six Rats’ Breathing Measured 100 Times per Second for Five Minutes

I needed to generate a 1/0, True/False column identifying whether a given row was a “sniff” peak.

I ended up creating a TimeID column so that I could work in integers:

Multiply by 100 So I Can Work in Integer Time Increments

And then I wrote the following calc column formula:

[IsPeakCandidate]

=IF(OR([TimeID]=0,[value]=0),0,
IF([value] > 0,0,   
    IF(
       AND(
           CALCULATE
             (MIN([value]),
             FILTER(ALL(Data), Data[Rat]=EARLIER(Data[Rat])),
             FILTER(ALL(Data),
                Data[TimeID]=EARLIER(Data[TimeID])-1)
             )
           >[value]
           ,
           CALCULATE
             (MIN([value]),
             FILTER(ALL(Data), Data[Rat]=EARLIER(Data[Rat])),
             FILTER(ALL(Data),
                Data[TimeID]=EARLIER(Data[TimeID])+1)
             )
           >[value],
          )
       ,1
       ,0
      )
   )

)

What is that formula doing?

Ok that looks complex.  But all it’s doing is asking three questions:

1. Is the CURRENT row’s value less than zero?
2. Was the PRIOR row’s value greater than this row’s value?
3. Is the NEXT row’s value also greater than this row’s value?

If all three are “yes,” then this row represents a negative peak.

Flaw:  A “Hiccup” Becomes a Peak

Unsurprisingly, it turns out that formula wasn’t quite good enough:

In Basketball This is Called This a “Pump Fake”
(In American Football, a “Juke,” in Hockey, a “Deke…”)

So I added another calculated column:

[MinOverInterval]

=CALCULATE(MIN(Data[value]),
           ALL(Data),
           FILTER(ALL(Data),
                  Data[Rat]=EARLIER(Data[Rat])
           ),
           FILTER(ALL(Data),
                  Data[TimeID] <= EARLIER(Data[TimeID]) +5 &&
                  Data[TimeID] >= EARLIER(Data[TimeID]) –5
           )
)

For each row in my data that calculates what is the minimum value over an 11-row interval – five rows before, five rows after, and the row itself:

MinOverInterval:  The Smallest Value in the 11-Row Window

One Last Column!

Then, tying it all together, the last calc column is this:

[IsRealPeak]

=IF(
   AND(
       Data[PeakCandidate]=1,
       Data[MinOverInterval]=Data[Value]
      )
   ,1,0
)

This column says “if this row was flagged as a peak candidate already, AND it is the most negative row in its 11-row interval, then we bless it as a REAL peak row.”

And if I use that column instead, that fixes the false peaks:

Some Final Notes

First, I suspect that I can simplify my calc columns a bit.  There was some exploratory trial and error in this process and it might be that the “MinOverInterval” test is ALL I need, since there’s no way a row can be its minimum in the interval and NOT be more negative than its immediate neighbors.

But hey, I wanted to get this post out ok?

Second, yes, these calc columns are something I’d ideally want to have done in a database.  But since I want to give Dan a completely self-contained toolset, I had to do it in calc columns.

Lastly, this “peak detection” is NOT the end of the road!  Far from it.  It’s just the beginning.  Now that we have a reliable “peak flag” column in our data, we can start doing the normal PowerPivot thing – measures of frequency, slicing by properties of the rat itself (age, sex, etc.), properties of the trial… 

So I expect Pivotstream will be doing some real work for Dan’s lab, helping support his Alzheimer’s research.  All starting from a quick conversation in Dan’s backyard over the weekend.  How cool is that?

“IT’S ALIVE!!!!”
(Just your average Excel Pro after converting
his first PowerPivot Workbook into a Web App)

I’ve got another article about to go live on CIMA Insight, but I’m gonna jump the gun a bit and post basically the entire thing here ahead of time.

At Pivotstream we recently went live with our first full-time demo site for Hosted PowerPivot.  We’re going to be adding to it over time, but it’s got enough on it already that I think it’s worth looking at – it shows that “spreadsheets have become live web applications” thing that has to be seen, live, to really sink in.

Customizable Homepage

Pretty self-explanatory – customizable content plus a menu of applications.  Link below. 

(OK, one note:  I say “customizable” but customization is only allowed for Authors/Owners of the site.  What you are seeing here in the Consumer experience, and Consumers cannot customize this home page).

https://insights.hostedpowerpivot.com/sites/Demo/Pages/default.aspx 

App #1: Based on Microsoft’s “AdventureWorks” Sample Data Set

This is the workbook from the Budget vs. Actuals Part One and Part Two posts. Here’s a picture of that same workbook after it’s been saved to a SharePoint web server and then accessed in my browser:

Notes on this application:

1. This is just an XLSX file, created with PowerPivot and then saved to the server
2. But it’s rendering in my browser (Firefox in this case), and the XLSX is NOT being downloaded to my computer
3. I do NOT have to have Excel installed on my computer in order for this browser application to function
4. This picture is of the menu page of the app, which is just a worksheet with the gridlines and headers turned off
5. The graphics are two image files inserted into the sheet via Insert.Picture on the ribbon
6. The four hyperlinks are merely links to other worksheets within the workbook

If I click the “Sales vs. Budget” hyperlink, I am taken to the report I built in last month’s article:

Click the slicer – it works

Try this application out here:

https://insights.hostedpowerpivot.com/sites/Demo/Pages/Adventureworks-Sample.aspx

Application #2: Retailer Overlap Competitive Analysis

This application is based on two real-world data sets. One is a list of the addresses of almost every retail food/drug store in the United States. The other is a detailed list of demographic information about every ZIP code (postal code) in the United States.

Blend them together in PowerPivot and you get an application like this:

Note that I have selected the two warmest temperature ranges, circled in orange. If I click the link at the top of the sheet I then see the following analysis:

Notes:

1. I have the application set as if “I” were Walgreens, and evaluating my competitors’ geographic overlap with my Walgreens retail locations
2. The first column indicates to me that CVS competes with me (Walgreens) for 41.5% of the potential customers that I try to reach
3. In other words, 41.5% of the people who live near a Walgreens also live near a CVS
4. The second column reports that Walgreens competes with me for 47.3% of my customers in Warm locations
5. So the first column ignores the slicer selections I made on the menu page, and the second column respects them!
6. The third column represents the delta between columns 1 and 2. Interesting, for instance, that Rite Aid does NOT compete with me at all really in warm locations
7. Try it out, slice away – there are many ways to slice and analyze this data set

Application is located here:

https://insights.hostedpowerpivot.com/sites/Demo/Pages/Retailer%20Overlap%20Analysis.aspx

Application #3: CRM Analyzer

This one is also based off of a sample data set, but it is one pulled from a popular CRM package and therefore represents real-world value.

Note that there are multiple reports in this application as well, plus a menu page, but I’m just showing one here for space reasons.

Application can be tried out here:

https://insights.hostedpowerpivot.com/sites/Demo/Pages/CRM-Analyzer.aspx

Application #4: UFO Sightings

Many of you have seen this one already, on Mr. Excel’s Hosted PowerPivot site, but we put it on this demo site as well.

Application is located here:

https://insights.hostedpowerpivot.com/sites/Demo/Pages/UFO-Sightings-Real-Data,-Humorous-Conclusions.aspx

…and the word “forum” gives me an excuse for a blast from the past…

“What you are about to witness is real. The participants are not actors.  They have agreed to dismiss their tech support cases and have their questions settled here, in our forum:
THE POWERPIVOT COURT

(Click here for theme music!)

The much-neglected calculated column

OK, neglected by me, not by everyone else.  At Pivotstream I have the benefit of a great SQL team.  Generally speaking, when I need a calculated column, I ask them to provide it.  The benefits of doing that are documented several places on this site, including here and continued here.

But hey, not everyone has a database rapid-response team at their disposal

So questions like this one come up a lot on the forums, reminding me of my blind spot:

You have two tables of data.  In one, you have a list of your clients and their effective discount rate during particular date ranges – marked by a start and an end date.  If the End date is blank, that discount rate is assumed to still be active.

Rates Table:  Discount Rate per Client,
Over Distinct Date Ranges (Start and End Effective Date)

Then there’s a second table, listing Clients and the days that you called each of them:

Calls Table:  Multiple Clients, and Multiple Calls to Each Client

Desired Result

You want to add a calculated column to that Calls table which contains that client’s effective discount rate on the date of the call:

We Want to Add the Highlighted Calc Column – What’s the Formula?

So… what’s the formula?

=CALCULATE(AVERAGE(Rates[Rate]),
   FILTER(Rates,
          Rates[Start]<=Calls[ContactDate] &&
          Rates[EffectiveEnd] >= Calls[ContactDate] &&
          Rates[Client]=Calls[Client]
   )
)

Hey, That’s a Lot Like a Measure!

Yeah, it IS a lot like a measure.  It uses CALCULATE, the wonder function.

But in this case, we’re just using CALCULATE to apply filters in our calculated column.

Works basically the same way, though.  The three clauses in the FILTER() function are all AND’d together using the && operator, so that only rows from the Rates table meeting the following three criteria are “kept” and then “fed” to the AVERAGE function:

1. The Start date in the rates table must be prior to (or equal to) the Contact Date in the Calls table.
2. The End date in the rates table must be after (or equal to) the Contact Date in the Calls table.
3. The Client ID in the rates table must match the Client ID in the Calls table

Note for those who desire deeper understanding:  The biggest difference between this calc column formula and a similar formula we’d have to write in a measure is that in a measure, we’d have to use the VALUES() function wrapped around the right hand side of the criteria – VALUES(Calls[ContactDate]) rather than just Calls[ContactDate]).  And we’d have to “protect” those VALUES functions with an IF(COUNTROWS()) since you can’t use VALUES() as part of an “=” test, except when there is only one value returned, or maybe use MAX() instead of VALUES().  The point is that in a calc column, we DO have a current row in the table, so we don’t need the wrapper function like we do in a measure.

What’s that EffectiveEndDate column?

Yeah I sneaked that one in.  It’s another calc column I added to the Rates table:

You can see its formula in the formula bar in the image above.  I just wanted to put a non-blank value in whenever End was blank.  You could hard-code a date in there if you wanted, or use TODAY() and a much bigger number than 365.  I just figured that setting a date one year in the future from today is good.  Keep in mind though that TODAY() will only be re-evaluated when you refresh your data or force a re-calc.

Is There a Relationship Between Those Two Tables?

No, there is not.  Neat huh?  And really, there couldn’t be anyway – each Client ID appears more than once in each table, and PowerPivot doesn’t handle relationships on “many to many” like that.  The date columns are even less “relationship friendly” since they don’t match up at all, and are based on ranges in one table and single dates in the other.

Download the Workbook

I’m trying to do this more often, especially with the forum questions.

Download the workbook here

And now you need to downgrade to v1, so that your workbooks function on your server, or can be opened by other people running v1 on their desktops.

So… how to do that?

The Obvious Thing Works for Most People

Simple:  just uninstall v2 (from Windows Control Panel) and then install v1.

But if you see this error when you try to build a new workbook, or open an old one, well, it’s not working out:

What to do if you get that error?

1) Open Start Menu

2) Find “Command Prompt” – it is under Accessories

3) Right Click on Command Prompt, choose Run as Administrator

4) Once in the cmd window (looks like the old DOS window), type the following:

regsvr32 “C:\Program Files\Microsoft Analysis Services\AS OLEDB\10\msolap100.dll”

You may need to correct for your actual folder location – that’s the location on my machine.  The quotes ARE required.

Still not perfect though, as the v2 link remains first

And since not everyone knows that “2012 RC0” means “test version that won’t work with your servers, or anyone using v1” and that “2008 R2” means “version 1, the one you need for production environments,” well, this could still use some serious clarification. 

So let me make it 100% clear which link is good for what purpose:

First Link is a Test Version of V2, Second Link is Released Version of V1

See Also

Downgrading from V2 to V1

“Danger, Will Robinson!”

I’m seeing this pop up a lot now, both in our hosting business and on forums.

If you have gone to PowerPivot.com lately and downloaded PowerPivot, you downloaded a beta (a release candidate) of PowerPivot v2, and probably didn’t know it.

That’s not a big deal except for:

1. The release candidate likely isn’t as stable as the most up-to-date release of PowerPivot  v1
2. Workbooks created in v2 CANNOT be opened by v1, and that includes v1 server!
3. There is no way to “downgrade” a v2 workbook to v1, so if you want to use a v2 workbook on v1, you have to start over

So be careful – v2 is awesome but using it unknowingly may have side effects you didn’t expect, at least until v2 is official released and the rest of your organization upgrades.

I’ve notified MS of this problem but so far they haven’t fixed it.  Both v1 AND v2 should be available for download, with v1 the default, and both clearly marked.

If you want to download v1, here is the link:

V1 PowerPivot for Excel – Download Here

See Also

Downgrading from V2 to V1

Microsoft Fixes the Download Page…  Sorta

“…then they start thinking that, ‘where there’s smoke there’s fire’ logic…”

Continuing the Impromptu Series of Simple Real World Examples

The Mr. Excel PowerPivot Forum has inspired me to change gears a little bit and focus more time on simple techniques that don’t break the DAX Spicy Scale while still being very useful, everyday stuff. I sometimes take this stuff for granted and end up looking for topics that are much more “clever” when in reality we all can use a good dose (or two, or ten) of basic useful examples.

Medical Treatments – Are They Effective?  Counterproductive?

A user over at the forum named “Mirknin” posted the following question:

I have a relatively simple data set where each row represents a week, each week has a number of data columns. Occassionally, however, we have treatments and I set up Excel with a column where the treatment is recorded as 1 – i.e. a treatment occurred that week, otherwise the cells are left blank.

I AddColumn in PowerPivot to generate a unique identifier (through the usual ampersand formula) to represent whether a treatment occurred in a specific week. Using the Slicer, I can PivotTable to see the data from the row where the treatment took place because all this info is related to that week, but seeing the data for following four weeks would let me see whether the treatment had made an effect.

I guess my query is whether the Slicer can be modified in such a way as to report the row it’s associated with plus subsequent rows…

Data Set:  Let’s Use Trees Rather than People

When I was manufacturing sample data for this I got a bit squeamish about it.  Not for the obvious reasons though – I’ll explain at the end of the post.  But for now, just know that we’re going to work with Trees, like in a Botany lab, and some experimental treatment they are receiving.

We have a calendar of treatments – a list of dates and Yes/No for each:

Calendar of Treatments

And then a table of health readings taken for various trees in the lab on specific dates:

Tree Health Measurements

Normal Slicer Function Across Relationships

Notice that the two tables have the little “relationship” icons on the Date column?  These two tables are related on that column.  So if I write an “average health” measure and put the Yes/No treatment field on a slicer, I get:

But if I put Date on rows, we’ll see that all I am “getting” in my health scores is the health of the tree on the day the treatment was applied:

But I Want the Health Scores Over the Two Days AFTER Treatment!

I want to track a range of dates after a treatment is applied, so I need to do something different here.

What I need is a new column in the TreatmentCalendar table:

If I use THAT column as a slicer, or even better, put it on rows of the pivot, I get what I want:

Hey, check it out!  Trees are doing a little bit better in the two days after they receive treatment!  (That’s lucky, since this sample data was created using RANDBETWEEN).

But how did I build that “Recently Treated” Calculated Column?

Did I say up front that this was going to be LOW on the DAX Spicy Scale?  Hmm.

First let me repeat a longstanding recommendation:  if you are using a database as your data source for PowerPivot, you should seriously consider having this calculated column generated in the database and NOT in PowerPivot.  There are multiple benefits of that, as long as it’s an option.

If it’s not an option, you still have two choices.  For something relatively quick and dirty, you can do the calc column in Excel and just paste an entirely new TreatmentSchedule table into PowerPivot.

But for larger data sets, or cases where you can’t have that manual intervention step every time you get new data, you’re gonna have to write the calc column in PowerPivot.

And since PowerPivot lacks A1-style reference, and this calc column has to look at rows other than just the current row in order to get its answer, you have to use the dreaded EARLIER function.

Primer:   Simple Use of the EARLIER Function

Before we do something advanced with EARLIER, let’s cover the basic usage first:

Say I have the following VERY simple table like this:

And I want to add a third column that is the total for each customer:

How do I do that?

The calc column formula for that third column is this:

=CALCULATE(SUM([Amt]),
           FILTER(ALL(‘Table’),
             ‘Table’[Customer]=EARLIER(‘Table’[Customer])
                 )
          )

Explaining EARLIER() in depth may be its own post.  Yeah.  This was probably the LAST function I learned to use.  It’s an ongoing joke between me and the Italians actually.

Here’s the Formula for the RecentTreatment Yes/No Column

With that background in mind, here is the formula for the Yes/No column.  I actually did it in two columns:

That 1/0 column is the tricky one.  The Yes/No column is just a “cosmetic” column built using IF.

Here’s the 1/0 formula:

=CALCULATE(COUNTROWS(TreatmentCalendar),
           TreatmentCalendar[IsTreatmentDay]=”Yes”,
           FILTER(ALL(TreatmentCalendar),
                  TreatmentCalendar[Date] <
                       EARLIER(TreatmentCalendar[Date])
                       &&
                  TreatmentCalendar[Date] >=
                       EARLIER(TreatmentCalendar[Date])-2))

Easy right?

I Promise to Come Back and Explain This!

EARLIER isn’t all that bad really and neither is the formula above.  But it definitely feels a lot harder than an Excel pro wants it to be.  Given the length of this post however I think I’ll do that next time.

Calling Marco and Alberto!

There are a couple of obvious questions here that my simplified example just outright skipped:

1. What if I have more than one kind of treatment?
2. What if not all “patients” receive the same treatment, and/or on different days?
3. Can I use a slicer to control how “wide” the range of dates is rather than hardcoding 2 into my formulas?

The fact that I was dodging those problems in this post is why I was squeamish about using people as the example rather than trees.  And all three of those make this problem a lot harder.

And what do we do when we hit a problem that goes beyond the powers of mortal Excel Pros?  We throw up the Boot Signal of course: