Power BI Quick Measures is a Game Changer

Every few months a feature is added to Power BI that gets my attention and really seems to make  difference.  Quick Measures is one such feature that has my attention.  As a long-time SQL Server Analysis Services practitioner, I subscribe to the belief that anything that you want to calculate should be created as a measure.  The counter argument is that, just like in Excel, every numeric column can be assigned an aggregate function in the “Summarize By” property which makes the column behave like a simple measure.  I fought this at first and then learned to embrace the default behavior because that’s what most self-service BI users will expect.  These “implicit measures” work just fine in Power BI report visuals.  The catch is that client tools like Excel can only use real measures instead of numeric columns, to perform calculations.  In serious SSAS and Power BI solutions I still create measures for any numeric values that should be aggregated.  Here’s a peek at the new Quick measures dialog:

image

For my training classes and workshops, I have been demonstrating the Quick Calcs feature.  In a workshop yesterday, I went to find it and the feature wasn’t there.  This is the screenshot from my slide deck.

image

Change is inevitable but it can be hard to keep-up.  The Quick Calcs feature doesn’t generate any code and it doesn’t create a real measure (so it won’t work if I use Excel as a front-end for the Power BI model.)

I had added the new Quick Measures feature to Power BI Desktop in the Options/Preview page.  This, apparently disables Quick Calcs and enables Quick Measures.  Although it flustered me me for a minute in front of an audience, I found this to be welcome news.  So, what is Quick Measures?  It’s a DAX calculation generator that automatically writes useful measures.  Here’s how it works…  Start by right-clicking or clicking on the ellipsis for a numeric column in the Field list and choose Quick measure…

The Quick measures dialog prompts for the necessary fields, which might be different for each calculation.  The tool generates appropriately formatted DAX calculations.  It even includes conditions to raise errors if used in the wrong context.  There are currently 19 different calculation variations that the tool will generate.  Following are two examples.  Creating a Quick measure from my [Flights] measure and choosing the Airline field for categorization produces this calculation:

Average Flights per Airline =
AVERAGEX(
    KEEPFILTERS(VALUES(‘Airlines'[Airline])),
    CALCULATE(‘Airline Performance'[Flights])
)

I’m not a fan of this naming convention.  It’s silly to begin a measure name with “Average” or “Sum”.  Users struggle enough to find what they need in the field list.  As a rule-of-thumb, name measure with the most important information first.  In this case, perhaps “Flights Avg per Airline” would be a better choice.  If there were several variations of the “Flights” measure, they should be listed together in the field list.

This one calculates the average year-to-date departure delay:

DepDelay YTD =
IF(
    ISFILTERED(‘Flight Date'[Flight Date]),
    ERROR(“Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy”),
    TOTALYTD(
        AVERAGE(‘Airline Performance'[DepDelay]),
        ‘Flight Date'[Flight Date].[Date]
    )
)

Example is often the best teacher.  Beyond the obvious benefit of creating useful calculations, this feature provide several excellent examples that you can use to learn DAX function syntax and usage.  For me, this will be the most value.  I can easily write basic calculations but don’t always remember to use filtering and context preservation functions is ISFILTERED() or KEEPFILTERS(). 

4 thoughts on “Power BI Quick Measures is a Game Changer

  1. Hey Paul – nice blog post… and very good point about a training opportunity (re: ISFILTERED/KEEPFILTERS)

    i’m still straddling the fence on how I feel about enabling end users to create custom measures against an Analysis Services database. Sure it speeds up the value delivery chain, but it also opens the door for users to create nasty performance problems that can bleed into the performance of other users (e.g. a poorly written DAX measure that results is a super-large materialization)… i think a good compromise would allow admins to control which users have this ability.

    Like

    • No doubt, Bill. I think self-service is a quandary with bad news at both extreme ends of the spectrum and goodness somewhere in the middle. How do we give users tools that empower them to be self-sufficient and creative without making a huge mess? Even though we all have ideas and stories to tell about successes a failures in the journey to achieve the holy grail of self-service BI, one size doesn’t fit all. I do like the idea of there being a review and vetting process for user-defined measures. In my mind, this would also entail transitioning them from Power BI Desktop data models to SSAS Tabular. That’s what we plan to do with an enterprise client I’m currently working with.

      Like

  2. This is probably built for IT Developers, it is way too complicated for a normal end user. I don’t see it as game changer at all, not in its current incarnation.

    Like

    • Josh, please share your specific thoughts about your experience with this feature. What do you find confusing or complicated about the Quick measures feature for business users? How could it be improved? The goal was to generate common measure without hand-writing code. Don’t you think they have achieved this? The product team welcome and respond to feedback and yours would be valued if you can explain.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s