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:
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.
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().