PowerXL Course Live in Victoria, BC

We are very excited to announce that we will be hosting an “Introduction to Power Excel” session in Victoria, BC on June 6, 2014.

PowerPivot is revolutionizing the way that we look at data inside Microsoft Excel. Allowing us to link multiple tables together without a single VLOOKUP statement, it enables us to pull data together from different tables and databases where we never could before. But linking data from multiple sources, while powerful, only scratches the surface of the impact that it is making in the business intelligence landscape. Not only do we look at PowerPivot in this session, but we’ll also explore the incredible companion product Power Query; a tool that will surely blow your mind. Come join Ken as he walks you through the process of building a Business Intelligence system out of text files, databases and so much more.

Full details, including an early bird signup offer, can be found at http://www.excelguru.ca/forums/calendar.php?do=getinfo&e=42&day=2014-6-6

From TXT files to BI solution

Several years ago, when Power Pivot first hit the scene, I was talking to a buddy of mine about it. The conversation went something like this:

My Friend: “Ken, I’d love to use PowerPivot, but you don’t understand the database culture in my country. Our DB admins won’t let us connect directly to the databases, they will only email us text files on a daily, weekly or monthly basis.”

Me: “So what? Take the text file, suck it into PowerPivot and build your report. When they send you a new file, suck it into PowerPivot too, and you’re essentially building your own business intelligence system. You don’t need access to the database, just the data you get on a daily/weekly basis!”

My Friend: “Holy #*$&! I need to learn PowerPivot!”

Now, factually everything I said was true. Practically though, it was a bit more complicated than that. See, PowerPivot is great at importing individual files into tables and relating them. The issue here is that we really needed the data appended to an existing file, rather than related to an existing file. It could certainly be done, but it was a bit of a pain.

But now that we have Power Query the game changes in a big way for us; Power Query can actually import and append every file in a folder, and import it directly into the data model as one big contiguous table. Let’s take a look in a practical example…

Example Background

Assume we’ve got a corporate IT policy that blocks us from having direct access to the database, but our IT department sends us monthly listings of our sales categories. In the following case we’ve got 4 separate files which have common headers, but the content is different:


With PowerPivot it’s easy to link these as four individual tables in to the model, but it would be better to have a single table that has all the records in it, as that would be a better Pivot source setup. So how do we do it?

Building the Query

To begin, we need to place all of our files in a single folder. This folder will be the home for all current and future text files the IT department will ever send us.

Next we:

  • Go to Power Query –> From File –> From Folder
  • Browse to select our folder
  • Click OK

At this point we’ll be taken in to PowerQuery, and will be presented with a view similar to this:


Essentially it’s a list of all the files, and where they came from. The key piece here though, is the tiny little icon next to the “Content” header: the icon that looks like a double down arrow. Click it and something amazing will happen:


What’s so amazing is not that it pulled in the content. It’s that it has actually pulled in 128 rows of data which represents the entire content of all four files in this example. Now, to be fair, my data set here is small. I’ve also used this to pull in over 61,000 records from 30 csv files into the data model and it works just as well, although it only pulls the first 750 lines into Power Query’s preview for me to shape the actual query itself.

Obviously not all is right with the world though, as all the data came in as a single column. These are all Tab delimited files, (something that can be easily guessed by opening the source file in Notepad,) so this should be pretty easy to fix:

  • Go to Split Column –> Delimited –> Tab –> OK
  • Click Use First Row as Headers

We should now have this:


Much better, but we should still do a bit more cleanup:

  • Highlight the Date column and change the Data Type to Date
  • Highlight the Amount column and change the Data Type to Number

At this point it’s a good idea to scroll down the table to have a look at all the data. And it’s a good thing we did too, as there are some issues in this file:


Uh oh… errors. What gives there?

The issue is the headers in each text file. Because we changed the first column’s data type to Date, Power Query can’t render the text of “Date” in that column, which results in the error. The same is true of “Amount” which can’t be rendered as a number either since it is also text.

The manifestation as errors, while problematic at first blush, is actually a good thing. Why? Because now we have a method to filter them out:

  • Select the Date column
  • Click “Remove Errors”

We’re almost done here. The last things to do are:

  • Change the name (in the Query Settings task pane at right) from “Query 1” to “Transactions”
  • Uncheck “Load to worksheet” (in the bottom right corner)
  • Check “Load to data model” (in the bottom right corner)
  • Click “Apply & Close” in the top right

