Implicit versus explicit measures

[WorkHard]

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?

[parry2k · Accepted solution]

@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.⚡

[edhans · Accepted solution]

A few reasons:

1. You cannot control what an implicit measure does beyond the basics. Sum, Count, etc.
2. 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:
   1. Sales = SUM(Table[Sales])
   2. COGS = SUM(Table[COGS])
   3. Margin = [Sales] - [COGS]
   4. Now you are reusing explicit measures in step #3. If you had not created those, you would have to write this:
   5. Margin = SUM(Table[Sales]) - SUM(Table[COGS])
   6. 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.
   7. Margin = CALCULATE(SUM(Table[Sales])) - CALCULATE(SUM(Table[COGS]))
3. 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.

[edhans · Accepted solution]

A few reasons:

1. You cannot control what an implicit measure does beyond the basics. Sum, Count, etc.
2. 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:
   1. Sales = SUM(Table[Sales])
   2. COGS = SUM(Table[COGS])
   3. Margin = [Sales] - [COGS]
   4. Now you are reusing explicit measures in step #3. If you had not created those, you would have to write this:
   5. Margin = SUM(Table[Sales]) - SUM(Table[COGS])
   6. 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.
   7. Margin = CALCULATE(SUM(Table[Sales])) - CALCULATE(SUM(Table[COGS]))
3. 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.

[WorkHard]

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?

[edhans · Accepted solution]

#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
)

[WorkHard]

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!.

[parry2k]

@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.

[edhans]

@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. 

[parry2k]

@edhans @I never said you are advocating it, I was just sharing my thoughts. Thanks for the extra info you shared. 

[parry2k · Accepted solution]

@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.⚡