I am well under way in my career as a Power Pivot Consultant and Trainer. And I have to say (now that I have delivered a number of Rob’s “PowerPivot for Excel” training classes) that I am finding delivering training to be one of the most rewarding things I do.
It occurred to me recently that people like me (and also Rob, Avi, Scott), that train users in PowerPivot are able to glean useful insights into the way people learn (and incorrectly learn) PowerPivot. Today I am going to share with you 5 common mistakes that I have personally observed – maybe you will identify yourself in some of these things, or maybe you will confirm that you are doing just great. Either way, it is worth a read to either discover a gap or confirm your skill.
But first: I have trained 2 general types of students
I have found there are 2 groups of students that sign up for my PowerPivot for Excel classes. There are students that are very new, very green (see what I did there – green, get it?) and are using the class to get started. They come in with very little knowledge about PowerPivot, but enough to know that this could be something awesome. In the second group are students who have a reasonable amount of PowerPivot experience under their belt but realise there is more to it. What is a “reasonable amount” of course can vary, but I would classify these people as “active” for 6-26 weeks with a total number of “invested hours” in the vicinity of 10 – 60 hours or so. Often they are struggling to move forward, and this post covers the main reasons why.
I always ask my students to rank their knowledge on a scale of 0 to 10, and they normally rank themselves 0 (newbies) or about 3 or 4.
What has really (pleasantly) surprised me is that both groups of students seem to walk away with a similar positive level of satisfaction with what they have learned. This is a bit curious because I am teaching the same content to people with no experience with PowerPivot, and also to those with many hours of experience. But what I have observed is that self taught students in my courses have often missed some of the fundamental principles, or not fully understood certain key concepts. And the newbies come with an open mind like a sponge to learn, and they generally walk away with a solid foundation from which to continue to learn, but clearly not the experience of the more experienced students. The biggest risk for the newbies is failure to practice what they have learnt. I don’t see this in the more experienced group because they tend to want to get back to work immediately and apply their new found knowledge to solve problems they have known about but couldn’t solve before. I have seen the light bulb turn on many times with a student saying “I just realised I’ve been doing it all wrong”!
The 5 Common Mistakes in the Self Taught Group
One feature of being self taught, is that they often don’t get all the right information the first time. Don’t get me wrong – I think it is fantastic that so many people are teaching themselves. And if you buy a good book (Rob/Avi’s book or my book) and apply the learnings, then you are off to a great start. But many people find that supplementing self learning with instructor lead coaching and tuition can pay back big dividends.
Here are my top 5 most common mistakes I observe with self taught students. If you associate with any of these things, then do yourself a favour and seek out some new knowledge to help yourself become more powerful and effective with PowerPivot.
1. Too many custom columns in Data tables
This is by far the most common sin – there is daylight between this one and the next one. I think the issue is that so many (most?) of us come from an Excel world, certainly the self service clients that I am engaged with in my business do. In the past, us Excel professionals always had to bring all our data into a single monolithic table prior to creating a pivot table, normally using vlookup or index/match as our tool of choice. And it is a hard habit to break because we feel comfortable in the world of creating formulae in a table (ie custom column).
Custom Columns are a LOT like Excel, and measures are NOT! So we gravitate to custom columns like a magnet.
If this is you, then please do yourself a favour and stop already! My best advice to Excel professionals learning PowerPivot is don’t ever ever use a custom column in a data table unless it is technically not possible to do the same thing as a measure/calculated field. Seek out the skills to learn to create the measures you need instead. If you need to put the results of your DAX on the Rows, Columns, Filter or Slicer, then Calculated Columns are the only choice (but it is still better to bring them in from a source DB if possible). Calculated columns are generally ok in your lookup tables too, however 99.9% of data table DAX can/should be done as a measure/calculated field. Alright, I am exaggerating, it is more like 99.8%, but you get the point.
If you are not writing these DAX formulae in a ratio (Measures to Calculated Columns) of 999 to 1, then you are a candidate for some remedial “calculated column” behavioural modification!
So the point is this: If you a coming from an Excel background and you are writing your data table DAX in Calculated Columns, you are most likely on the wrong track. I am yet to find a single calculated column in a data table from this group that was the right approach – not 1 single column.
2. Thinking you can incrementally learn off an Excel base
In short – you can’t. Don’t fall into the trap of thinking “PowerPivot sounds like Pivot Tables, it must be the same”. I truly believe that anyone that is competent in Excel can become competent in PowerPivot, but I don’t think you can incrementally learn off your Excel heritage. PowerPivot IS incrementally learnable, but only once you get to base camp. PowerPivot is not the same as traditional Excel – it is a completely new piece of software that just happens to come bundled with Excel. You need to invest some time to learn the basics properly to have a proper foundation from which you can THEN learn incrementally. Self taught is fine, but make sure you get a good book (at a minimum) and do it right. Just because you can drive a car, doesn’t mean you operate a boat without learning some new fundamental skills – it is a bit like that.
3. Building PowerPivot Data Models like you would a Relational Database
This is a common problem I see with people that have made the journey from Excel to Access at some time in their career (but also in students that come from a relational DB background). They learnt that Access could do things they couldn’t do in Excel (in a pre-PowerPivot world) anyway. These skills are above what the average Excel user had in their arsenal, but now it can be a crutch if you don’t learn how/why PowerPivot is different. I came from this camp, and I wasted quite some time not understanding how relationships work in PowerPivot, and why they are different to Access and other relational database products. Key mistakes include importing the full snowflake schema from the DB, and missing the opportunity to structure (and understand) the difference and behaviour of “data tables” and “lookup tables”.
4. Trying to build big DAX formula in a single step
I have read a lot of books by John Walkenbach – as I am sure many of you have too. I learnt from John’s books a technique called “mega formulas” where you create individual parts of the formula piece by piece and in the end you bring it all together into 1 “mega formula”. Rob also teaches that you should build your measures up piece by piece. I actually believe there is no need to take the Walkenbach final step and build a mega formula, but instead leave your complex formulae referring to the individual intermediate measures. It is a bit like moving the elephant on your door step – the easiest way to tackle it is to chop it up into manageable pieces
The trouble with DAX – particularly when you are learning – is that there is a lot that can go wrong with a DAX formula, particularly as you are learning. If your formulae are too big and complex, it can be difficult to even find all the problems to make it work. On the other hand if you chop the problem up into manageable pieces, you can incrementally build the measure one piece at a time, and finally assemble the finished masterpiece at the end.
5. Not using tried and tested best practices
There are quite a few best practices that I see communicated in all the right places (here, SQLBI.com to name just two). I believe the 2 most important best practices for Excel users are:
1. Using proper naming conventions. Always add a table name before a column name, and never add a table name before a measure name/calculated field.
- Column reference: Table[Column Name]
- Measure Reference: [measure name]
2. The second is how you layout your tables in diagram view. I will go as far to say that if you have a SQL RDBMS heritage, then you can set up your tables how ever you like. But if you come from the world of Excel (like me and many other new disciples of PowerPivot) then you are well advised to do it the way Rob teaches. Lookup tables go at the top, data tables go to the bottom. When you layout your tables this way, us Excel folk can “visualise” the filters flowing down hill across the relationship from the one side to the many side – not the other way around.
5. (again) Thinking you can passively learn DAX
Edit 11 Nov: I am adding another big on that I missed. I can’t call it number 6 so here is 5 again! This is another mistake I made early on and I know others are doing the same thing. You simply cannot learn DAX “passively”. By that I mean by reading a book in bed, watching Rob’s videos etc and not touching the keyboard – this is simply not going to work. You need to practice, practice practice if you want to make anything you learn stick. You can practice using your own data, or you can download one of the free databases on the web like Adventure Works and use that as your data source. But you must use something and practice practice practice.
_____________________
So do you associate with any of these common learning mistakes? If so, all is not lost. If you move from “Don’t know what you don’t know” to “I know what I don’t know”, then you are on the journey to greatness. If you don’t make these mistakes, then you are already doing just great, so go you good thing (Melbourne Cup on Tuesday you see).
A question for you: What lessons did you learn “down the track” that you wish you had learnt earlier in your DAX journey?
Matt Allington is a professional Self Service BI expert, consultant and trainer based in Sydney Australia.
