September 4, 2020
Implicit and Explicit Measures in Power BI
For Power BI version 2.76.5678.782 (December 2019).
Measures
Measure is a formula that tells Power BI what to do with data. Most often measures are aggregations, for example sum, average, count, etc. Power Bi knows implicit and explicit measures. We usually avoid implicit measures and try to use explicit measures whenever we can. Let’s look at characteristics of both.
Implicit measures
Power BI creates implicit measures by itself. Let’s look at a table with a filed named Price.
In field pane we see a sum icon next to Price name, meaning the field contains numerical data that will be aggregated to a sum. We create a Table visualization and drag Price field to Values.
Value we get is a sum of all the prices in the Price field. We can check to confirm this is a sum: we click the arrow by the name Price and see which of the aggregations is marked. In our case this is indeed a Sum.
In this place we can also change the measure to any of the other aggregations: average, maximum, minimum, standard deviation, etc.
Problem with implicit measures is we can’t use them in other measures. For example, if we want to add Price and Tax column, we have to use explicit measures.
Explicit measures
Whenever possible, we use explicit measures. Their advantage is we can use them as a reference in other measures. We also can’t change them, unless we change the formula of the measure.
Let’s look at explicit measure Price_EXPLICIT, that does the same as implicit measure Price.
1. Right click to field pane and select New Measure.
2. Enter Price_EXPLICIT = SUM(Sales[Price]) to formula bar, where Sales is a table name and Price is a field name.
3. Confirm with Enter.
We created explicit measure. We add it to the table from a previous example. We see they give the same result.
Using explicit measure in another measure
Biggest advantage of explicit measures is the fact we can use them in other measure and build complex formulas. Let’s look at a simple example of adding two columns Price and Tax.
1. Right click and select New Measure.
2. Enter Tax_EXPLICIT = SUM(Sales[Tax]) to formula bar, where Sales is the name of the table and Tax is the field name.
3. Confirm with Enter.
4. Right click and select New Measure.
5. Enter TotalPrice = [Price_EXPLICIT] + [Tax_EXPLICT] to formula bar.
6. Confirm with Enter.
We created TotalPrice measure, that adds two other measures. We can insert all measure into a table and check the results.
Measures can be very complex. They enable us to use custom slicers and other skillful tools. Example of a custom slicer can be found in Monthly and YTD filter in one measure article.