Quick Measures and Query by Example - Excelerator BI

Quick Measures and Query by Example

2 fabulous new updates again in the April update of Power BI Desktop.  Here is my quick review so you can see how great these things are.

Quick Measures

Quick measures are designed to help you get started with DAX, without knowing any DAX!  OMG I will be out of a job!!

Once again this is a preview feature, so you need to turn it on.  Go into File\Options and Settings\Settings and turn it on as shown below.

image

After restarting, you can now right click on any column in the fields list on the right (shown as 1) and select quick measures (shown as 2).

image

From there you can select the calculation type you want on the left.

image

I selected Year to Date (shown as 1) of the SalesAmount Column (shown as 2).  There was a date field box that I figured I needed to drag my Calendar[Date] field to as shown in 3.

image

And BINGO, the following MEASURE was written for me.

image

When I added the Date column to a Table, it just worked

image

Note, I have not written any DAX here – the UI wrote it for me.  This is an incredible advancement.  And what is really important is that I can also edit the DAX if I want to modify it.  This is a BIG DEAL. 

There are lots of quick measures already and I am sure there will be a lot more to come.

Power BI Online Training

Add Column by Example

This is the other SUPER advancement this month.  Miguel has already done a great job of explaining this new feature so I am not going to reinvent the wheel. Spoiler alert:  You can add a new column and make the change that you want on a couple of rows, showing Power Query what you want to do – and it will do it for you!!

Just watch the video here.

Power Query Online Training

If you want a comprehensive lesson on how to use Power Query, checkout my training course here https://exceleratorbi.com.au/product/power-query-excel-power-bi-online-training/

15 thoughts on “Quick Measures and Query by Example”

  1. The Running Total measure using ISONORAFTER is fantastic – Works on Text Columns like EMP_NAME also
    Split By Delimiter has as options called Split by Rows – is also fantastic

  2. Peter WIckwire

    The QUICK MEASURES are a fantastic feature, but unfortunately, all “calc measure” and “quick measure” features are useless if you are hitting an SSAS datasource. What we desperately need is the ability to add measures to the “report only” without adding them to the actual cube, so that these features can be accessed within a report running over an SSAS data connection.

  3. Ramana Varanasi

    Matt, how about you writing a book on Power BI?
    The way you explain cannot be replaced by any feature. True.
    Thanks

  4. What I’d really like to see is a function that allows a company to set a “fiscal year” period by month number, then have all DAX time measures respect that period.

    1. well you can certainly do that manually. I don’t think this will ever be a feature because there are so many different financial years for companies. It is easy to do with DAX.

  5. Dave Poppenhouse

    For someone that continues to struggle with DAX I think this Quick Measures is going to be very very helpful.

  6. Hi Matt

    This is going to be such a timesaver! Excellent addition to Power Bi Desktop. Also, it can be used as a great aid to learning DAX (other than your book of course)!

    1. Hi Matt, I was playing with this feature last night and discovered what I assume is a bug (could be wrong – might just be me), but I noticed the same issue on one of your screen shots above, so am interested to know if you hit the same problem.

      On the generated DAX code above, there is an extra “.[Date]” added to the end of the TOTALYTD statement. I got the same thing and only after removing this piece of code did the measure work for me. So, the statement says ‘Calendar'[Date].[Date] instead of ‘Calendar'[Date].

      Am I missing something here?

      Cheers

      Martin

      1. Yeah it’s not a bug – it is a nuance of the way Power BI is evolving. Power BI has a built in Date hierarchy tool and is uses this .[Date] extension to reference the natural levels of that hierarchy. I didn’t want to get into it on the blog (maybe a topic for another day). In short, if you use the Date column in your visual, it will build an “on the fly” hierarchy and you refer to those levels with the .[level] syntax. The great thing is you can edit this DAX formula to make it the way you want it (if you want to). So you don’t have to stick with what it gives you.

Leave a Comment

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

Scroll to Top