Locked lesson.
About this lesson
Quick measures are ready-rolled formula patterns built through a drag and drop user interface, allowing you to write really complicated formulas for you without having to learn all the nuances of Power BI's DAX formula language.
Exercise files
Download this lesson’s related exercise files. You can download the source data files for the course from the resources section of your Lessons page.
Quick Measures.pbix450.7 KB Quick Measures - Completed.pbix
451.5 KB
Quick reference
Quick Measures
Using quick measures for time intelligence.
When to use
Quick measures are handy patterns that you can use to quickly create complex DAX patterns that you may not be able to write on your own.
Instructions
Before you create a quick measure
- Create a basic measure to aggregate your data (SUM, COUNT, etc…)
Creating quick measures
- Right click your measure in the Fields window -> Quick Measures
- Choose your measure pattern
- Configure as desired
Example: Year to date Sales based on the Sales[Amount] column
- Create a basic measure: Sales $= SUM(Sales[Amount])
- Right click the [Sales $] measure -> Quick Measures
- Select Time Intelligence -> Year-to-date total
- Set the Base Value to [Sales $]
- Set the Date to Calendar[Date]
- Use the measure on a visual
Hints & tips
- Always base your quick measures off explicit measures
- Once created, Quick Measures can be edited like any other measure
- Rename by right clicking the measure in the Field list -> Rename
- Change formats by selecting the measure in the Field list -> Modeling -> change the formatting
- If you don’t see Quick Measures, go to File -> Options -> Preview Features to turn it on
- 00:05 We're now gonna jump into something kinda cool called Quick Measures.
- 00:09 But before we can do that,
- 00:11 I need to show you how to activate preview features inside Power BI Desktop.
- 00:15 Now, just to be clear on this one,
- 00:18 the reason why I need to show you how to activate preview filters is
- 00:21 because at the time of shooting, this particular feature is in preview.
- 00:26 If it's several months since the course has been released, you may find
- 00:29 that this is actually out in general population and there's no need to do this.
- 00:32 But it's still a good exercise to see what we're gonna do here.
- 00:35 Because you'll be able to see where there's other preview features as this
- 00:38 product is being rapidly developed over time here.
- 00:42 So to find preview features, what we're gonna do is we're gonna
- 00:45 go to the File menu, and we're gonna go to our Options and Settings.
- 00:49 And we're gonna go to Options.
- 00:52 And inside the Options window, you'll notice down on the left-hand side,
- 00:56 there is something called Preview features.
- 00:59 When you find this, you'll notice that right now, I have a bunch of them in here.
- 01:03 The one that I'm focused on is Quick measures, and I'm now gonna go and say OK.
- 01:08 At which point, I'll be told that I have to restart Power BI Desktop.
- 01:13 So that's fine, we'll say OK, and then we'll close and reload Power BI Desktop.
- 01:19 All right, we're back in Power BI Desktop, the quick measures feature has been added.
- 01:23 So what I'm gonna do now, rather than confuse things,
- 01:25 is I'm gonna grab the chart that I have here, hit Ctrl+C to copy it.
- 01:30 I'm gonna go drop it on to a new page by pressing Ctrl+V.
- 01:34 And I think I'm just gonna go and pull the budgets off of this one.
- 01:39 Just to try and get back to our sales number for sales by the end of month.
- 01:43 Because this is a chart that we might actually want to go and
- 01:46 add something to, like maybe some date, time, and totals.
- 01:49 Wouldn't it be kinda neat to have a year-to-date totals
- 01:53 showing on our chart as well.
- 01:55 So, to do that, this is where the quick measures functions actually come in.
- 02:00 So, I can get to quick measures a couple of different ways.
- 02:02 I can right-click on a field in the values area, and say Quick measures.
- 02:06 Or I can pick it up from the field that's here.
- 02:08 Either way, it's the same entry point.
- 02:10 I'm gonna right-click and say Quick measures, and it brings up this dialog.
- 02:13 And it says, all right, select a calculation.
- 02:16 So I'm gonna go and take a look, what looks interesting?
- 02:18 Hey, Time intelligence, Year-to-date total, that looks cool.
- 02:22 So we're gonna grab that one.
- 02:24 Now it asks us, what's the base value that you would like to actually base this on?
- 02:28 And it pulls all of this stuff in from the visual that we've already created.
- 02:31 Are we stuck with this?
- 02:33 No, not at all.
- 02:33 We could x this out and it will say, hey, you've gotta give me a field here.
- 02:37 So I could go to the sales table.
- 02:40 I could grab Sales and drag it in there, or, now sales, by the way,
- 02:44 is an explicit measure cuz it has got the calculator icon.
- 02:47 I don't have to use an explicit measure.
- 02:49 I could grab something else and drag it in and
- 02:51 you'll see that it gives me a sum all of unit price or something like that.
- 02:55 I'm not really a big fan of this because this actually gives us the sum of,
- 02:58 that may not be what I want.
- 02:59 So I would always encourage you to build an explicit measure first so
- 03:02 that you can know what you're actually aggregating.
- 03:06 The second thing is it says, tell me what your date field is.
- 03:09 Well, it's End of Month, no problem, so I'll say OK.
- 03:14 And what it does, it creates me a new measure.
- 03:16 Here's the docs pattern for it and it tells me that it can't display the visual.
- 03:20 And if I click See details, I'm gonna get something that has this beautiful little
- 03:24 thing about an MDX script model error that nobody really understands.
- 03:27 But you'll notice that the text here is the same as the text in red.
- 03:31 And it says the time intelligence quick measure can only be grouped or
- 03:33 filtered by the Power BI-provided date hierarchy.
- 03:36 You go, what the heck is that?
- 03:37 I mean we haven't even talked about hierarchies at all.
- 03:39 Well, here's what's gonna happen.
- 03:41 We're gonna go down and
- 03:42 we're gonna actually delete this new measure that was created for us.
- 03:46 We're gonna get rid of this altogether.
- 03:48 Because that has a specific pattern in it that's key to a field that won't work, and
- 03:53 we're also gonna remove it from our visual.
- 03:55 The challenge is that we tried to build a space on the end of month field, and
- 03:58 that's not gonna work.
- 03:59 We need to replace that with something that Power BI uses,
- 04:02 which is called the date hierarchy.
- 04:04 So we're gonna grab our plain old date field.
- 04:06 Remember, this is the calendar table that has that repeating list of unique dates.
- 04:10 I'm gonna put that in place on end of month.
- 04:13 And we're gonna remove the end of month piece.
- 04:15 Now this gives me the years 2015, 2016 as a hierarchy.
- 04:18 And I'm gonna talk about this more in the next module.
- 04:21 The key part that I want you to recognize now though is that if we go and
- 04:25 right-click and say Quick measures, and we create this measure again,
- 04:29 we'll start a calculation.
- 04:31 We'll go with Year-to-date total, we use Sales.
- 04:34 But this time,
- 04:35 we're using Date as our primary key from our calendar table, and we say OK.
- 04:41 We're gonna see that we get something new added.
- 04:43 It creates the measure for us, that's all there.
- 04:46 You can always edit it if you want to, but
- 04:48 it gives us a visual that shows us our sales and our year-to-date sales.
- 04:52 And of course at the top level of year,
- 04:54 you would expect that they're kind of the same.
- 04:56 Now I'm gonna show you what happens if we actually go and
- 04:59 we expand down a level of the hierarchy.
- 05:01 And we'll talk more about this a little bit later in the next video and
- 05:04 what this means.
- 05:04 But you'll notice that Q1, we have sales of 272,000.
- 05:08 We have sales of 853,000 in Q2.
- 05:11 But if I look up the year-to-date, the sales are 1.1 million.
- 05:14 So the darker bars are giving this year-to-date.
- 05:17 And then, when we move into the next month, you can see that it resets.
- 05:21 If I drill down one more level, you can see that we have our nice little cyclical
- 05:25 set of sales going on here, as well as the ever increasing year-to-date numbers that
- 05:29 reset at the beginning of the year.
- 05:32 Quick measures is a really cool feature that Power BI team is adding new things to
- 05:36 all the time in order to make it easier to build really complex DAX narrow.
- 05:40 So it's well worth checking out but
- 05:42 always review what you get to make sure that's really what you want.
Lesson notes are only available for subscribers.