At this point, the data will all be streamed directly into a table in the Data Model! In fact, if we open PowerPivot and sort the dates from Oldest to Newest, we can see that we have indeed aggregated the records from the four individual files into one master table:



Because PowerQuery is pointed to the folder, it will stream in all files in the folder. This means that every month, when IT sends you new or updated files, you just need to save them into that folder. At that point refreshing the query will pull in all of the original files, as well as any you’ve added. So if IT sends me four files for February, then the February records get appended to the January ones that are already in my PowerPivot solution. If they send me a new file for a new category, (providing the file setup is 3 columns of tab delimited data,) it will also get appended to the table.

The implications of this are huge. You see, the issue that my friend complained about is not limited to his country at all. Every time I’ve taught PowerPivot to date, the same issue comes up from at least one participant. IT holds the database keys and won’t share, but they’ll send you a text file. No problem at all. Let them protect the integrity of the database, all you now need to do is make sure you receive your regular updates and pull them into your own purpose built solution.

Power Query and Power Pivot for the win here. That’s what I’m thinking. :)

PowerPivot training live in Victoria, BC!

Anyone who follows my website or Facebook Fan Page knows that I’m a huge fan of PowerPivot. Well great news now, that you can come and learn not only why this is the best thing to happen to Excel in 20 years, but also how to take advantage of it yourself!

I’ll be teaching a course on PowerPivot and DAX in Victoria, BC on November 22nd, 2013. While the course is hosted by the Chartered Professional Accountants, it’s open to anyone who wishes to subscribe.

If you’ve been trying to figure out how to get started with Power Pivot, you’re confused as to how and why things work, or you want to master date/time intelligence in PowerPivot, this course is for you.

100% hands on, we’ll start with basic pivot tables (just as a refresher.) Next we’ll look at how to build the PowerPivot versions and why they are so much more powerful. From linking multiple tables together without a single VLOOKUP to gaining a solid understanding of relating data, you’ll learn the key aspects to building solid PowerPivot models. We’ll work through understanding filter context, measures and relationships, and finish the day by building measures that allow you to pull great stats such as Month to Date for the same month last year, among others.

Without question, you’ll get the most out of this course if you’re experienced with PivotTables. If you don’t feel like you’re a pro in that area though, don’t worry! As an added bonus, to anyone who signs up via this notice, please let me know. I’ll provide you with a free copy of my Magic of PivotTables course so that you can make sure you’re 100% Pivot Table compatible before you arrive.

This is a hands on course, so you need to bring a laptop that is pre-loaded with either:

  • Excel 2010 and the free PowerPivot download
  • Excel 2013 with Office 2013 Professional Plus installed (yes the PLUS is key!)
  • Excel 2013 with Excel 2013 standalone installed.

If you have any questions as to which you have installed, simply drop me a note via the contact form on my website, and I’ll help you figure it out.

Full details of the course contents as well as a registration link, can be found at http://www.icabc-pd.com/pd-seminars-seminar.php?id=2849. Don’t wait too long though, as registration deadline is November 14th!

Hope to see you there!

A review of Add-in Express

A while ago I started transitioning from VBA to VB.NET again, attempting to build a tool to manipulate the new Power Pivot components in Excel 2013. While I was able to get part way to a working solution using Visual Studio 2012 and VSTO, I ran into two key issues:

1. VSTO seems to be “bit” specific, meaning that I would need to keep one version of the solution for 32 bit versions of Office, and another for 64 bit versions, and

2. While I could run my code, the Power Pivot engine would crash on me unless I opened Power Pivot BEFORE I ran any of my code.

Both of these issues were rather severe to me, as I didn’t want to maintain multiple versions of the same code, nor could I release something and expect users to open Power Pivot before running my project. A friend of mine suggested I try Add-in Express to deal with these issues.

In the spirit of full disclosure, I contacted them and asked if they had a trial version. They don’t, but offered to let me trial it if I’d blog on my experiences. I agreed to do that, and what follows is my honest observations of the software.

I do want to preface that, like my friend Rob Collie, I am not a “Read the manual” kind of guy. Ironic, since I write a lot of material, but I take the approach of diving in, and hitting Bing for a quick pointer when I get stuck. It usually causes me a lot of pain, but I tend to learn better that way.

So, here’s how it all came together for me…

When you install Add-in Express, you get a new set of templates. I started my project by creating a new ADX COM Add-in targeting the .NET Framework 2.0. (For those not in the know, in order to target Excel 2010 with VSTO, you need to use .NET 4.0 and Excel 2013 is .NET 4.5. So .NET 2.0 is way too early a framework to have ever even heard of Power Pivot!)


