Quick Measures in Power BI: You don’t have to write DAX!?

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

You read it right, you don’t have to write DAX. If you are calculating a year to date, running total, or every rolling average, you may be able to do it without writing a single line of DAX code. How this is possible? With Quick Measures. Quick measures are an easy way to create DAX measures. This functionality is easy but still not very known or used by many users of Power BI. I believe this is a good starting point for business users to get their way into analytics powers of Power BI. If you want to learn more about Power BI, read Power BI book, from Rookie to Rock Star.

What is Quick Measure?

A Quick Measure is a quick way to create a DAX measure! That is why it is called so! This is I believe one of the best scenarios of naming a feature, it is self-explanatory. Do you need to have a special setup to use quick measures? No. let’s see how it works.

Let’s start with a Year to Date example. You want to create a year to date calculation, but don’t know how? Here is a quick measure built for you;

In the home tab of Power BI Desktop, select New Quick Measure.

You’ll see the Quick Measure dialog which asks for the calculation and shows the list of tables and fields in the right-hand side.

Select year-to-date total from the list of calculations.

For every calculation, there will be input parameters to set. For year-to-date, the input parameters are Date field and expression as Base Value:

Drag and drop SalesAmount from list of fields in the right-hand side to the Base Value section. You’ll see it comes as Sum of SalesAmount (You can change it to other aggregations if you want to);

If you want to change the aggregation, you can do it this way;

For this example, I’ll keep the aggregation as Sum. The Date field is the next input parameter. Even if you don’t know what those parameters are, you can hover your mouse on the information button beside the parameter name and see a description about it.

For the Date field, I pass the OrderDate field from FactInternetSales. Please note that for this example, I’m using the default/built-in Date dimension in Power BI. If you want to learn what the default Date dimension in Power BI is, read my blog post here.

Now click on OK. Your DAX expression is ready!

The whole expression created by the Quick measure wizard. You did not write even a single line of code for it. Let’s now use it in a visual. For this visual, I used OrderDate, SalesAmount, and the new quick measure created named SalesAmount YTD.

You can see in the above screenshot how beautifully this measure works with no need to write DAX statements.

Quick measures are pre-defined DAX templates for the most common calculations. It is an easy way to start the analytics with DAX and Power BI with no knowledge of the DAX language.

I’m not saying that you do not need to learn DAX, you would need it eventually later in your path towards being a Power BI developer. There is no way around it, if you want to be good at doing analytics in Power BI, you need to learn DAX. All I’m saying is that if you are a business user, with zero experience in DAX, you can still start building a model and create calculations for it easily. It is obvious that for making custom changes or some complex scenarios, you do need to write DAX statement yourself, or modify the created quick measure.

Running Total

Let’s say another example is to get the running total Sales through all years. Here is what the quick measure looks like for it;

Year part of the OrderDate will be used as the field to reset the running total if the value repeats, and the sum of SalesAmount would be our expression. Here is the result;

As you can see the quick measure simply provide the output needed.

Concatenated List of Values

Another example of quick measures is the Concatenated List of values, which can be helpful to see some details about a specific category. Here is measure configuration for that;

And the output, which shows 3 Full names from each category concatenated together;

Gallery of Quick Measures

The existing list of quick measures is limited, here are some of the existing options;

However, the list is getting updated every month with the new version of Power BI Desktop. If you are interested in a quick measure that is not in the list, you can submit an idea about what you want:

There is also a Gallery of quick measures in the Power BI community website which can be a good source of extra quick measures. Usually measures will be added from that gallery into the Quick Measures list of Power BI Desktop gradually.

Misconceptions

Quick Measures work with Default or Custom Date Dimension

There is a common misconception that says Quick measures only work with the custom date dimension, not with the default one. This is not true. As you see in above it works perfectly with the default Date dimension, and it also works perfectly with a custom Date dimension too.

DAX is not Obsolete

Another misconception is saying that now that we have a gallery of quick measures, then we don’t need to learn DAX! That is not true, and would never be. You have to learn writing DAX statements because, for every business and requirement, there are always challenging part that might be different from the generic solution suggested in the quick measure. The quick measure can be a good start towards creating your analytics calculation, but it is not the final answer. You need to write DAX to get the maximum ultimate analytics power in the Power BI toolset.

Summary

Quick measures are a simple way of using pre-defined templates of common DAX calculations in Power BI. If you have a requirement that can be solved with that, you may not need to write DAX statement. Further in the path, you will need DAX for your specific business case scenarios, however, the quick measures can be a good start for stepping towards that.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

4 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *