Power BI Quick Measures. Are you using them?

Hello!

In today's article I would like to talk about one of Power BI features that I often forgot to use.

Agenda

• Introduction
• Data sample
• Use Case 1
• Use Case 2
• Final thoughts

Introduction

Today I'm referring to quick measures. This is Power BI feature that has been around couple of years at least.

The idea behind quick measures is to provide insights how to build your DAX calculation. I believe it's something similar you can find in Microsoft Excel, when using "insert function" option.

There's at least one interesting use case, where quick measures can be used. I hope you will enjoy it!

Data sample

We got 3 tables:

• fact with sales - random data between 1 Jan 2023 and 31 Dec 2023
• dimension with countries
• calendar table - list of unique dates from 1 Jan 2023 to 31 Dec 2023

Here's fact:

Here's country dimension:

Calendar table:

And here is actual data model with 1 to many relationships:

Use Case 1

More than few times, I had a case where Client wanted to see filtered values from a slicer in form of a text.

For instance, for presentation purpose (taking screenshot of report page).

This is tricky, as we are moving away from Power BI and it's dynamics but we still want to see how data is filtered. Based on the country slicer, we want to achieve something like this:

Within the slicer we of course have selected 2 countries, Poland and Ukraine:

Before you keep reading. How would you solve this case? You can treat it as an excercise. Try to think of dax measure solving our scenario.

This is the perfect use of quick measures.

From modelling tab, you can select "quick measure" option:

Once you click it, you have a new panel opened with a set of measures to create:

In our case, we are looking for "concatenated list of values". Once we click it, we need to insert 2 parameters:

• field to based on
• number of values before truncation

In our example, I take country name field from dimension and put a number of 2:

Once I click "add", my measure is auto generated with DAX code, which is editable. Here you can change for instance limit for truncation, variable "__MAX_VALUES_TO_SHOW"

List of Country name values = 
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('dim_country'[Country name])
VAR __MAX_VALUES_TO_SHOW = 2
RETURN
	IF(
		__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
		CONCATENATE(
			CONCATENATEX(
				TOPN(
					__MAX_VALUES_TO_SHOW,
					VALUES('dim_country'[Country name]),
					'dim_country'[Country name],
					ASC
				),
				'dim_country'[Country name],
				", ",
				'dim_country'[Country name],
				ASC
			),
			", etc."
		),
		CONCATENATEX(
			VALUES('dim_country'[Country name]),
			'dim_country'[Country name],
			", ",
			'dim_country'[Country name],
			ASC
		)
	)        

This is the case, where I select more than 2 countries:

Of course this measure can be combined and edited for instance to add a slicer's name like "Countries selected: " and so on. Feel free to experiment on your own!

Use Case 2

This one is more of a standard thing, main reason behind quick measure will be to show you default validation step that Power BI is doing for us.

What we want is a simple YTD measure. As this is time intelligence certain rules needs to apply, such as date column with unique set of dates.

Let's see what will happen if I try to build YTD quick measure using date field from fact table.

I picked:

• quantity sold as a base value (in case it would be text column I would receive additional error in quick measure )
• fact date as a date to build YTD upon

As you can see on screen, Power BI is already telling me that there is something wrong and my measure would not work (if I would simply write DAX code by myself I would not have that error, just inaccurate results).

Let's use a proper date from calendar table then!

1st I need to mark my calendar table as "date table":

Usually there're more columns in calendar table with different formats. So we need to select our date column, which in our case is "date":

Now, if I drag my "date" column to quick measure, there is no error and I can proceed:

After I click "add", this is the measure that was created:

quantity sold YTD = 
TOTALYTD(SUM('fact_sales'[quantity sold]), 'Calendar table'[Date])        

Looks like a standard one in my opinion!

Final thoughts

I believe that quick meausures can help at some extend with specific tasks or with DAX learning. I started with learning Power Query in similar way. 1st I was clicking different buttons to transform data and then I was checking "advanced editor" to actually see how this code is being written.

This is my usual thinking process when I've a DAX challenge:

• I try to solve it on my own,
• Check on quick measures if there is something,
• if still nothing - google it / ask colleagues

Well that's it for today!

I hope you will have a great week and year ahead!

As always, feel free to put comments and questions below.