Blog

Hey Excel users, anyone remember this?

Image

Yes, it’s the now defunct “Lookup Wizard” which, according the MSDN Library entry of January 1996, “…helps Excel users create lookup formulas quickly. Even expert users will find the Wizard’s lookup formulas useful in advanced spreadsheet and application development projects”

Well, this was obviously not the case or else this “useful” wizard would still be with us today.  What “expert” Excel user can’t construct a simple Vlookup or even use the fiddly Index/Match combo?

So, it’s with the same sense of foreboding that I’ve been working through the Quick Measures supplied by the recent releases of Power BI Desktop.  The requirement for these Quick Measures is to give users a list of pre-done DAX solutions, presumably because Microsoft feel that their everyday users cannot get to grips with DAX, particularly with respect to creating measures.

They do have a point. One of my course attendees once told me that he had used DAX enough to know that “it’s frustratingly different from Excel”.

Beware the Quick Wizard!

Having trained extensively in Power Pivot, Power BI and DAX, I have a number of issues with these Quick Measures.  I’ve never been keen on anything called a “wizard” or anything prefixed with the word “quick” because I feel I’m quite capable, thank you, of doing it myself, as quick as I require.  More importantly, in doing so I retain control over what I’m doing and learn new skills along the way.

You may think I’m the sort of person that whittles their own spoons from a log, or knits their own Pivot Table, but I’m not.  There are good reasons to avoid using Power BI’s Quick Measures and to learn DAX instead.

One size fits all?

Firstly, thinking about “useful” wizards, how useful are Quick Measures really going to be? Because of the limitless variety of how data can prevent itself there are no amount of Quick Measures that can ever come anywhere near the requirements of most data.  All data models are unique and exceptional and there is no “one-size fits all” solution.  Just picking a few expressions (22 on the most recent count) is a drop in the ocean as compared to the variety of expression that would be required in most data models.

A good way to learn DAX?

The problem here is that the DAX expressions in the Quick Measures must be unimaginably complex and convoluted to work across the diversity of data that may present itself in any data model.  So, for example, there is the ubiquitous use of the KEEPFILTERS function which, according to Marco Russo in his Definitive Guide to DAX, “in the ranking of complex DAX functions, the KEEPFILTERS function has a very good position”.  Not such a good place to start learning DAX then!

Monkey see Monkey do?

In that case some people might say “don’t worry about understanding the expression, just edit the table and column names to be applicable to your data model”. But the complexity of the expressions makes it difficult, if not impossible for non-DAX users to simply edit them in a “Monkey see Monkey do” way, particularly when variables are added into the melting pot.

What, no Date Table?

My biggest concern, however, is attempting to negate the use of the Date Table with this warning in the Quick Measure; “Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy.”

One of the biggest benefits of moving across to Power BI and using DAX is the use of the Time Intelligence functions that allow you to analyse your data across any timeframe you care to mention.  The use of the Date Table is mandatory in most of these expressions.  My worry would be that if you started out by using the Quick Measure Time Intelligence, you would at some point come up against a brick wall when you want to move onto more complex calculations e.g. calculating new and returning customers.

Quick and Dirty

So at best, Quick Measures are a quick fix that just by chance may work for your data.  At worst, they are an ineffectual and short-term solution that won’t stand the test of time and stops the user from learning more. In the worst case Quick Measures present DAX as unnecessarily complex and inflexible, when in fact quite the reverse is true!

Here at Burningsuit, we specialise in Power BI Training, particularly  Microsoft Power BI Service and also run a 1-day DAX course for complete beginners.

To all Excel users, I would say, “Why not bite the bullet, take the challenge, do something new, get started on learning DAX!”