Teaching DAX, Data Modeling, Power Query

Welcome back readers! Today I want to talk to you about a few the reasons why I still like to teach DAX in EXCEL. A HUGE part of our jobs here at PowerPivotPro is teaching analysts & data junkies throughout the world how to use the best features of Excel and Power BI. Teaching is in our blood, we love doing it, and frankly we’re great at it (I definitely know this is last part is true because even my mom told me).

We teach a wide variety of classes here at PowerPivot Pro, including:

The training we MOST often give is our two day foundations course. Covering a broad stroke of the important topics like Data Models, DAX, and Power Query. Now you might think these topics could be taught just as easily in EITHER Excel or Power BI right?? Sadly…no. There are some key features and functionality that the Excel environment possesses that makes it a FANTASTIC learning environment for people unfamiliar with data modeling. Even for clients that request only Power BI training, we STILL RECOMMEND Excel for learning the fundamentals on Day One.

A common question I often get from students is why we don’t do the entire class in Power BI? I usually outline this at the beginning of each class, but an idea popped into my head the other day. Why not write a blog post about these reasons, and save myself valuable class time I could now give BACK to actual learning! So here I am today writing this post so all my students (and my readers!) can get a clearer picture as to why we teach in Excel. Smile

Two Steps Forward, One Step Back (In Features)

Before I start airing out my dirty laundry, I want to start this post by saying I LOVE POWER BI! It’s my favorite reporting tool today, and every months’ release update excites me like a kid in a candy store. Power BI really is a great tool, and it gets better and better every month. Unfortunately it’s just painful to teach in… Now…with that being said I’d like keep things short and sweet, so I’ll boil down my grievances to just my two primary features lacking in Power BI Vs. Excel.

Feature #1: Data Model Filtering

I cannot emphasize enough how important and vital this feature is to us as BI Consultants, and ESPECIALLY as trainers! Excel has had Data Model table filtering since DAY ONE back in Excel 2010 when PowerPivot was first released. However, since it’s inception there has never been an equivalent option in Power BI Desktop. Let’s take a look below and see exactly what I’m referring to between the two products.

Excel Data Model table filtering on Order Date

Excel Data Model Table Filtering

It’s beautiful isn’t it, like looking at a sunset… What, you don’t see a sunset? Maybe it’s just me then. Either way this feature is AWESOME to have in Excel. When you’re building out a model and importing data, it’s essential to be able to quickly look at the tables and filter down to relevant data. More importantly, when you’re writing DAX measures and it doesn’t work (which WILL happen), it’s MUCH easier to come into the data model and reproduce the filters that your DAX calculation is applying, as opposed to creating a pivot table to see the data.

Even our ICONIC REFERENCE CARD utilizes the table filtering options in Excel when troubleshooting issues! Now that we’ve seen what this looks like in Excel, let’s see how this environment looks in Power BI Desktop.

Power BI Data Perspective without table filtering

Power BI Desktop Data Model Table Filtering

Data Filtering in Power BI is SO IMPORTANT TO US that Rob even had a recent post asking our beloved readers (you!) to VOTE on this feature. One beautiful thing about Power BI is that Microsoft has a website dedicated to user submitted feature ideas, which then can be voted on by the world at large. Before Rob’s post Power BI Desktop table filtering had 50 votes… As of the time of writing this article it now has 806 votes and counting! If you haven’t already PLEASE take a moment and add some votes to this idea, and we can keep our collective fingers crossed that one day this will get added. Here’s the original link to the Table Filtering Ideas Page.

Feature #2: DAX Editor Window

What I LOVE about the DAX Editor Window in Excel, is that it gives us unlimited space to write our calculations. When writing (and ESPECIALLY debugging) DAX, it’s great to have large real-estate space for writing code. Having all the settings and formatting options in one place is also just easy and convenient. Excel technically has three ways to create or edit DAX measures, but I ALWAYS use this specific editor window, no exceptions. It can be found in the PowerPivot ribbon at top, under the Measures button. Let’s take a look at this marvel of engineering and see what all the fuss is about.

Excel DAX Measures Editor Window, in all it’s glory

Excel DAX Editor Popup Window

The DAX Editor Window is also your one-stop-shop for every option or setting needed to write DAX measures. In fact, BESIDES the editor window itself (which Power BI Desktop doesn’t have). There are ALSO three more features within this editor that are not in Power BI Desktop. So like a white guy on prom night, I’m going to break these down for you.

Excel DAX Editor Window Feature 1: Description Field

Excel DAX Editor Popup Window Description

In Excel, the DAX Editor Window lets you add a text description above your measure. A very handy feature for leaving yourself notes when revisiting your workbook down the road. Now some of you know that it IS POSSIBLE to add in-line-comments in DAX. Chris Webb has a great article that discusses this at length here. However this requires you to know the correct syntax when adding those comments so your measure doesn’t break, and adds additional lines and noise inside of your measure.

Excel DAX Editor Window Feature 2: Check Formula Button

Excel DAX Editor Popup Window Check Formula

