Explicit Vs Implicit DAX Measures in Power BI

Explicit vs implicit DAX measures in Power BI

Have you ever tried to connect to a Power BI dataset from Excel using Analyze in Excel? if yes, you might have had this experience that your values don’t show in Excel’s Pivot table as values. This is because those values are not explicit measures, they are implicit measures in Power BI. In this article and video, I’ll explain the difference between these two types of measures in Power BI and the impact on the user.


Sample model

I have a simple dataset with two tables; DimCustomer and FactInternetSales, they are connected to each other based on CustomerKey, which means the DimCustomer can filter the FactInternetSales.

Sample Power BI data model

Sum of Sales: Explicit Measure

If I want to show the sum of sales in my report, I can create a measure with a simple calculation below;

Sales measure defined explicitly
Sales = SUM(FactInternetSales[SalesAmount])

The measure will show the sum of sales aggregated by whatever filtering the FactInternetSales table. In the below, I have the Sales measure filtered by the EnglishEduction field from DimCustomer.

Sales measure displayed in a visual

Whenever you create a measure yourself in Power BI, that is called Explicit measure.

Sum of Sales: Implicit Measure

The measure above shows the sales value, however, there is another and more popular way of getting the sales value. That is by dragging and dropping the SalesAmount column (from the FactInternetSales table) directly into the report’s visual.

adding implicit measure by drag and drop

If you notice, you will see a Sigma (or sum) icon beside the SalesAmount column. This is an icon you will see beside every numeric column (not columns participating in relationships) that are by default set in Power BI as auto-summarizable. This means whenever you visualize that column in the Power BI, Power BI shows you the aggregation (the summarization in this case) instead of individual values.

auto summarization

If Power BI didn’t summarize the values of SalesAmount, then we would have seen below: the list of values in the SalesAmount column;

disabling the auto-summarization will show the list of values

The auto-summarization that Power BI automatically applies on the numeric columns is called an Implicit Measure. An implicit measure is not only in the form of Sum. you can change that in the visual by changing the aggregation;

Choosing other aggregations in the implicit measure

Or you can change the aggregation at the field level so that it will be used with that default aggregation in all visuals, For example, for something like Rating, you might use Average.

changing the default summarization at the field-level

Still, the above measures are called Implicit measures.

Quick measures are also Explicit measures

Although, you don’t really write the DAX expression when you create a quick measure, however, it is still considered as an explicit measure.

Quick measure is an explicit measure

The difference between Explicit and Implicit measures

So, in fact, there are two ways to achieve the sum of sales; using an implicit measure created by Power BI automatically, or writing an explicit measure. What is their difference then? let’s explore.

Implicit Vs Explicit measures


I get this guess at first whenever I explain about the explicit and implicit measures; “Are they different in their performance?” The answer is No. Because Power BI is just creating a calculation very similar to what you might have had written. For the SalesAmount, it would be Sum(SalesAmount) when Power BI creates that automatic summarization. So it is not different at all in the performance.


Writing an explicit measure will give you more flexibility of course. You can use DAX functions such as Calculate, filter functions, or whatever you want. Using the implicit measures, you are limited to the aggregation listed in the Power BI Desktop.

Analyze in Excel

The main difference between explicit and implicit is when you use the measure in Excel. Power BI enables you to connect to the model published in the Power BI Service using Excel. This feature is called Analyze in Excel and I explained it here. Analyze in Excel is a much better way to connect to the data model and extract information than exporting data because Analyze in Excel has a live connection to the Power BI dataset. I explained about it here:

I can connect to a Power BI dataset using Analyze in Excel from the Power BI service.

Analyze in Excel from the Power BI Service

I cannot use any implicit measures in the values section of my pivot table or pivot chart in Excel.

Implicit measures cannot be used as the value

But explicit measures are absolutely fine;

Explicit measures can be used as value in the Pivot table

This is a big difference between the two types of measures from Excel’s point of view. Excel doesn’t understand the implicit measure created automatically by Power BI.

If your users are not using Analyze in Excel feature at all, then you can just continue using implicit measures in Power BI whenever you can. However, if your users are Excel users, then the usage of explicit measures will help them in analyzing that data in excel later on.

Consider hiding the column

If you are creating simple explicit measures for your numeric columns (Such as Sum(SalesAmount)), then I strongly recommend hiding the actual column, because having both the column and the measure would make users (and sometimes even yourself) confused.

Also; a quick tip is that if you hide all the columns from your table and only measures are visible, your table will show the icon of a measure group. You can see that in the FactInternetSales below.

Hide the column


There is no performance difference between the implicit and explicit measures. However, you will have better flexibility with explicit measures (because you can write whatever you want in DAX). The main advantage of explicit measures is they can be used as a value in Excel’s pivot table when connected using Analyze in Excel. This is a big advantage because there are many Excel users who will gain the benefit from analyzing the Power BI dataset.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply