Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Implicit versus explicit measures

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Implicit versus explicit measures

07-01-2020
02:43 PM

I'm reading and watching tutorials and a lot of people mention that you should avoid using the implicit measures that Power BI conveniently lets you use, like this:

No one is mentioning why though. Is this something that people just repeat or is this a convenience thing? I understand that an explicit measure can be reused in other parts of reports but if I start creating a measure for every single count card I have, I'll have a sea of measures.

Someone said:

*"When you design any Power BI report, all measures should be explicit, i.e., you shouldn't allow your end users to create implicit measures because it enables them to do things they shouldn't, e.g., Sum of Unit Price."*

What's wrong with the sum of unit price?

Solved! Go to Solution.

3 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020
04:07 PM

@WorkHard the one reason could be to keep the logic at one place and not to do the same calcuation again and again. for example

if you want to calculate sum of previous date and previous year, you will add two measures like this

```
Prev Day Sum = CALCULATE ( SUM ( Table[Amount] ), PREVIOUSDAY ( DateTable[Date] ) )
Prev Year Sum = CALCULATE ( SUM ( Table[Amount] ), PREVIOUSYYEAR ( DateTable[Date] ) )
or you can create explicit measure for sum and then use it
Base Measure = SUM ( Table[Amount] )
Prev Day Sum = CALCULATE ( [Base Measure], PREVIOUSDAY ( DateTable[Date] ) )
Prev Year Sum = CALCULATE ( [Base Measure], PREVIOUSYYEAR ( DateTable[Date] ) )
```

in this example, if any logic is changed to the base measure it will automatically change dependent measures but in 1st case, you have to change it manually, in most complex models/calculations it becomes really handy and easy to work with. BTW, the above measure example was super simple but just to showcase why it is useful.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

Proud to be a Super User! Appreciate your * Kudos* 🙂

Feel free to email me with any of your BI needs.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020
04:38 PM

A few reasons:

- You cannot control what an implicit measure does beyond the basics. Sum, Count, etc.
- You cannot reuse an implict measure. Say you want to have a Sales measure, a COGS measure, and a Margin measure. If you write explicit measures as follows:
- Sales = SUM(Table[Sales])
- COGS = SUM(Table[COGS])
- Margin = [Sales] - [COGS]
- Now you are reusing explicit measures in step #3. If you had not created those, you would have to write this:
- Margin = SUM(Table[Sales]) - SUM(Table[COGS])
- Believe it or not, #3 and #5
*are not the same thing*. #3 wraps in implicit calculate around each measure, #5 does not, so to be accurate, #5 has to be #7 to produce intended results. - Margin = CALCULATE(SUM(Table[Sales])) - CALCULATE(SUM(Table[COGS]))