The Check Formula button is an easily overlooked feature. However, before I hit ok and save my DAX Measure I ALWAYS press this button first! But what exactly does this button do? Well it’s checking your DAX syntax and making sure everything is written correctly. Now you COULD simply hit OK after writing your DAX and see if it errors, this is true. However when doing that your data model is actually attempting to calculate the DAX measure in the background as well. Not a big deal with a few thousand rows, but if you’re working with a model that has millions of rows then that could take a long time for it to calculate, and then error!

The smart thing to do is to check your DAX syntax using the Check Formula button BEFORE hitting ok. Checking your DAX syntax doesn’t run your calculation and returns a rewarding No errors in formula output if everything was written correctly. Such a simple thing that can save you SO MUCH TIME! I highly recommend as a best practice to always use this before hitting ok and saving your measures, you’ll thank me later. Smile

Excel DAX Editor Window Feature 3: Resizing text

Excel DAX Editor Popup Window Text Resize

The ability to increase text size might be the SINGLE MOST IMPORTANT FEATURE of the DAX Editor Window! During model development or while teaching, it’s incredibly helpful to be able to zoom in on the text you’re typing in. For those of us on high resolution screens or ESPECIALLY when instructing a class with a projector, this is an absolute must have! I’ll even play devil’s advocate again, and acknowledge that there is software that let’s you zoom in on your screen. However not everyone has that, and honestly should we require separate software to be installed on our machines to accomplish this?

For those of you wondering HOW TO increase the text size, hold the control key and scroll forward/backward with your mouse. We also mention how to do that in our handy dandy reference card. If you haven’t actually seen that yet, please take a look, the reference card is INCREDIBLY useful.  Now that we’ve seen all the awesome bells and whistles Excel has, let’s take a look at the DAX development environment in Power BI Desktop.

Power BI Desktop DAX Editor Window

Power BI Desktop DAX Editor Window

In Power BI Desktop we are limited to writing ALL DAX CALCULATIONS using only the formulas bar. I’d like to start this section on a high note and say one thing that ROCKS in Power BI is the color formatting. In Power BI Desktop anytime you’re referencing another DAX measure it colors that text purple. Super helpful to further separate column vs. measures references within a calculation, Excel doesn’t do this! We even have a blog post written that talks about this issue, which is why we ALWAYS recommend using a Table Name & Column Name reference (E.g. TableName[ColumnName]) in your calculations. We do this to help differentiate it from a DAX measure reference (E.g. [Measure Name]), otherwise they look really similar.

Unfortunately neither the DAX Editor Window or any of the features mentioned are available in Power BI Desktop. I could live without the separate editor window, or the description box for notes, neither of those are essential. My BIGGEST grievance is the lack of zoom functionality on the text. I won’t claim to know why it was omitted in the software, or why it hasn’t been added yet. However, as a developer and user I miss it dearly. If you agree with me that the editor window SHOULD BE ADDED to Power BI Desktop, please vote for that idea here! At the end of the day though, it does legitimately come down to personal preference!

Among all the industry professionals I know, there are equal handfuls of them that prefer to teach DAX in Excel, and some that prefer Power BI, with valid reasons for both! I even had a discussion about this topic with Ken Puls (Excel Guru Blog), an industry professional, author, and instructor. He had some valid points around why teaching in Power BI can be easier (at least with DAX). Here’s what he had to say:

The environment in Excel is nicer, for sure. However, the issue is that I have to teach Excel people how to un-learn Excel formulas first, then teach DAX. My issue is all around the experience they bring from their Excel career.  If they come to power BI, they don’t have pre-conceived notions. If they are in Excel, they expect it should work like Excel always has.

~Ken Puls

The point is, there’s no actual right answer to to the question of teaching in Excel Vs. Power BI. But I think most industry professionals would agree that if these Excel features were also in Power BI, it WOULD be a better teaching environment! Well that’s it for today’s post folks. I hope this helped shed some light on why Excel is STILL an essential teaching tool. Until next time P3 Nation!

  Subscribe to PowerPivotPro!
X

Subscribe

Reid Havens

Reid Havens is a Principal Consultant for PowerPivotPro and the Owner of Havens Consulting Inc. His main goal is to collaborate with individuals and organizations by helping them analyze data to understand their business. Using his formal backgrounds in technology and organizational management Reid has worked with various local and multi-national companies. He is also an adjunct professor at Bellevue College, guest lectures at the University of Washington, and provides corporate trainings centered around teaching Business Intelligence, Reporting, & Design. 