From there you pretty much follow the prompts through the setup. I called it “Connect”, I set the minimum supported version to Office 2010 (since PowerPivot didn’t exist in 2007), I chose a Visual Basic Project, I selected Excel, and I left the rest of the settings at their defaults. Pretty easy, that part.

Once I had a project to work with, I created a new VB Module, just like normal, and built the code I would need to refresh my PowerPivot model and all the PivotTables. (Be aware that, for simplicity of the post, this is 2013 specific code, and will not work with Excel 2010.)

The key piece in this is making sure the xlApp declaration is correct, as you need to refer to the AddinModule portion in order to bind it to Add-in Express’s handlers, instead of just binding to the Interop.Excel objects. That change is what makes Add-in Express work:

[vb]Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

Public xlApp = Connect.AddinModule.CurrentInstance.ExcelApp

Public Sub RefreshPowerPivotTables()

Dim xlWorkbook As Excel.Workbook = xlApp.ActiveWorkbook

Dim ws As Excel.Worksheet

Dim pvt As Excel.PivotTable

‘Attempt to connect



Catch ex As Exception

‘Data connection could not be refreshed.

MsgBox(“Sorry, but I could not refresh the model! Are you sure this workbook has one?”)

Exit Sub

End Try

‘Refresh all PivotTables

For Each ws In xlWorkbook.Worksheets

For Each pvt In ws.PivotTables

With pvt



End With



End Sub

End Module[/vb]

So far so good. Now I needed a user interface.

To be fair, it took me a bit to figure this one out. Once I finally realized that you need to open the “AddinModule.vb” portion in the solution explorer, then add a Ribbon tab to the canvas, then things got easier.

Despite reading very little documentation, with a little help from the blogs and articles on the Add-in Express site when I did get stuck, I was very quickly able to build a simple UI. There’s a good article on doing this here, which I wish I’d read earlier in the process.

As I say, it’s a really simple UI: a tab called “Model”, a single group, and a button with an image on it.

One criticism I do have is that it would be nice to be able to link the buttons and other controls to their callbacks inside the visual designer. As is, it’s a bit clunky, as you have to select the designer, then choose the other controls in the properties window. It’s not totally intuitive, but once you know where to look (read the article linked to above), it is workable.

My callback code for my button (which I didn’t bother renaming) is as follows:

[vb]Private Sub AdxRibbonButton1_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean) Handles AdxRibbonButton1.OnClick

Call RefreshPowerPivotTables()

End Sub[/vb]

And with that done, it came time to debug. Again, fairly straight forward:

  • Build -> Build the project
  • Build -> Register ADX project
  • Make sure Excel is closed
  • Start the debugging engine

My “Model” tab showed up, with the command I’d built, as shown below:


And with a single click (and a bit of a wait since Power Pivot is so slow), my Power Pivot data was refreshed, and the PivotTable updated to reflect the changes I made in my database. Notice the new customer and the new sales transactions for 8/1/2013:


But the best part is this:

  • The solution is deployable to both 32 bit and 64 bit Office platforms, and
  • I can open my Power Pivot project even if I run my code first, and it doesn’t crash.

I can honestly say that I fought that Power Pivot crash issue for about 2 months with VSTO, and I was really worried that it was going to kill my project completely. No amount of searching would turn up a fix, and other help calls didn’t yield any gold either; where they were answered, it was with a “don’t know” answer. Add-in Express has actually made this goal achievable.

I’ll also tell you that, while refreshing Power Pivot isn’t the focus on my full project, I have been able to use Add-in Express to successfully target and manipulate Power Pivot in both 32 and 64 bit versions of both Excel 2010 and 2013. I.e. multi version deployment with one code base. Pretty damn awesome.

I should also mention that their support has been phenomenal as well. Not only have they answered my emails, but I even ended up on a call with one of their lead people to examine why I didn’t seem able to use the debugging tools at first (a blog post for another day). 30 minutes, problem solved, and I’m good to go. Again, pretty damn awesome.

Readers of this blog will know that I don’t endorse very many products at all. Sure, I use Google Adwords and stuff, but I don’t write too many blog posts talking about how awesome a product is. Here’s my word on Add-in Express:

I’m sold. This product has been a life saver, and I won’t develop using VSTO.

Review – Creating Data Models with PowerPivot How-to

