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 P3 Adaptive 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 [link removed due to 404] 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 Adaptive Nation!

0 Shares