Power BI

Quick Measures in Power BI: how AI simplifies the creation of DAX formulas

In today’s guide, we will delve into Quick Measures, better known as Quick Measure, in Power BI and examine their enhancement through the integration of Copilot and Artificial Intelligence technologies. In the course of 2023, a new feature was indeed introduced that allows users to create measures with DAX using natural language. This feature, powered by AI, aims to simplify the process of creating the formulas used in measures, and thus proves to be a very useful tool for both beginners and experts who want to save time in creating dashboards.

In this article, we will explore the potential of this feature, highlighting its strengths and potential limitations.

What are Quick Measures and how do they differ from other measures?

Quick Measures are measures that follow a pre-built template to produce a very common calculation. To create a Quick Measure, simply drag the desired fields from the data panel to the template, just as if we were creating a visualization. Compared to other measures, the difference lies precisely in the method of creation. Quick Measures do not require the user to write lines of DAX code to be created. Apart from this aspect, the two types of measures are identical. In fact, once created, it is possible to select a Quick Measure and see how it was calculated through DAX.

Below we can see how the measure formula created through Quick Measures appears:

Advantages of Quick Measures

Using Quick Measures instead of manually writing DAX formulas can have its advantages. Among these are certainly ease of use. It is much simpler to drag the necessary fields into the panel and generate the measure we want, compared to writing the entire formula. A second advantage is that by relying on predefined templates, and therefore not having to write the formula, we have a lower chance of making errors in its definition. Obviously, the Quick Measures functionality has limits regarding the creation of measures with a high level of customization. In this case, however, they can still be useful if used as a basis for subsequent customization.

How to Create Measures Using Quick Measures in Power BI

To create a Quick Measure, you need to go to the “Home” section of the menu in Report View and click on “Quick Measures”.

Clicking on Quick Measures will open the panel that allows you to build the measure.

First, you need to choose the calculation you intend to make. By opening the dropdown menu “Select a calculation,” you will have a list of possible measures to create, divided by category.

For example, in this case, we decide to create a Running Total. In the “Base Value” box, we drag the Sales Amount field, while in the “Field” box, we insert the “DateKey” field, which is our date field. Finally, we choose the direction to create the Running Total. Next to each box, there is also an icon with an “i” that suggests how to choose the field to drag into the box.

Below, we see the DAX formula that was generated as soon as we clicked on Add:

We can also test the formula by using it in a table along with the “Year” and “Monthname” fields. The formula performs the calculation correctly:

The name that Power BI assigns to the measure created with Quick Measures is very long and often not very descriptive. We can change it at any time either from the measure pane where the DAX formula is present or by double-clicking on the measure in the data panel.

Creating Custom Functions with Copilot

As we’ve seen, the list of measures that can be selected from the dropdown menu is limited.
How can we use the Quick Measures feature to create measures beyond that list?

Artificial Intelligence comes to our rescue. Microsoft has decided to incorporate a feature that generates measures through AI directly in Power BI.

Before describing the features of this functionality, check that it is actually enabled within Power BI desktop. To do this, go to File > Options and Settings > Options > Preview Features and make sure that the “Quick Measures Suggestions” option is checked.

Once you’ve opened the Quick Measures panel, you may have noticed that next to the Calculations menu there is an option “Suggestions with Copilot“.

Clicking on this option will allow us to access the related functionality.

If clicking on “Suggestions with Copilot” does not grant you access to the functionality, you need to verify that you have enabled the feature in “Options and settings”. In this case, go to Preview features and enable “Quick Measures Suggestions” and “Improve Q&A with Copilot”.

If after completing this step, you are still unable to access the Copilot feature, then it probably needs to be enabled on Power BI Service as well.

Once the feature is activated, we can describe the measure we want to create within the window.

Let’s try, for example, to write “Sales in Europe”. As we type, Copilot will suggest fields we can use. Clicking on “Generate” will display the result of the calculation that has been performed and the developed DAX formula.

As shown in the image, Power BI shows us the suggested measure before creating it and adding it to the data panel. This way, we can verify that the result is what we expected and possibly try to describe in another way what we want to achieve, in case the first attempt was not successful.

In this case, the result is precisely what we expected, namely the sum of sales, considering only Europe as the continent. In this case, we will click on Add to add the measure to the data panel.

It’s important to note that the Copilot feature often applies the “Keepfilters” function in measures, even when not necessary. Depending on our needs, we can decide to remove it from the measure if unnecessary, just as we can make any other desired modification.

In the case just presented, we asked Copilot to create a relatively simple measure, and the result was correct. However, this feature starts to encounter some problems as soon as the difficulty of the measure increases. In these cases, the generated formulas turn out to be inaccurate or entirely wrong. What we can do to improve Copilot’s response is to describe more precisely the calculation we want to achieve, making sure to explicitly write if specific tables and measures must be used in the calculation.

Is Knowing DAX Still Necessary?

Despite Copilot’s ability to create measures upon request, knowing the DAX language remains absolutely necessary. First of all, Copilot does not always provide a correct result, and in these cases, it’s up to the user to understand how to correct the formula that has been created in DAX. Secondly, we often need to create very complex measures, which Copilot could hardly generate based on our description.

Conclusions

The Quick Measures feature in Power BI, along with the help of AI, can assist in creating new measures, especially for beginner users and in cases where the measure’s complexity is very low. Using this feature can speed up the measure creation process, replacing the manual writing of the formula.

However, the Copilot function is just at the beginning and still needs to improve. Therefore, it is not advisable to rely entirely on this function for creating measures, but it is necessary to know the DAX language and supervise the created measures, or correct them if necessary.

Learn the DAX Language

If you want to learn the DAX language to oversee the measures created or correct them, sign up for our course.

Visualitics Team
This article was written and edited by one of our consultants.

Share now on your social channels or via email:

Corso Vittorio Emanuele II 111, 10128 – Torino (TO)

info@visualitics.it
+39 011 19621 859

Services & Solutions

© 2024 P.IVA 11490480016 – REA: TO-1217259 – Cap. soc. € 10.000 i.v. – info@pec.visualitics.it

Privacy • Cookies