A couple of weeks back I was approached by PackT Publishing, asking me if I’d be willing to read and post a review of Creating Data Models with PowerPivot How-to, by Leo Taehyung Lee.  As I’m always interested to see what others are saying in this space, I agreed.

The e-Book is 40 pages of content (after you discount the copyright, author profiles and other stuff that goes with every book), and is intended to cover off PowerPivot 2010 from install to basic usage.  Being an author, and having written many how-to webpages, I can say that it’s a lot to cover in that few pages.

Overall, you get a pretty basic glimpse into PowerPivot.  Topics covered include:

  • Installing PowerPivot
  • Installing SQL Express
  • Importing a bunch of tables
  • Making a basic Pivot
  • Making a Pivot Chart
  • Adding another table
  • Creating a relationship
  • Creating calculated columns
  • Optimization

The PowerPivot install is pretty simplistic; it basically points you to Microsoft’s site and tells you to follow the instructions.  I guess you can’t complain about that too much, although I think I would have highlighted the pre-requisites a bit more myself.

Honestly, I was more than a little surprised to see the installation of SQL Express in here.  Again, it was a very short coverage, and all in an effort to get access to the AdventureWorks database.  (I’ve never understood the fascination with AdventureWorks myself.)  Of all the pieces covered in this e-Book, I seriously have to question this one.

PowerPivot connects to Access databases, whether Access is installed or not, so that would have been a far easier route to demonstrate connecting to databases in my opinion.  Yes, I understand that corporate users don’t like Access, but that’s not the point.  Using an Access file requires no extra software be installed, unlike SQL Express, and therefore leaves no unneeded software on your machine once you are finished with the e-Book subject.  To my mind, it would have made much more sense to provide a downloadable Access sample database, or even push the user to download data from an online feed that didn’t require SQL Express installation.

Back to the actual PowerPivot specific stuff though… The Author’s approach to pulling in tables was quite interesting to me; he connects to the database and pulls in a whole bunch of them… way more than he needs.  I could criticize that, but I won’t, as that is EXACTLY how an Excel pro builds a PowerPivot solution.  Suck in as much data as possible, and then figure out what you need to link to get what you want.  The reason why this is interesting to me is that I had a conversation at the last MVP summit with some SQL MVP’s and they were horrified at this development approach.  SQL pros explore data first, publish as little as possible, so that the solutions are optimized from the beginning.  Excel people generally only optimize when their file is too big to email, or when it gets so slow they can’t stand waiting for it to refresh any more.

Cleaning up these unused columns was covered, although to my mind there were some key points that probably should have been made.  I would HIGHLY recommend that before any users starts pruning columns from their workbook that they save it first.  Then I’d refresh all of my pivots every 2-3 columns I remove.  Yes, it’s painful.  Yes, PowerPivot is slow to refresh.  But the first day you accidentally delete a column that has a dependency and catch it, you’ll be thankful.  Rebuilding PowerPivot model logic sucks.  (Trust me, I’ve been there.)

The only true technical issue I found in the book is related to the final statements regarding version compatibility issues.  The Author states that “… Excel files created with the older version of PowerPivot will be accessible in the new version…”  While that can be argued to as technically true, it is quite misleading.  PowerPivot files created in 2010 must be upgraded to be used in Excel 2013, and that writes permanent changes to the model structure, preventing it from being used in 2010 again.  I think that the author should have been much clearer in this regard as mixed version Offices will find no joy when trying to share/co-author PowerPivot files.

Overall, the e-Book gives a fairly decent intro level coverage to the topic.  It starts assuming no PowerPivot knowledge at all, and I can certainly see many things in here that remind me of the way I navigated my way through the basics of the PowerPivot learning curve.

If you’ve never used PowerPivot before, this e-book will walk you through some of the basics to get you up and running.  But be aware that PowerPivot is not easy, and you’re very quickly going to require a more in depth book to create good models, let alone master DAX (the real power in PowerPivot.)

If you have used PowerPivot, created a relationship between two tables and based a PivotTable off the related fields, then this book wont’ do anything for you.

To buy directly from PackT, you can do so from the following link:  http://link.packtpub.com/MQIfoM

Giving PowerPivot a Keyboard Shortcut

When I was down at Microsoft for the MVP Summit, I suggested that, as the “other most powerful part of Excel” that PowerPivot, like the Visual Basic Editor, should be given it’s own keyboard shortcut.  Since Alt + F12 is currently empty, and since it’s right next to the VBE’s Alt + F11 shortcut, it only seems logical that Alt + F12 would make a logical choice.

