Measures in Power BI

Measures are dynamic calculations (written in DAX) that aggregate data at query time based on the current filter context. They are essential for metrics like sums, averages, or ratios that change with user interactions (e.g., slicers, visuals).

We can write a DAX formula to add a measure to any table in our model. A measure formula must return a scalar or single value. Similar to a calculated column, the formula must return a single value. Unlike calculated columns, which are evaluated at data refresh time, measures are evaluated at query time. Their results are never stored in the model.

Note: • We can create measures on calculated columns also.
• Measures do not create columns in the table.
• Measures (or measure expressions) are always evaluated in filter context.

How Filter Context Works Filter context is the set of active filters applied to your data model when a measure is calculated. Sources include:
• Visual-level filters (e.g., rows/columns in a table visual).
• Slicers, page/report filters.
• Model relationships (propagation of filters across tables).

Let’s we have the following Countries table.

Measures in Microsoft Power BI models are either implicit or explicit.
• Implicit measures are automatic behaviors that allow visuals to summarize model column data.
• Explicit measures, also known simply as measures, are calculations that we can add to our model.

Also, measures can reference other measures directly. It's known as a compound measure.

Let’s create a measure in Power BI. We will add a measure to the Countries table. In the Fields pane, select the Countries table. Click on New measure in the Home menu.

Use the following DAX expression to create the measure.

DAX

GDP Measure = SUM(Countries[GDP (Billion USD)])

The output of the above code is shown below:

On the Measure tools menu, inside the Formatting group, we can set the decimal places and its format. Also, we can specify the Name of the measure and specify the ‘Home’ table for the measure.

We can see that the direct column addition and explicit measure creation both gives us the same result.

The following two visuals use the exact same DAX measure: GDP Measure.

Though each visual uses the same DAX measure and, therefore, the same DAX formula, the visuals produce different results. For instance, the first visual shows the GDP Measure is broken down by Country, and in the second visual, GDP Measure is broken down by year. For instance, in 2022, GDP Measure is $51,700.

With Power BI, even though the measure was only defined once, it can be used in these visuals in different ways. Each of the totals is accurate and performs quickly. It is the context of how the DAX measure is used that calculates these totals accurately.

Understanding Filter Context Filter context is a core concept in DAX that defines the subset of data available for measure calculations at any given moment. It is the set of filters applied to the data model, influenced by various elements within a Power BI report, including:

Report Filters: Applied at the report level, affecting all pages and visuals.
Page Filters: Specific to a report page, narrowing data for visuals on that page.
Visual Filters: Applied directly to a visual, such as a table or chart, to refine its data.
Slicers: Interactive filters users can manipulate, often displayed as dropdowns or lists.
Rows and Columns in Visuals: In table or matrix visuals, the values in rows and columns act as implicit filters for measures.

And more can affect how a DAX formula is calculated and displayed.

Let’s modify the measure.

DAX

GDP Measure = IF( HASONEVALUE('Countries'[Country]), SUM(Countries[GDP (Billion USD)]) )

The HASONEVALUE() function tests whether a single value in the Country column is filtered. When true, the expression returns the sum of GDP column (for just that country). When false, BLANK is returned. Notice that the GDP Measure column total is now BLANK. The output of the above code is shown below:

How Measure interacts as a filter on other visuals Let’s discuss on the things that how a measure interacts with other visuals and how filter applies on that. Let’s we have a Sales table. Add a Clustered column chart on the page, add the fields Profit, Sales and a measure “Electronics Sales Measure”.

The definition of measure “Electronics Sales Measure” is shown below:

DAX

Electronics Sales Measure = CALCULATE(SUM(Sales[Sales]), Sales[Category]="Electronics")

Also, one table visual is created with fields Date, Category and Profit.

In the image below, when we have selected the Electronics Sales Measure of the September month, we have seen that the table visual is filtered on the Month not based on Electronics Category as shown in the table visual. So, we can say that the other visual are not filtered based on the interaction with measure like the way as shown below, instead of that it fileted based on categorical value in the Y-axis i.e. Month.

Create compound measures We can create compound measures in Power BI.

Let’s we have one measure named Electronics Quantity Measure.

DAX

Electronics Quantity Measure = CALCULATE(SUM(Sales[Quantity]), Sales[Category]="Electronics")

Let’s we have another measure named Grocery Quantity Measure.

DAX

Grocery Quantity Measure = CALCULATE(SUM(Sales[Quantity]), Sales[Category]="Grocery")

We can create a compound measure by using the two or more measures.

DAX

Total Measure = [Electronics Quantity Measure] +[Grocery Quantity Measure]

The output of the above code is shown below:

Previous Next