- If you go to the trouble to add conditional formatting or other field settings, like a custom number format with an implicit measure then you decide you want to tweak the formula for that measure, you cannot. You have to now create an explicit measure with your correct formula (Say you want to change a budget number to budget + 10% and round to zero places- very simple in a measure - ROUND(SUM(Table[Budget) * 1.1,0). Then you have to remove the implicit measure, and add the explicit measure. Now you have to redo all of the conditional formatting and other field level custom formatting that the visual allows.

There are other reasons, but those hit the highlights.

And one caveat: if you want to use the new Personal Visuals setting, actually *implict *measures are better as it allows the end consumer to make changes easier. However, I would specifically do this for only those visuals but not for the model itself, and that is still not a reason to use them in general. I would call this an edge case.

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-16-2020
09:44 AM

#1 and #3 are the same thing.

#2 is not.

Often they will produce the same results in a simple model, but #1/3 go through context transition because CALCULATE (both implicit and explicit) trigger it. You can read more about it here.

Where this becomes a big deal is in their last example:

```
DEFINE
MEASURE Product[TotalSales] = SUM ( Sales[Quantity] )
VAR SalesOfAllProducts = [TotalSales]
EVALUATE
FILTER (
ADDCOLUMNS (
VALUES ( Product[Product Code] ),
"SalesOfProduct", [TotalSales] <---- This line
),
[SalesOfProduct] >= SalesOfAllProducts * 0.01
)
```

It is not the same as this:

```
DEFINE
MEASURE Product[TotalSales] = SUM ( Sales[Quantity] )
VAR SalesOfAllProducts = [TotalSales]
EVALUATE
FILTER (
ADDCOLUMNS (
VALUES ( Product[Product Code] ),
"SalesOfProduct", SUM(Sales[Quantity])
),
[SalesOfProduct] >= SalesOfAllProducts * 0.01
)
```

BUt is the same as this:

```
DEFINE
MEASURE Product[TotalSales] = SUM ( Sales[Quantity] )
VAR SalesOfAllProducts = [TotalSales]
EVALUATE
FILTER (
ADDCOLUMNS (
VALUES ( Product[Product Code] ),
"SalesOfProduct", CALCULATE(SUM(Sales[Quantity]))
),
[SalesOfProduct] >= SalesOfAllProducts * 0.01
)
```

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020
04:38 PM

A few reasons:

- You cannot control what an implicit measure does beyond the basics. Sum, Count, etc.
- You cannot reuse an implict measure. Say you want to have a Sales measure, a COGS measure, and a Margin measure. If you write explicit measures as follows:
- Sales = SUM(Table[Sales])
- COGS = SUM(Table[COGS])
- Margin = [Sales] - [COGS]
- Now you are reusing explicit measures in step #3. If you had not created those, you would have to write this:
- Margin = SUM(Table[Sales]) - SUM(Table[COGS])
- Believe it or not, #3 and #5
*are not the same thing*. #3 wraps in implicit calculate around each measure, #5 does not, so to be accurate, #5 has to be #7 to produce intended results. - Margin = CALCULATE(SUM(Table[Sales])) - CALCULATE(SUM(Table[COGS]))

- If you go to the trouble to add conditional formatting or other field settings, like a custom number format with an implicit measure then you decide you want to tweak the formula for that measure, you cannot. You have to now create an explicit measure with your correct formula (Say you want to change a budget number to budget + 10% and round to zero places- very simple in a measure - ROUND(SUM(Table[Budget) * 1.1,0). Then you have to remove the implicit measure, and add the explicit measure. Now you have to redo all of the conditional formatting and other field level custom formatting that the visual allows.

There are other reasons, but those hit the highlights.

And one caveat: if you want to use the new Personal Visuals setting, actually *implict *measures are better as it allows the end consumer to make changes easier. However, I would specifically do this for only those visuals but not for the model itself, and that is still not a reason to use them in general. I would call this an edge case.

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-16-2020
08:58 AM

Appreciate the knowledge, do you mind explaining a bit more about the difference in these 3? To me, they look like they will all produce the same result.

Say: Sales = 100 and COGS = 30

1. Margin = [Sales] - [COGS] = 70?

2. Margin = SUM(Table[Sales]) - SUM(Table[COGS]) = 70?

3. Margin = CALCULATE(SUM(Table[Sales])) - CALCULATE(SUM(Table[COGS])) = 70?

Where can I read more about this?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-16-2020
09:44 AM

#1 and #3 are the same thing.

#2 is not.

Often they will produce the same results in a simple model, but #1/3 go through context transition because CALCULATE (both implicit and explicit) trigger it. You can read more about it here.

Where this becomes a big deal is in their last example:

```
DEFINE
MEASURE Product[TotalSales] = SUM ( Sales[Quantity] )
VAR SalesOfAllProducts = [TotalSales]
EVALUATE
FILTER (
ADDCOLUMNS (
VALUES ( Product[Product Code] ),
"SalesOfProduct", [TotalSales] <---- This line
),
[SalesOfProduct] >= SalesOfAllProducts * 0.01
)
```

It is not the same as this:

```
DEFINE
MEASURE Product[TotalSales] = SUM ( Sales[Quantity] )
VAR SalesOfAllProducts = [TotalSales]
EVALUATE
FILTER (
ADDCOLUMNS (
VALUES ( Product[Product Code] ),
"SalesOfProduct", SUM(Sales[Quantity])
),
[SalesOfProduct] >= SalesOfAllProducts * 0.01
)
```

BUt is the same as this:

```
DEFINE
MEASURE Product[TotalSales] = SUM ( Sales[Quantity] )
VAR SalesOfAllProducts = [TotalSales]
EVALUATE
FILTER (
ADDCOLUMNS (
VALUES ( Product[Product Code] ),
"SalesOfProduct", CALCULATE(SUM(Sales[Quantity]))
),
[SalesOfProduct] >= SalesOfAllProducts * 0.01
)
```

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-21-2020
11:19 AM

Thank you! This makes total sense now. Yes, in that example you gave it will calculate the SUM in "that" context which might be different than the SUM that would be calculated outside that context/filter.

Thank you for the lesson!.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020
05:42 PM

@edhans great detail but I do not agree with following

*And one caveat: if you want to use the new Personal Visuals setting, actually implict measures are better as it allows the end consumer to make changes easier. However, I would specifically do this for only those visuals but not for the model itself, and that is still not a reason to use them in general. I would call this an edge case.*

If there is a business logic need to deploy in the measures, end-user shouldn't work outside that logic and create visuals using their own calculations/measure that leads to some discrepancy, and then we all know what happens. I would let end users only use explicit measures. Just my 2 cents.

Proud to be a Super User! Appreciate your * Kudos* 🙂

Feel free to email me with any of your BI needs.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020
06:29 PM

@parry2k - I am not advocating the use of implict measures, but they do make the Personalized Visual feature more powerful for the end user if they want to tinker around and explore the data. See this article.

The key bit of info:

It is like everything else in Power BI. There isn't often a right/wrong answer, just a better/worse answer, with each side having some pros and cons.

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2020
04:07 PM

@WorkHard the one reason could be to keep the logic at one place and not to do the same calcuation again and again. for example

if you want to calculate sum of previous date and previous year, you will add two measures like this

```
Prev Day Sum = CALCULATE ( SUM ( Table[Amount] ), PREVIOUSDAY ( DateTable[Date] ) )
Prev Year Sum = CALCULATE ( SUM ( Table[Amount] ), PREVIOUSYYEAR ( DateTable[Date] ) )
or you can create explicit measure for sum and then use it
Base Measure = SUM ( Table[Amount] )
Prev Day Sum = CALCULATE ( [Base Measure], PREVIOUSDAY ( DateTable[Date] ) )
Prev Year Sum = CALCULATE ( [Base Measure], PREVIOUSYYEAR ( DateTable[Date] ) )
```

in this example, if any logic is changed to the base measure it will automatically change dependent measures but in 1st case, you have to change it manually, in most complex models/calculations it becomes really handy and easy to work with. BTW, the above measure example was super simple but just to showcase why it is useful.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

Proud to be a Super User! Appreciate your * Kudos* 🙂

Feel free to email me with any of your BI needs.

Announcements

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Featured Topics