Well, I got tired of waiting… not that I ever expected they’d release a patch for that or anything.  So I up a macro to do it.  It’s pretty short, works with Excel 2010 and 2013, and can be stored in the Personal Macro Workbook.*

If you know your macros, or you already have code in your personal workbook, then add the Workbook_Open line and the Sub OpenPowerPivot to your project.  But if you’ve never written a macro, or you’ve never used the personal macro workbook, here’s how you do it:

  • Expose the Developer tab
  • Click on “Record New Macro”
  • Choose to store it in the Personal Macro Workbook.  (That will create your personal macro workbook.)
  • Stop recording
  • Press Alt + F11
  • Find VBAProject(Personal.xlsb) in the Project Explorer.  (The project explorer is the treeview on the left.  If it’s not showing, press CTRL+R to display it.)
  • Double click the ThisWorkbook module inside it
  • Paste the following code in the code pane:

[vb]Private Sub Workbook_Open()
Application.OnKey “%{F12}”, “OpenPowerPivot”
End Sub

  • Now open the Modules folder and double click Module 1
  • Replace the code in there with this:

Sub OpenPowerPivot()
On Error Resume Next

Select Case Val(Application.Version)
Case Is = 14
Application.SendKeys “%GY2″
Case Is > 14
Application.SendKeys “%BM”
End Select

On Error GoTo 0
End Sub[/vb]

  • Close the Visual Basic Editor
  • Close Excel and say Yes when prompted to save changes to the Personal Macro Workbook
  • Re-open Excel and Press Alt+F12

Isn’t that just the height of lazy?  (I mean efficient!)  Smile

*Just a quick note here… if you float back and forth between Excel 2010 and Excel 2013 on the same machine, Excel locks the personal macro workbook for editing when you open the first Excel instance.  So if you open a new copy of Excel, you’ll get a prompt.  Just say okay though, and it will still work fine.

PowerPivot Online Course

I don’t think it’s a secret to anyone who reads this blog that I’m a big fan of PowerPivot.  It’s got some huge capabilities, and I’m convinced that it will be a game changer in the BI landscape.

The challenge though, is that PowerPivot isn’t just a “pick it up, it’s easy” kind of thing.  Take it from an Excel pro, you’re going to need some help to get up to speed with it and really make it sing.  Well good news… Chandoo is releasing the first online PowerPivot course, and registration starts now!

Here’s the details of what Chandoo is going to cover:

What is in this course:
Power Pivot, an Excel add-in makes it easy to connect, analyze & visualize massive amounts of data. This course aims to teach you how to use Power Pivot to analyze data, create advanced reports & prepare dashboards all from the familiar Excel interface. This is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.

Who should sign up for this course?
This course is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.
Please note that you should be familiar with Excel & Pivot Tables and running at least Excel 2010 to enjoy this course.

Important Dates:
- Course registration opens on – 6 Feb 2013
- Registration closes on – 15 Feb 2013
- Classes begin on – 18 Feb 2013
- Classes end on – 31 May 2013
- Online access is valid until – 18 August 2013

The total cost is $247, and you also get a bonus copy of Rob’s DAX eBook (which you’re going to need).

Chandoo is no stranger to online training, having run his online Excel School for some time now.  But what makes this one even better is that, not only does it cover all the awesome content you need to get up to speed, it also includes a couple of guest lectures by some pretty cool people.  One is Rob Collie of www.powerpivotpro.com and the other is… me!  Smile(I’m really looking forward to being a part of this!)

So what are you waiting for?  Click the image below to sign up!

PowerPivot And DAX Just Got A Whole Lot Easier…

As many readers of this blog will know, I am a huge fan of PowerPivot, and honestly believe that this is THE most important feature to hit Excel since VBA was introduced to the product. And anyone who has ever taken a course from me knows that VBA is the greatest thing to be added to Excel since the grid was mapped into rows and columns.

For those who don’t know, PowerPivot was a free add-in to Microsoft Excel 2010, and is now baked into the Excel 2013 release. It basically allows us to pull data from different sources (multiple databases, text files, web data feeds, excel worksheets and more) into a separate layer of our file, and create relationships between the resulting tables. Basically you can aggregate data from a bunch of different sources and EASILY aggregate it into your very own business intelligence engine.

The amazingly cool part is that it’s not hard to do this. With a little knowledge about relational data (and I mean a little), and knowledge of text functions like these, you can build the columns necessary to relate your data. From there you create the table relationship (PowerPivot even tells you if you try to build it backwards), and you’re off to the races.