This Post Has 26 Comments

  1. 100% agree Reid. The lack of zoom is a killer.
    Also I like the natural progression and familiarity of the Excel environment for transitioning Excel users to PowerBI

    btw Windows key and + allows anyone to zoom 🙂

  2. For consistencies sake both interfaces the same would be helpful for sure. I will humbly take exception on the documentation/comments need with you. DAX is coding and I could argue vlookup not so much. The lack of an easy method to document ones DAX code is a bad idea IMHO. For instance Matt writes some excellent posts on DAX process and writing formulae, his documentation is the blog post which is fine. But in a real world situation where my company has decided to grow self service BI people and send them to training and such at some point they are going to move on, just the way it works. How am I supposed to now maintain their code? SQL and other programming languages I have tried to use are very big on commenting code just for this reason, I do not think DAX or M should be any different. So commenting in your code is not noise, certainly not the level of noise that will be heard when the new “guy/gal” tries to deal with your code and you are nowhere to be found – primal screen levels possible methinks.

    1. You provide some good points Carl. I want to clarify that I’m not at all against taking notes as I know they can be extremely valuable. Whether your notes are in a description box or in-line, taking notes anywhere is preferred to no notes at all. I do appreciate that Excel at least gives you a few options of where to put them, compared to Power BI which just limits you to in-line.

      1. I appreciate that Reid and apologize if my comments inferred you were not a document fan. I do think we as a community need to find some hopefully standard way to document our code and insist it is best practice. I have been thinking about this for several years and I have no solution so I hope someone much smarter (basically everyone else) comes up with something.

        1. Absolutely and no apologies needed. I always welcome feedback and love posts like these which are more subjective and elicit a lot of comments. I agree with you that as a community a best practice for documentation would be super helpful! Wishful thinking for someday. 🙂

  3. Thanks for the article! We’ve (PBI Desktop team) been talking about these things for ages. The spike in “ideas” votes helps a TON in getting this stuff prioritized. I’ll share this article with the rest of the team and see what we can do! In the meantime, vote, vote, vote!

    1. I’m super happy to see this got noticed by you all! I always tell anyone reading or in our training’s to please vote on what is important to them in the Ideas site. Thanks for sharing it internally as well, because we love your product and love to see it improve every month. 🙂

  4. Not to mention that developing in Excel is faster (by far) and you lack the wonderful Powerpivot Utilities add-in in Power BI

  5. Great points (and yes, I voted for the table filtering option request for Power BI Desktop when Rob announced it). Thanks! I would like to add that when writing DAX code, especially for longer measures, the daxformatter.com tool is a SUPER USEFUL utility! I usually write or paste my code there, run the syntax checker and formatter, then copy/paste the beautified DAX code into either Power Pivot (Excel) or Power BI Desktop. The syntax checker has helped me a lot to get the code right.

  6. Mmmm…. in order to get around the oversight of the table filtering feature in PBI I usually just click the ‘Edit Queries’ option on the Home Tab and then just filter whatever table I am interested in there… So that’s never really bugged me too much. However, I strongly agree that the lack of zoom in PBI is a real killer on the eyes and I would certain vote for this feature if someone raised it…

    1. Hi Craig, using the query editor is certainly a workaround to see “filtered” table data. However it requires adding (and deleting) applied steps to do so. Sometimes I forget to delete the step which then loads the data wrong, and I have to go back and fix it, etc… So it does add a bit more work (for me) doing it that way. There is a place to vote for a DAX editor window here too.

  7. Hello Reid – Denton here.

    Next question: What are the advantages to editing DAX in Analysis Services (either Azure or on-premises Tabular) vs. Power BI vs. Excel?

    My experience has been that DAX in Analysis Services can instantly feed Power BI, or Excel as a Graphical User Interface. Write the DAX, hit refresh and whammo – There’s the formula in both environments.

    I have found that Excel is FAR superior as a Front End for Validating DAX formulas. As we all know you will never get the DAX right the first time. Heaven forbid it takes 5 tries, or half a day for some really hard stuff. I would always prefer to use Excel as the visualization layer vs. Power BI because a spreadsheet/pivot-table provides an immediate Table view for spot checking.

    1. Great question Denton. I will say I don’t develop often in Visual Studio unless I have to, but I don’t find it an easier experience (personally) for developing DAX. Tabular models are great in that multiple sources can point to it (and now PowerBI Service does that too).

  8. Thanks Reid for article. I find that in Excel one of best enhancements to PP/DAX has been that the Measures box now displays the measure formulas so you can quickly see if all are formatted correctly and what they do. Instead of just staring at a box with Measure name only. I love this feature. Really helps with documentation. I have found though that not every instance of Excel has this feature yet. Its frustrating when working on Client’s instances of Excel PP and this feature is not available to them yet.

    1. I completely agree, the recent edition to the measures box window where you can actually see the DAX code next to each query is really amazing.

  9. Calculated Tables (under Modeling menu) – with DISTINCT has helped me to debug / see partial results of a calculation …kind of like Excel’s table filtering (again …kind of like)

  10. Good article and well-explained. However… “real-estate space” – haha really? That’s 1) kinda redundant, and 2) does not make any sense. Real estate is comprised of land and buildings, nothing else. Other uses of this term amount to silly wrong-context corporate speak like “bandwidth” and “champion” and the like.

    1. Hi there, so if you check Merriam-Webster’s definition of “real-estate” their second definition has it defined as “space, or capacity” with an example of “the limited real estate on hard drives”. So it’s certainly not an incorrect use of the definition of space or capacity, like I was attempting to do with it’s use in this article. Though I apologize if that analogy didn’t make sense to you, I am glad you enjoyed the article overall. 🙂

      Reid

Leave a Comment or Question