The results of this are, quite frankly, groundbreaking. You can then build PivotTables off this data: PivotTables that are sourced from multiple tables at once. No more massive VLOOKUP tables to build one huge data table with everything. It’s simply no longer necessary. And with the variety of sources, you can even pull in and start adding items to your pivots that you never thought of before, like the weather for example. Does your corporate database have that? I doubt it, but who cares? Just source a weather feed from the internet, relate the dates to the dates in your sales table and presto! You can now see how sales were on the sunny days in a month vs the cloudy ones.

It’s amazingly easy to get some cool stuff out of PowerPivot… at least… to a point. To over-simplify things, I’d say that PowerPivot allows you to do four overall things:

  1. You can source and relate tables to build your own aggregated data source (a mini BI database if you like.)
  2. You can build PivotTables pulling in fields from multiple tables.
  3. You can build OLAP formulas, allowing you to pull data for a specific element right into the worksheet, without building a whole PivotTable to do it.
  4. You can build super duper wickedly complex and powerful calculated fields for your PivotTables using DAX.

To me, the first three were easy. As an Excel pro with a bit of relational database knowledge, I’ve been able to work through the first 3 over the past couple of years with very little in the way of roadblocks. And by doing so, I’ve built some amazingly cool things for our company. Yet despite this, I’ve always been aware that the real piece de resistance, the part that really makes things sing in PowerPivot is DAX.

What the heck is DAX? It stands for Data Analysis eXpressions, and is the formula language that you use to create what PivotTable users know as “Calculated Fields”. It is unbelievably powerful but, without tutelage, it is unbelievably hard. For almost two years I’ve been struggling to understand DAX.

That ended this past week.

Last week I bought a bought Rob Collie’s new book “DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX”. When you are ready to take the DAX journey, you MUST have this book.
Rob’s writing style is identical to that you’ll find on his blog at http://www.powerpivotpro.com/. In fact, the only difference is that he doesn’t fill up any real estate with movie quotes. :) What you do get is a huge amount of insightful information.

I can honestly say that, despite being a fairly accomplished Excel pro, I have been unable to wrap my arms around DAX. But that is over. Four days of working through this book with my own data and I am now making some damn cool stuff that eluded me before.

What I love about this book is that Rob sat down and carefully thought through how an Excel pro should learn DAX, then started at the beginning. It progresses logically, is very easy to read, and actually gets you there. Yes, you need to practice, but weren’t you going to do that anyway? And when you practice…

There’s something really important between these pages that I doubt you’ll ever see in the advertising. With any programming language (yes, Excel formulas fit that bill, as do DAX formulas) there are two critical things to learn. The first is Syntax (how you do something), and the second is how to debug something. Rob gives you both, which is critical on the path to mastery. He has some nice little charts that explain how filters are applied to the CALCULATE function, (which is like SUMIF on steroids.) With those charts, you can actually follow the steps to figure out exactly why your measure is not doing what you want. (Trust me, it was doing this that actually helped me finally get that Eureka moment that I’ve been looking for!)

Four days. I’ve gone from not being able to make anything but the most simple DAX measure to the point where I can create some pretty cool CALCULATE functions (my new best friend), with nested date/time intelligent functions as well. I’ve been pursuing that for TWO YEARS!

In an hour this morning, I was able to build a nice little “Server Contest” dashboard for our food and beverage department. It has slicers to drive down into sales areas, major groups (entrees vs desserts), and dates. And it will correctly report the week-to-date, month-to-date and prior weekly sales in units, as at the selected point in time. (Previously, I was able to get the “effective date” to work, and my “current month” to work, but I could never figure out how to filter things to give me the correct month to date number if I clicked an earlier date. It always kept returning the real month to date, rather than the effective month to date.)

The dashboard is a beautiful thing (to us anyway), that we’ve never been able to produce until today:

11-22-2012 10-38-01 PM

After struggling with DAX for so long, I think it’s fair to say that I wouldn’t have been able to break through this without Rob’s help. I certainly wouldn’t have been able to build the report above in such a short time.

If you’re struggling with DAX, or just getting ready to start the journey get his book. You owe it to yourself.

You can pick it up from Amazon.ca (for all my Canadian colleagues) by clicking here. If you’d prefer to deal with Amazon.com, you can get it by clicking here. And/or from other routes as detailed on Rob’s Website.